// SSISDB crash – Certificate, asymmetric key or private key data invalid
One of my longterm customers had a database-server crash a while ago. The server would not spin up after reboot. The SSISDB crash also gave problems.
The whole installation including tweeks and other stuff had to be re-attached to a new and fresh installation of SQL Server 2012.
It was s fairly young BI-server, so the damage was not that big after all.
Untill today…
SSISDB crash
The development of SSIS project to maintain the BI sollution had to be deployed to the SSISDB catalog.
I got an error like below:
The certificate, asymmetric key, or private key data is invalid. Changed database context to ‘SSISDB’.
(Microsoft SQL Server, Error: 15297)
Microsoft has defined the error but not yet documentet it – this link is displayed when I press the copy button in the dialog from above and paste into notepad:
I found this blogpost – which guided me to the sollution. A place in his guide – he mentions this code:
ALTER SERVICE MASTER KEY FORCE REGENERATE;
But that this not do the trick – I got an error telling me that I could not force regenerate the key. I tried to remove the ‘FORCE’ command and things started to happen upon execution.
I got a ‘command completed successfully’ and thought that now I could carry on with Gilberts guide. But but – again I ran into troubles.
I ran the above script but another error apeared. The login was allready existing on the server. But when browsing the login folder I then came to a halt. I do not know what caused this, me scripting or the guy reinstating the server from the crash. But the login as mentioned above had changed name – not much – but still enough for me not to notice at first glanse.
The prefix and suffix with ‘##’ were gone from the login. The login had been changed from the correct ‘[##MS_SQLEnableSystemAssemblyLoadingUser##]’ to ‘[MS_SQLEnableSystemAssemblyLoadingUser]’.
I manually changed the login name and added the two ‘##’ in each end of the name – and voila!
Everything is now up and running again.
Conclusion
I got a good guide from Gilbert Quevauvilliers – thank you so much. I may have found out myself – but the time to come to the thought that the login had changed name somehow – that would have been the last thing I would check on the list of – well – alot of things.
I would hope that Microsoft could be a little more explicit in their product help – but as allways it’s a hard prioritized list of things for them to do, and they cannot do it all at once.
I’ve learned a but from this – and hope that You the reader could use this help.