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

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

Reference

Concepts

Other Resources

Help and Information