Post reply

Master Key Error

  • 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 ?

  • December 17, 2011 at 5:33 am

    #1422819

    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

  • December 17, 2011 at 6:16 am

    #1422826

    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

  • December 18, 2011 at 2:19 pm

    #1422920

    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" 😉

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

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