SQL Server Integration Services (SSIS) is a huge step forward from its predecessor, Data Transformation Services (DTS). One thing that they have in common, though, is that there is no one simple method to allow a database user to execute a SSIS package on demand.
SSIS is THE centerpiece of Microsoft's ETL (Extract-Transform-Load) offering. In lay terms, SSIS is the tool that you use to import data into your SQL Server database from any number of sources, and it's a fantastic tool for doing so... so long as your environment doesn't change often... and you execute the package from Visual Studio or the command prompt... and you're aware of other pain points...
It's when you want to give the end user the ability to import data on an ad hoc basis that SSIS starts to show its limitations.
My current project has a number of identically structured SQL Server databases (one per "department") with different data in each database. The users have a MS-Access "application" that utilize linked tables in order to offer some lightweight viewing and editing of their SQL Server data. The SQL Server database is actually just a middleman, for the data gets sent to another application for heavy-duty processing, and results are saved back to SQL Server for reporting.
The source data in SQL Server needs to be refreshed on an ad hoc basis so that new data only appears when the user knows that it exists and is ready for it. This prevents the ability to schedule SSIS packages for execution - we need the users to start packages on demand. At the present time, this is accomplished by the user contacting a developer to manually execute the package.
My users (think domain accounts) have access to the SQL database, albeit via the Access database on their desktop. I want them to be able to click a button on an Access form that then executes a stored procedure in SQL Server that then executes a SSIS package that then imports data into that database. Simple, right?
SQL Server Agent can execute SSIS packages as job steps. Great! I can use the sp_start_job system stored procedure to kick off a SQL Server Agent job without needing to schedule it. Only, to do this, the user starting the job must be a member of the msdb's SQLAgentUserRole (taking a least-permissive approach). And furthermore, members of this role can only start jobs that they own. And I don't want to make all of my users members of this role, and I certainly don't want to have to define duplicate jobs (one for each user). So I needed to think of a workaround.
But, Jason, the title of this post says nothing about SSIS or SQL Server Agent? Where are you going with this?
Here's where I had the idea of just creating a SQL Server Login, making it a member of the SQLAgentUserRole, and then creating the jobs that I need while connected to the database as that login. In the stored procedure that my users will execute to perform the ad hoc import, I planned to just use the EXECUTE AS LOGIN feature to change context on-the-fly so that the sp_start_job is executed as my SQL Server Login instead of the domain user.
Brilliant, right!??!
Well, that's what I thought, too, and gleefully began to set my plan in motion:
- Create the SQL Server Login
- Assign a Credential to the Login (to allow the Login to perform activity in "the real world" outside of SQL)
- Assign the Login to the SQLAgentUserRole role in msdb
- Create a SQL Server Agent proxy to specify a "real world" principle to execute SSIS packages as
- Grant the SQL Server Login access to use this proxy
- Use this Login to create SQL Agent jobs that execute my SSIS packages
Now we're cooking with fire!
Only, turns out that I found a showstopper with this approach. Sure, I could create a job as this user. Sure, this user can execute their own jobs. Sure, the SSIS package ends up being executed as whatever the proxy was configured to use...
But, one critical requirement in my situation is that I must be able to specify different config files for each job (so that the same SSIS packages can be reused across multiple databases). This is done in Management studio on the "Configurations" tab in the Job Step dialog. It simply opens a "Browse for File" dialog which allows you to select a file from the server's filesystem instead of your own.
As it turns out, the "Browse for File" dialog showed me nothing. No files, no folders, no errors... nothing! If I made the Login a member of the sysadmin server role, I could see files. Without sysadmin, it simply failed.
This was a showstopper.
I fired up the SQL Server Profiler to capture activity, and then reviewed the log to see what was happening when Management Studio displayed the "Browse for File" dialog. Behind the scenes, the extended stored procedure xp_dirtree was being executed.
I confirmed that no rows were returned when my Login executed the following:
xp_dirtree 'c:\somedirectory', 1, 1
(Note: You want to specify a directory instead of, say, the root of C: in order to limit the number of rows returned).
For hours, I played around with different permissions and role memberships to try to find something that would produce rows of data when a SQL Login executed this extended SP. The only thing that I could find that would work was making the Login a member of the sysadmin role - something that simply was not feasible for production.
I also played around with creating a non-admin local Windows account and configuring it within SQL Server as I had the SQL Server Login. This account, while still being restricted from doing almost everything, was indeed able to successfully execute the xp_dirtree proc and get rows of data returned. It was also able to browse the server's filesystem from Management Studio running on my laptop. Awesome!
The fact that the Windows account worked and the SQL Login didn't, even though they were configured almost identically within SQL Server, leads me to believe that there's a bug at play here. Since xp_dirtree is an unsupported and undocumented stored procedure, I doubt that there's any explanation of why this behavior exists. To be clear, though: I'm not trying to use xp_dirtree in any of my code. It's SQL Server Management Studio that uses it for the "Browse for File" dialog, and this is not working as I would expect with a SQL Server Login.
So, my revised solution is to use a local non-admin Windows account in place of the SQL Login. We'll see how well my original plan of attack still holds up, or what the next roadblock will be.