moving cert/key encrypted data server to server

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

SQL Server 2005 Forums

Transact-SQL (2005)

moving cert/key encrypted data server to server

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-02-17 : 15:29:29

I need to encrypt some data on our live server and have started playing with that.

Here's what I have so far:

IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB Master key password!'
END
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'TestCertificate'
)
BEGIN
CREATE CERTIFICATE TestCertificate
WITH
SUBJECT = 'Test Certificate'
END
GO

IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'TestSymmetricKey'
)
BEGIN
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[encrypt_test_enc]') AND type in (N'U'))
DROP TABLE [dbo].[encrypt_test_enc]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[encrypt_test_enc]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[encrypt_test_enc](
[tablepk] [int] NULL,
[data] [varbinary](max) NULL
)
END
GO
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate

INSERT encrypt_test_enc (
tablepk
,data
)
SELECT tablePK
,EncryptByKey(Key_GUID('TestSymmetricKey'),data)
FROM encrypt_test_plain

CLOSE SYMMETRIC KEY [TestSymmetricKey]

Now, so far when I want a copy of the live data and such, I have been using the database publishing wizard because the functionality os management studio is so limited.

But now that I am using certs and keys to encrypt, I don't think it will work. How do I pull my data in such a way that it will be usable?

The live server is on a web host and there is a charge for backup/restores, so that's not an option.

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-02-18 : 10:03:54

Ok, I could export the db once the key and cert is created. Will copying the tables containing encrypted data after that work? That way I would have to do the export and restore of the db only once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources