Problem:
When I’m trying to the MSDB database on a remote Integration Server (SSIS) using the integration services connection listed in the drop down box on SQL Server 2005 Management Studio. Every connection attempt ends with an ‘Access is Denied’ error message.
Solution:
For all Access Denied related error always ensure to check the relevant user has db_dtsadmin fixed database role rights on that remote server. Refer to the http://msdn2.microsoft.com/en-us/library/aa337083.aspx link where it explains the things to take care about connecting to a remote SSIS server.
Check the following list of configuration settings and status on local and remote servers:
- If the user running under non-admin account it needs to be added to Distributed COM Users group.
- Run %windir%\system32\Com\comexp.msc to launch Component Services
- Expend Component Services\Computers\My Computer\DCOM Config
- Right click on MsDtsServer node and choose properties
- In MsDtsServer Properties dialog go to Security page
- Restart SSIS Service
If you are running under a local user account on your client machine, you can still access remote SSIS server under the following conditions:
- The machine running remote SSIS server should have an account with exactly same name and password as the one you are running under.
- The account should be configured to access SSIS server as described above.
When it comes to the account delegation, the SSIS 2005 instance doesn’t support the scenario when a client SSIS Server and underlying SQL Server are running on 3 different machines. This scenario can emerge when you change the default MsDtsSrvr.ini.xml to point to a remote SQL server (for example when you implement a shared SQL Store or want to separate SQL Server and SSIS Server for security reasons).
]]>