SSISDB crash – Certificate, asymmetric key or private key data invalid

ferrarif1crash

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)

SSISDB key invalid

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:

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.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish