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:

http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=15297

I found this blogpost – which guided me to the sollution. A place in his guide – he mentions this code:

1

    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.