Let me know what you think, what is wrong with it, and anything that might be added.

CEWII

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?

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.

Let me know what you think, what is wrong with it, and anything that might be added.

CEWII

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?

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

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.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Jeff Moden (2/8/2012)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.

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:http://msdn.microsoft.com/en-us/library/ms345101.aspx

To quote:"SAFEOnly 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.

Jeff Moden (4/29/2011)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?

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

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.

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.

Solomon Rutzky (2/9/2012)xp_cmdshell is a wide-open hole for anything.

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.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Jeff Moden (2/8/2012)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.

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:http://msdn.microsoft.com/en-us/library/ms345101.aspx

To quote:"SAFEOnly 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

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.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

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

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

Solomon Rutzky (2/9/2012)xp_cmdshell is a wide-open hole for anything.

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.

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