Share via

  • Article

How to: Create Identical Symmetric Keys on Two Servers

In order to decrypt ciphertext, you need the key that was used to encrypt it. When both encryption and decryption occur in a single database, the key is stored in the database and it is available, depending on permissions, for both encryption and decryption. But when encryption and decryption occur in separate databases or on separate servers, the key stored in one database is not available for use on the second database. This topic shows how to provide a shared symmetric key to databases on two separate servers.

Overview

Creating identical symmetric keys is not difficult. Symmetric keys created with the same KEY_SOURCE, ALGORITHM, and IDENTITY_VALUE key options will be identical. Before creating a symmetric key, you should verify that SQL Server key management mechanisms have been initialized. If they do not already exist, you should create a database master key to enable automatic key management, and a certificate with which to encrypt the symmetric key. Optionally, you can protect the symmetric key with a password. For more information, see CREATE SYMMETRIC KEY (Transact-SQL).

Example

This example creates identical symmetric keys on two servers. The example uses the AES_256 encryption algorithm. The AES encryption algorithms are not supported on Windows XP or Windows Server 2000; you may need to specify another algorithm such TRIPLE_DES.

  1. Create the keys by running the following CREATE MASTER KEY, CREATE CERTIFICATE, and CREATE SYMMETRIC KEY statements on both servers.

    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

  2. Test the keys by first running the OPEN SYMMETRIC KEY statement and the SELECT statement on one 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

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

  4. Close the symmetric key on both servers.

    CLOSE SYMMETRIC KEY [key_DataShare];
GO