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