How to: Create Identical Symmetric Keys on Two Servers

Artikkeli
12/03/2008

New: 14 April 2006

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.

Security Note:
If it is necessary to pass the key creation data (KEY_SOURCE, ALGORITHM, and IDENTITY_VALUE) over network connections between the two servers, ensure that the network connections are encrypted. For example, by using SSL or IPSec. To choose and configure the appropriate network encryption method, refer to your operating system documentation.

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.

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

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

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

Close the symmetric key on both servers.

CLOSE SYMMETRIC KEY [key_DataShare];
GO

Change History

Release	History
12 December 2006	Changed content: Corrected the example.
14 April 2006	Changed content: Clarified introduction.

Jaa

How to: Create Identical Symmetric Keys on Two Servers

Overview

Example

See Also

Reference

Other Resources

Help and Information

Change History

Lisäresursseja