November 5, 2012 at 6:19 pm
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
November 5, 2012 at 7:59 pm
Are you sure the test database is owned by "SA"?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2013 at 1:14 am
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.
November 22, 2013 at 4:07 am
.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
November 22, 2013 at 5:19 am
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 :))
November 22, 2013 at 5:34 am
Thierry,
That would mean there is no way to obfuscate your intellectual property. Protecting it is a whole other matter.
November 22, 2013 at 5:49 am
Thx for the reply Robert.
I don't quite follow. If you can regenerate (reverse-engineer) the code using the .NET Reflector how can you protect your software? Ok, if it's obfuscated then -if I'm not mistaken- variables, methods... have no meaning but one can still get to the logic (if it's really worth it). Right?
November 22, 2013 at 7:48 am
thierry.vandurme (11/22/2013)
Thx for the reply Robert.I don't quite follow. If you can regenerate (reverse-engineer) the code using the .NET Reflector how can you protect your software? Ok, if it's obfuscated then -if I'm not mistaken- variables, methods... have no meaning but one can still get to the logic (if it's really worth it). Right?
Yes,
Obfuscation makes all the classes, structs, variables, methods, etc. have nonsense names (how so depends on the obfuscator), but all the logic is there if you want to try and follow it. It can be very difficult though.
Jedak
November 22, 2013 at 8:37 am
First, thank you to Jeff Moden for pulling out xp_dirtree, which is exactly what I was thinking when I saw the title.
With thanks to Jeff (and Robyn and Phil on Simple-Talk), let me post a minimal directory details snippet I copied from his post - functionally identical to both what I actually use, and fairly similar to the output of
dir /b path
and which has always been as much as I've needed SQL Server to do for me for handling backup files:
SET NOCOUNT ON
DECLARE @piFullPath VARCHAR(128)
DECLARE @DirTreeCount INT --Remembers number of rows for xp_DirTree
SET @piFullPath = '\\YourServer\YourShare\YourPath'
--SET @piFullPath = 'c:\temp'
IF OBJECT_ID('TempDB..#DirTree','U') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
(
RowNum INT IDENTITY(1,1),
Name VARCHAR(256) PRIMARY KEY CLUSTERED,
Depth BIT,
IsFile BIT
)
--=======================================================================================
-- Get all the file names for the directory (includes directory names as IsFile = 0)
--=======================================================================================
--===== Get the file names for the desired path
INSERT INTO #DirTree (Name, Depth, IsFile)
EXEC Master.dbo.xp_Dirtree @piFullPath,1,1 -- Current diretory only, list file names
-- Remember the row count
SET @DirTreeCount = @@ROWCOUNT
--===== Update the file names with the path for ease of processing later on
UPDATE #DirTree
SET Name = @piFullPath + Name
SELECT * FROM #dirtree
P.S. If you're trying to do OS level work from SQL Server, I have to submit that you may be using the wrong tool. Go the other way - write OS level software (batch files, scripting language, executables) that either use something like sqlcmd to call the database, or that connect to your database directly.
P.P.S. If you folks are really going to get into email parsing, then at least follow a known standard, like RFC 822. Here's an example of a RegEx for RFC822 email address parsing: http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html
November 22, 2013 at 10:12 am
November 22, 2013 at 2:54 pm
thierry.vandurme (11/22/2013)
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?
I think you might be missing the point with UNSAFE. There really is only one major difference between EXTERNAL_ACCESS and UNSAFE, it is the ability to use of pinvoke. The difference between SAFE and EXTERNAL_ACCESS is ENORMOUS. As I read your post it seems to be centered on security (access) and not necessarily on safety.
As far as a back door, I would suggest this line of thought. We allow vendor code to be applied to our systems with absolutely no idea what it contains, I don't see SQLCLR as all that different.
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 you basically built xp_cmdshell in SQL CLR, not sure that proves what you think it does..
So I'm still not convinced CLR is more secure than xp_cmdshell when it comes to assemblies you don't own.
If you are going to allow it to call arbitrary EXE files then it isn't. However, I had a project about 4 years ago where we had to call a single EXE and I knew ALL the parameters that it had to be called with but 2, and they were both numbers. My SQL CLR accepted only those two numbers as parameters and I called the EXE with a command that *I* built. This precluded any kind of injection. I knew EXACTLY what parameters would and would not be passed and EXACTLY what EXE would be called. I would consider this FAR more secure than xp_cmdshell where ANY command can be sent.
I have two projects on CodePlex related to these topics:
File Functions in SQL CLR: https://nclsqlclrfile.codeplex.com/[/url]
Cmd Exec Framework in SQL CLR: https://nclsqlclrcmdexec.codeplex.com/[/url]
CEWII
November 22, 2013 at 3:38 pm
thierry.vandurme (11/22/2013)
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.
You have no idea how much I appreciate your post. Thank you, VERY much, for taking the time to post it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2013 at 3:45 pm
Elliott Whitlow (11/22/2013)
If you are going to allow it to call arbitrary EXE files then it isn't. However, I had a project about 4 years ago where we had to call a single EXE and I knew ALL the parameters that it had to be called with but 2, and they were both numbers. My SQL CLR accepted only those two numbers as parameters and I called the EXE with a command that *I* built. This precluded any kind of injection. I knew EXACTLY what parameters would and would not be passed and EXACTLY what EXE would be called. I would consider this FAR more secure than xp_cmdshell where ANY command can be sent.
That's absolutely true if you allow for the unbridled use of xp_CmdShell. Except for those who already have SA privs, you can indeed create stored procedures with pre-programmed limits as to what xp_CmdShell can actually do much as you have for your CLR and can easily be made as safe as the CLR.
We allow vendor code to be applied to our systems with absolutely no idea what it contains...
It costs me a bit in convenience as a DBA but I don't have any third party software installed on any of my servers and for the very reason you mention. Ironically, I will allow CLR functionality but only after I do a code review on the code and witness the compilation of the DLL.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2013 at 4:15 pm
Jeff,
I have yet to come across a bigger company where they didn't have SOME off the shelf software. Which is kind of my point, if you are running purely your own software then it is less of an issue. Personally I like to code review it as well, for security as well as a review of whether we SHOULD be doing these actions in SQL..
CEWII
November 22, 2013 at 5:00 pm
Sorry, Elliott. I absolutely agree in most cases and didn't mean to make it sound contrary to what you said. Rather, my intent was to support your position on the use of CLR as I've allowed it into my environment long before I've allowed 3rd party code on the servers.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 136 through 150 (of 168 total)
You must be logged in to reply to this topic. Login to reply