question

FrankBr001 avatar image
FrankBr001 asked

SQL Server Column Encryption

Hello SSC, Please help me with a solution for the below scenario: I have two servers **SQLServerONE** (Vendor) and **SQLServerTWO** (Local). SQLServerONE has SSN,Name,DOB. I want to encrypt the column SSN using SQL encryption and export to a txt along with Name and DOB. I will get the file in SQLServerTWO, restore that into a table and Decrypt the SSN. Steps Performed: **Step 1:** Created Master Key in both the server with same password. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncyTest123!' **Step 2:** Created Certificate and Symmetric key (Both the server same script) CREATE CERTIFICATE DemoEncy WITH SUBJECT = 'SSN'; CREATE SYMMETRIC KEY Sym_SSNEncy WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE DemoEncy; **Step 3** : Created a table in both the server and added value only in SQLServerONE CREATE TABLE [dbo].[Ency_SSN]( [SSN] [varchar](50) NULL, [Ency_SSN] [varbinary](128) NULL ) ON [PRIMARY] Insert into [dbo].[Ency_SSN] (SSN) Values (‘123456789’) Insert into [dbo].[Ency_SSN] (SSN) Values (‘987654321’) Insert into [dbo].[Ency_SSN] (SSN) Values (‘111223333’) **Step 4** : Encrypted the SSN column and added the encrypted value into new column in **SQLServerONE** Open Symmetric key Sym_SSNEncy Decryption by Certificate DemoEncy Update [dbo].Ency_SSN SET Ency_SSN = EncryptByKey(Key_GUID('Sym_SSNEncy'),SSN) GO **Step 5:** Decrypt the value (Ency_SSN) in the same server (just for testing), all works good. Open Symmetric key Sym_SSNEncy Decryption by Certificate DemoEncy Select CONVERT(VARCHAR(50),DECRYPTBYKEY(Ency_SSN)) From Ency_SSN GO OUTPUT : 123456789 987654321 111223333 **Step 6:** I copied the table from SQLServerOne (with the encrypted column) to SQLServerTwo Open Symmetric key Sym_SSNEncy Decryption by Certificate DemoEncy Insert into [SQLServerTWO].[Ency_DB].[dbo].[Ency_SSN] (Ency_SSN,SSN) Select Ency_SSN,SSN From [SQLServerONE].[Ency_Db].[dbo].Ency_SSN GO Step 7 : Tried decrypting Ency_SSN from SQLServerTWO and it was showing null value Open Symmetric key Sym_SSNEncy Decryption by Certificate DemoEncy Select CONVERT(VARCHAR(50),DECRYPTBYKEY(Ency_SSN)) From Ency_SSN GO **Output:** NULL NULL NULL I'm not sure if I have to generate the Master key, Certificate and Symmetric key from SQLServerONE and restore it in SQLServerTWO. I tried that but it errors out. Gurus please help with a solution. Thanks, Frank
encryption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
If it's acceptable in your environment, you might start with EncryptByPassPhrase rather than EncryptByKey - [ https://msdn.microsoft.com/en-us/library/ms190357.aspx][1]. That would remove the key management complexity from the solution and still give you a file that is encrypted during transport. When I need keys to match across servers, I tend to back them up from one server and restore them on the other as you mentioned. If you need help debugging that part of the process, please post the error messages you are seeing. However, I think you'll get satisfactory results using the pass phrase methods. [1]: https://msdn.microsoft.com/en-us/library/ms190357.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

FrankBr001 avatar image
FrankBr001 answered
Hi Kenj, Thanks for your reply. I did think about using the EncryptByPassPhrase but the password is visible if you see the function or procedure. We dont want to expose the password anywhere. That is the reason we are going for Key level encryption. How ever, I was able to make the code work. The algorithm was different and caused the issue. All good now. Thanks guys. Regards, Frank
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.