Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Wednesday, 29 June 2011

EJ's first trip mowing, obviously comfortable with the job !


Wednesday, 29 June 2011 11:07:24 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Tuesday, 07 June 2011

SQL Server Encryption for Express or Standard

Security is the game that needs to be played to make management think they are taking adequate steps to safe guard data.  Ask Sony if this appropriate.

I've recently been asked to provide encryption for a sql server express database implementation.  There is much written about sql server's encryption features, but you don't see many details about how to do it for real in an application.  Of course the standard replies about Tabular Data Encryption (TDE) are not available in any edition of SQL Server, except enterprise, so this methodology I am laying out here works for Express, Standard or Work Group.

The goal was to provide a standard guidance for developing applications that require encryption, and also it should work for legacy applications with as few changes as possible (Oh, that's a beauty).

What I came up with was an encryption architecture that pulls from things I learned from:
- Protegrity (a security vendor, appliance)
- Expert SQL Server 2008 Encryption by Michael Coles and J. Luetkehoelter
- Database Encryption and Key Management for Microsoft SQL Server 2008: Understanding cell-level encryption and Transparent Data Encryption in Microsoft by Rob Walters and Christian Kirsch

[Damn, now that's a title for a book, they should have chosen "The Rats of SQL" or something just to make it interesting!]

Here it is, comments and suggestions are welcomed as often there are just too many bull shit blog posts on encryption; giving simple regurgitated examples from books on line.  That's crap, working with encryption for real in your application does not need to be nightmare, you do not need to fork over 20K+ a socket for enterprise edition [well, you might!!], you do not need to re-write your whole application, performance may suck, but that's not the point of this guidance....

Encryption is bad, but so is Cicada flavored ice cream.


SQL Server Express Database Encryption


The goal of encrypting data in the database engine is to protect data at rest (including backups) at the column (cell) level.  It does not protect data during transit over the network or from users who have the appropriate permissions and/or passwords.


The approach outlined here is only for the field database engine, it is targeted for SQL Server Express 2008, small databases with limited number of users; this solution is not designed for highly scalable OLTP databases.


The goal of the encryption process is to abstract the tables with cell level encryption by over-laying them with views.  This allows the application (with the proper permissions) to continue to work with the encrypted data seamlessly via the normal development methodologies without having to consistently write the code to decrypt / encrypt the columns (cells).  This process is the generally the same for new applications and existing applications that need to be modified to encrypt data with the key difference being the requirement to open the keys for new applications to provide additional security.


The SQL Server encryption functions return varbinary data, so all columns to be encrypted need to be changed to varbinary.  The use of Views to over-lay the tables provides an abstraction layer so the data types can be properly viewed for data modelers, developers, administrators, analysts and other end users.  To encrypt a SSN, varchar(9) field requires the column in the base table to be implemented as varbinary(200).  The data modeling group creates a logical model subject area in Erwin that uses the views and other non-encrypted tables to create a data model that is useable (not displaying varbinary as the data type for encrypted fields, but their actual un-encrypted data type).


Users that access the views, who do not have permissions to open the proper key(s) will not receive an error, but the column will not be decrypted and will be populated with nulls.


Database developers will need to work closely with the data modeling team and the administrators to ensure the data types, views, encryption keys, backup and restore of keys are implemented properly.  Data is not recoverable if the backup of keys and databases are not implemented properly.


Guidelines for implementing cell level encryption.


All tables that include an encrypted cell (column) follow the normal standard naming conventions with the addition of an underscore and the word base to the table name {tablename}_base.  All columns follow the standard implementation with the exception of those to be encrypted, which must use a varbinary data type.  The length of the varbinary column is determined by the max length returned from the encryption function.  Please work the data modeler and the administrators to determine the proper length based on the requirements for each column.


A view is created that follows the normal standards, except the name is implemented off the base table by dropping the underscore and the word base that was used to name the table (this allows legacy code to continue to work).  The view will make use of the decryption function and the proper Cast and Convert statements to manipulate the varbinary column to the correct un-encrypted data type.


Inserts, updates and deletes are handled in the normal manner, with the tsql statements being executed against the view, NOT the underlying tables.  “Instead of Triggers” are created on each view to handle the Inserts, updates and deletes.  This provides an additional layer of abstraction so that consistency can be maintained with the code.


All data access should continue according to the published standards (generally this is through stored procedures), refer to the Database governance document for details.


The ability to truncate a table is not available on views (by design), or on base tables according to our standard development methodologies, use the stored procedure truncate_tbl, the procedure will determine if it is a view and truncate the underlying table.


Text and Blob columns are not explicitely covered by this guidance, blob and text columns must be encrypted using CLR functions or by the application.  A more detailed example encrypting blobs will be published later.  (Be especially careful of free form text columns, end users often put PII data elements in these, thus requiring them to be encrypted).


A short tsql script example:



Create database encrypt_test;



use encrypt_test;



Create master key encryption by password = 'knights12$gzmlauncher#1@%dmissionisclear*()'



create certificate cert_sk with subject = 'Certificate for accessing symmteric keys - for use by App'









create table dbo.Client_Base

      ( client_Id int Identity(1,1) primary key,

            ssn varbinary(200),

            Amount_due varbinary(200),

            Comments varchar(1000)




create view dbo.Client



      Select Client_ID,


                  Null,SSN,1,convert(varchar(10),client_Id))) AS SSN,


                  Null,Amount_Due,1,convert(varchar(10),client_Id)))) AS Amount_Due,


      From dbo.Client_Base;



select * from dbo.client;



create trigger dbo.trg_client_insert

      on dbo.Client





            Declare @Client_ID int, @SSN varchar(9), @Amount_Due money,

                  @Comments varchar(1000);


            open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk;


            DECLARE cur_Client CURSOR FOR

                  SELECT      SSN,Amount_Due,Comments FROM INSERTED;

            OPEN cur_Client;

            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments;

            WHILE @@FETCH_STATUS = 0


                  Insert into dbo.Client_Base (Comments) values (@Comments)

                  set @Client_Id = scope_identity()

                  Update dbo.Client_base

                        set SSN = encryptbykey(key_guid('sk_encrypt_test'),@SSN,1,Convert(varchar(10),@Client_ID))

                              ,Amount_Due = encryptbykey(key_guid('sk_encrypt_test'),convert(varchar(10),@Amount_Due),1,Convert(varchar(10),Client_ID))

                        where Client_ID = @Client_ID


            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments


            CLOSE cur_Client

            DEALLOCATE cur_Client        


            CLOSE SYMMETRIC key sk_encrypt_test





create TRIGGER trg_Client_Update ON dbo.Client





      open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk


      Update Client_base

            Set SSN = encryptbykey(key_guid('sk_encrypt_test'),i.SSN,1,Convert(varchar(10),i.Client_ID))

                  ,Amount_Due = encryptbykey(key_guid('sk_encrypt_test'),convert(varchar(10),i.Amount_Due),1,Convert(varchar(10),i.Client_ID))

            FROM Client_Base

            inner join inserted i on Client_Base.Client_ID = i.Client_Id


      CLOSE SYMMETRIC key sk_encrypt_test





create trigger dbo.trg_Client_Delete

      on dbo.Client




      Delete Client_Base

      from Client_Base db

      inner join deleted d on db.Client_ID = d.Client_ID




-- Stored procedures are written as normal


Create procedure dbo.usp_Client_get_by_SSN

      @ssn varchar(9)



      Select * from Client

            where ssn = @ssn





-- MUST OPEN KEY or all insert statements will FAIL


DECRYPTION BY PASSWORD = N'knights12$gzmlauncher#1@%dmissionisclear*()';


Insert into dbo.client (ssn,amount_due,comments) values

      ('123456789',256.01,'This is a test of encryption')


select * from dbo.client;

select * from dbo.client_base;



Update Client

      set ssn = 987654321,

            Amount_Due = 100

      where ssn = 123456789;



select * from Client;



Close Master Key;

-- stored procedures work normally, though no data if key is not open

exec dbo.usp_Client_get_by_ssn @SSN = 987654321

-- now open key and data is returned.


DECRYPTION BY PASSWORD = N'knights12$gzmlauncher#1@%dmissionisclear*()';


exec dbo.usp_Client_get_by_ssn @SSN = 987654321;



delete from client where SSN = 987654321



select * from Client;







Tuesday, 07 June 2011 11:37:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

Smith and Wesson 357 Model 66, What's in your nightstand ?

Tuesday, 07 June 2011 08:24:47 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
DTA - Failed to initialize MSDB dat...
SQL Server Security, not where it n...
Check the Uptime of a Windows Serve...
Recent Posts
May, 2017 (2)
April, 2017 (1)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Admin Login
Sign In