CODEWITHSUNDEEP
sql-serverencryption-symmetric
Vivek
Vivek

Reputation: 329

SQL Server symmetric key is lost, how to decrypt the data?

We are in a situation where symmetric key and certificates are deleted, Is there any way can we decrypt the data?

We tried decrypting the data with same script which was used for creating the master key , certificate and symmetric keys.

Thanks Vivek

Upvotes: 0

Views: 3918

Answers (2)

Sumesh KP
Sumesh KP

Reputation: 1

When using symmetric key to encrypt data, it uses a GUID for encryption. The GUID is generated automatically at the time of creatiion of symmetric key. So if u are trying to decrypt the data using newly generated symmetric key (same as old one), it will use the new GUID and as a result you cant decrypt it.

You can see this by using the following query: (Key_Guid is changing)

CREATE SYMMETRIC KEY KeyTest WITH ALGORITHM = TRIPLE_DES  ENCRYPTION BY PASSWORD = 'EncryptPwd'

select * from sys.symmetric_keys

DROP SYMMETRIC KEY KeyTest 

CREATE SYMMETRIC KEY KeyTest WITH ALGORITHM = TRIPLE_DES  ENCRYPTION BY PASSWORD = 'EncryptPwd'

select * from sys.symmetric_keys

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294487

By definition: NO

If it would be possible, it would mean the entire cryptography feature in SQL Server was useless. Can you define what we lost the symmetric key and database certificate means? Your only chance is if your understanding of 'lost' is incorrect and you still have the keys somewhere. SQL Server will refuse to drop keys if there is still data encrypted with them. Also it would worth defining what you understand by 'database certificate'.

Upvotes: 2

Related Questions