Turning on SQL08 Database Encryption, Putting it to the test
So first of all you need to create your database if you haven’t already, then open ‘query’ on the ‘master’ database.
This is very important make sure you have the master database and not the user database. (of course I could have put ‘use Master’ but I am outlining this so you know and are learning 
)
-- create a Database MasterKey
create master key encryption by password = 'password here';
go
-- Create a certificate to a database Create certificate testingCert with subject = 'Testing Certinifcate for SQL' go
Now for this part you need to ‘use databasename’ then paste the below Script, or of course just use the query on the database and then copy and paste
– creates the key for the database selected
– below I have used AES_128 this is more then enough but there are others
– such as:
– aes_192, aes-256 and tripleDes
create database encryption key with algorithm =AES_128
– notice I am using the cert name I have just created
encryption by server certificate testingcert
go
– use the database you are applying the encryption to:
alter databasename testing
set encryption on
go
You can now test to ensure that the encyption has been applied by simply going to the GUI.
This is found at the database level, in tasks toward the bottom.
Now for the testing of the data.
To show what small overhead is used, personally I see it is little to none.
Here is what was used and added to both databases (got this from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=671045&SiteID=1 slightly modded)
create tableemployeesdemo
(
recordno int identity(1,1),
employeeno varchar(20),
lastname varchar(50),
firstname varchar(50),
middlename varchar(50)
)
insertemployeesdemo(employeeno,lastname,firstname,middlename)
values(‘emp000′,‘green’,‘kat’,‘blue’)
declare @CTR INT
declare@ctrstr varchar(7)
select @ctr=0
WHILE @CTR<100
BEGIN
select @ctr=@ctr+1
select@ctrstr=Str(@ctr)
insertemployeesdemo(employeeno,lastname,firstname,middlename)
values(‘empno’+@ctrstr,‘lname’+@ctrstr,‘fname’+@ctrstr,‘mname’+@ctrstr)
end
This is applying the above script to the non encrypted database
this is applying the same script to the ENCYRPED database and the below images are what you should see once the above query has completed.
They both have the same table and same amount of rows.
Now its time to backup the databases, I am not going to tell you how you do that I am hoping you can do that one Now this is what I get in terms of file sizes
As you can see there is little difference, obviously the difference may grow a little with a bigger database, which is something I am yet to try and will shortly , but I am doubting the file size will be all that different, from the original backup file.
So here is the results, first the NON-Encrypted
As you can see I do a find for green, which is something found in the table we created
Now this is the encrypted one
and look at that, no green found…
Conclusion it works, and it works well with little overhead of disk space, as for time frames well it’s hard to tell with such a small databases maybe with a bigger one it might be easier to judge. But this is looking promising.
Being that Elcom is on one of our SQL 2008 servers I am going to be applying this to the production database.
We love security.
