xp_cmdshell Issue

• siddharthsingh02

  Newbie

  Points: 7

  More actions

  April 5, 2016 at 9:40 pm

  #306248

  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

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  April 6, 2016 at 12:12 am

  #1870047

  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