SQL 2008 DTSX

The Problem

Earlier today, I was working on setting up DTSX so some end users could run some packages. After loading and testing the packages successfully, the users tried running the package and encountered an interesting error:

SSIS Execution Properties
Failed to open package file “C:\Program Files\Microsoft SQL Server\100\DTS\Packages\dts_filename.dtsx” due to error 0x80070005 “Access is denied.”. This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. ({FFEE8F2F-A0A6-40BE-8CDA-86BEC124F874})

The packages were provided by another vendor so I wasn’t keen on trying to modify things within the packages themselves. I was able to run the packages under my admin account but the end users kept running into the error which lead me to believe that the user needed some special permissions. The users were connecting to this virtual server via remote desktop. While it was a dedicated virtual machine specifically for this project, I really didn’t want to give users admin rights because…well I don’t think that needs to be explained so I hunted around and of course there are no settings for controlling access via permissions in management studio. It was time to take to the interwebs and use my Google-Fu and see what others have found on this error. I found others who had similar errors but none had the exact issue. Some similar errors:

• http://msdn.microsoft.com/en-us/library/aa337083.aspx – This was the closest except that it dealt with remote access which wasn’t the case here. I tried it anyways in case it was the problem.
• http://www.mssqltips.com/tip.asp?tip=1199 – Proxy permissions for SQL agent which is useful to know when creating scheduled jobs.

The Solution

I remembered that SQL Management Studio had issues with accessing files in different locations (i.e. My Documents). With the new security settings in Windows, you may have noticed you need admin rights to add, run, or or modify folders/files in locations like c:\Program Files in Windows 7/2008. I wondered if DTSX used a special permission that allowed it to access files and checked the groups under the Server Manager. I found a group called SQLServerDTSUser$[MachineName]. I added the users who were executing the packages to this group and then checked the permissions on the folder C:\Program Files\Microsoft SQL Server\100\DTS which didn’t have the group listed. I added the group to the folder permissions, tested the package and voila – it worked.