June 15, 2015 at 3:53 am
Hi,
in my environment I am running the SQL Server agent job.i am getting below message.
Please create a master key in the database or open a master key in the session before performing this operation” error
can any one help me how to resolve to this
June 15, 2015 at 4:00 am
hi,
I have ran the following command
ALTER SERVICE MASTER KEY REGENERATE on the master database again I am getting below error
Msg 15320, Level 16, State 12, Line 1
An error occurred while decrypting master key 'DbName' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable
can any one suggest
June 15, 2015 at 8:37 am
Check the following
USE master
SELECT name FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'
Assuming a DMK exists what password was used, check the encrypted state using this too
SELECT CASE is_master_key_encrypted_by_server
WHEN 0 THEN 'Not encrypted by SMK'
WHEN 1 THEN ' Encrypted by SMK'
END AS 'EncryptedbySMK'
FROM sys.databases
WHERE database_id = db_id('master')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 15, 2015 at 11:07 pm
For this to happen you either would have gone to a lot of trouble to overwrite your master database with another copy from somewhere else, or someone changed the service account in services.msc and so SQL Server can't decrypt the existing master key using the service master key. Service master keys are stored with the accounts the service runs under (not sure of the exact mechanism).
A possible fix is to modify the service account back to what it was, start up SQL Server, make sure it can regenerate the master key, then change your service account back to the new account ONLY IN SQL Server Configuration Manager (as there it will generate a new service master key and re-encrypt the master key with it for you).
If you aren't using the master key for anything then you can use that FORCE parameter which just means it loses anything (and access to anything) that was encrypted by it. Encrypted databases, some certificates, etc (not sure of a complete list).
June 16, 2015 at 3:29 am
the problem is not with the SMK the problem is with a DMK
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 16, 2015 at 5:37 am
Are you sure? The MK is signed by the SMK. If the service account changed SMK changed through services.msc incorrectly then the MK could not be decrypted automatically and so would not be open and it would generate this error or something like it?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply