6

Following a restart of a SQL Server 2012 SP3 instance caused by a reboot of the unresponsive host WINTEL server, I have a SQL Agent Log Shipping Copy job (which uses a proxy account to access the NAS file-share where the log backups reside) that is failing with this error:

Error authenticating proxy MyDomain\MyProxyAccount, system error: The user name or password is incorrect.

I have attempted to reapply the correct password to the proxy’s Credential and to recreate a new Credential and both fail with this error:

A key required by this operation appears to be corrupted. (Microsoft SQL Server, Error: 15507)

I’ve read an MSDN page that says:

SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. The Service Master Key is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key.

I’m uncertain how to proceed. Do I have a problem with the Service Master Key? If so, can anyone suggest how to resolve this issue or direct me to further reading online?

Update: I've found entries in the SQL Server log (written when the instance was restarted following the host WINTEL server reboot) that further suggest that the issue lies with the Service Master Key:

Service Master Key could not be decrypted using one of its encryptions. An error occurred during Service Master Key initialization.

I'd still welcome any suggestions on how to fix this...

Improve this question
7
  • Could you do a quick test, add a new credential and see if you receive any errors. If not it may be a localized issue just with that credential of which you could drop and re-create it.
    – Sean Gallardy
    Jan 1, 2017 at 16:58
  • @Sean Gallardy: any attempt to create a new credential on the instance fails with this error: A key required by this operation appears to be corrupted. (Microsoft SQL Server, Error: 15507)
    – SQLServerVillain
    Jan 3, 2017 at 12:15
  • Just a note to say I've updated my question above with some further information at the end.
    – SQLServerVillain
    Jan 3, 2017 at 12:21
  • Does sys.key_encryptions show anything odd (like all 0's, or errors querying it)?
    – Sean Gallardy
    Jan 3, 2017 at 13:57
  • Also, please look at the ring buffer to see if there are any other error messages. It does look like an issue with the SMK, but it doesn't hurt to take an extra look.
    – Sean Gallardy
    Jan 3, 2017 at 16:10

1 Answer 1

Reset to default
1

I am going to attempt to answer my own question (as my experience may help others). Firstly, regarding the issue I experienced, I found this blog helpful (as were the comments from Sean Gallardy above).

As I had no Service Master Key (SMK) backup to restore from, my intention was to regenerate a new SMK using the ALTER SERVICE MASTER KEY REGENERATE with the FORCE option (causing loss of encrypted entities). I was prepared to have to recreate my credential secrets and Linked Server passwords. This instance had no Database Master Keys but, had it done so, I could have opened them using their password and regenerated them by the SMK (thus avoiding data loss).

However, I decided first to see if a simple restart of the SQL Server instance would resolve the issue - and it did! Upon restart, the Service Master Key verified successfully (and I confirmed this by creating a new test credential successfully).

My best guess is that whatever issue prevented the SMK verification previously (causing the "Service Master Key could not be decrypted using one of its encryptions" error log entry) had disappeared. I know that the SMK always has two encryptions: the machine key encryption and the service account encryption (and these provide an either-or decryption method in the case of cluster failover or service account change).

As the host WINTEL server was having issues, I imagine that invalidated the machine key encryption. I can't imagine what might have been invalidating the service account encryption (that account did not change) but whatever it was, it seemed to be a temporary problem. So my best guess is that, upon restart, the SMK was able to self-verify using the service account encryption (and, at that point, create a new machine key encryption).

Improve this answer
1
  • I've now had this same error a second time, on a different SQL Server instance (albeit one with an AlwaysOn Availability Group relationship to the first) and this time a simple instance restart did not fix the issue. I had no choice but to force regenerate the Service Master key thus: ALTER SERVICE MASTER KEY FORCE REGENERATE (having first ensured that I had all the passwords for the credentials present, and also confirmed that no Linked Servers nor Database Master Keys existed).
    – SQLServerVillain
    Jan 18, 2017 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.