InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 14:33:43 GMT20http://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxAre you sure the test database is owned by "SA"?Mon, 05 Nov 2012 19:59:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (10/7/2012)[/b][hr][quote][b]rmechaber (10/6/2012)[/b][hr]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:[code="sql"]-- 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;[/code]The returned error on the second part is:Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1The 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[/quote]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...[code="sql"]-------------------------------------------------------------------------------------------------------------------------===== Now, show that the "TestDummy" user can...[/code]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?[/quote]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 [url=http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/40d67d22-28ac-44ab-b7d2-0eec25f873a6]explained here, however[/url], 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:[code="sql"]-- Fails, as expected: EXECUTE AS LOGIN = 'LENOVOHOME\TestUser'; EXEC xp_cmdshell 'DIR H:\';-- Succeeds REVERT; EXEC xp_cmdshell 'DIR H:\';[/code]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,RichMon, 05 Nov 2012 18:19:13 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]rmechaber (10/6/2012)[/b][hr]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:[code="sql"]-- 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;[/code]The returned error on the second part is:Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1The 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[/quote]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...[code="sql"]-------------------------------------------------------------------------------------------------------------------------===== Now, show that the "TestDummy" user can...[/code]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?Sun, 07 Oct 2012 07:04:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxHi, 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:[code="sql"]-- 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;[/code]The returned error on the second part is:Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.I'm running this on a home machine. Any ideas?Thanks,RichSat, 06 Oct 2012 16:34:11 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]rmechaber (10/4/2012)[/b][hr]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[/quote]Thank you for the kind words, Rich. When you've completed your look-see, I'd love to hear your feedback on the method.Thu, 04 Oct 2012 20:36:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxJeff, 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!RichThu, 04 Oct 2012 06:00:05 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxHere'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 [font="Arial Black"]password should be protected from non-DBA users as should any other password used by a system[/font] 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).[code="sql"]--===== 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 userGO--===== 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];GODROP 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.GetDirInfoSELECT 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;[/code]Thu, 04 Oct 2012 05:39:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]robert.baker 21596 (10/3/2012)[/b][hr]I resolved my error:The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.Sorry for polluting the conversation with newbie stuff[/quote]Nah... it's good stuff. Thank you for taking the time to post back. It'll help others.Wed, 03 Oct 2012 10:57:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr][quote][b]Jeff Moden (8/24/2012)[/b][hr][quote][b]rmechaber (8/24/2012)[/b][hr][quote][b]Jeff Moden (8/23/2012)[/b][hr]As a bit of a sidebar, I use XP_CmdShell to run PowerShell. :-D It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).[/quote]Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?Thanks very much,Rich[/quote]I have a demo script at home that I could share. I'll post it tonight.[/quote]Hi, Jeff, any luck running down that demo? I'd very much appreciate it!Rich[/quote]My apologies. I lost track of this thread. I'll try to post the demo tonight. Yeah... I know... said that before and, again, apologies for not following up.Wed, 03 Oct 2012 10:51:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxI resolved my error:The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.Sorry for polluting the conversation with newbie stuffWed, 03 Oct 2012 08:41:47 GMTrobert.baker 21596http://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (8/24/2012)[/b][hr][quote][b]rmechaber (8/24/2012)[/b][hr][quote][b]Jeff Moden (8/23/2012)[/b][hr]As a bit of a sidebar, I use XP_CmdShell to run PowerShell. :-D It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).[/quote]Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?Thanks very much,Rich[/quote]I have a demo script at home that I could share. I'll post it tonight.[/quote]Hi, Jeff, any luck running down that demo? I'd very much appreciate it!RichWed, 03 Oct 2012 06:19:06 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxHi thereSorry if this came up already, I skimmed through the thread and couldn't see anything, but I have upgraded a 2005 installation which had this CLR assembly installed for copying files. I set up the same assembly from an asymmetric key in SQL 2008 R2 but when I try to use it I get error: The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.Anybody have any pointers as to what permissions I need to set - would this be a permission problem for the login I created against the key?Wed, 03 Oct 2012 04:51:43 GMTrobert.baker 21596http://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]rmechaber (8/24/2012)[/b][hr][quote][b]Jeff Moden (8/23/2012)[/b][hr]As a bit of a sidebar, I use XP_CmdShell to run PowerShell. :-D It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).[/quote]Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?Thanks very much,Rich[/quote]I have a demo script at home that I could share. I'll post it tonight.Fri, 24 Aug 2012 10:03:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (8/23/2012)[/b][hr]As a bit of a sidebar, I use XP_CmdShell to run PowerShell. :-D It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).[/quote]Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?Thanks very much,RichFri, 24 Aug 2012 08:17:53 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxAs for not having the source code, that might not be as big of a thing as you think. If you can extract the assembly as a file then you can the go ask the good folks at Red Gate. They have a fine product that helps with Dot Net assemblies.Thu, 23 Aug 2012 18:43:50 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Lempster (8/23/2012)[/b][hr]I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion. :-) And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.RegardsLempster[/quote]Very cool feedback all around.As a bit of a sidebar, I use XP_CmdShell to run PowerShell. :-D It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).Thu, 23 Aug 2012 13:50:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxI don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion. :-) And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.RegardsLempsterThu, 23 Aug 2012 09:43:58 GMTLempsterhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxThanks for the feedback, Solomon. Mighty kind of you to spend the time you did explaining what you thought.Shifting gears a bit... On the subject of 3rd party CLR and having the source code. We all put up with the faults of a lot of 3rd party software (and a lot of it is "shrink wrapped"). Avoiding CLR just because you don't have the source code is a bit like saying you don't want to use PKUnZip because you don't have the source code. I understand the fear there, though. This is code that touches "our" database(s) and a lot of us would really like to know what it's going to do, how many resources it's going to use to do it, and how long it's going to take to run.Of course, a lot of folks blindly installed sp3 for SQL Server 2000 so maybe there's not enough fear. :-PSat, 11 Feb 2012 23:43:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Perry Whittle (2/10/2012)[/b][hr]The problem with a vendor supplied CLR is you don't have access to the source, it could be doing literally anything, you place an awful lot of trust in them :w00t:[/quote]Hi Perry. I have heard this concern voiced once or twice before and I always felt it was rather mind-boggling for several reasons:1) Unless are you using mostly or all open source software (e.g. Linux/Apache/MySQL/PHP), you generally do not have any source code to the major software that you are using. Not having source code to Windows, SQL Server, .Net, Visual Studio, or even smaller tools like SQL Prompt, .Net Reflector, Fiddler, etc. doesn't seem to bother most people.2) Why would there be an assumption that anything malicious is being done in the code? Sure, I suppose it is always possible, but how often has this really happened within the realm of business software? Maybe more often in free software as that is a wider audience and more likely to accomplish whatever goal of the malicious code, but for paid software? What incentive would there ever be for that? In fact, as a software vendor I have every incentive to NOT do anything malicious and to make sure that my code performs as best as possible.3) If you want to claim that some business software vendors still can, or maybe even have in the past, put in malicious code, you still can't distinguish between large/established companies and smaller/newer companies. Microsoft Word had personal identifying information in it, at least as of Office 2000 or 2003 which is how they tracked down the author of one of the more famous viruses. And Excel (I think also Office 2003) had a flight simulator Easter Egg in it?4) What would most people do even if they had the source code? Would they be able to read it? Depending on how much there was, would they have time to read through it? Sure, for a project that has its source code available for all to see online, then theoretically over time you would expect that eventually someone will find it and point it out. But that doesn't guarantee that anyone who previously downloaded it would be notified of that.5) Being developed in-house in either SQLCLR or even T-SQL doesn't truly guarantee safety. Yes, [i]more likely[/i] to be safe as you at least have the option to review the code, but in larger shops with many developers it is highly unlikely that the DBAs ever see even the T-SQL code that is running until it shows up in search for longest-running queries or seen in Profiler as taking up too much CPU, memory, or reads. But we have well over 10,000 stored procs, functions, views, triggers, etc and there is no real practical way to guarantee against bad code, whether malicious or accidental. And I have seen DBA’s write absolutely horribly inefficient SQL code and it didn’t help that other DBA’s had seen it as none of them knew exactly why it was inefficient.It seems that there are really only two reasons to want or even need the source code for externally produced software:1) Performance. You want code that goes into Production to perform well. If you do not have the source code you most likely can’t see what it is doing, BUT you can test the effects of the code on the system before it goes to Production. This is something that should be done for inhouse code as well. Hence this is not a true reason to shy away from vendor supplied software as you would have fair warning against poor performing code.2) Maintenance. Some vendors do go out of business and it is reasonable that people incorporating someone else’s software as part of their software that generates their revenue would want some safety net in case a problem is found and needs to be fixed. In these cases many companies provide a source-code license for an additional cost that mitigates this particular concern while still protecting the software vendor.Take care,Solomon...Sat, 11 Feb 2012 15:17:47 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (2/10/2012)[/b][hr]Since you brought up international time differences for doing a DIR in two different countries, let me ask... what did you do with your CLR to solve this problem? Heh... me? I just had two different versions of the code and sent the correct version to the correct place.[/quote]That is the beauty of dealing with information in native/rich datatypes: I didn't have to do anything. The issue was self-correcting because in both the C# code and what the C# passed back to the query, the date value was a real DATETIME. Using a directory listing method like Jonathon's example in the main article, you just add a regular WHERE condition and filter as you would normally in T-SQL. This is much more elegant and maintainable than having two different versions of the code like you had to do. Text-parsing localized strings--whether for dates, currencies, numbers, etc.--is highly error-prone as any new localization can break your code. Meaning, if a third date format is introduced in your scenario, your code will break and you will need to add a third variation of the text parsing which increases the number of areas of code that can have bugs. It also means added complexity which means less maintainable code, especially if someone other than yourself has to make updates and is not entirely sure how your process is parsing some values one way and other values another way.[quote][b]Jeff[/b][hr][quote][b]Solomon[/b][hr]2) You get more functionality than you would using standard DOS commands as DOS is not a very rich language. For example, if you wanted to allow deleting of files but only with a particular name pattern that was complex enough to not work with the basic ? and * wildcards in DOS, you could easily filter the directory list in C# or VB using Regular Expressions. Sure, you might be able to do this with a powershell script or a call to some other shell (some people might have PERL scripts to do more complex stuff or have even written C# or VB.Net EXE's to do this), but that is just another level removed from SQL Server (the issue here is explained in the next item).[/quote]Heh... why does everyone think that xp_CmdShell is limited to calling DOS? I can call EXEs, VBS, Perl Scripts, and much, much more. I can even create new batch files from T-SQL based on "conditions" and execute them. I can even call Powershell Script files using xp_CmdShell.[/quote]I am unsure if you are implying that I was only thinking in terms of calling DOS or if you are speaking of other people given that I did mention calling EXEs, PERL, PowerShell, etc. Regardless, my point is still that if you are needing the power of those languages then you might as well just do the same things without ever leaving SQL Server, especially if you are calling custom written .Net code as most likely that would work with little or no modification in SQLCLR.AND, an added benefit was what I discussed above in terms of the interface bewteen your query and the external code you are calling. If you have an XML document or any number of large strings, binary data, etc., you can pass those in natively as input parameters. Using xp_cmdshell you need to construct a DOS command-line which might require translations and the entire command to be submitted to xp_cmdshell is limited to varchar(8000) or nvarchar(4000). There are no limits with SQLCLR and you can even pass in custom CLR datatypes to CLR functions and procs. And getting data out, whether from a result set or output parameter or scalar return value, can be done inline and without text parsing.[quote][b]Jeff[/b][hr][quote][b]Solomon[/b][hr]3) You also get to contain and manage the dependency on the database functionality. Basically you make a truly external dependency into mostly an internal one. If you are responsible for backing up and recreating this system either for testing or disaster recovery, it is another step to be concerned with as you have to have that EXE in your backup plan and make sure to install it either in the right place or make sure the PATH environment variable is set correctly. With SQLCLR assmblies, they are part of the database backup and you are not going to restore a database with any procs / functions / views that reference assemblies and somehow not have that assembly. It simplifies / centralizes the code making for easier management. And if you are creating external .Net programs to do things that cannot be done in T-SQL, you might as well just put that same code (in most cases) in the DB and remove a point of failure.[/quote]While that's all true and good, it also means that I have to maintain externally managed code in a language that I prefer not to work with. ;-) Just including EXEs and DLLs in a new instance of SQL Server isn't all that needs to be done. Of course, that would be true of BAT, VBS, PS Scripts, etc. I do understand your point, though.[/quote]I am not saying that there is nothing to manage in the case of SQLCLR assemblies, just that it is [i]less[/i] (possibly MUCH less) to manage.[quote][b]Jeff[/b][hr][quote][b]Solomon[/b][hr]4) A tentative additional benefit would be that security [i]can[/i] be more fine-grained. I say "tentative" as I have not tried this and I doubt it is used very often, but it is still available. Using xp_cmdshell the default security context that the OS sees is the service account that the SQL Server (DB Engine) process is running as. There is the lesser used (I assume) option of having a proxy account, but it seems that the proxy account is used in place of the "run as" account for users who are not a member of the sysadmin fixed server role and the caller of xp_cmdshell doesn't get to specify which account to run under (either "run as" account or the proxy account, if configured). But with CLR code, it is possible (again, I haven't yet tried) to set up impersonation to allow the caller of the CLR proc/function (assuming a Windows login) to use their own credentials when interacting with external resources such as the filesystem and internet. Again, I have not tried this so I hope I am not misstating how the CLR impersonation works, but that is my understanding from what I read and I hope to soon prove this out as it does seem rather interesting.[/quote]Understood but, again, the security is controlled by what the procs do, not who does them. If someone needs to do something specific, they do it be being given privs to run a particular stored procedure.[/quote]Not entirely understood ;-). What I am speaking of here is an option that CLR code can do to interact with external resources based on the Windows account of the person running the proc / function / trigger. When calling xp_cmdshell, and by default with SQLCLR code, the execution context is of the account that SQL Server is running under. Assuming that SQL Server is running as a restricted account that cannot do much else outside of manage its own datafiles, this is an option to allow people to interact with the system without granting extra rights to the SQL Server account.Jonathon has an article [url=http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/34/Default.aspx]here[/url] that explains more.[quote][b]Jeff[/b][hr][quote][b]Solomon[/b][hr]Hope this makes sense and helps. Take care, Solomon...[/quote]It actually does help and, just to be sure, I'm not trying to be argumentative or contrary. I'm just explaining the different ways I do things. :-)[/quote]Understood. I am just explaining some of the benefits of SQLCLR over xp_cmdshell as I see them. But I am not saying or implying that you or anyone needs to agree or ever use SQLCLR.[size="1"]And I would be fine even if you were being argumentative and/or contrary as I know I am right about this ;-).[/size]Sat, 11 Feb 2012 11:10:00 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Perry Whittle (2/10/2012)[/b][hr][quote][b]Jeff Moden (2/10/2012)[/b][hr] I can call EXEs, VBS, Perl Scripts, and much, much more. I can even create new batch files from T-SQL based on "conditions" and execute them. I can even call Powershell Script files using xp_CmdShell.[/quote]That's what makes it so vulnerable in the wrong hands.I don't generally veto it's use, just make your code switch it on when it needs it and then ensure you switch it off afterwards.The problem with a vendor supplied CLR is you don't have access to the source, it could be doing literally anything, you place an awful lot of trust in them :w00t:[/quote]Very good point. I am not in favor of the turn on/off methodology, I don't believe you need it. As part of my standard SQL audit policies it checks to make sure it is disabled. In the case of in-house developed SQLCLR, policy requires submission of the source code for review with enough lead time to actually review it. In case where a call to the command line is required it is handled by a SQLCLR that calls ONLY what is explicitly required. I don't want arbitrary calls to the command line. An upside is that I don't run SQL as a highly privileged user so what an xp_cmdshell call CAN do is more limited.CEWIISat, 11 Feb 2012 09:46:08 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (2/10/2012)[/b][hr] I can call EXEs, VBS, Perl Scripts, and much, much more. I can even create new batch files from T-SQL based on "conditions" and execute them. I can even call Powershell Script files using xp_CmdShell.[/quote]That's what makes it so vulnerable in the wrong hands.I don't generally veto it's use, just make your code switch it on when it needs it and then ensure you switch it off afterwards.The problem with a vendor supplied CLR is you don't have access to the source, it could be doing literally anything, you place an awful lot of trust in them :w00t:Fri, 10 Feb 2012 16:54:12 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Solomon Rutzky (2/9/2012)[/b][hr]Hi Jeff. Yeah, I kinda realized as I was writing my reply that you could just put the xp_cmdshell calls into Procs and control access via Roles in the same way that I was saying would be done with the SQLCLR procs. Ok, so fair enough that there is not a huge difference there, but I did think of some other benefits:[/quote][quote]1) If you need to pull in the output of the DOS command into SQL Server for processing (for example, the article this forum is associated with does a DIR and pulls in that output), you get one row per line and have to do your own text parsing, which is error prone. Where I currently work a DBA needed to get a list of backup files and parse their create dates and on his machine the dates were written as DD/MM/YYYY as he was in Ireland but our servers in the US were showing a date format of MM/DD/YYYY so his process broke once he tried to roll it out. Getting a directory listing via SQLCLR gives you full control over that data in rich datatypes. Interacting with that output is even easier if you do User-Defined Functions as you can simply SELECT from them rather than having to dump their output into a temp table (or even call from OPEN QUERY).[/quote]Since you brought up international time differences for doing a DIR in two different countries, let me ask... what did you do with your CLR to solve this problem? Heh... me? I just had two different versions of the code and sent the correct version to the correct place.[quote]2) You get more functionality than you would using standard DOS commands as DOS is not a very rich language. For example, if you wanted to allow deleting of files but only with a particular name pattern that was complex enough to not work with the basic ? and * wildcards in DOS, you could easily filter the directory list in C# or VB using Regular Expressions. Sure, you might be able to do this with a powershell script or a call to some other shell (some people might have PERL scripts to do more complex stuff or have even written C# or VB.Net EXE's to do this), but that is just another level removed from SQL Server (the issue here is explained in the next item).[/quote]Heh... why does everyone think that xp_CmdShell is limited to calling DOS? I can call EXEs, VBS, Perl Scripts, and much, much more. I can even create new batch files from T-SQL based on "conditions" and execute them. I can even call Powershell Script files using xp_CmdShell.[quote]3) You also get to contain and manage the dependency on the database functionality. Basically you make a truly external dependency into mostly an internal one. For example, people install 7zip.exe to compress files as it is easy to call from a command line and handles many formats and zip64 for files larger than 4 GB. If you are responsible for backing up and recreating this system either for testing or disaster recovery, it is another step to be concerned with as you have to have that EXE in your backup plan and make sure to install it either in the right place or make sure the PATH environment variable is set correctly. With SQLCLR assmblies, they are part of the database backup and you are not going to restore a database with any procs / functions / views that reference assemblies and somehow not have that assembly. It simplifies / centralizes the code making for easier management. And if you are creating external .Net programs to do things that cannot be done in T-SQL, you might as well just put that same code (in most cases) in the DB and remove a point of failure. Going back to the 7zip example, gzip compression is built into .Net so it does not take too much effort to write an internal zip function. And if you need more than the basic gzip (maybe you need zip64 for 4+ GB files or RAR format) then you can find ways to get that as well (for example, the FULL / Paid For version of SQL# includes a gzip function that handles zip64 for 4+ GB files :-)).[/quote]While that's all true and good, it also means that I have to maintain externally managed code in a language that I prefer not to work with. ;-) Just including EXEs and DLLs in a new instance of SQL Server isn't all that needs to be done. Of course, that would be true of BAT, VBS, PS Scripts, etc. I do understand your point, though.[quote]4) A tentative additional benefit would be that security [i]can[/i] be more fine-grained. I say "tentative" as I have not tried this and I doubt it is used very often, but it is still available. Using xp_cmdshell the default security context that the OS sees is the service account that the SQL Server (DB Engine) process is running as. There is the lesser used (I assume) option of having a proxy account, but it seems that the proxy account is used in place of the "run as" account for users who are not a member of the sysadmin fixed server role and the caller of xp_cmdshell doesn't get to specify which account to run under (either "run as" account or the proxy account, if configured). But with CLR code, it is possible (again, I haven't yet tried) to set up impersonation to allow the caller of the CLR proc/function (assuming a Windows login) to use their own credentials when interacting with external resources such as the filesystem and internet. Again, I have not tried this so I hope I am not misstating how the CLR impersonation works, but that is my understanding from what I read and I hope to soon prove this out as it does seem rather interesting.[/quote]Understood but, again, the security is controlled by what the procs do, not who does them. If someone needs to do something specific, they do it be being given privs to run a particular stored procedure.[quote]Hope this makes sense and helps. Take care, Solomon...[/quote]It actually does help and, just to be sure, I'm not trying to be argumentative or contrary. I'm just explaining the different ways I do things. :-)Fri, 10 Feb 2012 16:35:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxGetting that library accessible to SQL is a challenge, I had to do that in 2008. I'll have to see what I can do about releasing the code on CodePlex. I'm sure I committed a number of sins in writing it but it worked pretty good. It was a bit of a memory hog when doing large files, but that could have been my fault..I also believe that it was an UNSAFE assembly.As a side note, I'm not sure I have seen this in MS docs but I would suggest segragating UNSAFE objects together, SAFE objects together, and EXTERNAL objects together. I got this recommendation during a small group discussion at PDC05 specifically talking about SQLCLR best practices when they were new in SQL. YMMV..CEWIIFri, 10 Feb 2012 08:06:52 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jedak (2/9/2012)[/b][hr]You could use [url=http://www.icsharpcode.net/opensource/sharpziplib/]SharpZipLib[/url] instead. It's free and open source. It has support for most types of compression.Jedak[/quote]True, SharpZipLib and a few other open source libraries are free alternatives. But each of those options still requires someone to make some minor updates (assuming EXTERNAL_ACCESS is preferred over UNSAFE as most of the libraries I have seen make use of constructs that are only valid with the UNSAFE permission level). And then you have a library but still need to do the work of exposing that library to SQL Server. For anyone with the time and skill to do this it is a very good option. But to be fair, "free" is a matter of perspective. For people lacking either the skill or the time to implement the code, these are not truly "free" options.Take care,Solomon...Thu, 09 Feb 2012 22:38:55 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Solomon Rutzky (2/9/2012)[/b][hr]3) You also get to contain and manage the dependency on the database functionality. Basically you make a truly external dependency into mostly an internal one. For example, people install 7zip.exe to compress files as it is easy to call from a command line and handles many formats and zip64 for files larger than 4 GB. If you are responsible for backing up and recreating this system either for testing or disaster recovery, it is another step to be concerned with as you have to have that EXE in your backup plan and make sure to install it either in the right place or make sure the PATH environment variable is set correctly. With SQLCLR assmblies, they are part of the database backup and you are not going to restore a database with any procs / functions / views that reference assemblies and somehow not have that assembly. It simplifies / centralizes the code making for easier management. And if you are creating external .Net programs to do things that cannot be done in T-SQL, you might as well just put that same code (in most cases) in the DB and remove a point of failure. Going back to the 7zip example, gzip compression is built into .Net so it does not take too much effort to write an internal zip function. And if you need more than the basic gzip (maybe you need zip64 for 4+ GB files or RAR format) then you can find ways to get that as well (for example, the FULL / Paid For version of SQL# includes a gzip function that handles zip64 for 4+ GB files :-)).[/quote]You could use [url=http://www.icsharpcode.net/opensource/sharpziplib/]SharpZipLib[/url] instead. It's free and open source. It has support for most types of compression.JedakThu, 09 Feb 2012 12:47:12 GMTJedakhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Elliott Whitlow (2/9/2012)[/b][hr]Solomon,I have to disagree about the differences..From Professional SQL Server 2005 CLR Programming by Derek Comingore and Douglas Hinson, Page 296, Unsafe Permission Level."We cannot say this any more plainly: Do not be fooled by these SQL CLR permission level names. It is a common misunderstanding to assume EXTERNAL access permissions level opens up some of the permissions and then UNSAFE opens up some more. The reality is that the EXTERNAL access permissions level gives away most of the operating system -level permissions. The only additional thing the UNAFE permission level does is allow access to unmanaged code. The reason for this is that the permission setsare named from the perspective of SQL Server. Assemblies are external because the access external resources to the SQL Server. Assemblies are UNSAFE because, like extended stored procedures, they can call code outside the CLR, can corrupt memory buffers in use by SQL Server (think blue screen of death), and can subvert security mechanisms."And from the [url=http://msdn.microsoft.com/en-us/library/ms345101.aspx]link[/url] posted above:Security NoteSAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside SQL Server. EXTERNAL_ACCESS is recommended for assemblies that access resources outside SQL Server. EXTERNAL_ACCESS assemblies by default execute as the SQL Server service account. It is possible for EXTERNAL_ACCESS code to explicitly impersonate the caller's Windows Authentication security context. Since the default is to execute as the SQL Server service account, permission to execute EXTERNAL_ACCESS should only be given to logins trusted to run as the service account. From a security perspective, EXTERNAL_ACCESS and UNSAFE assemblies are identical. However, EXTERNAL_ACCESS assemblies provide various reliability and robustness protections that are not in UNSAFE assemblies. Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space, and hence can potentially compromise the robustness and scalability of SQL Server. For more information about creating CLR assemblies in SQL Server, see Managing CLR Integration Assemblies.[/quote]Hi Elliot. Thank you for sharing that info. I definitely agree with the security aspect with regards to the second paragraph (the MSDN quote) as was not trying to state or imply something different with my earlier statement. But I find what they said in the first paragraph interesting and a little surprising so I will do a little more investigation.Take care,Solomon...Thu, 09 Feb 2012 12:27:35 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (2/9/2012)[/b][hr][quote][b]Solomon Rutzky (2/9/2012)[/b][hr]xp_cmdshell is a wide-open hole for anything. [/quote]First, thank you very much for the explanation. Your explanation is more along the lines of what I understood.On the thing I quoted above... that's not exactly true. It's absolutely true that most people deploy xp_CmdShell in such a haphazard and footloose fashion so I certainly understand where you're coming from on that but there are simple methods to make it where someone with only PUBLIC privs can run only specific stored procedures which contain calls to xp_CmdShell and they can't even see what's in the stored procedure and they certainly can't run xp_CmdShell directly. To your point, airtight methods are fairly easy to allow one group to execute not only certain "classes" of DOS level files (.BAT, .EXE, .VBS, etc, etc), but it limits them to ONLY being able to execute what's actually in the proc.The gross and improper use of xp_CmdShell has given a great tool one hell of a black eye that very few people are willing to look beyond.[/quote]Hi Jeff. Yeah, I kinda realized as I was writing my reply that you could just put the xp_cmdshell calls into Procs and control access via Roles in the same way that I was saying would be done with the SQLCLR procs. Ok, so fair enough that there is not a huge difference there, but I did think of some other benefits:1) If you need to pull in the output of the DOS command into SQL Server for processing (for example, the article this forum is associated with does a DIR and pulls in that output), you get one row per line and have to do your own text parsing, which is error prone. Where I currently work a DBA needed to get a list of backup files and parse their create dates and on his machine the dates were written as DD/MM/YYYY as he was in Ireland but our servers in the US were showing a date format of MM/DD/YYYY so his process broke once he tried to roll it out. Getting a directory listing via SQLCLR gives you full control over that data in rich datatypes. Interacting with that output is even easier if you do User-Defined Functions as you can simply SELECT from them rather than having to dump their output into a temp table (or even call from OPEN QUERY).2) You get more functionality than you would using standard DOS commands as DOS is not a very rich language. For example, if you wanted to allow deleting of files but only with a particular name pattern that was complex enough to not work with the basic ? and * wildcards in DOS, you could easily filter the directory list in C# or VB using Regular Expressions. Sure, you might be able to do this with a powershell script or a call to some other shell (some people might have PERL scripts to do more complex stuff or have even written C# or VB.Net EXE's to do this), but that is just another level removed from SQL Server (the issue here is explained in the next item).3) You also get to contain and manage the dependency on the database functionality. Basically you make a truly external dependency into mostly an internal one. For example, people install 7zip.exe to compress files as it is easy to call from a command line and handles many formats and zip64 for files larger than 4 GB. If you are responsible for backing up and recreating this system either for testing or disaster recovery, it is another step to be concerned with as you have to have that EXE in your backup plan and make sure to install it either in the right place or make sure the PATH environment variable is set correctly. With SQLCLR assmblies, they are part of the database backup and you are not going to restore a database with any procs / functions / views that reference assemblies and somehow not have that assembly. It simplifies / centralizes the code making for easier management. And if you are creating external .Net programs to do things that cannot be done in T-SQL, you might as well just put that same code (in most cases) in the DB and remove a point of failure. Going back to the 7zip example, gzip compression is built into .Net so it does not take too much effort to write an internal zip function. And if you need more than the basic gzip (maybe you need zip64 for 4+ GB files or RAR format) then you can find ways to get that as well (for example, the FULL / Paid For version of SQL# includes a gzip function that handles zip64 for 4+ GB files :-)).4) A tentative additional benefit would be that security [i]can[/i] be more fine-grained. I say "tentative" as I have not tried this and I doubt it is used very often, but it is still available. Using xp_cmdshell the default security context that the OS sees is the service account that the SQL Server (DB Engine) process is running as. There is the lesser used (I assume) option of having a proxy account, but it seems that the proxy account is used in place of the "run as" account for users who are not a member of the sysadmin fixed server role and the caller of xp_cmdshell doesn't get to specify which account to run under (either "run as" account or the proxy account, if configured). But with CLR code, it is possible (again, I haven't yet tried) to set up impersonation to allow the caller of the CLR proc/function (assuming a Windows login) to use their own credentials when interacting with external resources such as the filesystem and internet. Again, I have not tried this so I hope I am not misstating how the CLR impersonation works, but that is my understanding from what I read and I hope to soon prove this out as it does seem rather interesting.Hope this makes sense and helps. Take care, Solomon...Thu, 09 Feb 2012 11:01:23 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Solomon Rutzky (2/9/2012)[/b][hr]Hello Elliott and Jeff.First, to Elliott's comment about "there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS", that is not exactly correct. In fact, EXTERNAL_ACCESS is much closer to SAFE than it is UNSAFE. EXTERNAL_ACCESS is basically SAFE that also allows the code to see outside of SQL Server so it can access things like the file system, internet, other database connections (to the same server or anything else). UNSAFE, on the other hand, has NO restrictions. It will allow for unmanaged code, non-static variables, access to system areas, loading untested managed assemblies (any assembly that is part of regular .Net but not available by default to SQL Server assemblies).So then to Jeff's point, we only need EXTERNAL_ACCESS and UNSAFE when doing things that do more than the basic set of .Net assemblies allow for or needing to look outside of the database. In this case being discussed here, just viewing the filesystem requires EXTERNAL_ACCESS. Very few things need UNSAFE. Sometimes either one of these is necessary, but certainly not for a split function. One should not use a higher lever of access than they truly need.Regarding SQLCLR vs xp_cmdshell, there is a large difference with regards to controlling access. xp_cmdshell is a wide-open hole for anything. You can restrict access by database role, etc, but if two groups need to run CMDs or BATs or EXEs then you can't control who gets to call which ones. But with xp_cmdshell turned off and using SQLCLR instead, you can create a View Directories proc and grant EXECUTE only to RoleA and then create an FTP proc and grant EXECUTE only to RoleB. You can create those functions in CMD scripts but then both RoleA and RoleB would be able to execute both View Directories AND FTP since both would have access to xp_cmdshell. You might be able to abstract xp_cmdshell by restricting direct access and creating procs for both roles that have the explicit calls in there, but I haven't tried that to see if it is a practical solution.Take care,Solomon...[/quote]Solomon,I have to disagree about the differences.. From Professional SQL Server 2005 CLR Programming by Derek Comingore and Douglas Hinson, Page 296, Unsafe Permission Level."We cannot say this any more plainly: Do not be folloed by these SQL CLR permission level names. It is a common misunderstanding to assume EXTERNAL access permissions level opens up some of the permissions and then UNSAFE opens up some more. The reality is that the EXTERNAL access permissions level gives away most of the operating system -level permissions. The only additional thing the UNAFE permission level does is allow access to unmanaged code. The reason for this is that the permission setsare named from the perspective of SQL Server. Assemblies are external because the access external resources to the SQL Server. Assemblies are UNSAFE because, like extended stored procedures, they can call code outside the CLR, can corrupt memory buffers in use by SQL Server (think blue screen of death), and can subvert security mechanisms."And from the [url=http://msdn.microsoft.com/en-us/library/ms345101.aspx]link[/url] posted above:Security NoteSAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside SQL Server. EXTERNAL_ACCESS is recommended for assemblies that access resources outside SQL Server. EXTERNAL_ACCESS assemblies by default execute as the SQL Server service account. It is possible for EXTERNAL_ACCESS code to explicitly impersonate the caller's Windows Authentication security context. Since the default is to execute as the SQL Server service account, permission to execute EXTERNAL_ACCESS should only be given to logins trusted to run as the service account. From a security perspective, EXTERNAL_ACCESS and UNSAFE assemblies are identical. However, EXTERNAL_ACCESS assemblies provide various reliability and robustness protections that are not in UNSAFE assemblies. Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space, and hence can potentially compromise the robustness and scalability of SQL Server. For more information about creating CLR assemblies in SQL Server, see Managing CLR Integration Assemblies. There are limited reasons to use UNSAFE and I try to avoid it. However if you still need to call a command line tool you can't do it with EXTERNAL_ACCESS. In the past I have had to do this a couple times. xp_commandshell was not something I was willing to use so I built a CLR that was capable of ONLY calling the tools specified. I can post the code for that.The SAFE level is very restrictive and what I generally shoot for, however, it won't work for many things. Once you need to use an EXTERNAL_ACCESS or UNSAFE assembly you either need to set the database trustworthy or handle the certs that the assemblies were signed with. Many (most?) people go with trustworthy because its the easiest, but the certs is the best method and most secure.CEWIIThu, 09 Feb 2012 07:51:56 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]rmechaber (2/9/2012)[/b][hr][quote][b]Jeff Moden (2/8/2012)[/b][hr]Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.[/quote]Jeff, if you're only manipulating data passed to a CLR-based UDF or SP, then SAFE works and no key is needed. The explanation of what you can and cannot access with CLR at the different security levels is summarized here:[url=http://msdn.microsoft.com/en-us/library/ms345101.aspx]http://msdn.microsoft.com/en-us/library/ms345101.aspx[/url]To quote:"[b]SAFE[/b]Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. "So if you don't need access to those external system resources, you can create the assembly as SAFE.As an example, if you are using a CLR-based UDF to validate a passed-in email address, you only need to create the assembly with SAFE permission set. If you want to create a CLR-based TVF that returns file information, then SAFE won't work.Rich[/quote]Thanks for the link, Rich. Understood on the file access thing. Like Solomon posted, my understanding was that there are actually very few times where you'd want to use UNSAFE. EXTERNAL_ACCESS... sure. But someone really needs strong justification to use UNSAFE.Can you good folks give me an example of where UNSAFE is actually required? Again, I'm definitely a neophyte on this subject and just trying to learn more from folks that have personal experience with CLR.Thu, 09 Feb 2012 07:38:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Solomon Rutzky (2/9/2012)[/b][hr]xp_cmdshell is a wide-open hole for anything. [/quote]First, thank you very much for the explanation. Your explanation is more along the lines of what I understood.On the thing I quoted above... that's not exactly true. It's absolutely true that most people deploy xp_CmdShell in such a haphazard and footloose fashion so I certainly understand where you're coming from on that but there are simple methods to make it where someone with only PUBLIC privs can run only specific stored procedures which contain calls to xp_CmdShell and they can't even see what's in the stored procedure and they certainly can't run xp_CmdShell directly. To your point, airtight methods are fairly easy to allow one group to execute not only certain "classes" of DOS level files (.BAT, .EXE, .VBS, etc, etc), but it limits them to ONLY being able to execute what's actually in the proc.The gross and improper use of xp_CmdShell has given a great tool one hell of a black eye that very few people are willing to look beyond.Thu, 09 Feb 2012 07:35:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (2/8/2012)[/b][hr][quote][b]Elliott Whitlow (2/7/2012)[/b][hr][quote][b]Jeff Moden (4/29/2011)[/b][hr]I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?[/quote]Jeff,I'm sorry I missed this one and I know it is an ancient post..But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.Not sure if this fully answered your question..CEWII[/quote]Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.[/quote]Hello Elliott and Jeff.First, to Elliott's comment about "there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS", that is not exactly correct. In fact, EXTERNAL_ACCESS is much closer to SAFE than it is UNSAFE. EXTERNAL_ACCESS is basically SAFE that also allows the code to see outside of SQL Server so it can access things like the file system, internet, other database connections (to the same server or anything else). UNSAFE, on the other hand, has NO restrictions. It will allow for unmanaged code, non-static variables, access to system areas, loading untested managed assemblies (any assembly that is part of regular .Net but not available by default to SQL Server assemblies).So then to Jeff's point, we only need EXTERNAL_ACCESS and UNSAFE when doing things that do more than the basic set of .Net assemblies allow for or needing to look outside of the database. In this case being discussed here, just viewing the filesystem requires EXTERNAL_ACCESS. Very few things need UNSAFE. Sometimes either one of these is necessary, but certainly not for a split function. One should not use a higher lever of access than they truly need.Regarding SQLCLR vs xp_cmdshell, there is a large difference with regards to controlling access. xp_cmdshell is a wide-open hole for anything. You can restrict access by database role, etc, but if two groups need to run CMDs or BATs or EXEs then you can't control who gets to call which ones. But with xp_cmdshell turned off and using SQLCLR instead, you can create a View Directories proc and grant EXECUTE only to RoleA and then create an FTP proc and grant EXECUTE only to RoleB. You can create those functions in CMD scripts but then both RoleA and RoleB would be able to execute both View Directories AND FTP since both would have access to xp_cmdshell. You might be able to abstract xp_cmdshell by restricting direct access and creating procs for both roles that have the explicit calls in there, but I haven't tried that to see if it is a practical solution.Take care,Solomon...Thu, 09 Feb 2012 07:00:04 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (2/8/2012)[/b][hr]Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.[/quote]Jeff, if you're only manipulating data passed to a CLR-based UDF or SP, then SAFE works and no key is needed. The explanation of what you can and cannot access with CLR at the different security levels is summarized here:[url=http://msdn.microsoft.com/en-us/library/ms345101.aspx]http://msdn.microsoft.com/en-us/library/ms345101.aspx[/url]To quote:"[b]SAFE[/b]Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. "So if you don't need access to those external system resources, you can create the assembly as SAFE.As an example, if you are using a CLR-based UDF to validate a passed-in email address, you only need to create the assembly with SAFE permission set. If you want to create a CLR-based TVF that returns file information, then SAFE won't work.RichThu, 09 Feb 2012 06:56:43 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Elliott Whitlow (2/7/2012)[/b][hr][quote][b]Jeff Moden (4/29/2011)[/b][hr][quote][b]Elliott Whitlow (4/29/2011)[/b][hr]Oh well.. I released a SQL CLR file function library on codeplex in march 2011. It is at:[url=http://nclsqlclrfile.codeplex.com/]http://nclsqlclrfile.codeplex.com/[/url]Let me know what you think, what is wrong with it, and anything that might be added.CEWII[/quote]I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?[/quote]Jeff,I'm sorry I missed this one and I know it is an ancient post..But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.Not sure if this fully answered your question..CEWII[/quote]Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.Wed, 08 Feb 2012 22:54:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Jeff Moden (4/29/2011)[/b][hr][quote][b]Elliott Whitlow (4/29/2011)[/b][hr]Oh well.. I released a SQL CLR file function library on codeplex in march 2011. It is at:[url=http://nclsqlclrfile.codeplex.com/]http://nclsqlclrfile.codeplex.com/[/url]Let me know what you think, what is wrong with it, and anything that might be added.CEWII[/quote]I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?[/quote]Jeff,I'm sorry I missed this one and I know it is an ancient post..But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.Not sure if this fully answered your question..CEWIITue, 07 Feb 2012 21:56:32 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Chrissy321 (2/7/2012)[/b][hr]I know I'm late to this party but does this code traverse subdirectories? Thanks[/quote]The SQL# package that Solomon mentioned a few posts up this thread does [url=http://www.sqlsharp.com/]http://www.sqlsharp.com/[/url]Tue, 07 Feb 2012 16:39:42 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxI know I'm late to this party but does this code traverse subdirectories? ThanksTue, 07 Feb 2012 16:25:59 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Elliott Whitlow (4/29/2011)[/b][hr]Oh well.. I released a SQL CLR file function library on codeplex in march 2011. It is at:[url=http://nclsqlclrfile.codeplex.com/]http://nclsqlclrfile.codeplex.com/[/url]Let me know what you think, what is wrong with it, and anything that might be added.CEWII[/quote]Thanks for that, I found it via Google and have used the MFGetDirectoryList as a jumping off point. Based on Jonathan's comment earlier in this thread, I added in a check that the passed directory actually exists, and return an empty result set if not. I also added both the FileExtension and Archive attributes as 2 new columns returned in the TVF. Including FileExtension (N.B.: you have to strip off a leading ".") allowed me to remove the passed-in file extension parameter: I just use a WHERE clause on the TVF. Yeah, I return more data than I need, but to me the logic is cleaner and I'm unlikely to be parsing directories with more than a few hundred files anyway.The ability to grab the archive bit is great, b/c now I can use the TVF to find, say, SQL backup files that haven't been backed up to tape. Prior to this, I had a clunky work-around that (1) periodically called an Agent job, that (2) called a batch file with a DOS ATTRIB command, that (3) sent its output to a text file, that (4) was then consumed by SSIS and dumped into a SQL table. Whew! This is [u]much[/u] cleaner.So thanks to you and to Jonathan for encouraging me to look into CLR as a solution here. I'm new to CLR, and there's been a few gotchas I've had to learn and quite a few pieces to implement.Jonathan, I couldn't ask for a better walk-through than [url=http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx]http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx[/url] for creating an asymmetric key (and avoiding SET TRUSTWORTHY). Thanks!Finally, I'd like to offer a nice quote from Beginning SQL Server 2005 Administration, publised by Wrox, on the issue of CLR and security:[quote]"Before you get excited about using terms like 'Unsafe,' let's put this into perspective. We have had the capability to extend SQL Server's reach using external applications and components for several years in the form of command-line executables and extended stored procedures. Many trustworthy SQL Server solutions send email messages using COM-based Collaboration Data Object (CDO) code or interact with the file system using external VBScript.... The point is that these are all examples of what is now called 'unmanaged code' and falls into the category of 'Unsafe' code from the perspective of a SQL Server CLR object.Using the Unsafe setting .... just means that the .NET CLR can't guarantee that it's safe -- you and your developers have to do that, just like many of us have been doing for the past 10 or 12 years."[/quote]Thanks,RichFri, 06 May 2011 08:27:58 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspxJust wanted to add my experience of CLR....Used in the right place (i.e. where it was worth optimisation), it doubled the speed of some of my TSQL (doing some heavy string manipulation in my case), which was significant in the context of where I used it.For me it also allowed code reuse (D.R.Y.). e.g. I have functions that are shared in use across a windows app, (indirectly) ms access, web app, tsql. All call the same .net functions. Very handy not having to re-write/test seperate versions and gives me consistency of problems. (This might sound strange, but as part of my case, im using various encoding methods for data matching/de-duplicating data from multiple sources. If there are problems due to a bug in one version of a function on one platform encoding differently to another, then thats bad. At least if all versions behave the same, then I have consistency. And a single point of update requirements). Luckily I haven't had any problems, but I feel its good to plan :)M.Wed, 04 May 2011 02:48:52 GMTMartin Bastablehttp://www.sqlservercentral.com/Forums/Topic650581-1365-1.aspx[quote][b]Solomon Rutzky (5/1/2011)[/b][hr]The only way to access the methods within the Assembly are the T-SQL wrapper Procedures and Functions (as well as Types and Aggregates) that themselves can only point to the CLR method.[/quote]My apologies... my "lingo" on the subject certainly isn't correct. I was, in fact, speaking of the The "T-SQL wrappers" that Solomon pointed out.I've got some more reading to do on the links Jonathon provided on assembly privs.Tue, 03 May 2011 20:30:21 GMTJeff Moden