Sunday, August 28, 2005

Encrypt and decrypt data in SQL Server 2005

In this article we would examine how to encrypt a column data in SQL Server 2005. Needless to say we would also look into the decryption part. The complete source code is also provided below for your better understanding.

Script for creating an EmployeeInfo table

Create table tblEmployeeInfo
(
EmpId int primary key,
Firstname varchar(50),
Age varbinary(300), -- this is the field we would encrypt while storing dataEmailID varchar(50)
)

Generate a key to protect the AGE of the employee:

Create symmetric key symKeyemployees
with algorithm = TRIPLE_DES
encryption by password = 'smart3a?'

Other encryption algorithm which we could use instead of TRIPLE_DES are:

1. DES,
2. RC2,
3. RC4,
4. DESX,
5. AES_128,
6. AES_192 and
7. AES_256

Decrypt the key and makes it available for use:

Open symmetric key symKeyemployees using password = 'smart3a?'

In order to know whether the key has been opened or not query sys.openkeys table as follows:

Select * from sys.openkeys

Result of running the above query in our case is as follows:

database_id = 8
database_name = AdventureWorks
key_id = 256
key_name = symKeyemployees
key_guid = A383AB00-E692-49EE-9252-E6AD4371F865
opened_date = 2005-08-27 09:52:07.693
status = 1

Since the ground work has been done, let us now insert some sample data into the table.

Insert into tblEmployeeInfo values ( 1, 'Vadivel', encryptbykey(key_guid('symKeyemployees'), '29', 1, '1'), 'vmvadivel@yahoo.com')

The 3rd parameter of encryptbykey is an integer which helps us to specify whether we are going to use an authenticator value or not.

Value 1 means = an authenticator value would be supplied.
Value 0 means = an authenticator value would not be supplied. By the way, this is the Default.

The next parameter is the actual authenticator data. In our case it is '1' because we are using EmpID as our authenticator value. FYI, default is NULL.

Query the table to find that the value of AGE field is encrypted.

Select * from tblEmployeeInfo

Result of running the above query would be something like this (Note that I have formatted the output for better understanding):

EmpID = 1
Firstname = Vadivel

Age (Encrypted value) = x00AB83A392E6EE499252E6AD4371F86533996F7339B9E43B8D6477142A785FB6292
D0683C5ABFA06734429C37BBDFB43C80A4EAA458678E328F4250A24AAEC74

EmailID = vmvadivel@yahoo.com

Decrypt the data:

Select EmpId, Firstname, emailid,
convert(varchar(10), decryptbykey(Age, 1, convert(varchar(30), EmpId))) as Age
from tblEmployeeInfo

Close the symmetric Key:

Close symmetric key symKeyemployees

Run the below script to clean up:

Drop table tblEmployeeInfo
Drop symmetric key symKeyemployees

Friday, August 26, 2005

Basics of DDL Triggers in SQL Server 2005

This article would explain in detail about the new feature "DDL triggers" in SQL Server 2005. I have explained it with an example whose complete code snippet is also attached below.

Till SQL Server 2000 one can create DML triggers only. i.e., triggers which would fire on Insert, Update or Delete of a table or a view.

In SQL Server 2005 (Yukon) we can create triggers for DDL (Data definition Language) statements. i.e., we can write triggers which would react to CREATE, ALTER & DROP statements as well.

Syntax for creating DDL statements:

Create Trigger trigger_name
ON { ALL SERVER DATABASE }
[ WITH ENCRYPTION ]
{ FOR AFTER } { event_type event_group } [ ,...n ]
AS { sql_statement [ ...n ]
EXTERNAL NAME <> }
[ ; ]
::=
assembly_name.class_name[.method_name]

Major points which I thought might interest you are:

i) The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE).

ii) Event_type argument is used to define the event for which the trigger would fire.

iii) Inside a trigger, you can access information related to the event by calling EventData() function. EventData() function would return a value of type XML. The base XML schema returned by the eventdata() function depends on the scope and event type.

iv) On Database: Applies the scope of a DDL trigger to the current database. If specified, the trigger fires whenever event_type or event_group happens in the current database.

v) All Server : Applies the scope of a DDL trigger to the current server. If specified, the trigger fires whenever event_type or event_group happens anywhere in the current server.

Lets create a sample trigger which would fire when ever an "Alter table" statement is executed in our database.

Script to Create a sample table

Create table test
(
sno int identity,
firstname varchar(25)
)
Go

Code snippet to create a sample DDL trigger

Create trigger trgAlterTableForDatabase
On database
For alter_table As
Select eventdata()
Print 'You are not authorized to execute Alter Table statement in this database.'
Rollback
Go

Try and alter the table structure

Alter table test alter column firstname varchar(50)

Result:

On executing the above "Alter table" statement we would be welcomed with the below error message.

(1 row(s) affected)
You are not authorized to execute Alter Table statement in this database.
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

As said earlier eventdata() would return a XML string and in our case it would like this:


ALTER_TABLE
2005-08-26T07:46:48.880
58
VadivelDB
VadivelDB\Administrator
dbo
AdventureWorks
dbo
test
TABLE


Alter table test alter column firstname varchar(50)



Clean up

drop trigger trgAlterTableForDatabase on database
drop table test

Creating a DML trigger using CLR in SQL Server 2005

In this article, we will take a look at the new CLR integration feature and learn how to create triggers in SQL Server using a managed code. We would create a simple DML delete trigger for a table and access the deleted value from our C# code.

Visual Studio .NET 2005

1. Open a C# -- Database project
2. Create a new trigger file into it.
3. The .NET class that will implement the functionalities of the trigger is given below just copy-paste it into the new file.

GetTriggerContext() :: Get reference to the current trigger context by invoking this method of the SqlContext class. This help us in fetching the data from the virtual table which would be created during a execution of a trigger.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// [SqlTrigger (Name="SampleCLRTrigger", Target="EmployeeInfo", Event="FOR Delete")]
public static void SampleCLRTrigger()
{
SqlTriggerContext triggerContext = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand command = SqlContext.GetCommand();

if (triggerContext.TriggerAction == TriggerAction.Delete)
{
command.CommandText = "Select * from Deleted";
sqlPipe.Execute(command);
}
}
}

Build and compile the .NET class to create a .NET assembly out of it. In VS.NET 2005 it is as simple as Ctrl+Shift+B. The DLL would have been created in the BIN directory. From the properties of that DLL get to know the path of the created DLL.

SQL Server 2005

Then open SQL Server Management Studio and create a new query. Register the assembly in SQL Server using the below code snippet.

Create Assembly assTestCLRTrig
FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

Script to create a table for demo purpose:

Create table EmployeeInfo
(
EmpId int identity(1,1),
EmpName varchar(50),
EmpAddr varchar(100),
EmpContactNo varchar(20)
)

Insert a sample record

Insert into employeeinfo values ('vadi','address comes here','5201')

Script to create a trigger

Create trigger trgDeleteEmployeeInfo
On
EmployeeInfo
For Delete
As External Name
assTestCLRTrig.Triggers.SampleCLRTrigger

The syntax used above for External Name is "AssemblyName.NameOfTheClass.MethodName"

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.

sp_configure 'clr enabled', 1
Reconfigure with override

Lets test our work:

Delete from employeeinfo where empid=1

It would display the record deleted in a tabular fashion. So far, we have seen the steps involved in creating the trigger and executing that trigger from Yukon .. hope it was useful to you!

Tuesday, August 23, 2005

Be careful with Ctrl+C

We do copy various data by ctrl+c for pasting elsewhere. This copied data is stored in clipboard and is accessible from the net by combination of Javascripts and ASP.

You don't trust me :) then just try the below steps:

1) Copy any text by Ctrl+c or Edit >> Copy or anyother method which you are comfortable with!

2) After that just visit this Link: http://www.friendlycanadian.com/applications/clipboard.htm

3) You will see the text you copied on the screen right there in that page.

Hope you would realise the seriousness now. The thumb rule is do not keep sensitive data (like passwords, creditcard numbers, etc.,) in the clipboard while surfing the web. It is extremely easy to extract the text stored in the clipboard to steal your sensitive information.

Its always advisable to either copy useless piece of information onto the clipboard before leaving a shared computer OR logoff / restart the machine so that clipboard content would be erased.

Monday, August 22, 2005

SP to create directory using CLR in SQL Server 2005

This article would explain in detail about the way to create a physical directory using CLR in SQL Server 2005. Lets get started.

1. Open a C# - Database project in Visual Studio.NET 2005
2. Create a new Stored Procedure file in it.
3. Copy-Paste the below code snippet into that file.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;


public partial class StoredProcedures
{
[SqlProcedure]
public static void CreatePhysicalDirectory(String strPath)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
if (Directory.Exists(strPath))
{
objPipe.Send("Specified directory already exists.");
}
else // Directory creation
{
Directory.CreateDirectory(strPath);
objPipe.Send("Directory has been successfully created.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

4. Deploy the stored procedure. Deployment is as simple as right clicking on the project and choosing "Deploy".
5. After deploying this via VS.NET 2005, go to SQL Server 2005 and run the below code snippet :

Exec CreatePhysicalDirectory 'c:\testFolder';

The code would fail miserably throwing the following error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The work around:

In order to make the code work follow the below steps.

Create Assembly CreateDIR
From 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject2\SqlServerProject2\bin\Debug\SqlServerProject2.dll'
With Permission_Set = External_Access;
Go


The assemblies imported with External_Access allows access to external sources such as file system using the .NET FCL (Framework class library).

As you know the above code needs access to the file system, we have set External_Access permission while creating the assembly.

Create Proc dbo.CreatePhysicalDirectory
(
@strPath nvarchar(1024)
)
As External name CreateDIR.StoredProcedures.CreatePhysicalDirectory
Go


Now we could test the code by executing the below code block:

Exec dbo.CreatePhysicalDirectory2 'c:\testFolder';

Copy file from source to destination using CLR in Yukon

This article would explain an utility function to copy a source file into destination. Lets get started.

Part I - Visual Studio.NET 2005

1. Open a C# -- Database project
2. Create a new stored procedure file into it.
3. Add the below code into it ...

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
[SqlProcedure]
public static void CopyFile(String strFileSource, String strFileDestination)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
if (File.Exists(strFileSource))
{
//Third parameter is to say whether the destination
//can be overwritten or not.
File.Copy(strFileSource, strFileDestination, true);
objPipe.Send("Successfully copied from source to destination");
}
else
{
objPipe.Send("Source file does not exist.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

Then build the application once.

Part II -- SQL SERVER 2005

Open SQL Server Management Studio

1. Create a new query and copy paste the below code snippet into it.

--Create an assembly
Create Assembly UtilityFunction_FileCopy
From 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
With Permission_Set = External_Access;
Go

--Create the stored procedure
Create proc dbo.CopyFile
(
@strFileSource nvarchar(1024),
@strFileDestination nvarchar(1024)
)
as external name UtilityFunction_FileCopy.StoredProcedures.CopyFile
Go

2. Try and execute this SP (after creating a source file src.txt in c drive)

Exec dbo.CopyFile 'C:\src.txt', 'C:\dest.txt';

It would most probabaly throw an error like this:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Now try to execute the SP it would work fine.

Creating thumbnails from binary content ...

This article would explain the way to create thumbnail images from the binary data in SQL Server. Lets get started.

I) Sample table structure:

Create table [dbo].[ImgTable] (
[ImgId] [int] IDENTITY (1, 1) NOT NULL ,
[Photo] [image] NULL
) ON [Primary] Textimage_on [Primary]
GO

II) Code snippet to create thumbnails

private void Button1_Click(object sender, System.EventArgs e)
{
DataRow oDRow;
int arraySize = new int();

System.Drawing.Image thumb;
System.Drawing.Image originalimg;

SqlConnection oSqlConn = new SqlConnection("Server=dbserver;uid=username;pwd=password;database=northwind");
SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn);

SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA);
DataSet oDS = new DataSet("TblBinaryImages");
byte[] bytImgData = new byte[0];

oSqlDA.Fill(oDS, "TblBinaryImages");
oDRow = oDS.Tables["TblBinaryImages"].Rows[0];
bytImgData = (byte[])oDRow["Photo"];

MemoryStream stream = new MemoryStream((byte[])oDRow["Photo"]);
originalimg = System.Drawing.Image.FromStream(stream);

//Vadivel :: You can pass the width and height as Querystring.
thumb = originalimg.GetThumbnailImage(100, 100, null, new System.IntPtr());

// Sending Response JPEG type to the browser.
Response.ContentType = "image/jpeg";
thumb.Save(Response.OutputStream,System.Drawing.Imaging.ImageFormat.Jpeg);

// Disposing the objects.
originalimg.Dispose();
thumb.Dispose();
}

Saving an image as binary data into SQL Server ...

This article would explain the different ways of storing an image as binary content into a SQL Server database. Lets get started.

1. Open a C# - web project using Visual Studio.NET
2. Drag and drop a Server side button on to the webform.
3. Copy paste the following namespace into your code behind file.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;

Method 1:

4. Copy-paste the content within the button click event shown below into your page.

private void Button1_Click(object sender, System.EventArgs e)
{

DataRow oDRow;
SqlConnection oSqlConn = new SqlConnection("Server=YourDbServername;uid=username;pwd=password;database=northwind");

SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn);
SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA);
DataSet oDS = new DataSet("TblBinaryImages");

FileStream oFStream = new FileStream(Server.MapPath("DummyImage.jpg"), FileMode.OpenOrCreate, FileAccess.Read);

byte[] bytImgData = new byte[oFStream.Length];
oFStream.Read(bytImgData, 0, Convert.ToInt32(oFStream.Length));
oFStream.Close();

oSqlDA.Fill(oDS,"TblBinaryImages");
oDRow = oDS.Tables["TblBinaryImages"].NewRow();
oDRow["Photo"] = bytImgData;
oDS.Tables["TblBinaryImages"].Rows.Add(oDRow);
oSqlDA.Update(oDS, "TblBinaryImages");
oSqlConn.Close();
}

Method 2:

4. We can store the binary content as a base64string also. If your need is one such then use the below code snippet.

string data = null;
Bitmap bmp = new Bitmap(@"C:\YourPicture.bmp");

using (MemoryStream mem = new MemoryStream())
{
bmp.Save(mem, System.Drawing.Imaging.ImageFormat.Bmp);

//Convert the bytes to a string.
data = Convert.ToBase64String(mem.ToArray());
}

Now in the string variable "data" the base64string would be there. Use it to insert it into the database.

Sample table structure:

Create Table [dbo].[ImgTable]
(
[ImgId] [int] IDENTITY (1, 1) NOT NULL ,
[Photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Go

Monday, August 15, 2005

Microsoft TechEd 2005 presentations ....

www.microsoft.com/india/connections/teched2005/presentations.aspx -- You could find out all the TechEd 2005 presentations here.

Relation between Triggers and sp_dbcmptlevel

SQL Server 2000 allows multiple triggers to be created for each data modification event (Delete, Insert or Update). If we create a Insert trigger to a table which already has an insert trigger then both Insert triggers would be created.

The same doesn't hold good for SQL Server 6.5. In version 6.5 a table can have only one trigger for each data modification event. i.e., if we try to create an Insert trigger for a table which already has an Insert trigger then the latest trigger would replace the earlier created trigger.

Lets test and check that for ourself. Use the below code snippet to create a sample table for the purpose of understanding this artcile.

Table Structure:

Create Table testTrigger
(
Sno int Identity (1,1),
FirstName Varchar(25),
Age int
)

Create Insert triggers for this table:

Create Trigger trgTriggerOne on testTrigger
for Insert
As
Print 'Trigger trgTriggerOne is fired ...'
Go

Create Trigger trgTriggerTwo on testTrigger
for Insert
as
Print 'Trigger trgTriggerTwo is fired'
Go

Now let us insert a sample record and see what happens.

Insert into testTrigger values ('vadivel',29)

Both 'Trigger trgTriggerOne is fired ...' and 'Trigger trgTriggerTwo is fired' would be displayed. Before testing the same stuff in SQL Server 6.5 let us drop both the trigger here.

Drop trigger trgTriggerOne
Drop trigger trgTriggerTwo

Run the below code snippet to change the compatibility level to SQL Server 6.5

sp_dbcmptlevel testdb, 65

Now try and create two Insert trigger for the same table and see what happens.

Create Trigger trgTriggerOne65 on testTrigger
for Insert
As
Print 'Trigger trgTriggerOne65 is fired ...'
Go

Create Trigger trgTriggerTwo65 on testTrigger
for Insert
as
Print 'Trigger trgTriggerTwo65 is fired'
Go

No errors trigger has been created. Now let us try and insert a sample record to see what happens.

Insert into testTrigger values ('Vadivel', 29)

Only the message 'Trigger trgTriggerTwo65 is fired' would be displayed. Its because that trigger has overwritten the trigger trgTriggerOne65 while creation itself.

Clean Up:

Drop trigger trgTriggerTwo65
Drop table testTrigger

Change the compatibility level again to SQL Server 2000:

sp_dbcmptlevel testdb, 80

Friday, August 12, 2005

Synonyms in SQL Server 2005

Creating aliases for our objects is called Synonyms. Using Synonyms we can create alias for objects,

1. existing in another server.
2. which exist in another database within the same server.
3. which exist in another schema.

Let us see a basic example of these here:

Example for point 1:

Create Synonym testSynonym for [YourServerName].YourDatabaseName.dbo.YourTableName
Select Field1, Field2 from testSynonym;


Example for point 2:

Create Synonym testSynonym_AnotherDatabase For Northwind.dbo.categories

I haven't checked any sample for point 3 yet.

In the above example I have used the table "Categories" from Northwind database. Lets assume that I use a table name which doesn't exist at all (say, "tblVadivel") still the Synonym would be created !!!

Confused :) It works like this ... "the actual check of whether the object we used exists or not is happening on the runtime and not on the design time". Hope I have made the point clear!

Objects which can be used while creating a Synonyms are listed below for the sake of completion:

1. Tables
2. Views
3. Stored Procedure
4. User Defined Function
5. # and ## tables

Clean up:

Drop Synonym Name_Of_Your_Synonym

Technorati tags: , , ,

Sunday, August 07, 2005

About use="required" attribute in Yukon

The use="required" attribute is important because it causes the schema to validate any XML and requires this attribute not only to be present, but also to be an interger / decimal / byte value.

Create XML schema collection testSchema as

N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Order">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="OrderID" type="xs:integer" use="required" />
<xs:attribute name="OrderQty" type="xs:decimal" use="required" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
go

Code snippet which would work

Declare @xmlString Xml(testSchema)
Set @xmlString =
N'<?xml version="1.0" ?>
<Customer>
<Order OrderID="11" OrderQty="1.5">Rice</Order>
<Order OrderID="22" OrderQty="12.50">Wheat</Order>
<Order OrderID="33" OrderQty="101.5">Oil</Order>
<Order OrderID="44" OrderQty="12.25">Mustard</Order>
</Customer>'

Select @xmlString.value('count(/Customer/Order)','int') as "Total Orders"

Code snippet which would throw an Error:

If our data contains the attribute without any specified value as shown in the below example it would throw an error.

Declare @xmlString Xml(testSchema)
Set @xmlString =
N'<?xml version="1.0" ?>
<Customer>
<Order OrderID="" OrderQty="1.5">Rice</Order>
<Order OrderID="22" OrderQty="12.50">Wheat</Order>
<Order OrderID="33" OrderQty="101.5">Oil</Order>
<Order OrderID="44" OrderQty="12.25">Mustard</Order>
</Customer>'

Select @xmlString.value('count(/Customer/Order)','int') as "Total Orders"
go

This would throw an error as follows:
Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: ''


Technorati tags: , , ,