Clustered SQL Server 2008 Integration Services Error : “Access denied”

Today i ran into a new problem on a newly installed SQL Server 2008 cluster. Everything seemed to work fine, but it was now time to start using SSIS. Then weird things started to happen.

When connecting to Integration Serivces using Management Studio and the Cluster Name as Server name in the dialog box, I got “Access denied” . Obviously I hat that one coming as I was supposed to connect unsing the Node Name…

But to my surprise I got the same error. Beeing an Enterprise Admin in the domain, Local admin on both cluster nodes and member of the sysadmin role in SQL Server I was quite puzzled. And I started to wonder about how to configure Integration Service security. Reading up a bit it turned otu that there is no such thing however. All the config you can do si configuring the SSIS Service using the usual Windows tools, and there is actually a ini file! Or to be more precise ini.xml file. That file is called MsDtsSrvr.ini.xml and located in C:Program FilesMicrosoft SQL Server100DTSBinn.

I noticed in the file that it refered to the SQL Server instance as “.Instancename”. I changed this to “clustered-sql-servernameinstancename” and restarted SSIS. It worked! Now I could browse the pacages stored in MSDB and it worked as expected. Actually I had some problems editing the file, as the server ran Windows 2008. I had to copy the file, edit in another folder, take away the readonly flag on the Binn folder, rename the original file (And verifying it by an extra click, thanks UAC and Microsoft…) and move the new file to the Binn directory (Once again confirming…)

But all this is probably due to my lack of Windows skills 🙂 One more thing: Remember to edit the ini.xml file on both nodes!