While setting up a warm
standby instance for one our job servers, I encountered an issue with
decryption of credential passwords. I
have included the error text from the popup message box below.
An error occurred during decryption. (Microsoft
SQL Server, Error: 15466)
This error occurred either
during creation of a new credential or modification of an existing credential.
In our environment, we
have elected to run all user jobs from a separate server, the job server
referenced above. A second instance has
been brought online and we are configuring them for warm standby for each other. Each instance will have the same
configuration as the other; i.e. jobs, proxies, packages etc. However, the jobs will only be enabled on the
primary node for that job.
In an attempt to get the
two instances in sync, the master and msdb databases from the original node were
restored to the new node. Restoring of
the databases went well and it wasn’t until testing of the jobs to verify the
functionality that the issue was uncovered.
The job failed and logged
the error:
Error authenticating proxy domain\userid,
system error: Logon failure: unknown
user name or bad password.)
user name or bad password.)
Having seen this error in
the past when researching other job failures, I assumed that for some reason
the password had been corrupted during the restore of the master database.
After opening the
credential in question and rekeying the password, the error above was
displayed. I tried again with the same
results. I then decided just to delete the credential and re-add with the
correct password, failure again. Attempting
to add a new credential that had not existed on the server before ended with
the same error.
After the repeated failures,
I opened my browser to see what others had done to resolve this issue. To my surprise, I couldn’t find another case
of this happening. I did find a couple
of mentions of this perhaps pointing to a corrupt Server Master Key for the new
instance.
Since this instance had
been restored from another instance, I tried to import the Service Master Key
from the original instance. This
attempt also failed. When reviewing the
articles above, the mention of the SQL Service Account got me to thinking. What would happen if I changed the service
account on my new instance to match the original instance?
Using SQL Configuration
Manager, I made that change and restarted the instance. Going back to SSMS I was able to create the
proxy with the correct password, and I was able to run the job to success.
However, leaving the
server in this configuration violated our security policy. I hesitantly changed the service account back
to the correct domain id, restarted the instance and then executed the job. Success was once again reported by the job.
Below are the steps that
were taken to resolve the issue:
- Restore master database
- Change SQLService Account to original account from source server
- Restart SQL Services
- Change SQLService Account to correct id for this instance
- Restart SQLServices
I hope that by documenting
the issue, troubleshooting steps taken, and the successful resolution, it may save someone else some time and headaches.
