Encrypted database deployment

I have an encrypeted database that I need to move from one development
environment to another. If I back it up and restore it to the new
environment, I get decryption errors, even though the Master Keys have been
created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD = .
.
How can I move the database to the new environment?
Thanks.This issue is that the password is used to encrypt the database master key -
it isn't the database master key so you now have two different master keys
encrypted with the same password which doesn't help you a lot. If you
haven't already blown away the master key, you can re-encrypt it with the
new service master key like this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>I have an encrypeted database that I need to move from one development
> environment to another. If I back it up and restore it to the new
> environment, I get decryption errors, even though the Master Keys have
> been
> created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD =
> ...
> How can I move the database to the new environment?
> Thanks.|||Thanks. I did still have the password, so ran the below which ran without
error.
However, I still get:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
when I try to open a symmetric key.
Can you help?
Thanks.
"Roger Wolter[MSFT]" wrote:

> This issue is that the password is used to encrypt the database master key
-
> it isn't the database master key so you now have two different master keys
> encrypted with the same password which doesn't help you a lot. If you
> haven't already blown away the master key, you can re-encrypt it with the
> new service master key like this:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>
>|||Did you run a CREATE MASTER KEY in this database before trying the OPEN
operation? If so, you just opened the new key you created - not the key the
symmetric keys were encrypted with. You may have to go back to your backup
that contains the original key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...[vbcol=seagreen]
> Thanks. I did still have the password, so ran the below which ran without
> error.
> However, I still get:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> when I try to open a symmetric key.
> Can you help?
> Thanks.
> "Roger Wolter[MSFT]" wrote:
>|||There's more information on OPEN here:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> Did you run a CREATE MASTER KEY in this database before trying the OPEN
> operation? If so, you just opened the new key you created - not the key
> the symmetric keys were encrypted with. You may have to go back to your
> backup that contains the original key.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...
>|||I restored the database, ran the open and alter commands as below, but still
get the error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
Still looking for a way to get this successfully moved...
"Roger Wolter[MSFT]" wrote:

> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||I'll bow out at this point. This should have worked if the problem was
indeed an issue with your mater key encryption so I assume something else is
going on. Maybe someone smarter than me can see what's happening.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:F60CD778-4E38-4209-8E88-E960CB530579@.microsoft.com...[vbcol=seagreen]
>I restored the database, ran the open and alter commands as below, but
>still
> get the error:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> Still looking for a way to get this successfully moved...
>
> "Roger Wolter[MSFT]" wrote:
>|||I'm also getting the same error now on another database that was working in
this new environment, even when I restore a backup...
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
"Roger Wolter[MSFT]" wrote:

> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||Did you back up all your Service Master Keys and Database Master Keys, like
BOL suggests? If so, you might see if you can successfully restore them
without the FORCE option.
"Gerhard" <acsla@.community.nospam> wrote in message
news:7D9F7228-93F7-48E9-B776-2E90EDB54C0F@.microsoft.com...[vbcol=seagreen]
> I'm also getting the same error now on another database that was working
> in
> this new environment, even when I restore a backup...
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
>
> "Roger Wolter[MSFT]" wrote:
>|||Hello,
The issue might be related to SQL service account. Is the service account
the same on both SQL development/test server instances? If not, you may try
to change the service account to the same domain user to test. The
following link is for your reference:
http://groups.google.com/group/micr...ls/browse_threa
d/thread/8596eefe3ed0edfc/a0979b976b3c7a57?lnk=st&q=Encrypted+database+peter
+yang&rnum=2&hl=en#a0979b976b3c7a57
If you have any update, please feel free to let's know. We look forward to
your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============