Create Identical Symmetric Keys on Two Servers

Article
06/13/2017

This topic describes how to create identical symmetric keys on two different servers in SQL Server 2014 by using Transact-SQL. 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

In This Topic

Before you begin:

Limitations and Restrictions

Security
To create identical symmetric keys on two different servers, using Transact-SQL

Before You Begin

Limitations and Restrictions

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. The key can have more than one encryption of each type. In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time.
When a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm.

Security

Permissions

Requires ALTER ANY SYMMETRIC KEY permission on the database. If AUTHORIZATION is specified, requires IMPERSONATE permission on the database user or ALTER permission on the application role. If encryption is by certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. Only Windows logins, SQL Server logins, and application roles can own symmetric keys. Groups and roles cannot own symmetric keys.

Using Transact-SQL

To create identical symmetric keys on two different servers

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.

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

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.

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

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

For more information, see the following:

Share via