Master Key Error

Question

Master Key Error

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
December 16, 2011 at 8:33 am

#253663

There is no Master Key in the database but when I run this statement it gives me an error.
Create Master Key Encryption BY Password = 'password'
Error :Msg 15466, Level 16, State 2, Line 1
An error occurred during decryption.
Any idea ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

It might be a restored database (with encryption) or Master Key might have already been generated by someone outside your knowledge. You can generate it again by forcing but it might lead you to some issues. Please don’t try it on PROD server (unless successful verification on test server).

For More:

http://msdn.microsoft.com/en-us/library/ms186937.aspx

http://msdn.microsoft.com/en-us/library/ms186336.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Has anyone used any encryption on this server before (create master key in any of the databases)?. If so, has the SQL Service account changed since then? If so, then this is a problem with decrypting the service master key.

Have a read through this: http://blogs.msdn.com/b/lcris/archive/2005/07/08/437048.aspx and this http://blogs.msdn.com/b/lcris/archive/2005/09/30/sql-server-2005-a-look-at-the-master-keys-part-2.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

I would of thought you would see a message indicating that the master key already exists, firstly check the results of

select * from sys.symmetric_keys

If you have more than 1 row returned check the result set for a database master key value. Also check the following query

select is_master_key_encrypted_by_server from sys.databases where name = 'master'

If the result of the query above is 1 then the database master key is encrypted by the service master key. Post back your results and we'll take it from there.

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

"Ya can't make an omelette without breaking just a few eggs" 😉