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.
| 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.
|
 |
|
|
|
|
|
|
|
Transfer Encrypted data Between 2 servers