SQL Server 2012 Integration Services Error Creating Catalog Database

This is the error that I got when trying to create the SSIS Catalog Database in SQL Server 2012.

The certificate, asymmetric key, or private key data is invalid. Changed database context to ‘SSISDB’. (Microsoft SQL Server, Error: 15297)

Just a quick background to how I got to the error when creating the SSIS Catalog database in SQL Server 2012.

I was planning to upgrade my current installation of SQL Server 2008 R2 to SQL Server 2012. So in order to do this I got a VM created. I then took across my Model, Master and MSDB Databases. I then restore the Master database, then the Model and finally the MSDB database. There were quite a few more steps but I got my VM in the same state as my current live server was.

I then went ahead and upgraded to SQL Server 2012. Everything looked to be 100% until I tried to create the SSIS Catalog Database in SQL Server 2012.

Below are the steps that I completed in order to finally create my SSIS Catalog Database in SQL Server 2012.

1. The first thing that I did was to change the account for the SQL Server Integration Services 11.0 to the System Account.
2. I then added the System Account to the SQLServerMSSQLUser$SQLServer$InstanceName under the Groups in your Server.

Next after hitting and missing many times I ran the following script in my SQL Server 2012 installation.

I opened SQL Server Management Studio

I created the script below

Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey
FROM Executable File = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'

The reason for creating this Asymmetric Key is because in my SQL Server 2008 R2 database I could not find thisAsymmetric Key in my Master Database.

I then ran the following script below twice to ensure that it would force the Regenerate of the Serivce Master key

ALTER SERVICE MASTER KEY FORCE REGENERATE;

Now what I think might have happened in my case is that when I created and installed my Original SQL Server I had been running the SQL Server as a Domain Account. Yes I know this is best practise and I did change the Service Account back at a later Stage.

So I then ran the following script below

ALTER SERVICE MASTER KEY WITH OLD_ACCOUNT = 'DomainName\User1', OLD_PASSWORD = 'GuessThePassword01010'

The thing to note here is that I had to keep on Guessing the Old_Password until I got the following error below:

Msg 15507, Level 16, State 1, Line 1
A key required by this operation appears to be corrupted.

NOTE: I am not 100% sure if this works or does not work, but in my case when I did run it and completed the following steps I could then create the SSISDB.

After completing the above steps I then restarted my Server.

Once the Server has been restarted go back into SQL Server Management Studio. I then ran the following to create the user account which is used when creating the SSISDB. As you can see this user uses the Asymmetric Key wecreated earlier.

USE [master]
GO

/****** Object:  Login [##MS_SQLEnableSystemAssemblyLoadingUser##]    Script Date: 04/04/2012 15:39:57:PM ******/
CREATE LOGIN [##MS_SQLEnableSystemAssemblyLoadingUser##] FROM ASYMMETRIC KEY [MS_SQLEnableSystemAssemblyLoadingKey]
GO

Once the above user has been created I then went to my Integration Services Catalogs, right clicked and selectedCreate Catalog. I then put in my Password and clicked Ok.

My Integration Services Catalog was created.

NOTE: I then made sure to backup my SSIS Master Key File for the SSISDB

I hope that this might help someone else out if they get the above error. It took me 2 weeks of uninstalling and re-installing, and upgrading to SQL Server 2012 to get this working.