Error refreshing encrypted database

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

SQL Server Administration (2005)

Error refreshing encrypted database

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-04-14 : 14:13:25

We are restoring a development database from a backup of our production database. This database is encrypted. Once the database is restored errors are generated on attempts to open the symmetric key. The code that raises the error is

OPEN SYMMETRIC KEY <key> DECRYPTION BY CERTIFICATE <certificate>

The error generated is

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

The server key, master key and certificate were created the same on all environments. Once the backup file was restored to dev, the following statements where executed:

USE [<database>];
GO
OPEN MASTER KEY 
    DECRYPTION BY PASSWORD = <password>;
ALTER MASTER KEY 
    ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

This concept appeared to work just fine during the development and testing stages but not in actual practice. Any suggestions?

Production Environment
A 2 node active/passive failover SQL Server cluster – Windows Server 2003 Enterprise Edition SP2, SQL Server 2005 Enterprise Edition SP2 (9.00.3042.00) - 32 bit.

Development Environment
A standalone SQL Server database server - Windows Server 2003 Standard Edition SP2, SQL Server 2005 Developer Edition SP2 (9.00.3042.00) - 32 bit.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-04-15 : 09:28:37

Shameless bump to see if any encryption gurus are lurking out here today.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-04-15 : 11:48:31

I think we found a solution but would like to validate it against some of you. We had to add one additional statement as outlined here:

USE [<database>];
GO
OPEN MASTER KEY 
    DECRYPTION BY PASSWORD = <password>;
ALTER MASTER KEY 
    DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY 
    ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

I suspect that issuing the ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; statement did not do anything since the server thought the DMK was already encrypted by the SMK. So dropping and readding it back appears to have done the trick. Also, initial tests demonstrate this works and any data encrypted previously can be decrypted just fine.

Any opinions on if this is a viable approach or if there is a risk of data loss?

preinsko
Starting Member

1 Post

Posted - 2010-06-10 : 04:19:49

I am not a guru; but your solution saved me! I was having a problem with asymetric keys and I commented out you extra line. It worked and my @$$ is saved.

I have been trying to figure out this problem for weeks.

Thank you!!

Pamela Reinskou
Dir Web Services
VersusLaw Inc.

LostFromTheStart
Starting Member

2 Posts

Posted - 2010-09-23 : 13:01:42

HELP!!!!
I am having the same issue.
I installed SQL Server on a new server last night. During the install I supplied a domain user and password to use as service accounts. During the install SQL Server failed to start so I changed the services to run under 'Local System' and the install retried and finished. I applied SP3 with out issue.

Now when I try to create a linked server I get the following

error.TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed for LinkedServerLogin ''. (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+LinkedServerLogin&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

An error occurred during decryption.
There is no remote user 'USERNAME IS HERE' mapped to local user '(null)' from the remote server 'LINKED SERVERNAME AND INSTANCE IS HERE'. (Microsoft SQL Server, Error: 15466)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=15466&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

If I try
ALTER SERVICE MASTER KEY force regenerate
I get
The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.
Msg 15209, Level 16, State 1, Line 1
An error occurred during encryption.

I don't need the key, how do I tell sql not to use it or look for it?
I followed the link above but that does not seem to be for Service Master Key

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources