The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Saturday, December 7, 2024 Login
Public

Blog posts for the month of June,2011.
Mowing6/29/2011 12:07:24 PM

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

 

SQL Server Encryption for Express or Standard6/7/2011 12:37:24 PM

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;

go

 

use encrypt_test;

go

 

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

go

 

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

go

 

CREATE SYMMETRIC KEY sk_encrypt_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE cert_sk

go

 

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

go

 

create table dbo.Client_Base

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

            ssn varbinary(200),

            Amount_due varbinary(200),

            Comments varchar(1000)

      );

go

 

create view dbo.Client

as

 

      Select Client_ID,

            convert(varchar(9),decryptbykeyautocert(cert_id('cert_sk'),

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

            convert(money,convert(varchar(10),decryptbykeyautocert(cert_id('cert_sk'),

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

            Comments

      From dbo.Client_Base;

go

 

select * from dbo.client;

go

 

create trigger dbo.trg_client_insert

      on dbo.Client

INSTEAD OF INSERT

AS

      BEGIN

 

            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

            BEGIN

                  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

            END

            CLOSE cur_Client

            DEALLOCATE cur_Client        

 

            CLOSE SYMMETRIC key sk_encrypt_test

 

      END

go

 

create TRIGGER trg_Client_Update ON dbo.Client

INSTEAD OF UPDATE

AS

BEGIN

 

      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

 

END

go

 

create trigger dbo.trg_Client_Delete

      on dbo.Client

INSTEAD OF DELETE

AS

BEGIN

      Delete Client_Base

      from Client_Base db

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

END

go

 

-- Stored procedures are written as normal

 

Create procedure dbo.usp_Client_get_by_SSN

      @ssn varchar(9)

As

begin

      Select * from Client

            where ssn = @ssn

end

 

 

----------------------------------------------------------

-- MUST OPEN KEY or all insert statements will FAIL

OPEN MASTER KEY

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

GO

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

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

go

select * from dbo.client;

select * from dbo.client_base;

go

 

Update Client

      set ssn = 987654321,

            Amount_Due = 100

      where ssn = 123456789;

go

 

select * from Client;

go

 

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.

OPEN MASTER KEY

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

GO

exec dbo.usp_Client_get_by_ssn @SSN = 987654321;

go

 

delete from client where SSN = 987654321

go

 

select * from Client;

go

 

 

 

 


 

Revolvers are sexy !6/7/2011 9:24:47 AM

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

Ebenezer the Donkey5/30/2011 8:10:03 AM

Took a bike ride to visit Ebenezer the Donkey in Grandview !

http://www.ebenezerthedonkey.com/

 

Memorial Day 20115/30/2011 9:17:13 AM

Thanks to all Vets, this was at the Liberty Memorial.

A Memorial Day t-shirt with attitude is a nice touch.

Even old school vets rise from their chair when it's appropriate.

Thanks Tony.

Hail does damage roofs !4/3/2011 2:07:07 PM

Quarter size hail does damage a roof !

 

 

 

 

 

Horseradish does flower5/26/2011 2:00:43 PM

Horseradish does flower, not every spring, seems to be about every other or every 3rd year.

 


Blog Home