Hello DBForums Members,

Please help me with a solusion 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 Symmatric key from SQLServerONE and restore it in SQLServerTWO. I tried that but it errors out.

Guru's please help with a solution.

Thanks,
Frank