SQL Server – Successfully Using Symmetric Keys across databases

Background

In the database world, Table definitions and SQL Query writing is considered an art.

On the other hand, Encryption, broadly speaking Security, can be experientially placed in the science bucket.

Forward

Seemingly, each new version of SQL Server is augmented with contemporary encryption apparatus to make it more approachable.

Functionality

Here is a quick snapshot of each SQL Server release along with the Encryption tooling that it introduces.

Version Edition Types Granularity Changes Need Additional Requirements
v2005 (all) Granular or Cell Level Encryption  Column Table Level Structural – Change column type to binary(n)
Programmable Change ( encryptbykey, decryptbykey )
 v2008 Enterprise  Transparent Level Encryption  Database
 v2016  Always Encrypted  Enhanced ADO.net library

 

Encryption Hierarchy

Here is Microsoft’s nice layout of Encryption Hierarchy

EncryptionHierarchy

 

Key & Certificate Management

Let us take this opportunity to outline Key and Certificate Management.  By the term management we mean can the entity be backed-up and restored ? Can it be scripted and re-applied ?

Quick Definition

  1. Backup
    • Can we backup the entity
  2. Restore
    • Can we restore the entity
  3. Scriptable
    • Create
      • Can we create the entity via Script
      • Yes, in all cases
    • Generate
      • Can we reverse-generate the creation script

Exercise

After the quick introduction, let us define the actual exercise we will try to tackle in this post…

Outline

Our underlying purpose is to prepare a database for encryption, encrypt selected columns,  decry-pt the encrypted data, and review our results.

Once the above is fully tested, we will enter Phase 2.  In the secondary phase, we will copy data from the primary system into our Stress Testing environment.  And, verify that we have successfully copied the data.

Steps

  1. Primary Database
    • Create a new database ( DBSecure )
    • Create a master database key, if one does not exist
    • Create a certificate, if one does not exist
    • Create a symmetric key, if one does not exist
    • Create table
      • Make sure that you have one or more binary columns that will house the encrypted data
    • Add data
  2. Backup the Keys and Certificates
    • Backup Database Master Key
    • Backup Certificate
  3. Secondary
    • Create a new database ( DBSecurePerf )
    • Restore master database key
    • Restore certificate
    • Re-apply Symmetric Key code creation snippet
    • Recreate table ( created on Primary )
  4. Copy Data from Primary to Secondary
  5. Compare Data in Primary and Secondary
  6. Ensure data in Secondary is accessible

 

 

Passwords

There are a few passwords that we will need.

We generate them through Symantec.  And, they are listed here.

Entity Password
Master Database Password kaPEva2h6PHutre
Certificate Backup Password Dretet8Uhakuj3w

 

Code

Primary Database

Create database

Let us create a new database DBSecure….

 


if db_id('DBSecure') is null
begin

   exec('create database [DBSecure]; ')

end
go

if 
    (
           ( db_id('DBSecure') is null )	
       and not exists
	   (

		select *
		from   sys.databases tblSD
		where  tblSD.state_desc != 'SIMPLE'

	   )
    )
begin
   
   print 'Set database recovery mode to simple ...'
      alter database [DBSecure] set recovery simple
   print 'Database recovery mode to simple'

end
go

Master database Key

Does the database master key exist?

Does the Master Database Key exist?  Please make sure that you change to the contextual database …

Code:
 
use [db-name] 
go; 

/* if symmetric_key_id = 101, 
   then it is database master key 
*/ 

SELECT * 
FROM   sys.symmetric_keys tblSK 
where  tblSK.symmetric_key_id = 101 
go 

Output:

listDatabaseMasterKey

 

Good, nothing was returned for our database ( DBSecure )

 

Create Database master key

Code:

set nocount on
go

declare @commit bit

set @commit = 0

begin tran

    /*
     if symmetric_key_id = 101, 
     then it is database master key
    */
    SELECT 
	  source = 'Before'
	, [database] = db_name()
	, tblSK.*

    FROM   sys.symmetric_keys tblSK

    where  tblSK.symmetric_key_id = 101

    if not exists
	(

	   SELECT 1

	   FROM   sys.symmetric_keys tblSK

	   where  tblSK.symmetric_key_id = 101

	)

   begin

	print 'Database Master key does not exist, '
               + ' and so we are creating one ...'
     
	CREATE MASTER KEY ENCRYPTION 
		BY  PASSWORD = 'kaPEva2h6PHutre'
	;
 
	print 'Database Master key created'


  end

   /*
       if symmetric_key_id = 101
     , then it is database master key
   */
   SELECT 
	  source = 'After'
	, [database] = db_name()
	, tblSK.*

   FROM   sys.symmetric_keys tblSK

   where  tblSK.symmetric_key_id = 101


   if (isNull(@commit, 0) = 0)
   begin

	rollback tran;

   end
   else
   begin

	commit tran;

   end

go

Review Master Key

Let us quickly make sure that our master key has been successfully created.

Code:
 

set nocount on;
go

select 
       source
         = 'sys.symmetric_keys'
     , [database]
	= DB_NAME()
     , tblSK.[name]
     , [owner]
	= user_name(tblSK.principal_id)
     , [symmetricKeyID]
	= tblSK.symmetric_key_id	
     , [keyLength]
	= tblSK.key_length								
     , [algorithm]
	  = tblSK.[algorithm_desc]
     , [createDate]
	  = convert(varchar(30), tblSK.[create_date], 101)			
     , [keyGUID]
	 = tblSK.[key_guid]						

FROM   sys.symmetric_keys tblSK

where  tblSK.symmetric_key_id = 101

Output:

listDatabaseMasterKeyDBSecure

Explanation:

Here are a couple of quick takeaways:

  1. Name
    • ##MS_DatabaseMasterKey##
      • All Database Master Keys will have this name
  2. Symmetric Key ID
    • 101
  3. Key Length
    • 128
  4. Algorithm
    • TRIPLE_DES

Certificate

Create Certificate if it does not exist

We will create a certificate called certEnc and though not necessary, we will add an additional attribute, subject, for better expressiveness.

Sample

   CREATE CERTIFICATE [certEnc]
 	authorization [dbo]    
        WITH SUBJECT = 'Certificate for Encryption' 

 

Programmable

set nocount on
go

while (@@trancount > 0)
begin

   print 'rollback';

   rollback tran;

end
go

declare @certificateName sysname
declare @certificateSubject sysname

declare @log	nvarchar(4000)
declare @FORMAT_CERTIFICATE_DOESNOTEXIST nvarchar(4000)
declare @FORMAT_CERTIFICATE_EXISTS  nvarchar(4000)
declare @FORMAT_CERTIFICATE_CREATING nvarchar(4000)
declare @FORMAT_CERTIFICATE_CREATE_STATEMENT nvarchar(4000)
declare @FORMAT_CERTIFICATE_CREATED nvarchar(4000)

declare @sqlSyntax nvarchar(4000)
declare @sql nvarchar(4000)


declare @commit	bit

set @commit = 0

set @FORMAT_CERTIFICATE_DOESNOTEXIST 
		= 'Certificate (%s) does not exist!'

set @FORMAT_CERTIFICATE_CREATING
		= 'Creating Certificate (%s)...'

set @FORMAT_CERTIFICATE_CREATE_STATEMENT
	= ' (%s)'

set @FORMAT_CERTIFICATE_CREATED
	= 'Created Certificate (%s)'

set @FORMAT_CERTIFICATE_EXISTS 
	= 'Certificate (%s) already exists'

set @sqlSyntax =
		'CREATE CERTIFICATE [%s] '
	      + '	authorization [dbo] '
	      + '   WITH SUBJECT = ''%s'' '

set @certificateSubject = 'Certificate for Encryption'
set @certificateName = 'certEnc'

begin tran

     SELECT 
	  source = 'sys.certificates - Before'
	, [database] = db_name()
	, [certificateName] = tblSC.[name]
	, tblSC.[certificate_id]
	, tblSC.principal_id
	, tblSC.pvt_key_encryption_type
	, tblSC.pvt_key_encryption_type_desc
	, tblSC.issuer_name
	, tblSC.[subject]
	, tblSC.[start_date]
	, tblSC.[expiry_date]
	, tblSC.pvt_key_last_backup_date

    FROM   sys.certificates tblSC


    if not exists
    (

        SELECT 1

         FROM   sys.certificates tblSC

         where  tblSC.name = @certificateName

   )

   begin

	exec master.dbo.xp_sprintf 
	        @log output
	      , @FORMAT_CERTIFICATE_CREATING
   	      , @certificateName

	print @log

	exec master.dbo.xp_sprintf
	       @sql output
	     , @sqlSyntax
	     , @certificateName
	     , @certificateSubject

	set @log = char(9) + @sql

	print @log

	exec ( @sql )

	print 'Certificate created'


   end

   SELECT 
        source = 'sys.certificates - After'
      , [database] = db_name()
      , [certificateName] = tblSC.[name]
      , tblSC.[certificate_id]
      , tblSC.principal_id
      , tblSC.pvt_key_encryption_type
      , tblSC.pvt_key_encryption_type_desc
      , tblSC.issuer_name
      , tblSC.[subject]
      , tblSC.[start_date]
      , tblSC.[expiry_date]
      , tblSC.pvt_key_last_backup_date

   FROM   sys.certificates tblSC


   if (isNull(@commit, 0) = 0)
   begin

      rollback tran

   end
   else
   begin

     commit tran

   end

go

 

Output

Output from creating certificate…

createCertificate

 

Explanation:

  1. Private Key Encryption Type
    • pvt_key_encryption_type = MK
    • pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY
  2. Start Date
    • Date we want certificate activated.  We can specify during creation
    • If not specified, then it is the date the statement was executed
  3. End Date
    • Date we want certificate deactivated.  We can specify during creation
    • If not specified, a year post the start date
  4. Please keep in mind that for our current subject matter (Encryption) the system does not consider Start and End dates.
    • This is a very good thing as we will obviously not want to lose access to our beloved data, just because we did not offer sufficient date ranges

Symmetric Keys

Here is an hard-coded sample:


   CREATE SYMMETRIC KEY [skEnc] 
      authorization [dbo] 	
      with ALGORITHM = AES_256   
   ENCRYPTION BY CERTIFICATE [certEnc] 

Here is an programmatic example:


set nocount on
go

while (@@trancount > 0)
begin

   print 'rollback';

   rollback tran;

end
go

declare @symmetricKey    sysname
declare @certificateName sysname

declare @log	nvarchar(4000)
declare @FORMAT_CERTIFICATE_DOESNOTEXIST nvarchar(4000)
declare @FORMAT_CERTIFICATE_EXISTS nvarchar(4000)
declare @FORMAT_CREATING nvarchar(4000)
declare @FORMAT_CREATE_STATEMENT nvarchar(4000)
declare @FORMAT_CREATED	nvarchar(4000)
declare @sqlSyntax nvarchar(4000)
declare @sql	   nvarchar(4000)


declare @commit	bit

set @commit = 0

set @FORMAT_CERTIFICATE_DOESNOTEXIST 
	= 'Certificate (%s) does not exist!'

set @FORMAT_CREATING
    = 'Creating Symmetric Key (%s) encrypted '
       + ' by Certificate (%s)...'

set @FORMAT_CREATE_STATEMENT = '(%s)'

set @FORMAT_CREATED = 'Created Symmetric Key (%s) '
      + ' encrypted by Certificate (%s)...'

set @FORMAT_CERTIFICATE_EXISTS 
     = 'Certificate (%s) already exists'

set @sqlSyntax =
		'CREATE SYMMETRIC KEY [%s] '
	      + '	authorization [dbo] '
	      + '	with ALGORITHM = AES_256 '
	      + '   ENCRYPTION BY CERTIFICATE [%s] '

set @certificateName = 'certEnc'
set @symmetricKey = 'skEnc'


begin tran

	SELECT 
	        source = 'sys.symmetric_keys - Before'
	      , [database] = db_name()
	      , [name] = tblSK.name
	      , [username] = user_name(tblSK.principal_id)
	      , [keyLength] = [key_length]
	      , [keyLength] = tblSK.algorithm_desc
	      , [createDate] = [create_date]
	      , [keyGUID] =    [key_guid]

	FROM   sys.symmetric_keys tblSK

	where  tblSK.symmetric_key_id not in (101)

	if not exists
	(

	    SELECT 1

	    FROM   sys.symmetric_keys tblSK

	    where  tblSK.symmetric_key_id not in (101)
			
	    and    tblSK.name = @symmetricKey

	)

	begin

            exec master.dbo.xp_sprintf 
		  @log output
		, @FORMAT_CREATING
		, @symmetricKey
 		, @certificateName

	    print @log

	    exec master.dbo.xp_sprintf
		  @sql output
		, @sqlSyntax
		, @symmetricKey
		, @certificateName

	    set @log = char(9) + @sql

	    print @log

	    exec ( @sql )

	    exec master.dbo.xp_sprintf 
		  @log output
		, @FORMAT_CREATED
		, @symmetricKey
  	        , @certificateName

	    print @log


	end


	SELECT 
	      source = 'sys.symmetric_keys - After'
	    , [database] = db_name()
	    , [name] = tblSK.name
	    , [username] = user_name(tblSK.principal_id)
	    , [keyLength] = [key_length]
	    , [keyLength] = tblSK.algorithm_desc
	    , [createDate] = [create_date]
	    , [keyGUID] =    [key_guid]

	FROM   sys.symmetric_keys tblSK

	where  tblSK.symmetric_key_id not in (101)


	if ( isNull(@commit, 0) = 0) 
	begin

	    rollback tran

	end
	else
	begin

	   commit tran

	end



Output:
createSymmetricKey

DDL

Create Table

encryption.FriendAndFamily


if schema_id('encryption') is null
begin

    exec('create schema [encryption] authorization [dbo]')

end
go

if object_id('[encryption].[FriendAndFamily]') is null
begin

   create table [encryption].[FriendAndFamily]
   (

       [id]		bigint not null identity(1,1)
     , [personName]	nvarchar(100)	 not null
     , [email]		nvarchar(60)	 not null
     , [emailEnc]	varbinary(300) null

     , constraint PK_Encryption_FriendAndFamily
	 primary key
	 (
  	    [personName]
	)

   )

end
go


Add Data


use [DBSecure]
go

set noexec off;
set nocount on;
set ansi_padding on;
go

while (@@TRANCOUNT > 0)
begin
	print 'Rollback Tran';
	rollback tran;
end;
go

declare @keyname sysname
declare @keyGUID uniqueidentifier
declare @commit  bit
declare @tblVarFF TABLE
(
    [personName] nvarchar(100)
  , [email]	 nvarchar(60)
)		


set @keyname = 'skEnc'
set @keyGUID = key_guid(@keyname)
set @commit = 0

if @keyGUID is null
begin

	print 'Key_GUID for ' + @keyName + ' is null'
	print 'Exiting!'

	set noexec on;

end

begin tran

   truncate table [encryption].[FriendAndFamily];

   OPEN SYMMETRIC KEY [skEnc]
	   DECRYPTION BY CERTIFICATE  [certEnc];

        insert into @tblVarFF
        ([personName], [email])
        select 'Paul Young', 'paulyoung@yahoo.com'
        union all
        select 'Johnny Cash', 'johnnycash@msn.com'
        union all
        select 'Lola Falana', 'lolafalana@gmail.com'
        union all
        select 'Tory Bishop', 'ToryBishop@outlook.com'
        union all
        select 'Jaime Stevens', 'JaimeStephens@gmail.com'
						
       insert into [encryption].[FriendAndFamily]
       ([personName], [email], [emailEnc])
       select 
	    [personName]
	  , [email]
 	  , ENCRYPTBYKEY( @keyGUID, [email], 0)		
       from  @tblVarFF

		
  CLOSE SYMMETRIC KEY [skEnc]


if (@commit = 0)
begin

   print 'rollback tran'
   rollback tran;
	
end
else
begin

   print 'commit tran'
   commit tran;
	
end
go

set noexec off
go

 

Retrieve Data

Retrieve Data



set nocount on;
set ansi_padding on;
go


OPEN SYMMETRIC KEY [skEnc]
  DECRYPTION BY CERTIFICATE  [certEnc];

  select 
	  [personName]
	, [email]
	, [emailEnc]
	, [emailDecrypted]
	    = cast(DECRYPTBYKEY([emailEnc]) as nvarchar(255))

  from  [encryption].[FriendAndFamily]
		

CLOSE SYMMETRIC KEY [skEnc]

go

 

Output:

retrieveData

 

Configuration

Allow access to Command Line Shell (xp_cmdshell)

As we will like to access the OS Shell and remove previous backup files, we will make sure that xp_cmdshell access is enabled.

Code


use master
go

exec sp_configure 'show advanced options', 1
reconfigure with override

exec sp_configure 'xp_cmdshell',1
reconfigure with override

 Output:
xp_cmdshell

 

Create Folder

Create Backup Folder

 

Create Folder d:\temp\SQLServerKeys\

 


exec master.dbo.xp_cmdshell'If not exist "d:\temp\SQLServerKeys\" mkdir "d:\temp\SQLServerKeys\" '

 

On Source, Backup Keys

 

On Source, Remove previous backup keys

 


set nocount on
go

/*
Create Folder
*/
exec master.dbo.xp_cmdshell'If not exist "d:\temp\SQLServerKeys\" mkdir "d:\temp\SQLServerKeys\" '

/*
Delete Folder Files
*/
exec master.dbo.xp_cmdshell'If exist "d:\temp\SQLServerKeys\DBSecure*.key" del "d:\temp\SQLServerKeys\DBSecure*.key" /s '


 

On Source, Backup Database Master Key


/*
   Backup Master Key 
   https://msdn.microsoft.com/en-us/library/ms174387.aspx
*/
BACKUP MASTER KEY 
     TO FILE = 'd:\temp\SQLServerKeys\DBSecure.MasterKey.key' 
     ENCRYPTION BY PASSWORD = 'Dretet8Uhakuj3w'
GO
 

On Source, Backup Certificate

/*
Backup Certificate
*/
BACKUP CERTIFICATE [certEnc]
 TO FILE = 'd:\temp\SQLServerKeys\DBSecure.Certificate.certEnc.public.key'
 WITH PRIVATE KEY 
 ( 
    FILE = 'd:\temp\SQLServerKeys\DBSecure.Certificate.certEnc.private.key' 
  , ENCRYPTION BY PASSWORD = 'Dretet8Uhakuj3w' 
 );

GO

On target, Restore Keys

Restore Database Master Key


set nocount on
go

use [DBSecurePerf]
go

declare @commit	bit
declare @CHAR_TAB varchar(30)

set @CHAR_TAB = char(9);
set @commit = 1

print 'begin tran'

begin tran

	print ''; 

	select source = 'Before', tblSK.*
	from   sys.symmetric_keys tblSK
	where  tblSK.symmetric_key_id = 101


	/*
		Drop Database Master Key
	*/
	if exists
	   (
		select 1
		from   sys.symmetric_keys tblSK
		where  tblSK.symmetric_key_id = 101
	   )

	begin
		
		print @CHAR_TAB + 'Removing Master Key ...'

		DROP MASTER KEY;

		print @CHAR_TAB + 'Removed Master Key'

	end


	print ''; 
	/*
		If Master Key is not present, then simple overwrite it
	*/
	if not exists
	  (
		select tblSK.*
		from   sys.symmetric_keys tblSK
		where  tblSK.symmetric_key_id = 101
	   )


	begin

	  print @CHAR_TAB + 'Restoring Master Key ....'

          RESTORE MASTER KEY FROM FILE = 'd:\temp\SQLServerKeys\DBSecure.MasterKey.key' 
		-- Password used during MASTER KEY backup
		DECRYPTION BY PASSWORD = 'Dretet8Uhakuj3w'
		-- Original password used during CREATE MASTER KEY ENCRYPTION 
		ENCRYPTION BY PASSWORD = 'kaPEva2h6PHutre'

		print @CHAR_TAB + 'Restored Master Key'

	end

	print ''

	select source = 'After.1', tblSK.*
	from   sys.symmetric_keys tblSK
	where  tblSK.symmetric_key_id = 101

	print ''

	print @CHAR_TAB
           + 'Open Master Key with password....'

	   open master key 
           DECRYPTION BY PASSWORD = 'kaPEva2h6PHutre'

	print @CHAR_TAB 
           + 'Opened Master Key with password'

	print ''

	print @CHAR_TAB
            + 'ALTERing MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ....'

	   ALTER MASTER KEY 
           ADD ENCRYPTION BY SERVICE MASTER KEY;

	print @CHAR_TAB
             + 'ALTERed MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY'

	print ''

	select source = 'After.2', tblSK.*
	from   sys.symmetric_keys tblSK
	where  tblSK.symmetric_key_id = 101


if (@commit = 0)
begin

	print 'rollback tran'
	rollback tran

end
else
begin

	print 'commit tran'
	commit tran;

end
go

Output:

setupSecondary

 

Restore Certificate



set nocount on;
go

declare @commit	bit
declare @CHAR_TAB varchar(30)

set @CHAR_TAB = char(9);
set @commit = 0

print 'begin tran'

begin tran

   print ''; 

   select 
	  source = 'Before'
	, tblSC.name
	, tblSC.pvt_key_encryption_type
	, tblSC.pvt_key_encryption_type_desc
   from   sys.certificates tblSC


   if exists
   (
	select *
	from   sys.certificates
	where  name = 'certEnc'
   )
   begin

	DROP CERTIFICATE [certEnc];

   end

   if not exists
   (
	select *
	from   sys.certificates
	where  name = 'certEnc'
   )
   begin

      print @CHAR_TAB + 'Create Certificate certEnc ....'

      --DROP CERTIFICATE [certEnc]
      CREATE CERTIFICATE [certEnc]
      FROM FILE =           
            'd:\temp\SQLServerKeys\DBSecure.Certificate.certEnc.public.key'
      WITH PRIVATE KEY 
	( 
	  FILE =  
           'd:\temp\SQLServerKeys\DBSecure.Certificate.certEnc.private.key' 
	 , DECRYPTION BY PASSWORD = 'Dretet8Uhakuj3w' 
	);


       print @CHAR_TAB + 'Created Certificate certEnc'

  end

  select 
	  source = 'After'
	, tblSC.name
	, tblSC.pvt_key_encryption_type
	, tblSC.pvt_key_encryption_type_desc
  from   sys.certificates tblSC

  print ''; 

if (@commit = 0)
begin

	print 'rollback tran'
	rollback tran

end
else
begin

	print 'commit tran'
	commit tran;

end

Restore Symmetric Key

As we have no backup for symmetric key, we have to re-create from script.

Please issue the same statement outlined in the create symmetric key statement.

Create DDL

Create Table


use [DBSecurePerf]
go

if schema_id('encryption') is null
begin

   exec('create schema [encryption] authorization [dbo]')

end
go

if object_id('[encryption].[FriendAndFamily]') is null
begin

   create table [encryption].[FriendAndFamily]
   (
          [id]	bigint not null identity(1,1)
	, [personName]		nvarchar(100)	 not null
	, [email]			nvarchar(60)	 not null
	, [emailEnc]		varbinary(300) null

	, constraint PK_Encryption_FriendAndFamily
	    primary key
		(
		  [personName]
		)

	)

end
go


Copy Data

set nocount on;
go

truncate table [DBSecurePerf].[encryption].[FriendAndFamily]
go

set identity_insert  [DBSecurePerf].[encryption].[FriendAndFamily] on
go

insert into [DBSecurePerf].[encryption].[FriendAndFamily]
(
   [id], [personName], [email], [emailEnc]
)
select [id], [personName], [email], [emailEnc]
from   [DBSecure].[encryption].[FriendAndFamily]
go

set identity_insert  [DBSecurePerf].[encryption].[FriendAndFamily] off
go

Review Data

Let us review data on our secondary database:


set nocount on;
set ansi_padding on;
go

OPEN SYMMETRIC KEY [skEnc]
   DECRYPTION BY CERTIFICATE  [certEnc];

   select 
       [DB] = db_name()
     , [personName]
     , [email]
     , [emailEnc]
     , [emailDecrypted]
	= cast(DECRYPTBYKEY([emailEnc]) as nvarchar(255))

   from  [encryption].[FriendAndFamily]
		

CLOSE SYMMETRIC KEY [skEnc]

go

Output:

reviewData
Explanation:

  1. Unfortunately, in our target DB, the decry-pt function failed

Reason De-crypt Failed

De-crypt failed for multi-layered reasons:

  1. If you re-collect, we are not able to backup nor restore symmetric keys

Compare Keys

Compare Symmetric Keys

Let us compare symmetric keys across our two databases.

SQL

Here is the SQL for such a comparison:


set nocount on;
go

select 
     source = 'DBSecure'
   , tblSK.name, tblSK.symmetric_key_id
   , tblSK.key_length, tblSK.algorithm_desc
   , tblSK.[key_guid]
from   [DBSecure].sys.symmetric_keys tblSK
where  symmetric_key_id != 101 
union all
select 
    source = 'DBSecurePerf'
   , tblSK.name, tblSK.symmetric_key_id
   , tblSK.key_length, tblSK.algorithm_desc
   , tblSK.[key_guid]
from   [DBSecurePerf].sys.symmetric_keys tblSK
where  symmetric_key_id != 101 

order by [name], source

 

Output:
compareSymmetricKeys

Explanation:

  1. The consequential difference is the Key_GUID column

What could we have done differently?

To have matching symmetric GUIDs we have to add two additional arguments to our Symmetric Key creation script.

Guide to Changes

  1. Add new symmetric key
  2. Add new column
  3. Encrypt email address with new symmetric key and place generated column in our new column

Create new symmetric Key

Create “fully qualified” symmetric Key.

Fully Qualified Symmetric keys have the following attributes mentioned in there creation:

  • Key Source
  • Identity Value

Here is an hard-coded sample:


   CREATE SYMMETRIC KEY [skEncFQ] 
      authorization [dbo] 	
      with ALGORITHM = AES_256   
         , KEY_SOURCE = 'keySource'   
         , IDENTITY_VALUE = 'KeyIdentityValue'    
   ENCRYPTION BY CERTIFICATE [certEnc] 

Here is an programmatic example:


set nocount on
go

while (@@trancount > 0)
begin

   print 'rollback';

   rollback tran;

end
go

declare @symmetricKey	   sysname
declare @keySource	   sysname
declare @keyIdentityValue  sysname
declare @certificateName   sysname

declare @log		   nvarchar(4000)
declare @FORMAT_CERTIFICATE_DOESNOTEXIST nvarchar(4000)
declare @FORMAT_CERTIFICATE_EXISTS nvarchar(4000)
declare @FORMAT_CREATING nvarchar(4000)
declare @FORMAT_CREATE_STATEMENT nvarchar(4000)
declare @FORMAT_CREATED	 nvarchar(4000)

declare @sqlSyntax	nvarchar(4000)
declare @sql		nvarchar(4000)


declare @commit	bit

set @commit = 1

set @FORMAT_CERTIFICATE_DOESNOTEXIST 
		= 'Certificate (%s) does not exist!'

set @FORMAT_CREATING
		= 'Creating Symmetric Key (%s) encrypted by Certificate (%s)...'

set @FORMAT_CREATE_STATEMENT
	= '(%s)'

set @FORMAT_CREATED
	= 'Created Symmetric Key (%s) encrypted by Certificate (%s)...'

set @FORMAT_CERTIFICATE_EXISTS 
	= 'Certificate (%s) already exists'

/*
  https://msdn.microsoft.com/en-us/library/ms187798.aspx
*/

set @sqlSyntax =
		   'CREATE SYMMETRIC KEY [%s] '
		 + '	authorization [dbo] '
		 + '	with ALGORITHM = AES_256 '
		 + '  , KEY_SOURCE = ''%s'' '
		 + '  , IDENTITY_VALUE = ''%s'' '
		 + '   ENCRYPTION BY CERTIFICATE [%s] '

set @certificateName = 'certEnc'
set @keySource = 'keySource'
set @keyIdentityValue = 'KeyIdentityValue'
set @symmetricKey = 'skEncFQ'


begin tran

	SELECT 
	    source = 'sys.symmetric_keys - Before'
	  , [database] = db_name()
	  , [name] = tblSK.name
	  , [username] = user_name(tblSK.principal_id)
	  , [keyLength] = [key_length]
	  , [keyLength] = tblSK.algorithm_desc
	  , [createDate] = [create_date]
	  , [keyGUID] =    [key_guid]

	FROM   sys.symmetric_keys tblSK

	where  tblSK.symmetric_key_id not in (101)

	if not exists
		(

		  SELECT  tblSK.*

		  FROM   sys.symmetric_keys tblSK

		  where  tblSK.symmetric_key_id not in (101)
			
		  and    tblSK.name = @symmetricKey

		)

	begin

	   exec master.dbo.xp_sprintf 
	         @log output
	       , @FORMAT_CREATING
	       , @symmetricKey
   	       , @certificateName

	   print @log

	  exec master.dbo.xp_sprintf
		 @sql output
		, @sqlSyntax
		, @symmetricKey
		, @keySource
		, @keyIdentityValue
		, @certificateName

	 exec master.dbo.xp_sprintf 
	        @log output
	      , @FORMAT_CREATE_STATEMENT
   	      , @sql

	 set @log = char(9) + @sql

	 print @log

	 exec ( @sql )

	 exec master.dbo.xp_sprintf 
	       @log output
	     , @FORMAT_CREATED
	     , @symmetricKey
   	     , @certificateName

	 print @log


     end


     SELECT 
	    source = 'sys.symmetric_keys - After'
	  , [database] = db_name()
	  , [name] = tblSK.name
	  , [username] = user_name(tblSK.principal_id)
	  , [keyLength] = [key_length]
	  , [keyLength] = tblSK.algorithm_desc
	  , [createDate] = [create_date]
	  , [keyGUID] =    [key_guid]

    FROM   sys.symmetric_keys tblSK

    where  tblSK.symmetric_key_id not in (101)


    if ( isNull(@commit, 0) = 0) 
    begin

	rollback tran

    end
    else
    begin

	commit tran

    end


 

DDL

Adding new column

Add new column to emailEncFQ to our table [encryption].[FriendAndFamily].


if schema_id('encryption') is null
begin

  exec('create schema [encryption] authorization [dbo]')

end
go

if object_id('[encryption].[FriendAndFamily]') is not null
begin

  if not exists
  (
    select *
    from   sys.columns tblSC
    where  object_id
               = object_id('[encryption].[FriendAndFamily]')
    and    name = 'emailEncFQ'
  )
  begin

  print 'Add emailEncFQ to [encryption].[FriendAndFamily]..'

  alter table [encryption].[FriendAndFamily]
   add [emailEncFQ] varbinary(300) null

  print 'Added emailEncFQ to [encryption].[FriendAndFamily]'

  end
  else
  begin

   print 'Skipped Adding emailEncFQ to [encryption].[FriendAndFamily]'

  end


end
go


DML

On Primary, Populate new column



use [DBSecure]
go

set nocount on;
set ansi_padding on;
go

declare @keyname sysname
declare @keyGUID uniqueidentifier

declare @keynameFQ sysname
declare @keyGUIDFQ uniqueidentifier

declare @tblVarFF TABLE
(
	  [personName] nvarchar(100)
	, [email]	   nvarchar(60)
)		


set @keyname = 'skEnc'
set @keyGUID = key_guid(@keyname)

set @keynameFQ = 'skEncFQ'
set @keyGUIDFQ = key_guid(@keynameFQ)

if @keyGUID is null
begin

	print 'Key_GUID for ' + @keyName + ' is null'
	print 'Exiting'

	set noexec on;

end

truncate table [encryption].[FriendAndFamily];


  OPEN SYMMETRIC KEY [skEnc]
     DECRYPTION BY CERTIFICATE  [certEnc];

  OPEN SYMMETRIC KEY [skEncFQ]
     DECRYPTION BY CERTIFICATE  [certEnc];


  insert into @tblVarFF
  ([personName], [email])
  select 'Paul Young', 'paulyoung@yahoo.com'
  union all
  select 'Johnny Cash', 'johnnycash@msn.com'
  union all
  select 'Lola Falana', 'lolafalana@gmail.com'
  union all
  select 'Tory Bishop', 'ToryBishop@outlook.com'
  union all
  select 'Jaime Stevens', 'JaimeStephens@gmail.com'
						
  insert into [encryption].[FriendAndFamily]
  (
      [personName]
    , [email]
    , [emailEnc]
    , [emailEncFQ]				
  )
  select 
      [personName]
    , [email]
    , ENCRYPTBYKEY( @keyGUID, [email], 0)		
    , ENCRYPTBYKEY( @keyGUIDFQ, [email], 0)							
  from  @tblVarFF

  -- select * from [encryption].[FriendAndFamily]

  CLOSE SYMMETRIC KEY [skEnc]
  CLOSE SYMMETRIC KEY [skEncFQ]

go

On Primary, Copy data from Primary to Secondary


use [DBSecurePerf]
go

set nocount on;
go

truncate table [DBSecurePerf].[encryption].[FriendAndFamily]
go

set identity_insert [DBSecurePerf].[encryption].[FriendAndFamily] on
go

insert into [DBSecurePerf].[encryption].[FriendAndFamily]
(
  [id], [personName], [email], [emailEnc],[emailEncFQ]
)
select 
  [id], [personName], [email], [emailEnc],[emailEncFQ]
from [DBSecure].[encryption].[FriendAndFamily]
go

set identity_insert  [DBSecurePerf].[encryption].[FriendAndFamily] off
go


Read new column


set nocount on;
set ansi_padding on;
go

OPEN SYMMETRIC KEY [skEnc]
	DECRYPTION BY CERTIFICATE  [certEnc];

OPEN SYMMETRIC KEY [skEncFQ]
	DECRYPTION BY CERTIFICATE  [certEnc];


  select 
	  source = 'sys.openkeys'	
	, [database] = db_name()
			, tblSOK.*
   from   sys.openkeys tblSOK

   select 
	  [DB] = db_name()
	, [personName]
	, [email]
	, [emailEnc]
	, [emailDecrypted]
	 = cast(DECRYPTBYKEY([emailEnc]) as nvarchar(255))
	, [emailDecryptedFQ]
	 = cast(DECRYPTBYKEY([emailEncFQ]) as nvarchar(255))

	from  [encryption].[FriendAndFamily]
		

CLOSE SYMMETRIC KEY [skEnc]

CLOSE SYMMETRIC KEY [skEncFQ]

go

 

Output:

retrieveDataNewColumn

Explanation:

  1. From the screen shot above, we can see that if we fully qualify our symmetric key during creation, we are able to de-crypt the data
    • The differences is displayed in emailDecrypted & emailDecryptedFQ

Compare Keys

Compare Symmetric Key

Let us re-visit symmetric key creation.

Code


set nocount on;
go

select 
    source = 'DBSecure'
  , tblSK.name, tblSK.symmetric_key_id
  , tblSK.key_length, tblSK.algorithm_desc, tblSK.[key_guid]
from   [DBSecure].sys.symmetric_keys tblSK
where  symmetric_key_id != 101 
union all
select 
    source = 'DBSecurePerf'
  , tblSK.name, tblSK.symmetric_key_id
  , tblSK.key_length, tblSK.algorithm_desc, tblSK.[key_guid]
from   [DBSecurePerf].sys.symmetric_keys tblSK
where  symmetric_key_id != 101 

order by [name], source

Output

compareSymmetricKeys_v2

Explain:

  1. For Encryption Key skEnc, the Key GUID is not same  (  for databases DBSecure & DBSecurePerf )
  2. For Encryption Key skEncFQ, the key GUID is same (  for databases DBSecure & DBSecurePerf )

References

Certificate

 

Cryptography

 

Transparent Data Encryption ( TDE )

 

Always Encrypted


Backups

 

Q/A

 

Blogs – Creating identical symmetric keys

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s