Sql-server – how to decrypt one encrypted column with lost password

sql server

one column in sql server 2008 database is encrypted with symmetric key, master key password not found, how to decrypt this column? I create a new database with different name in the same server and transfre that table by generating script. When I tried o run query, it said symmetric key and certificate not found. Error 3102 and 3013

Best Answer

In order to decrypt the column that is encrypted by symmetric key you would have to create the exact same symmetric key on the new database.

Since symmetric keys cannot be backed up, in order to use them on another database you would have to provide them 2 attributes that have to be specified when creating a new symmetric key on a different database to decrypt a column.

Those two attributes are KEY_SOURCE and IDENTITY_VALUE

After you have specified these attributes you need to encrypt that key, with one of the following: password, certificate, another symmetric key, asymmetric key, or some third party provider. In your case is certificate, therefore you need to create a certificate from certificate(original DB) that you supposedly backed up already. Such as:

CREATE  CERTIFICATE NewDBCertificate
FROM FILE = 'E:\OldDBCertificate.crt'
WITH PRIVATE KEY(
FILE = 'C:\OldDBCertificateKey.pvk',
DECRYPTION BY PASSWORD = N'P@$$W0RD')

This DECRYPTION BY PASSWORD is password that you specified when you were making a certificate backup.

And last step is creating an actual symmetric key, with the same KEY_SOURCE and IDENTITY_VALUE as the one on original DB, with certificate(that you just created) specified encryption.

CREATE SYMMETRIC KEY NewSymmetricKey
WITH ALGORITHM = AES_256, --Note that algorithm has to be the same
KEY_SOURCE = 'PassPhrase used in Original Symmetric Key',
IDENTITY_VALUE = 'Phrase used in Original symmetric key'
ENCRYPTION BY CERTIFICATE NewDBCertificate

So in summary,

Find the name of symmetric key and see which certificate is used for encryption
Make sure you have certificate backup somewhere.
Create certificate on a new DB from the backup
Create new symmetric key using the same KEY_SOURCE & IDENTITY_VALUE and encrypt it using the newly created certificate

Related Solutions

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

SQL Server Security – Decrypt Encrypted Columns from Backup

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:

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' );
```
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.

Best Answer

Related Solutions

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

SQL Server Security – Decrypt Encrypted Columns from Backup

Related Question