xp_cmdshell

Question

xp_cmdshell

SQL_Surfer

SSCrazy Eights

Points: 8318
More actions
October 11, 2012 at 3:47 pm

#283009

A .net app needs to call a stored proc that needs to execute xp_cmdshell.
.Net app connects to sql server with a sql login that is a dbo on the databsase.
This is what I have tried.
1) Enable xp_cmdshell on the server
2) Created a proxy account
3) Mapped a sql login to the proxy account
GRANT exec ON xp_cmdshell TO 'WindDomain\WindosAccount' => Works
GRANT exec ON xp_cmdshell TO 'sqllogin' => doesn't work
Can you guys please help?
SQL server is running under local admin account....Does it has be running under WindDomain\WindosAccount ?
Any other suggestions to make it work?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003961 More actions · Answer 1

SQL_Surfer (10/11/2012)
A .net app needs to call a stored proc that needs to execute xp_cmdshell.
.Net app connects to sql server with a sql login that is a dbo on the databsase.
This is what I have tried.
1) Enable xp_cmdshell on the server
2) Created a proxy account
3) Mapped a sql login to the proxy account
GRANT exec ON xp_cmdshell TO 'WindDomain\WindosAccount' => Works
GRANT exec ON xp_cmdshell TO 'sqllogin' => doesn't work
Can you guys please help?
SQL server is running under local admin account....Does it has be running under WindDomain\WindosAccount ?
Any other suggestions to make it work?

I know this is what you're trying to avoid but I have to say it out loud for anyone else that may be reading this. [font="Arial Black"]Never ever grant the priv to run xp_CmdShell to a user or application. [/font] Grant it only to the proxy which must be a Windows account with a login to SQL Server. Once that's done, make sure the database is owned by "SA" or some special login you've created just for the purpose with "SA" privs. Then add the line "WITH EXECUTE AS OWNER" to the stored proc and Bob's your uncle.

Here's the test code that I use to demonstrate how to do all of this. READ THE COMMENTS!!! I'll also warn that if you allow the users to provide the command to xp_CmdShell that the proc will use, you're asking for trouble. Also make sure that anything provided by the user as a parameter is "DOS Injection Proof". Scan the parameter for things like "&&" etc.

--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works

SELECT '****************************** Making sure the things we need don''t already exist. ******************************';

USE MASTER;

DROP DATABASE MyTester; --BE REAL CAREFUL HERE!!! Drops the database I tested against

EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.

DROP USER [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP USER [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Recreate my test database and the user which only has "public" privs.

-- I believe the DEFAULT_SCHEMA is important here.

SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';

CREATE DATABASE [MyTester];

GO

USE [MyTester];

CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user

GO

--===== This just displays how limited the TestDummy user is

EXEC sp_helpuser [yourdomainname\TestDummy];

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.

-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.

-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!

SELECT '****************************** Building/Granting Proxy user stuff. ******************************';

USE [master];

CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];

EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';

--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...

GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];

GO

--===== This just displays how limited even the SqlCmdUser is!!!!

EXEC sp_helpuser [yourdomainname\SqlCmdUser];

GO

-----------------------------------------------------------------------------------------------------------------------

-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********

-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW

-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"

-- heh... And Bob's your Uncle!

-----------------------------------------------------------------------------------------------------------------------

--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.

-- Keep in mind that, right now, we're signed in as a member of "dbo".

USE [MyTester];

GO

DROP PROCEDURE dbo.GetDirInfo;

GO

CREATE PROCEDURE dbo.GetDirInfo

WITH EXECUTE AS OWNER

AS

EXEC xp_cmdshell 'DIR C:\';

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

;

GO

--===== Give the general puplic privs to run the sproc.

GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC

;

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.

-- Simulate logging in as a user with low privs...

EXECUTE AS LOGIN = 'yourdomainname\TestDummy'

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This works... (which is what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of dbo.GetDirInfo **********'

EXEC dbo.GetDirInfo

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This doesn't... (which is also what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of xp_CmdShell directly **********'

EXEC xp_cmdshell 'DIR C:\'

;

GO

--===== Test complete... go back to normal.

REVERT

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)