![]() ![]() That symmetric key is protected (encrypted) by the certificate, the certificate is protected (encrypted) by the database master key and the DMK is protected (encrypted) by the service master key. ![]() Take a look at this link which describes the encryption hierarchy in SQL Server.īasically, what is happening is you're creating a symmetric key on Source and using that to encrypt the column data. The problem here is that your symmetric keys are not the same key, so it cannot be used in the destination database to decrypt the database on another database/instance/machine. I am not getting any error but not getting expected outcome at Destination DB when I try to Decrypt Email at Destination which was Encrypted at Source. SELECT CONVERT(NVARCHAR, DECRYPTBYKEY()) AS DecryptedEmail,* FROM. ![]() SELECT ENCRYPTBYKEY(KEY_GUID('EmailID_Key_01'), ) AS ĭata loaded with encryption to Destination but when I try to Decrypt at destination using below query, It returns as NULL value for column DecryptedEmail OPEN SYMMETRIC KEY EmailID_Key_01 WITH PRIVATE KEY(FILE='C:\Shivendoo\PrivateKey.pvk',ĭECRYPTION BY SYMMETRIC KEY EmailID_Key_01įinally I loaded data from Source using this query: OPEN SYMMETRIC KEY EmailID_Key_01ĭECRYPTION BY CERTIFICATE Certificate_Customer_Data Next I restore Back to Destination DB RESTORE MASTER KEYĭECRYPTION BY PASSWORD = BY PASSWORD = MASTER KEY DECRYPTION BY PASSWORD = MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ĬREATE CERTIFICATE Certificate_Customer_Data FROM FILE = 'C:\Shivendoo\DBCertificate.cer' WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = FILE = 'C:\Shivendoo\PrivateKey.pvk') Then I backup Master Key and Certificate from Source DB to files: BACKUP MASTER KEYĮNCRYPTION BY PASSWORD = CERTIFICATE Certificate_Customer_Data TO FILE = 'C:\Shivendoo\DBCertificate.cer' WITH SUBJECT = 'Customer Sensitive Data' ĮNCRYPTION BY CERTIFICATE Certificate_Customer_Data PASSWORD = CERTIFICATE Certificate_Customer_Data In Source DB I have created Following: CREATE MASTER KEY ENCRYPTION BY I am following these steps but still not able to DECRYPT the data at destination. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |