Where is the msdb database for standalone SSIS Servers

Question

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?

Lueck

What is the type of application? where packages are stored? – Subak

Tha packages are by default stored in MSDB which I failed to locate. What do you mean by application type? – Lueck

From where your package are called, .exe file or anything else? – Subak

Scheduled task executing DTExec – Lueck

can you show the scheduled command? – Subak

Answer 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.

Lueck

This is helpful. Congratulations!! – Subak

Answer 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:

• Dtexec Utility MSDN article
• http://www.sqlshack.com/ways-use-execute-sql-server-integration-services-packages/

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:

• https://pacheco.wordpress.com/2011/11/22/situation-an-s/
• http://dbdevs.blogspot.com/2015/02/granting-access-to-ssis-server.html
• http://www.sqlservercurry.com/2009/07/resolving-error-execute-permission.html
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b1421866-1bd6-4978-a04c-75e2f8c7d69a/permission-issue-or-something-else?forum=sqlintegrationservices

Subak