August 2, 2011 at 1:01 pm
Thanks lowell.
August 2, 2011 at 1:35 pm
Lowell say if we encrypt the column of data in production and if we want to refresh dev environment using prod data how can we implement the same encryption/decryption in dev environment too? Do we have to create same keys? If we create the same keys in dev, can it decrypt the data (colun level) refreshed using prod data which is encrypted? Thanks!
Lowell (8/2/2011)
Laura_SqlNovice (8/2/2011)
when creating Symmetric key with Certification do I have to create certificate for every users who will access the data column?nope...typically you create one cert and use it everywhere....if you have something that needed a seperate cert, like some super secret project, you might have multiple certs.
typically it's lord of the rings style:
one cert to rule them all
August 2, 2011 at 1:43 pm
yes, it just requires a little setup.
you create a master key on production.
you create the cert on production.
you backup the master key and the cert from production.
you restore them on dev.
the articles i mentioned go into detail on that; when you get a chance, I really recommend reading through and testing their examples. they really jump started encryption for me when i needed it fast. BOL has some nice example son the backing up of master keys and certs as well.
.
Laura_SqlNovice (8/2/2011)
Lowell say if we encrypt the column of data in production and if we want to refresh dev environment using prod data how can we implement the same encryption/decryption in dev environment too? Do we have to create same keys? If we create the same keys in dev, can it decrypt the data (colun level) refreshed using prod data which is encrypted? Thanks!Lowell (8/2/2011)
Laura_SqlNovice (8/2/2011)
when creating Symmetric key with Certification do I have to create certificate for every users who will access the data column?nope...typically you create one cert and use it everywhere....if you have something that needed a seperate cert, like some super secret project, you might have multiple certs.
typically it's lord of the rings style:
one cert to rule them all
Lowell
August 3, 2011 at 8:43 am
Laura:
There are several objects that have to be taken into consideration:
SMK (Service Master Key)
DMK (Database Master Key)
Certificate
Symmetric Key (a re-createable symmetric key, there is a difference)
If you use a certificate and a re-createable symmetric key, then you can recreate all the objects on another server, which is definitely a plus, in case you ever have to move the databases.
SMK, DMK, and certificates can all be backed up. Using a re-createable symmetric key that is secured by the certificate will ensure all is recreateable, since the certificate can be backed up on one server and restored on another.
Kudos on your choice of using a Certificate with Symmetric Key. This is actually a very good way of doing the encryption, since it follows Microsoft's recommendation of securing the actual database data with a symmetric key for speed, but securing the symmetric key with either a certificate or asymmetric key. Over the two, I would prefer a certificate, since it can be backed up and moved if needed.
It is also a good idea to use Stored Procedures to do the encryption/decryption. They allow you to not have to transmit the actual encrypt/decrypt keys across the network. Use the WITH ENCRYPTION clause on the creation of the SPROC so that nobody can script out the SPROC and get the decryption keys. That means you have to keep the scripts that created the SPROCs in a safe place in case changes need to be made. You can also give execute permissions for the user to run the SPROC, but not give them access to the underlying tables, views, etc. Also, if you use SPROCs, you can devise an audit mechanism for each time the SPROC is executed, a record can be inserted into an audit history table keeping the SPROC name, name of user, the date and time, the computer name they ran it from, etc.
One thing to keep in mind is that the database backups can be restored on any instance, and the DBA that restored it can run the stored procedures that decrypt the data, so they would still have complete access to the data. Therefore, a backup that someone steals or somehow gets hold of is compromised. One way around this is to use passwords for the backups and restores. That way, the data in the database is encrypted (so it can’t be seen with an editor), and nobody can restore it to run the stored procedures to decrypt it.
I strongly suggest getting a copy of the book “Expert SQL Server 2008 Encryption”, 2009, Apress, by Michael Coles and Rodney Landrum. Excellent descriptions of the encryption options and details.
Some other resources:
“SQL Server 2008 Security Overview For Database Administrators”, Microsoft Whitepaper, , Jan. 2007, updated July 2008
“Protect Sensitive Data Using Encryption in SQL Server 2005”, Microsoft Whitepaper, Don Kiely, Dec. 2006
One of the greatest things about Column-Level Encryption is that it is available on the Express Editions (2005 and up)! That is so cool.
I spoke at the Oct 2010 SQLSaturday in Columbia, SC on Intro To Column-Level Encryption. Here is some info from my slides:
Service Master Key:
================
A symmetric key (uses Triple DES algorithm)
Instance-wide in scope
Generated when instance is installed
Used to encrypt the following:
Linked server passwords
Connection Strings
Account Credentials
All Database Master Keys
The SMK is secured by the Windows security API’s (DPAPI), utilizing the credentials of the SQL Server service account user. If that user is changed, special commands must be used to change the SMK to re-associate the key with the new user’s credentials.
ALTER SERVICE MASTER KEY
WITH NEW_ACCOUNT = ‘<domain or computer name>\<username>’,
NEW_PASSWORD=‘<new password>’;
Database Master Key:
===================
A symmetric key (uses Triple DES algorithm)
Secured by the Service Master Key
Serves as the starting point for any database encryption keys
By default, copies of the key are stored in database as well as the instance’s Master database (behavior can be changed)
Certificates:
==========
Another form of asymmetric key
Uses a digital signature to associate public and private keys with their owner
Can use internally created certificates, or certificates issued by external certificate authorities (Thawte, VeriSign, etc.)
Recommended over asymmetric key
RSA algorithms used for encrypting these
Symmetric Keys:
==============
Same key used to encrypt and decrypt
Lower security level since both use same key (can be mitigated by using special methods)
Better performance than Asymmetric
Full list of encryption algorithms available (DES, Triple DES, AES128, AES192, AES256, RC2, RC4, DESX)
All levels of symmetric keys must be opened in order to use them to decrypt or encrypt
Hope this helps.
Bob L
August 3, 2011 at 9:35 am
Here are all the scripts from the presentation (they are long.....). They should show you start to finish how to implement CLE with a cert and symmetric key. Hope it helps.
(WARNING: ONLY RUN THIS SCRIPT ON A DEVELOPMENT BOX UNTIL YOU MASTER THE CONCEPTS AND COMMANDS!!!!)
/* ==============================================================================================*/
/* Service Master Key */
/* Each instance has one Service Master Key, which was created at install. */
/* ==============================================================================================*/
/* ============================================================================================== */
/* ONCE OVERALL - back up the service master key using an encryption password
Only needs to be done once overall, period. Backup file should be saved to CD and
kept offsite in safe place (safe deposit box, etc.). Don't forget to safeguard the password
that was used to encrypt it. You'll need it to restore the backup. Only keep in the
same place as the backup if really safe (like safe deposit box).
*/
BACKUP SERVICE MASTER KEY TO FILE='c:\ServiceMasterKeyBackup_YYYYMMDDHHMM.bak'
ENCRYPTION BY PASSWORD='SQLServerIsReallyCoolServiceMasterBackup334$'
GO
/*
http://msdn.microsoft.com/en-us/library/aa337561.aspx
*/
-- To restore that key, use the following:
/*
RESTORE SERVICE MASTER KEY FROM FILE=’c:\ServiceMasterKeyBackup_YYYYMMDDHHMM.bak’
DECRYPTION BY PASSWORD=’SQLServerIsReallyCoolServiceMasterBackup334$’
http://msdn.microsoft.com/en-us/library/aa337510.aspx
*/
/* ============================================================================================== */
/* This service master key can be regenerated, to produce a different service master key,
by issuing the following (note that this replaces the current one, great care should
be taken to back up the existing one in case it is needed if anyone has encrypted
anything using it) */
/*
ALTER SERVICE MASTER KEY REGENERATE
*/
/* Listing 1: Code to Create EncryptionTestDB Database
---------------------------------------------------------------------------
Create EncryptionTestDB.
---------------------------------------------------------------------------
This is the code to create the EncryptionTestDB database.
---------------------------------------------------------------------------*/
Use Master
CREATE DATABASE EncryptionTestDB
/* EncryptionTestDB database table creation script
---------------------------------------------------------------------------
Create EncryptionTestDB tables.
---------------------------------------------------------------------------*/
/* ========================================================================================== */
USE EncryptionTestDB
GO
/* ========================================================================================== */
/* = = */
/* = = */
/* = Main Tables = */
/* = = */
/* = = */
/* ========================================================================================== */
/* ======================================================================================== */
CREATE TABLE dbo.Applicant
( AppID int IDENTITY NOT NULL
CONSTRAINT PK_Applicant_On_AppID PRIMARY KEY CLUSTERED,
AppFName varchar(35) NOT NULL,
AppMName varchar(35) NULL,
AppLName varchar(35) NOT NULL,
AppSSN varbinary(200) NULL,
LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(),
LCUser varchar(50) NOT NULL DEFAULT USER,
LCDate datetime NOT NULL DEFAULT GETDATE()
)
ON EncryptionTestDB_data
GO
/* ======================================================================================== */
CREATE TABLE dbo.AppErrorLog
( EID int IDENTITY NOT NULL
CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,
EDate datetime NOT NULL,
EUser varchar(50) NULL,
EMessage varchar(1000) NULL,
ESource varchar(150) NOT NULL,
ENo int NOT NULL DEFAULT 0,
ESeverity int NOT NULL DEFAULT 0,
ELineNo int NOT NULL DEFAULT 0,
EHost varchar(50) NULL
)
ON EncryptionTestDB_data
GO
/* ====================================================================================================== */
/* Create Database Master Key and any needed Symmetric or Assymetric keys */
/* ====================================================================================================== */
USE EncryptionTestDB
GO
/* ==============================================================================================*/
/* Database Master Key */
/* Each database can have only one Database Master Key. It has to be manually created. */
/* ==============================================================================================*/
/* Create the database master key using the server's service master key.
This becomes part of the database, and is included in any database backups.
Is encrypted with the Service Master Key and saved in the Master database, and is
encrypted with the passphrase and saved in the database. (this behavior can be changed)
Database Master Key can only be created once per database. Be sure to keep the database master key
password in a safe place in case it has to be recreated. */
/* This database master key is created once per database */
USE EncryptionTestDB
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='$iUksH8&!eDvBuLLQ24$&8*tYPkMnbV2$1SaFbGkEr'
/*
Remember: Only one DMK can be created.
*/
/* Back up the database master key
http://msdn.microsoft.com/en-us/library/aa337546.aspx
*/
BACKUP MASTER KEY TO FILE = '<complete path and filename>'
ENCRYPTION BY PASSWORD = '<password to encrypt the output file with>'
GO
/* Restoring the Database Master Key
RESTORE MASTER KEY FROM FILE = '<complete path and filename of file from above where backed up>'
DECRYPTION BY PASSWORD = '<password1>'
ENCRYPTION BY PASSWORD = '<password2>'
GO
Where password1 is the password that was used to encrypt the key on the backup medium, and password2 is the
password with which to encrypt a copy of the key in the database
http://msdn.microsoft.com/en-us/library/aa337511.aspx
*/
/* Create a Certificate */
CREATE CERTIFICATE Cert1
WITH SUBJECT = N'SQL 2008 Certificate 1',
START_DATE = '20101101',
EXPIRY_DATE = '20201231';
/* Make a backup of the certificate */
BACKUP CERTIFICATE Cert1
TO FILE = 'C:\SQLBackups_2008\Cert1_certificate_backup.cer'
WITH PRIVATE KEY
(
FILE=N'C:\SQLBackups_2008\Cert1_alt.pvk',
ENCRYPTION BY PASSWORD = N'SQLServerIsCool444@!'
);
/* Will export the backup to the file specified in the TO FILE = clause. It will export the private key to the file
specified in the WITH PRIVATE KEY, and FILE= clause. The password encrypts the private key file, and will be
needed if you need to restore the certificate from the backup file, or to move it to a new server. Be sure to move
the backup of the certificate to external backup media, and store it in a safe and secure place where you can
retrieve it if necessary. See below for restore statement, using the backup created above.
*/
/* drop a certificate (if needed) */
--DROP CERTIFICATE Cert1
/* restore the certificate if needed */
/*
CREATE CERTIFICATE Cert1
FROM FILE = 'C:\SQLBackups_2008\Cert1_certificate_backup.cer'
WITH PRIVATE KEY
(
FILE=N'C:\SQLBackups_2008\Cert1_alt.pvk',
ENCRYPTION BY PASSWORD = N'SQLServerIsCool444@!'
);
*/
/* Create a symmetric key. */
--This symmetric key is protected with the certificate, created just above, which is, in turn, protected by the DMK (Database Master Key)
CREATE SYMMETRIC KEY SymmKey1
WITH ALGORITHM=AES_256,
IDENTITY_VALUE = N'tU8$180$kMnH5T7#eJLknKW7qA',
KEY_SOURCE = N'C3&fHw#erTfv7*&4k@DNmWdXpL8#4$'
ENCRYPTION BY CERTIFICATE Cert1;
/*
Note that by using the IDENTITY_VALUE and KEY_SOURCE clauses, this symmetric key can be recreated if needed, and will be the same
as the original. Therefore, if the key is accidentally dropped (mitigated in SQL 2008 by DDL trigger, see below),
it can be recreated.
http://msdn.microsoft.com/en-us/library/ms366281.aspx
*/
/* ======================================================================================================================================== */
/* If needed, use the following statements to restore the certificate from the backup file taken above */
/*
DROP CERTIFICATE Cert1;
GO
CREATE CERTIFICATE Cert1
FROM FILE = 'C:\SQLBackups2008\Cert1_certificate_backup.cer'
WITH PRIVATE KEY
(
FILE=N'C:\SQLBackups2008\Cert1.pvk',
DECRYPTION BY PASSWORD = N'SQLServerIsCool444@!'
)
*/
/* ======================================================================================================================================== */
/* Multiple asymmetric, certificate, and symmetric keys can be generated per database.
Just be sure and keep any passwords in a safe place so they can be retrieved if needed. They
would be needed to create any new SPROCs, or modify existing ones, since the SPROCS are encrypted
and cannot be scripted out. */
/* Example of non-recreatable Symmetric Key: */
/*
CREATE SYMMETRIC KEY SymmKey1 WITH ALGORITHM=AES_256
ENCRYPTION BY PASSWORD='39eiIke$uY2$1!hBc5RfPm&%rFc3@L'
GO
*/
/* The reason this particular type is non-recreatable:
Symmetric keys such as this use a GUID each time it is created. Therefore, if the symmetric
key is dropped, it cannot be recreated, since the key is created with a brand new GUID each
time. Therefore, another key cannot be created with the exact same values, unless
IDENTITY_VALUE and KEY_SOURCE statements are used with appropriate values, as in the example
of a recreatable Symmetric Key above. */
/* ======================================================================================================================================== */
/* For SQL Server 2008 databases (and up), a DDL trigger can be created to prevent the accidental
deletion of the Symmetric Key. */
-- NOTE: Trigger only works on SQL SERVER 2008 databases (and up)
-- Now create the DDL Trigger that keeps anyone from accidentally deleting the symmetric key
/* To check compatibility level
DECLARE @dbname varchar(100)
DECLARE @compatlvl int
SET @dbname = 'EncryptionTestDB'
SELECT @compatlvl = compatibility_level FROM sys.databases WHERE name = @dbname
PRINT @compatlvl
80 = SQL 2000
90 = SQL 2005
100= SQL 2008
110= Denali
*/
CREATE TRIGGER ddl_tgr_EncryptionTestDB_prevent_Symmetric_Key_Drop --note, not a schema owned object, so no schema identifier
ON DATABASE
AFTER DROP_SYMMETRIC_KEY
AS
BEGIN
SET NOCOUNT ON -- to avoid the rowcount messages
SET ROWCOUNT 0 -- in case the cilent has modified the rowcount
BEGIN TRY
--Note: do not put the name of the trigger in the error message for security reasons
RAISERROR ('Trigger exists to prevent the dropping of the Symmetric Keys. Trigger must be disabled before making any trigger modifications.',16,1)
END TRY
--error handling
BEGIN CATCH
-- rollback the transaction. We do not want the key dropped
IF @@trancount >0
ROLLBACK TRANSACTION
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
--insert into error log if needed
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'ddl_tgr_EncryptionTestDB_prevent_Symmetric_Key_Drop', @ErrorNo, @Severity, @LineNo, HOST_NAME())
DECLARE @ERROR_MESSAGE varchar(8000)
SET @ERROR_MESSAGE=@Message
RAISERROR (@ERROR_MESSAGE,16,1)
END CATCH
END
/* To see the trigger information in the database, use the following: */
-- SELECT * FROM sys.triggers
-- SELECT * FROM sys.trigger_events
USE EncryptionTestDB
GO
CREATE PROCEDURE dbo.xp_usp_Read_Applicant_Rec_With_Spec_SSN
@ssn varchar(2000),
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
WITH ENCRYPTION
AS
DECLARE @numrecs int
DECLARE @stmta nvarchar(4000)
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
SET @stmta = N'OPEN SYMMETRIC KEY SymmKey1 DECRYPTION BY CERTIFICATE Cert1'
EXEC sp_executesql @stmta
SELECT AppID,AppFName,AppMName,AppLName,LTRIM(RTRIM(CONVERT(varchar(2000),DecryptByKey(AppSSN)))) AS AppSSN,LCHost,LCUser,LCDate
FROM Applicant
WHERE LTRIM(RTRIM(CONVERT(varchar(2000),DecryptByKey(AppSSN)))) = @ssn
SET @numrecs = @@rowcount
SET @recn = @numrecs
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
CLOSE SYMMETRIC KEY SymmKey1
--close all symmetric keys
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'xp_usp_Read_Applicant_Rec_With_Spec_SSN', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
CREATE PROCEDURE dbo.xp_usp_Read_Applicant_Rec
@appid int,
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
WITH ENCRYPTION
AS
DECLARE @numrecs int
DECLARE @stmta nvarchar(4000)
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
SET @stmta = N'OPEN SYMMETRIC KEY SymmKey1 DECRYPTION BY CERTIFICATE Cert1'
EXEC sp_executesql @stmta
SELECT AppID,AppFName,AppMName,AppLName,LTRIM(RTRIM(CONVERT(varchar(2000),DecryptByKey(AppSSN)))) AS AppSSN,LCHost,LCUser,LCDate
FROM Applicant
WHERE AppID=@appid
SET @numrecs = @@rowcount
SET @recn = @numrecs
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
CLOSE SYMMETRIC KEY SymmKey1
--close all symmetric keys
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'xp_usp_Read_Applicant_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
CREATE PROCEDURE dbo.xp_usp_Read_All_Applicant_Rec
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
WITH ENCRYPTION
AS
DECLARE @numrecs int
DECLARE @stmta nvarchar(4000)
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
SET @stmta = N'OPEN SYMMETRIC KEY SymmKey1 DECRYPTION BY CERTIFICATE Cert1'
EXEC sp_executesql @stmta
SELECT AppID,AppFName,AppMName,AppLName,LTRIM(RTRIM(CONVERT(varchar(2000),DecryptByKey(AppSSN)))) AS AppSSN,LCHost,LCUser,LCDate
FROM Applicant
ORDER BY AppID
SET @numrecs = @@rowcount
SET @recn = @numrecs
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
CLOSE SYMMETRIC KEY SymmKey1
--close all symmetric keys
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'xp_usp_Read_All_Applicant_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
CREATE PROCEDURE dbo.xp_usp_Create_Applicant_Rec
@appfname varchar(35),
@appmname varchar(35),
@applname varchar(35),
@appssn varchar(2000),
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
WITH ENCRYPTION
AS
DECLARE @numrecs int
DECLARE @stmta nvarchar(4000)
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
BEGIN TRANSACTION
SET @stmta = N'OPEN SYMMETRIC KEY SymmKey1 DECRYPTION BY CERTIFICATE Cert1'
EXEC sp_executesql @stmta
INSERT INTO Applicant (AppFName,AppMName,AppLName,AppSSN)
VALUES (@appfname,@appmname,@applname,EncryptByKey(Key_GUID('SymmKey1'), @appssn))
SET @recn = @@identity
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record added'
SET @recn = 0
END
CLOSE SYMMETRIC KEY SymmKey1
--close all symmetric keys
COMMIT TRANSACTION
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
ROLLBACK TRAN
close all symmetric keys
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'xp_usp_Create_Applicant_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
CREATE PROCEDURE dbo.xp_usp_Update_Applicant_Rec
@appid int,
@appfname varchar(35),
@appmname varchar(35),
@applname varchar(35),
@appssn varchar(2000),
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
WITH ENCRYPTION
AS
DECLARE @numrecs int
DECLARE @stmta nvarchar(4000)
SET NOCOUNT ON
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
BEGIN TRANSACTION
SET @stmta = N'OPEN SYMMETRIC KEY SymmKey1 DECRYPTION BY CERTIFICATE Cert1'
EXEC sp_executesql @stmta
UPDATE Applicant SET
AppFName=@appfname
,AppMName=@appmname
,AppLName=@applname
,AppSSN=EncryptByKey(Key_GUID('SymmKey1'), @appssn)
WHERE AppID = @appid
SET @numrecs = @@rowcount
SET @recn = @numrecs
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record changed'
END
CLOSE SYMMETRIC KEY SymmKey1
--close all symmetric keys
COMMIT TRANSACTION
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@Message varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
ROLLBACK TRAN
close all symmetric keys
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @Message, 'xp_usp_Update_Applicant_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
/* ============================================================================================================== */
USE EncryptionTestDB
GO
-- notice: Return Status value of 1 means OK
-- Run each section, one section at a time
/* ===================================================================================================================================== */
-- Create record for Jeff H Smith
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Create_Applicant_Rec 'Jeff','H','Smith','123-45-6789',@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Create_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage, @rn as RecNum
/* ===================================================================================================================================== */
-- Create record for Steve J Brown
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Create_Applicant_Rec 'Steve','J','Brown','444-55-6666',@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Create_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage, @rn as RecNum
/* ===================================================================================================================================== */
-- Create record for Christopher C Edmunds
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Create_Applicant_Rec 'Christopher','C','Edmunds','555-66-7890',@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Create_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage, @rn as RecNum
/* ===================================================================================================================================== */
-- read all Applicant table records, showing the encrypted value in the AppSSN column
SELECT * FROM Applicant
/* ===================================================================================================================================== */
-- read all Applicant table records, with value decoded
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Read_All_Applicant_Rec @spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Read_All_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage, @rn as RecNum
/* ===================================================================================================================================== */
-- read the record where AppID = 1 (the input parameter value) with value decoded
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Read_Applicant_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Read_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage,@rn as RecNum
/* ===================================================================================================================================== */
-- read the record where SSN = 555-66-7890 (the input parameter value) with value decoded
DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC xp_usp_Read_Applicant_Rec_With_Spec_SSN '555-66-7890',@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT 'xp_usp_Read_Applicant_Rec_With_Spec_SSN' as SPROC, @st as Status, @em as ErrorMessage,@rn as RecNum
/* ================================================================================================= */
--For SQL Server 2008 and up....
--Attempt to drop the Symmetric Key SymmKey1 (assuming the ddl trigger was created from previous script #3)
DROP SYMMETRIC KEY SymmKey1
--See the error message in the AppErrorLog table
SELECT * FROM AppErrorLog
--Now go back and run SPROC xp_usp_Read_All_Applicant_Rec to make sure the symmetric key is still there.
/* ======================================================================= */
/* Test database backups with passwords */
/* ======================================================================= */
/* Back up the database using a password */
BACKUP DATABASE EncryptionTestDB TO DISK='C:\SQLBackups_2008\EncryptionDB_backup_testpassword.bak'
WITH PASSWORD = 'ILikeDonuts'
/* Now edit the .bak file in notepad and look for any unencrypted and encrypted data that is known, like Steve or Jeff.
Then search for one of the SSN's that we used, like 444. */
/* Now delete the database */
/* Now attempt to restore the database without using the password (receives error message)*/
USE master
GO
RESTORE DATABASE EncryptionTestDB FROM DISK='C:\SQLBackups_2008\EncryptionDB_backup_testpassword.bak'
/* Now restore the database using the correct password */
RESTORE DATABASE EncryptionTestDB FROM DISK='C:\SQLBackups_2008\EncryptionDB_backup_testpassword.bak' WITH PASSWORD = 'ILikeDonuts'
/* This user defined function (udf) can be used to calculate the expected output length for encrypted data (using EncryptByKey) based on the key, plaintext length and if a hashed data/column is being used (optional parameter).
If you are using the results of the formula/udf to calculate the size of the column for a table,
I strongly suggest adding 1 or 2 blocks (i.e. 16 bytes) to the expected size to account for possible future changes to algorithms of choice or the stored format.
*
* (c) 2005 Microsoft Corporation. All rights reserved.
*
*************************************************************************/
-- @KeyName := name of the symmetric key.
-- @PTLen := length in bytes of the plain text
-- @UsesHash := if the optional MAC option of EncryptByKey is being using this value must be 1, 0 otherwise
-- returns the expected length in bytes of the ciphertext returned by EncryptByKey using @KeyName symnmetric key
-- and a plaintext of @PTLen bytes in length, either using the optional @MAC parameter or not.
/* NOTE: This UDF is from the white paper "Protect Sensitive Data Using Encryption in SQL Server 2005", by Don Kiely,
a SQL Server Technical Article from Microsoft, published Dec. 2006 */
--SELECT dbo.CalculateCipherLen('SymmKey1',1000,1) as EncryptionLength
USE EncryptionTestDB
GO
CREATE FUNCTION dbo.CalculateCipherLen( @KeyName sysname, @PTLen int, @UsesHash int = 0 )
RETURNS int
as
BEGIN
declare @KeyType nvarchar(2)
declare @RetVal int
declare @BLOCK int
declare @IS_BLOCK int
declare @HASHLEN int
-- Hash length that
SET @HASHLEN = 20
SET @RetVal = NULL
-- Look for the symmetric key in the catalog
SELECT @KeyType = key_algorithm FROM sys.symmetric_keys WHERE name = @KeyName
-- If parameters are valid
if( @KeyType is not null AND @PTLen > 0)
BEGIN
-- If hash is being used. NOTE: as we use this value to calculate the length, we only use 0 or 1
if( @UsesHash <> 0 )
SET @UsesHash = 1
-- 64 bit block ciphers
if( @KeyType = N'R2' OR @KeyType = N'D' OR @KeyType = N'D3' OR @KeyType = N'DX' )
BEGIN
SET @BLOCK = 8
SET @IS_BLOCK = 1
END
-- 128 bit block ciphers
else if( @KeyType = N'A1' OR @KeyType = N'A2' OR @KeyType = N'A3' )
BEGIN
SET @BLOCK = 16
SET @IS_BLOCK = 1
END
-- Stream ciphers, today only RC4 is supported as a stream cipher
else
BEGIN
SET @IS_BLOCK = 0
END
-- Calclulate the expected length. Notice that the formula is different for block ciphres & stream ciphers
if( @IS_BLOCK = 1 )
BEGIN
SET @RetVal = ( FLOOR( (8 + @PTLen + (@UsesHash * @HASHLEN) )/@BLOCK)+1 ) * @BLOCK + 16 + @BLOCK + 4
END
else
BEGIN
SET @RetVal = @PTLen + (@UsesHash * @HASHLEN) + 36 + 4
END
END
return @RetVal
END
go
/* ====================================================================================== */
/* Create Roles and Set Permissions To Test the */
/* ability of normal user to see data and execute SPROCs */
USE EncryptionTestDB
GO
/* ====================================================================================== */
CREATE ROLE EncryptionTestDBUser
GRANT EXECUTE ON dbo.xp_usp_Read_Applicant_Rec TO EncryptionTestDBUser
GRANT EXECUTE ON dbo.xp_usp_Read_Applicant_Rec_With_Spec_SSN TO EncryptionTestDBUser
GRANT EXECUTE ON dbo.xp_usp_Read_All_Applicant_Rec TO EncryptionTestDBUser
GRANT EXECUTE ON dbo.xp_usp_Create_Applicant_Rec TO EncryptionTestDBUser
GRANT EXECUTE ON dbo.xp_usp_Update_Applicant_Rec TO EncryptionTestDBUser
--for reference, but role should not have SELECT capabilities on the data for security reasons (restrict the columns they can see)
--GRANT SELECT ON Applicant TO EncryptionTestDBUser
--allow the role to use the symmetric key in the sprocs
GRANT REFERENCES ON SYMMETRIC KEY::SymmKey1 TO EncryptionTestDBUser;
GO
/*
possible permissions for Symmetric Key:
=======================================
ALTER
CONTROL
REFERENCES
TAKE OWNERSHIP
VIEW DEFINITION
*/
/* ========================================================================================================================= */
--set up the main login
USE MASTER
GO
CREATE LOGIN DBUser
WITH PASSWORD = 'doorjam', DEFAULT_DATABASE=[EncryptionTestDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- set up the user in the database
USE EncryptionTestDB
GO
CREATE USER DBUser FOR LOGIN DBUser
WITH DEFAULT_SCHEMA = dbo
GO
-- add the user to the role
EXEC sys.sp_addrolemember 'EncryptionTestDBUser','DBUser'
GO
/* Now log in to Management Studio as this user (using SQL Authentication and typing the username
and password). Then, run the Data Insert And Testing section,
one section at a time and see how security is implemented. */
August 3, 2011 at 9:51 am
Hi rhlangley your writeup and scripts are really good. I cannot thank you enough for your help. Thanks for the book names too.. I will certainly get one.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply