Transfer Encrypted data Between 2 servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Transfer Encrypted data Between 2 servers

Author

Topic

m.esteghamat
Starting Member

47 Posts

Posted - 2014-11-23 : 07:43:33

Hi
1- I Have a Table in server 1
2- I Created All Object related for Encryption in server 1
- master key
- Certificate
-Symmetric key
3- I Inserted Table's data To server 2 (and Encrypetd 1 Column of It.)
4- in server 2 I Created The same objects with the same passwords
5- When I select From That table in server 2, That column is null

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1
select
[ElementTitle],[ElementRef],
CONVERT(varchar, DecryptByKey(ElementValue)) as ElementValue,ElementValue,
[ElementType],[LoanInitValue],[LoanRemValue],[Sequence] from fts.dbo.adm_TableP_AllPers

CLOSE SYMMETRIC KEY SymmetricKey1
Please Help
Thank you

m.esteghamat
Starting Member

47 Posts

Posted - 2014-11-25 : 04:52:59

Hi again
This is All My commands. Please help me :
-----------------------------------------------
-- In server 1
-- Step 1 - Create Encryption Objects
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '123'
GO

CREATE CERTIFICATE PayFishCert
ENCRYPTION BY PASSWORD = '123'
WITH SUBJECT = 'Pay Column'

CREATE SYMMETRIC KEY PayFishSymm
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE PayFishCert;
GO
-- Step 2- Backup Encryption Objects
BACKUP MASTER KEY TO FILE = 'f:\EncryptBackup\MASTERKEY'
ENCRYPTION BY PASSWORD = '123';

BACKUP CERTIFICATE PayFishCert TO FILE = 'f:\EncryptBackup\PayFishCert.cer'
WITH PRIVATE KEY (FILE = 'f:\EncryptBackup\PayFishCert.pvk' ,
ENCRYPTION BY PASSWORD = '123',
DECRYPTION BY PASSWORD = '123');
GO

-- Step 3- Encrypt a column in server 1
OPEN SYMMETRIC KEY PayFishSymm
DECRYPTION BY CERTIFICATE PayFishCert
WITH PASSWORD = '123';

Update adm_TableP_AllPers
Set [ElementValue] = EncryptByKey(Key_GUID('PayFishSymm'),ElementValue)

-- Step 4- Check For Decryption in server 1
OPEN SYMMETRIC KEY PayFishSymm
DECRYPTION BY CERTIFICATE PayFishCert
WITH PASSWORD = '123';

select [PersId],[PersCode],[FullName],[Year],[Month],[AccNo],[CostCenter],[WorkLoc],
[ElementTitle],[ElementRef],
CONVERT(VARCHAR, DecryptByKey([Elementvalue])) AS 'ElementValue',
[ElementType],[LoanInitValue],[LoanRemValue],[Sequence] from adm_TableP_AllPers

-- All Things is ok

-- Step 5- Copy Data of this Table To Server 2
Insert into server2.mydb.dbo.adm_TableP_AllPers select * from adm_TableP_AllPers

--but in server 2
-----------------------------------------------
-- In server 2
Use mydb
-- Step 1 - Restore Encryption Objects

--Drop Master Key
restore master key from file = 'e:\EncryptionBackup\MASTERKEY'
decryption by password = '123'
encryption by password = '123'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '123'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '123';
CLOSE MASTER KEY

CREATE CERTIFICATE PayFishCert FROM FILE = 'e:\EncryptionBackup\PayFishCert.cer'
WITH PRIVATE KEY (FILE = 'e:\EncryptionBackup\PayFishCert.pvk',
DECRYPTION BY PASSWORD = '123',
ENCRYPTION BY PASSWORD = '123');
GO

CREATE SYMMETRIC KEY PayFishSymm
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE PayFishCert;

-- Step 2 -- Open Symmetric and Read data
OPEN SYMMETRIC KEY PayFishSymm
DECRYPTION BY CERTIFICATE PayFishCert
WITH PASSWORD = '123';

select [PersId],[PersCode],[FullName],[Year],[Month],[AccNo],[CostCenter],[WorkLoc],
[ElementTitle],[ElementRef],
CONVERT(VARCHAR, DecryptByKey([Elementvalue])) AS 'ElementValue',
[ElementType],[LoanInitValue],[LoanRemValue],[Sequence] from adm_TableP_AllPers

Unfortunatly show Empty Column in ElementValue
I Think this is a tip on backup and restore Encryption Objects.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources