Question about stored procs, xp_cmdshell, and .NET applications

Question

Question about stored procs, xp_cmdshell, and .NET applications

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

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

Yeah if you can find the example of the windows login that would be great. I assume I would have to log in to the machine that the database server is residing on, create a windows account on that machine, and then have the same account as the proxy account, but I'd like to be sure nonetheless.

To access the database I'm just launching SSMS and connecting to it like I normally would. I don't log on to the machine of the server and connect through there. But either way this isn't what is being used by the .NET app.

The .NET app is just using a connection string that points to the server, and then executing the stored procedure which runs xp_cmdshell. The xp_cmdshell path is referring to a .dtsx file that resides on the same machine as the SQL Server database, although a different drive.

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 2

[font="Arial Black"]WARNING... [/font]the following drops the xp_CmdShell proxy user and a database. Please double check to see if either will damage your system before running the script below.

Here's the script I used to demonstrate how to setup sp_CmdShell for safe usage to the folks at work.

"domainname" was the name of my desktop box but, in a Windows Server environment, it would actually have to be the domain name (IIRC).

You also need to setup the two users on the Windows Server. The "TestDummy" user (represents any user) should have virtually no privs ("Limited Account" according to my Control Panel on my desktop). I made the "SqlCmdUser" a "Computer Administrator" on my desktop, but I'm not sure that level of privilege is necessary and I don't know what they ended up doing at work because I told them I didn't want to know anything about that particular user. Even the DBA's shouldn't have that password. Both users must be Windows users. I'm not sure whether or not you can used just SQL Server Authentication because I've not tried it (yet).

--===== 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 [domainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

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

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

DROP LOGIN [domainname\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 [domainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];

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

GO

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

EXEC sp_helpuser [domainname\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 [domainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

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

EXEC sp_xp_cmdshell_proxy_account domainname\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 [domainname\SqlCmdUser];

GO

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

EXEC sp_helpuser [domainname\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 = 'domainname\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)