Sql-server – Not able to create SSISDB catalog

integrationsql serversql server 2014ssis

Getting the error below while trying to create a catalog in sql server 2014 integration services.
Any idea what I missed in installation or anywhere else?

The catalog backup file 'C:\Program Files\Microsoft SQL
Server\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure
the database file exist, and the SQL Server Service account is able to
access it(Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)

Best Answer

I decided to see what happens when you actually click Create catalog in SSMS. There are a host of steps that are performed but the basic premise is that a backup exists at

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak

and as part of the catalog creation, that backup is restored to make the SSISDB.

The pathway under Program Files is going to be protected to prevent accidental of the files in there. The error you are encountering indicates that the person running SSMS does not have access to the file at C:\Program Files\Microsoft SQL Server\120\DTS\Binn

Assuming you're not using runas or something like that to launch SSMS, try opening Windows Explorer (not Internet Explorer) and navigate to that folder. If you can get there, you might have to click Yes to something like "show me the files, I know I'm in a restricted area", then I propose UAC is affecting your ability to install SSISDB.

If that is the case, close out all your SSMS instances. Then, right-click on SSMS and choose "Run as Administrator" That should prevent the authorization popup from tossing a spanner into the works when the catalog installation is happening.

How file system permissions became fouled up is an issue for another site (SuperUser or sysadmin)

I stumbled upon this Connect item that indicates if you're using a different version of SSMS than the underlying SQL Server you've connected to (SSMS 2014 pointed at 2012 installation), the creation can fail. So, verify your 120 (SQL Server 2014) matches your instance. The other possibility in there "To resolve it, I simply ran the SQL Server 2012 setup from the installation media and added the Integration Services feature."

Related Question