There has been a bit of discussion following this post so I will start here, rather than assume people have been keeping up with the thread.
CLR is all nice but I still think it's not secure when you have 3rd-party assemblies that require the use of UNSAFE.
Hi there. This is actually a rather vague statement, even if most people don't recognize it as such, and without context, is a bit unfair. What packages / products require UNSAFE Assemblies? Are the required UNSAFE Assemblies vendor-produced or are they .Net Framework DLLs that are required for functionality that the vendor is needing? The list of "tested and approved" system DLLs is actually quite small, so something like image manipulation, which requires System.Drawing, is not natively available. Getting that to work requires importing the System.Drawing DLL into SQL Server, but this is only allowed if it is marked as UNSAFE. However, marking it as UNSAFE doesn't mean that it is necessarily doing unsafe things. And it is no less secure when it is sitting inside SQL Server than it is when it is in its natural home within C:\Windows\Microsoft.NET\Framework64\v2.0.50727 (path varies by OS version and 32 vs 64 bit) and available to all .Net-based applications.
How can we tell there's no backdoor in the assembly?
I feel that this is an odd and unfounded fear that many people seem to have. Why would you even be thinking along these lines? How sure are you that any software you run doesn't have backdoors? Are you certain that Microsoft doesn't have backdoor in both Windows and SQL Server, just in case they want to check up on your licensing or whatnot? Do you think that some vendors would never do such a thing? LG is a well known and well respected company so nobody expected this[/url]. So sure, it is always theoretically possible, but are there instances of this actually happening?
And there are means of seeing what is happening, even if you don't have the code:
If something is connecting to the DB, you will see those connections and processes coming in. And you can also capture SQL being executed.
If something is spawning new processes on the OS, you can see those in Task Manager or I am sure that there are utilities out there to log every process for a period of time, much like SQL Profiler.
You can secure your file system by properly setting up NTFS permissions for who has access to what AND running the SQL Server service account to a login that doesn't have permissions to things that it shouldn't
You can monitor network traffic with tools such as Fiddler [/url]
As far as I can tell, the only real concern with 3rd Party Assemblies is poorly written code that might have performance issue. And of course, the simple fact that all (or nearly all) software has some number of bugs, even if very minor. But the issue of bugs is moot given that there are bugs already in Windows and SQL Server, we don't have that source code, and still use those products.
.NET Reflector is ok but what if they obfuscate the assembly? I'm not a .NET developer but I assume there are ways to protect one's code?
As someone else mentioned, obfuscation just makes the code hard to read; it should not be confused with encryption. Phil mentioned that Smart Assembly by Red-Get can obfuscate. And to answer his question about anyone actually trying it: I did, although it was over a year ago and I don't recall what version they were on, maybe 5? So I haven't tried with version 6. But I do remember that parts of it worked and parts of it didn't. I think scalar UDFs worked but for some reason TVFs did not. I would love to test it again on Version 6 but am so swamped with stuff to do that I just don't have the time :-(.
If you can regenerate (reverse-engineer) the code using the .NET Reflector how can you protect your software? Ok, if it's obfuscated then -if I'm not mistaken- variables, methods... have no meaning but one can still get to the logic (if it's really worth it). Right?
Correct, there is no true way to protect your code from people seeing it, outside of never giving it to them; if people can copy not just Apple products, but also their stores[/url], then they can likely get your code. I completely agree with both points that Robert Sterbal made: 1) Protection is more of a legal issue than a technical one, and 2) a benefit of the SaaS / hosted model is that the company has the only copy of the software. To me, the best protections one has are to keep innovating and be very responsive to customers.
So I'm still not convinced CLR is more secure than xp_cmdshell when it comes to assemblies you don't own.
Well, the context of this article was using CLR yourself to replace xp_cmdshell, and at least in that context CLR is often, but not always, better. When it comes to 3rd party assemblies, clearly there are no guarantees, but I also don't see any real cause for concern.