Thursday, March 9, 2017

Where is the msdb database for standalone SSIS Servers

By Hường Hana 2:00 AM , Leave a Comment

I am working on a DWH application. The server I was granted access to only has SSIS installed with no database engine. I am getting errors like:

The EXECUTE permission was denied on the object 'sp_ssis_listfolders', database 'msdb', schema 'dbo'.

I need to verify the permissions on the database engine and on the msdb. Is there a way how to find which server is configured to host the msdb database?

2 Answers

Answers 1

Found it!

It is hidden in a configuration file in the installation folder of SSIS. Browse to C:\Program Files\Microsoft SQL Server\120\DTS\Binn then look for the file called MsDtsSrvr.ini.xml. The server name is just sitting there, and can be changed as you wish.

MsDtsSrvr.ini.xml

Answers 2

After reading your comments, You have to go to the windows scheduled task and read the command running the Dtexec utility

The command will look like the following:

C:\..\dtexec /SQL "\Package1" /SERVER "Test\Test" /USER "blabla" /PASSWORD "blabla"

The string after /SERVER keyword is the server name where the packages are located

if the package are not located in a SQL server and are executed from a package file the command will look like:

C:\..\dtexec /F "C:\Packages\Package1.dtsx"

Then go to the package open it and read connection strings from it.

Read more about DtExec utility in these articles:

Info about the exception Thrown

In SQL server, The user account associated with the connection in the Execute Package needs to be granted the db_ssisoperator role in the msdb database, otherwise it can’t find the other package that is being called.

And it might needs also to add yourself to the roles below:

  • db_ddladmin
  • db_ssisadmin

You can find other suggestions in these links:

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment