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