SQL Server Security

Test for presence of master key, certificate and symmetric key?

select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%' - Returns 1 if there is a master key

select * from sys.certificates - List of all certificates generated

select count(*) from sys.symmetric_keys - List of symmetric keys generated



Creating symmetric keys and master keys:


CREATE master key Encryption by password = 'Key_Name'

CREATE CERTIFICATE Certificate_Name with subject = 'Subject_Name'

CREATE symmetric key Key_Name with algorithm=DES Encryption by certificate Certificate_Name

'Open Keys Before encrypting/Decrypting
OPEN SYMMETRIC KEY Key_Name
DECRYPTION BY CERTIFICATE Certificate_Name


'Encrypt
select EncryptByKey(Key_GUID('Key_Name'), ValuetoBeEncrypted)

'Decrypt
DecryptByKey(ValuetoBeDecrypted)


Ex :


OPEN SYMMETRIC KEY Key_Name
DECRYPTION BY CERTIFICATE Certificate_Name

DECLARE @encrypted_str varbinary(max)
SET @encrypted_str = EncryptByKey(Key_GUID('Key_Name'), 'Testing')
SELECT @encrypted_str AS Encrypted
DECLARE @decrypted_str varbinary(max)
SET @decrypted_str = DecryptByKey(@encrypted_str)
SELECT CONVERT(VARCHAR, @decrypted_str) AS Decrypted


Result :
Encrypted
0x000B64F4BA1C634C8BB67A7E5E52CD77010000007BD87E72AD10A7FF7B26ADDD2F58C4BB635A1ECCCFDF0E96

Decrypted
Testing


A strange problem is occurred while doing encryption.I did encryption on a string which works fine using a Master Key. But when I did database copy the problem comes.

Each time the copy is performed we get an error while opening the symmetric keys:

"Please create a master key in the database or open the master key in the session before performing this operation."

This is resolved with the following SQL:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SQLAuthority'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Reason:
There are several levels of encryption on SQL Server. First is the Service Master Key, which is a server key. All lower keys are encrypted with this key. Next is a Database Master Key. This key is stored encrypted in the sys.symmetric_keys table in each database that a Master Key has been created in. All Symmetric, and Asymmetric keys keys created in a database are further encrypted with this Master key.

Opening a key means the Database master key is opened, which in turn requires the Service Master Key to be opened to decrypt the Database Master Key, so the user's key can be used.

At a minimum, i would expect the two database servers do not share the same Service Master Key. This can be synchronized with the Backup Service Master Key command, but you should take great care before changing the keys on any server. If you have other data that is encrypted with an old Service Master Key, or any of its descendants, you could lose that data entirely.

Any key called a Master Key will by definition be singular. Just like a backup carries user and role permissions, a database backup/restore will carry its (encrypted) Master Key, rendering the encrypted data unreadable, unless the Service Master Key has not changed.