Encrypted columns don't work when deployed

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)

Encrypted columns don't work when deployed

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-11 : 05:14:19

I'm trying the encryption feature in SQL for the first time. I have a problem that when I execute statements on my live server, the column data is no longer returned, implying to me that the values are being encrypted differently to that on my development machine. The encrypted column is a password column in a user table.

Here's the code I executed on BOTH machines when creating the database:


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeReallyReallyLongPa$$word...!!!!'
CREATE CERTIFICATE CPCertificate01 WITH SUBJECT = 'This is my Certificate'
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CPCertificate01
GRANT CONTROL ON CERTIFICATE :: CPCertificate01 TO [mydatabaseloginuser]
GRANT REFERENCES ON SYMMETRIC KEY :: Key TO [mydatabaseloginuser]

This code works perfectly on my development machine and returns a single record:


exec cp_SelectLogin @username=N'MyUserName',@password=N'mypassword!'

It fails however on the live server. The data within the live server's table was copied from my development machine using the Export Wizard - so perhaps this is the problem?

Can anyone advise what's wrong here please?

Kristen
Test

22859 Posts

Posted - 2011-10-11 : 06:02:45

I think you need to export the certificate from the DEV database, and import to the Live database. Creating the "same" certificate on both servers won't make the same encryption keys. I think ... sorry haven't actually tried, but have been reading up about it as I need to do something similar.

This is the "snippet" I have kept in preparation for attempting this task:




-- Copy keys to another server
-->> On Source Server:
USE master
GO
BACKUP CERTIFICATE NorthwindCert
	TO FILE = 'C:\NorthwindCert_File.cer'
	WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk',
	ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd')
GO
-->> copy the certificate and the private key file to the other SQL Server instance
-->> On Target Server:
USE master
GO
-->> NOTE: The password provided here is different from the one you used on the Source Server
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘EnKrYpt3d_P@$$w0rd’
GO
-->> Create a certificate by importing the certificate exported earlier
CREATE CERTIFICATE NorthwindCert
	FROM FILE = ‘C:\NorthwindCert_File.cer’
	WITH PRIVATE KEY (FILE = ‘C:\NorthwindCert_Key.pvk’,
	DECRYPTION BY PASSWORD = ‘mY_P@$$w0rd’);
GO

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-13 : 10:36:08

Thanks for that top tip!

I've tried following through, but obviously I need to remove the existing certificate I created on the live server. When I try this:

drop certificate CPCertificate01

I am getting this error:


Msg 15352, Level 16, State 1, Line 1
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Do you know how I might break these 'links' to the certificate, then recreate them?

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-10-29 : 06:08:35

Just for reference to help with anyone else experiencing this, here's what I did:

1) Create a new user (this automatically uses the new certificate) that matches the old user
2) Update the database data to reference the new user
3) Delete the original user
4) Change the new user's details to match the original user's

Bit of a hack but it worked with minimal fuss...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources