VanNiekerk


Hi,

Please could someone give me a pointer on how to resolve this no doubt basic problem I am having. I am new to creating DTS and the security is making go around in circles.

I am executing a DTS package using the xp_cmdshell in a stored procedure. This stored procedure is executed from a web application. So when the stored procedure is executed the connection to SQL Server database is not a WINDOWS account and is not an admin role but instead a database user set up with a public role. When I go to execute this stored procedure it fails with the error:

xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.

My stored procedure looks as follows:

CREATE PROC UP_ExecuteDTS
@GlobalVar1 varchar(255), --Global Variable 1
@PkgName varchar(255),

@Server varchar(255) = '(local)',
@ServerUser varchar(255) = Null,

@ServerPWD varchar(255) = Null

AS

declare @cmd nvarchar(4000)
set @cmd = 'DTSRun /S "'+ @Server + '" /N "' + @PkgName + '" ' --/G "{1B29D7A1-B27A-4355-8B02-C58221A6A0BF}" '
set @cmd = @cmd + '/A "FileName":"8"="' + @GlobalVar1 + '" ' + '/W "0" /E '

exec master.dbo.xp_cmdshell @cmd

GO

Is there a way to allow the database user calling the stored procedure to execute the DTS

I was thinking of adding a proxy account but am not too sure how to do this as it is not a Windows account. Does the window account have to exist etc I was thinking of the inserting the lines below before exec the xp_cmdshell

--Code to authenticate User to execute DTS

SET @ServerUser = IsNull(@ServerUser, '')
IF @ServerUser = 'SQLConnectionUser'
BEGIN
EXEC sp_xp_cmdshell_proxy_account @ServerUser, @ServerPWD
END

I would be most grateful if anyone could help.

Thanks

Tracey




Re: DTS and xp_cmdshell

Michael Hotek


I would NOT recommend doing that. You are drilling a hole from your web server, straight through your database server and whatever the SQL Server service account has access to, a user on the website would then be able to do if you enable this.







Re: DTS and xp_cmdshell

richbrownesq

Read this article on proxy accounts.

http://msdn2.microsoft.com/en-us/library/aa260700(SQL.80).aspx

Essentially, you need to run the following statements as a one off. These will mean any non sysadmins will have be able to run SQL Jobs and xp_cmdshell proc under the user.

Code Block

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',

N'YourDomain', -- agent_domain_name

N'YourUser', -- agent_username

N'YourPassword' -- agent password

GO

EXEC msdb..sp_set_sqlagent_properties @sysadmin_only = 0

GO

Be aware- i had a few issues with this and this link was useful:

http://support.microsoft.com/kb/926901

Also, this assumes you're using SQL2k. If you are running SQL2005, this may be better achived by just using the EXECUTE AS clause in your stored procedure statement.

HTH!







Re: DTS and xp_cmdshell

VanNiekerk

Hi,

Thanks for replying, I did think about the security part of it and had another go and came up with an alternative solution. I decided to run the DTS package from my site and therefore I didn't need to alter the SQL Server security as it doesn't go through a stored procedure.

Cheers

Tracey





Re: DTS and xp_cmdshell

VanNiekerk

Hi I came up with an alternative so that I don't have to alter the SQL Server security. I decided the best approach was to try and run the DTS package from my application. I some how got this to work so now I can avoid calling a stored procedure to execute it.

Thanks so much for getting back promptly.

Tracey