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
>
