0

I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

use master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'


CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Certificate';


-- Create a symmetric key
CREATE SYMMETRIC KEY FileName
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE BackupCert
GO

OPEN SYMMETRIC KEY FileName
DECRYPTION BY CERTIFICATE BackupCert
GO

UPDATE [TestDB].[dbo].[Order_Customer_tab]
SET [Process_File_Name] = ENCRYPTBYKEY(key_guid('FileName'), [Process_File_Name])
GO

OPEN Master Key decryption by PASSWORD = 'StrongPassword'

select Process_File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(Process_File_Name)) 
 from [TestDB].[dbo].[Order_Customer_tab]

I tried doing above still no luck.

Can anybody point me in the right direction? Any help is greatly appreciated.

Thanks

0

It's possible to move a private key from one server to another, if the symmetric key was created using a certificate. The certificate is what allows you to create the same symmetric key on a different server. Looks like that is your approach so you are off to a good start. You have created a symmetric key, secured with a certificate, to encrypt a column of data. To move a symmetric key to another server, you need to follow the following process:

  1. You need to back up the certificate and move this to the other server. To do this, use the BACKUP CERTIFICATE... WITH PRIVATE KEY command. Note that this creates two files: the certificate itself, and its private key file.

    BACKUP CERTIFICATE sales05 TO FILE = 'c:\storedcerts\sales05cert'
    WITH PRIVATE KEY ( FILE = 'c:\storedkeys\sales05key' , 
    ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );
    
  2. When you create the certificate on the destination server, you specify both the certificate file, and the private key file, along with the password you used to back up the certificate on the source server. Here is the example from MSDN:

    CREATE CERTIFICATE Shipping11 
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer' 
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', 
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
    

Once you have the private key over to the destination server, you can create a symmetric key using the certificate, and decrypt your columnar data using this.

It's not necessary to move the master database key from server to server, however, you do need to have a master database key created on each server. The master key is needed to protect the private keys internally, and to support server bare metal recovery. You don't need the master database key password to copy a certificate. It's the certificate that you need, and you should be able to access this as sysadmin, unless the certificate itself is protected locally, in which case you'd need the certificate's creation password to run the BACKUP operation above.

0

Looks like you did not encrypt the encryption key with the database master key. There has to be a complete chain from the service master key to the encryption key, usually is like this:

service master key -> database master key -> [optional certificate private key] -> encryption symmetric key

You are going to have to open the encryption key using the original password and then add the missing master key encryption.

  • I've edited the code in my original section. Am I missing something important? Thanks, again – user3055603 Jun 30 '15 at 16:20
  • please don't post the code you found on some thread, post what you did. How did you encrypt the data. For sure you did not encrypt the data in master and restore it, did you? – Remus Rusanu Jun 30 '15 at 18:23
  • I did not encrypt the data in the master. I created key and certificate in the master database using above SQL. Do I have to create key and certificate in the [TestDb] database? – user3055603 Jun 30 '15 at 18:45
  • Above SQL uses a master bound key to encrypt data in TestDB database. On backup/restore the key is, obviously, lost, as it stays behind on the old master. Doing such is a Bad Idea. Please read Encrypt a Column of Data. I strongly urge you to not follow posts from forum threads, including this one. Read the product spec and follow the official examples. – Remus Rusanu Jun 30 '15 at 18:54
0

SQL Server column level encryption consists of five types of keys. They are Service Master Key, Database Master Key, Asymmetric key, Certificate and Symmetric key. Whenever you implement column level encryption, a complete hierarchy of keys needs to be created and able to decrypt the other keys along the hierarchy. All keys are encrypted in the system at all times on disk and only decrypted in memory when they are in use. There are many diagrams of the SQL Server key hierarchy, which is based on the ansi X.917 standard. For your specific hierarchy, I'll describe it like this:

  • the symmetric key can only be used after it is decrypted by the private key of the certificate that protects it.
  • The private key of the certificate that protects the symmetric key can only be used if it is decrypted by the database master key that protects it
  • the database master key which protects the private key of the certificate can only be decrypted by the service master key that protects it
  • the service master key which protects the database master key can only be decrypted by the Data Protection API, which is part of the Windows OS, that protects it

When a database master key is created, it is encrypted using two methods. First, it is encrypted by the password that you provided, which in this case is "StrongPassword". Second, it is automatically encrypted by the service master key, which allows you to use the encryption without putting the password in procedures and other code on the server. When you opened the symmetric key, you specified to decrypt it using the private key of the certificate but did not need to specify that the DMK needed to be open to decrypt the private key needed. The system performed that action using the service master key for you. You can see this in the system tables by using the sql statement below:

select 
    name,
    key_guid,
    crypt_type_desc
from sys.symmetric_keys sk
join sys.key_encryptions ke
    on
    sk.symmetric_key_id=ke.key_id
where 
    name ='##MS_DatabaseMasterKey##'

Also, when you create a symmetric key, you can specify the argument key_source, which forms the basis of creating the actual key, but if you don't the database engine will create a random key for you. The symmetric key is protected by the certificate, not a derivative of it. It would be very dangerous if the symmetric key were able to be derived from the certificate or it's private key. The Open Master Key command is redundant since it is already been opened so that the private key from the certificate can be used.

I would also highly advise against using the master database for column level encryption for your user data.

I hope that the above description was clear because I wanted you to understand why you are having a problem before providing the resolution. The problem is that the Service Master Key on your local SQL server instance can't decrypt the Database Master Key. You can fix this in one of three ways. Back up the SMK from production and restore it on your local SQL Server or backup the DMK for the production database and restore it on the database on your local SQL Server or move the command to open the database master key by password before the open symmetric key command. Backing up the DMK would be the better and less impactful choice because restoring an SMK could be resource intensive. I would advise one of the first two resolutions since you don't want to put passwords in your code for security reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.