Create Identical Symmetric Keys on Two SQL Servers

I inherited a classic ASP web application (written in 2012) with a SQL Server back end using TDE (transparent data encryption).  Of course it took a little effort to locate the original password for the certificate and symmetric key.

Now I want a local development copy of the database and therefore I need to create a symmetric key identical to the one on the production server.

Found this reference:

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

To create identical symmetric keys on two different servers

  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. Create a key by running the following CREATE MASTER KEY, CREATE CERTIFICATE, and CREATE SYMMETRIC KEY statements.
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';
    GO
    CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection';
    GO
    CREATE SYMMETRIC KEY [key_DataShare] WITH
        KEY_SOURCE = 'My key generation bits. This is a shared secret!',
        ALGORITHM = AES_256, 
        IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
        ENCRYPTION BY CERTIFICATE [cert_keyProtection];
    GO
    
  4. Connect to a separate server instance, open a different Query Window, and run the SQL statements above to create the same key on the second server.
  5. Test the keys by first running the OPEN SYMMETRIC KEY statement and the SELECT statement below on the first server.
    OPEN SYMMETRIC KEY [key_DataShare] 
        DECRYPTION BY CERTIFICATE cert_keyProtection;
    GO
    SELECT encryptbykey(key_guid('key_DataShare'), 'MyData' )
    GO
    -- For example, the output might look like this: 0x2152F8DA8A500A9EDC2FAE26D15C302DA70D25563DAE7D5D1102E3056CE9EF95CA3E7289F7F4D0523ED0376B155FE9C3
    
  6. On the second server, paste the result of the previous SELECT statement into the following code as the value of @blob and run the following code to verify that the duplicate key can decrypt the ciphertext.
    OPEN SYMMETRIC KEY [key_DataShare] 
        DECRYPTION BY CERTIFICATE cert_keyProtection;
    GO
    DECLARE @blob varbinary(8000);
    SET @blob = SELECT CONVERT(varchar(8000), decryptbykey(@blob));
    GO
    
  7. Close the symmetric key on both servers.
    CLOSE SYMMETRIC KEY [key_DataShare];
    GO
    

Some helpful queries:

USE [mydatabase]
GO
OPEN MASTER KEY
DECRYPTION BY password = ‘mymasterencryptionpassword’
GO

RESTORE MASTER KEY
FROM FILE = ‘c:\temp\encryptedmasterkey.txt’
DECRYPTION BY PASSWORD = ‘myfileencryptionpassword’
ENCRYPTION BY PASSWORD = ‘mymasterencryptionpassword’;
GO
————————-
drop master key;
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘mymasterencryptionpassword’;
GO
CREATE CERTIFICATE PwordCert WITH SUBJECT = ‘Pword Fields’;
GO
CREATE SYMMETRIC KEY sk_pword WITH
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PwordCert;
GO

OPEN SYMMETRIC KEY sk_pword
DECRYPTION BY CERTIFICATE PwordCert;
GO
DECLARE @blob varbinary(8000);

SET @blob = CONVERT(varbinary(8000), (SELECT CONVERT(varchar(8000), decryptbykey(‘0x009E018E58E0244AB768868AFF9D93E701000000726B80281BC0589B5CD49443557090D52423AE00BF35BC49E727C1D5DA30C2472A8FE17AA33D3A1AAAB926010536D9EF’))));
GO

close symmetric key sk_pword;
go

————————–

USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[usp_checkPassword] Script Date: 3/20/2015 11:40:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_checkPassword]

@myusernamenvarchar(50) = NULL,
@mypasswordnvarchar(50) = NULL

AS

OPEN SYMMETRIC KEY sk_pword
DECRYPTION BY CERTIFICATE PwordCert;

SELECT COUNT(*) AS numFound
FROM tblLogin
WHERE myusername= @myusername
AND CONVERT(nvarchar(50),DecryptByKey(mypassword)) = @mypassword
AND intStatus = 1

CLOSE SYMMETRIC KEY sk_pword

————————
OPEN SYMMETRIC KEY sk_pword
DECRYPTION BY CERTIFICATE PwordCert;
go

SELECT top 100

[Id]
,[myname]
,CONVERT(nvarchar(50), DecryptByKey([mypassword])) as [mypassword]
FROM [tblLogins]

go

CLOSE SYMMETRIC KEY sk_pword;
go

————————-

update tblClients
set
strPassword = EncryptByKey(Key_GUID(‘sk_pword’), CONVERT(nvarchar(50),’mypassword’))
where id = 28323

—————————–

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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