Share via

Encrypting Data In SQL Server 2005

I've been looking at the new encryption functionality in SQL Server 2005. Here's some sql that executes on IDW15 - June CTP (most of it should work on IDW14 - April CTP apart from DecryptByKeyAutoCert I think...

This sample code sets up a database and a table that will contain some data (national insurance numbers) we want to store encrypted. It shows how to set up a master key, certicate and encryption key and how to use these to insert and select data. It also demonstrates how to set up a view that takes away a lot of the pain.

It does expose that in IDW15 at least you need to give CONTROL level permission to a certificate to make this work - which I hope gets fixed in the next release... with CONTROL you can do anything to a certificate, including dropping and altering it and removing the private key!!! The last of these works even if the cert is used to decrypt keys in the database - which presents an untenable security problem. What we want is just to require REFERENCES level permission (I think).

Other than this little niggle, the functionality is awesome! I have been doing encryption/decryption in the middle tier: it's great to be able to move this to the database - particularly as this makes it much easier to create reports in report server that otherwise had to use a custom assembly to decrypt data.

Copy the below into management studio and walk through it.

create database SecurityDemo
go
use SecurityDemo
go

-- Create a master key - this is used as the root of the encryption
-- hierarchy: all keys and certs are encrypted with this
-- it is scoped to the database
create master key encryption by password = <'pass@word1'>

-- Now we will create a simple table with a column that will store
-- national security numbers in encrypted form

create table people (
 id int identity constraint pk_people_id primary key clustered,
 firstname nvarchar(50),
 lastname nvarchar(50),
 encrypted_ninumber varbinary(128) )
go

-- Now lets create a certicate - we will use this to encrypt a key
-- this will give a warning about an invalid start date, but
-- we can ignore this since sql doesn't care about cert dates
create certificate ni_cert with subject = 'NI Certificate'
go

-- Now create a symmetric key to encrypt/decrypt the data
create symmetric key ni_key with algorithm = aes_256
encryption by certificate ni_cert
go

-- Now we will create a procedure to insert and encrypt the data
create procedure dbo.insert_people (
 @firstname nvarchar(50),
 @lastname nvarchar(50),
 @ninumber nchar(9)
) as

-- open the symmetric key
open symmetric key ni_key decryption by certificate ni_cert

-- insert and encrypt the data
insert people(firstname, lastname, encrypted_ninumber)
 values(
  @firstname,
  @lastname,
  EncryptByKey(Key_GUID('ni_key'), @ninumber)
)

-- close the key now
close symmetric key ni_key
go

-- now lets insert some data
insert_people 'James', 'World', 'JZ123488B'
go

-- looking at this data, you can see its nice and encrypted
select * from people
go

-- ok, so lets create a procedure to decrypt this
create procedure dbo.select_person (
 @id int
) as

open symmetric key ni_key decryption by certificate ni_cert

select
 id,
 firstname,
 lastname,
 convert(nchar(9),DecryptByKey(encrypted_ninumber)) as ninumber
from
 people

close symmetric key ni_key
go

-- and try it out
exec select_person 1
go

-- how about if we want another use to call this spoc?
create login bob with password = <'pass@word1'>
go
create user bob for login bob
go

-- this won't work as bob has no permissions
execute as user='bob' -- lets us run in the context of bob
exec select_person 1
revert -- puts us back to our dbo context
go

-- let's give him some and then the above should work
grant execute on dbo.select_person to bob
grant references on symmetric key::ni_key to bob
-- eek!!! currently need to grant *control* permission to bob
-- in IDW15... hope this improves!
grant control on certificate::ni_cert to bob

-- now it works
execute as user='bob' -- lets us run in the context of bob
exec select_person 1
revert -- puts us back to our dbo context
go

-- lets take away bob's permissions
revoke execute on dbo.select_person to bob
revoke references on symmetric key::ni_key to bob
revoke control on certificate::ni_cert to bob
go

-- a new feature lets us create a view that automatically decrypts
-- the data
create view view_people as
 select
  id,
  firstname,
  lastname,
  convert(
   nchar(9),
   DecryptByKeyAutoCert(cert_id('ni_cert'),
   null, encrypted_ninumber)
  ) AS ninumber
 from
  people
go

select * from view_people
go

-- can bob look at this view? clearly he needs permission on the view
grant select on view_people to bob
go

-- lets try...
execute as user='bob'
select * from view_people
revert
go

-- hmmm! we got null - this is what anyone gets that doesn't
-- have an open key when they try to read encrypted data
-- lets grant him permissions - control of the cert and references on the key
grant control on certificate::ni_cert to bob
grant references on symmetric key::ni_key to bob
go

-- lets try again
execute as user='bob'
select * from view_people
revert
go

-- this works! and best of all we didn't have to manage
-- opening the key
-- if they fix the level of permissions required on the cert
-- then this will be an awesome way to manage encrypted data

-- current weakness of needing CONTROL permission for bob on the cert:

-- we can do this and remove the ability to decrypt our data!
execute as user='bob'
alter certificate ni_cert remove private key
revert
go

-- certs can be backed up and restored, but this still sucks

-- other than that, this is an awesome feature set!

Comments