DTExec via xp_cmdshell

In my previous post here: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 Michael Entin provides a number of responses to my questions regarding programatic execution of remote SSIS packages.

Having experienced some significant reliability problems with the Microsoft.SqlServer.Dts.Runtime components from an ASP.NET process (the page either times out, or inevitably just stops responding), I have been prototyping the DTExec command option which Michael suggests as being a better approach to remote programability.

So, off I've been prototyping this all day today...

I have a stored procedure that wraps a call to xp_cmdshell which takes the DTS (DTEXEC) params as a big long argument. This scenario would hopefully allow me to call the sproc from an ASP.NET application.

The proc is deployed to a SQL 2005 machine running SSIS (which I now understand is a REQUIREMENT for targetting SSIS "remotely"). The package targets a seperate SQL 2000 machine and includes two connection managers which are set to use SSPI. I use configuration option files to allow for configurable connection manager target/sources.

In this scenario, it does not seem that the DTEXEC command runs in the same context as the caller. and as a result, a peculiar account called MACHINENAME$ is used (where MachineName is literally the name of the SQL 2005 machine). The account authentication fails (obviously) when the package tries to establish a connection to any of the connection managers because MACHINENAME$ does not exist on the connection manager servers.

Based on the following excerpt from the MSDN doc on xp_cmdshell, it would seem that MACHINENAME$ is probably the LOCAL SYSTEM, which is the process tha the SQL Server Service is running under:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Obviously, settting the ENTIRE SQL Server service to run as a fixed account or even a domain account is probably not appropriate for client sites. Any opinion to the contrary is welcome.

In reading Kirk Haselden's walkthrough for setting up a SQL Agent Proxy, this seems incredibly involved. Before I go through this exercise, can anyone validate that this is the way to go for doing SSPI?

A work-around is to use SQL Server Auth for the connection managers and use configuration files to try to obfuscate these details, but my preference would be SSPI/Windows Integrated.

Thanks.

SSIS was designed to interact with SQL Server Agent because it permits packages to be executed within a very secure environment. There are a variety stored procedures specific to SQL Server Agent.

I strongly recommend using SQL Server Agent stored procedures to execute SSIS packages programatically.

|||

Thanks.

Is sp_start_job the way to go then? Will this work for packages stored on the file system or package store, or only MSDB?

Also, do SQL Server Agent proxies have to be setup?

Any additional direction appreciated.

Thanks,

Rick

|||

Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.

Is there anyway to either block or determine the progress or outcome of the job?

Thanks,

Rick

|||

Assert.True wrote:

Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.

Is there anyway to either block or determine the progress or outcome of the job?

Thanks,

Rick

Alerts can be defined to fire when a job starts, is in progress, ends, etc. Then, the sp_add_notification stored procedure can be used to configure the notification type of the alert.