|
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
|