Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516»»»

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 5:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
Here's the code example that I promised. The two user names for this example must be created as Windows Users from the Windows admin tools. Both users are simple users WITHOUT Windows Admin privs. One is there for the correct type of xp_CmdShell proxy and the password should be protected from non-DBA users as should any other password used by a system even though it only has PUBLIC privs. The other user login is just for test purposes. The names of both will be painfully apparent.

The last section of this code is the real "meat" of the code. If you first run all of the other sections to set things up correctly and then run each commented subsection in that last section one at a time, you'll see that the TestDummy user can execute a stored procedure that uses xp_CmdShell but cannot execute xp_CmdShell directly.

This also gives a hint on how to lockdown an entire system. NO ONE but DBAs need to have or should have SA privs. Even the proxy created in the code below has only PUBLIC privs for a server/database role.

Note that you will have to make some changes in the following code. For example, you'll need to replace "yourdomainname" with your domain name (machine name if you're doing this from a desktop box with the Developer Edition).

--===== 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1368320
Posted Thursday, October 4, 2012 6:00 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:41 AM
Points: 706, Visits: 3,021
Jeff, I (and many others) have said it before, and I'll say it again: your generosity is much appreciated! Thanks, I'm looking forward to digging through this!

Rich
Post #1368327
Posted Thursday, October 4, 2012 8:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
rmechaber (10/4/2012)
Jeff, I (and many others) have said it before, and I'll say it again: your generosity is much appreciated! Thanks, I'm looking forward to digging through this!

Rich


Thank you for the kind words, Rich. When you've completed your look-see, I'd love to hear your feedback on the method.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1368801
Posted Saturday, October 6, 2012 4:34 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:41 AM
Points: 706, Visits: 3,021
Hi, Jeff, thanks very much for digging up and posting this. I think I follow the logic here, but I'm getting errors.

First off, I did enable xp_cmdshell on the server. Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine.

But, when I run the first test, it errors out trying to run the stored proc as my TestUser. In fact, I cannot run the proc when executing as sysadmin!

When run as sysadmin:
--	Succeeds (not in Jeff's original post):
use master;
exec xp_cmdshell 'dir c:\';

-- Fails (even though we are executing as sysadmin)
use MyTester;
exec GetDirInfo;

The returned error on the second part is:
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'.

I'm running this on a home machine. Any ideas?

Thanks,
Rich
Post #1369477
Posted Sunday, October 7, 2012 7:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
rmechaber (10/6/2012)
Hi, Jeff, thanks very much for digging up and posting this. I think I follow the logic here, but I'm getting errors.

First off, I did enable xp_cmdshell on the server. Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine.

But, when I run the first test, it errors out trying to run the stored proc as my TestUser. In fact, I cannot run the proc when executing as sysadmin!

When run as sysadmin:
--	Succeeds (not in Jeff's original post):
use master;
exec xp_cmdshell 'dir c:\';

-- Fails (even though we are executing as sysadmin)
use MyTester;
exec GetDirInfo;

The returned error on the second part is:
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'.

I'm running this on a home machine. Any ideas?

Thanks,
Rich


Let the troubleshooting begin. Sorry for the obvious questions but we have to start somewhere. This all works on every system I've tried it on (2005 and 2008 including servers and my home system) and I'm shooting in the dark on your system because I can't actually see it.

1. When you say you created the logins, did you create the SqlCmdUser and TestDummy user as Windows users in Windows first?

2. Also, you say you "Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine", Did you run the script in the order given?

3. Did you make any changes to the script other than the domainname?

4. Really stupid question but have to ask, are you sure you ran the whole script up to...
-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can...

5. When you ran as the user that has SA privs, are you sure you were executing as that user and not the TestDummy? Did you try REVERT before you ran the test as the SA user?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369524
Posted Monday, November 5, 2012 6:19 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:41 AM
Points: 706, Visits: 3,021
Jeff Moden (10/7/2012)
rmechaber (10/6/2012)
Hi, Jeff, thanks very much for digging up and posting this. I think I follow the logic here, but I'm getting errors.

First off, I did enable xp_cmdshell on the server. Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine.

But, when I run the first test, it errors out trying to run the stored proc as my TestUser. In fact, I cannot run the proc when executing as sysadmin!

When run as sysadmin:
--	Succeeds (not in Jeff's original post):
use master;
exec xp_cmdshell 'dir c:\';

-- Fails (even though we are executing as sysadmin)
use MyTester;
exec GetDirInfo;

The returned error on the second part is:
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'.

I'm running this on a home machine. Any ideas?

Thanks,
Rich


Let the troubleshooting begin. Sorry for the obvious questions but we have to start somewhere. This all works on every system I've tried it on (2005 and 2008 including servers and my home system) and I'm shooting in the dark on your system because I can't actually see it.

1. When you say you created the logins, did you create the SqlCmdUser and TestDummy user as Windows users in Windows first?

2. Also, you say you "Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine", Did you run the script in the order given?

3. Did you make any changes to the script other than the domainname?

4. Really stupid question but have to ask, are you sure you ran the whole script up to...
-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can...

5. When you ran as the user that has SA privs, are you sure you were executing as that user and not the TestDummy? Did you try REVERT before you ran the test as the SA user?

Jeff, sorry for the long hiatus after your extensive post back. House projects, work....

OK, in order:

First, I'm running this using 2008 R2 Express on a non-domain, stand-alone Win7 workstation. I'm assuming that's irrelevant, but always good to know for sure.

1. Users: I first created Windows users TestUser and SqlCmdUser. Domain name changed.

2. Setup. Yep, I ran the script as given. One problem I did run into was creating the proxy account, which failed. As explained here, however, I needed to start SSMS as an administrator to be able to execute that system stored proc. That then did run, and I verified the proxy existed in the SSMS browser (Server, Security, Credentials).

3. Changes: I changed the domain name and the test user name.

4. There are no stupid questions, certainly not from you. Yep, I ran everything up to that point. Logins, users, DB, SP, proxy all created.

5. Running as sa. Yes, I issued a REVERT and the code still failed, as indicated. Note that if I try this:

--	Fails, as expected:
EXECUTE AS LOGIN = 'LENOVOHOME\TestUser';
EXEC xp_cmdshell 'DIR H:\';

-- Succeeds
REVERT;
EXEC xp_cmdshell 'DIR H:\';

then the simple EXEC xp_cmdshell works as expected (blocked for non sysadmin).

But no matter the execution context, I cannot execute GetDirInfo.

Any thoughts or ideas to try?

Thanks,
Rich
Post #1381334
Posted Monday, November 5, 2012 7:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
Are you sure the test database is owned by "SA"?

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381343
Posted Friday, November 22, 2013 1:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:12 AM
Points: 729, Visits: 405
This is a re-published article and the last replies date back from 2012 but I'll throw my remark in anyway.
CLR is all nice but I still think it's not secure when you have 3rd-party assemblies that require the use of UNSAFE. How can we tell there's no backdoor in the assembly? .NET Reflector is ok but what if they obfuscate the assembly? I'm not a .NET developer but I assume there are ways to protect one's code?
Long time ago I conducted several tests and coded an (unsafe) assembly. I was able to call my method as a plain user but gain 'sa' access to the instance. The method involved executing sqlcmd.exe -E. That got me in as the SQL Server service account and gain full access. I was able to reset the password for sa etc...
So I'm still not convinced CLR is more secure than xp_cmdshell when it comes to assemblies you don't own.
Post #1516668
Posted Friday, November 22, 2013 4:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:24 AM
Points: 588, Visits: 2,559
.NET Reflector is ok but what if they obfuscate the assembly?

.NET Reflector enables you to see the source (C# IL or VB) of SQL Server CLR assemblies. You can even save it to disk. This is quite handy for checking that there is no code that could compromise the security of the server tucked in there. You can. of course, patch the code and pop the patched code back in, but you're the good guy aren't you. I don't know of any reliable way of obfuscating the code and I haven't heard of SmartAssembly being used successfully that way. Has anyone done this?



Best wishes,

Phil Factor
Simple Talk
Post #1516716
Posted Friday, November 22, 2013 5:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:12 AM
Points: 729, Visits: 405
Thx for the reply Phil. So that would mean there's no way of protecting your intellectual property if you code in .NET? I'm interested as well if anyone else has experience with this.
(oh btw, yep I'm the good guy :))
Post #1516725
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»»

Permissions Expand / Collapse