December 9, 2009 at 8:03 am
I created a MK and a Certificate to encrypt data and it works fine on the development server but once I moved the DB to the production server decryptbycert returns null.
Any idea?
December 14, 2009 at 3:02 am
fjmorales (12/9/2009)
I created a MK and a Certificate to encrypt data and it works fine on the development server but once I moved the DB to the production server decryptbycert returns null.Any idea?
I hope you've taken a backup of Service Master Key also and restored the same on the Prodn. along with the certificate?
If not, kindly restore the Service Master key on prodn. and then again restore the certificate on prodn.
& then Try again.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
December 14, 2009 at 4:46 am
I hope you've taken a backup of Service Master Key also and restored the same on the Prodn. along with the certificate?
If not, kindly restore the Service Master key on prodn. and then again restore the certificate on prodn.
& then Try again.
I think that you are mixing master key and service key. Service master key is created once when the server is installed, the DBA has no control on it and there is only one service master key per instance. Database Master key is created at the database level by the DBA and it is encrypted by both – the service master key and the password that was supplied the DBA that created the database master key. When you move the database to a different instance, the new instance has a different service master key then the original instance, so you can’t use it to open the database master key. In order to be able to use the database master key in the new instance, you need to “introduce” it to the new service key. You do that by opening the database master key with the original password that was supplied when it was created. Then you add a new encryption to the key that is based on the service master key:
open master key decryption by password = 'WriteYouOriginalPasswordHere'
alter master key add encryption by service master key
close master key
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2009 at 5:34 am
Is this done once?
December 14, 2009 at 7:22 am
Yes it is done only once. The fact that you ask this question shows that you didn’t understand my explanation, so I’ll try to explain in another way. When a database master key is created, the server saves 2 versions of key. One version is encrypted by the master service key and by default is used by the server. The second version is encrypted by the password that you supply to the server when you create the database master key. This version usually is not used. When you move your database into a different environment (production in your case), the new server has a different master service key. Since it is not the service key that was used to encrypt the database’s master key, it also can’t be used in order to open the database’s master key. This is where you should use the version that was encrypted with your password. You need to open the master key using your password, then encrypt it using the new service key and close it. After you do that, the database’s master key can work with the master service key, so you don’t need to do it again.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2009 at 7:31 am
Did it and it worked. Thanks for the help and the share of knowledge.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply