April 5, 2016 at 9:40 pm
Hi Members,
I have below scenario:
SQL Server 2008R2 SP2
These is an SSIS package which application team need to call from an ASP.Net code. This SSIS package requires a runtime variable which will be passed by ASP .Net code. This SSIS package uses user FakeSQLUser (SQL Server Authenticated User) to log onto database do its work.
Step 1
Created a stored procedure to call this SSIS package
[font="Courier New"]CREATE PROCEDURE [dbo].[spFakeName] @User varchar(200)
AS
DECLARE @sqlquery AS VARCHAR(2000)
SET @sqlquery = 'DTExec /SQL "\Fakename" '
SET @sqlquery = @sqlquery + ' /SET \Package.Variables[UserName].Value;^"'+ @User + '^"'
--print @sqlquery
EXEC master..xp_cmdshell @sqlquery
GO
GO[/font]
[/size][/size]
Step 2:
Since I did not want application to call this SP using a sysadmin account. I did below
a) Created a 'FakeDomain\FakeDomainAccount', added it to Sql Server as public, and provided public access to master database.
b) EXEC sp_xp_cmdshell_proxy_account 'FakeDomain\FakeDomainAccount','FakeDomainAccountPass'
c) Grant EXECUTE ON xp_cmdshell TO [FakeDomain\FakeDomainAccount]
d) Added Login FakeSQLUser to master
e) Granted EXECUTE ON xp_cmdshell TO FakeSQLUser
I reckon it should now be able to run xp_cmdshell without providing sysadmin role to FakeSQLUser
But it keeps on failing with
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
What am I doing wrong?
Regards,
Sid
April 6, 2016 at 12:12 am
What happens when you execute xp_cmdshell in the SQL query window using FakeDomain\FakeDomainAccount login ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply