December 8, 2010 at 11:07 am
In our environment we have several SQL-CLR assemblies that have been deployed with UNSAFE permission set.
These assemblies consume web services, and, according to the developers, they could not be deployed with the EXTERNAL_ACCESS permission set.
From what I have read, UNSAFE assemblies can potentially destabilize a server, cause memory leaks etc.
What are other DBAs doing about this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 8, 2010 at 11:14 am
It's kind of a big subject.
The security issues come from a couple of factors. First, have you verified the contents of the DLLs that you're loading as assemblies? Second, have you made sure that rights-escalation issues are prevented on your servers, where someone could end up with the right to load an assembly and have it to do harmful things?
The issues with memory leaks and stability mainly come from what the DLLs will do, not from an inherent instability in the security settings on them. Basically, they will be doing things outside of the SQL service's control, and its garbage cleanup can't make sure those outside processes are cleaned up properly. So, if the code they are accessing is fine, then you won't have issues on this.
Is there a means whereby you could have a service outside of SQL Server consume the web services and then interact with SQL Server, instead of calling the consumption from within?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 11:35 am
i have run into these type of issues before. COTS products and 3rd party vendors are always the culprits.
the only thing i ever demanded was that for those types of apps, they must reside on a seperate server. Cost more, but will protect other apps in a true shared enviroment or multiple instance server.
December 8, 2010 at 11:51 am
GSquared (12/8/2010)
It's kind of a big subject.The security issues come from a couple of factors. First, have you verified the contents of the DLLs that you're loading as assemblies? Second, have you made sure that rights-escalation issues are prevented on your servers, where someone could end up with the right to load an assembly and have it to do harmful things?
The issues with memory leaks and stability mainly come from what the DLLs will do, not from an inherent instability in the security settings on them. Basically, they will be doing things outside of the SQL service's control, and its garbage cleanup can't make sure those outside processes are cleaned up properly. So, if the code they are accessing is fine, then you won't have issues on this.
Is there a means whereby you could have a service outside of SQL Server consume the web services and then interact with SQL Server, instead of calling the consumption from within?
Thanks for the reply.
Regarding the questions you have raised:
(1) Verifying the contents of the DLLs:
Unfortunately, this is very much tied to the expertise of the DBA, to be able to check whether the .NET code follows best practices, disposes of objects gracefully etc. I have some experience with .NET (not a lot), and I'm sure many DBAs don't have any. Not an excuse, I know, but when it comes to something like this, DBAs are faced with a dilemma: if they block release of functionality of an UNSAFE assembly, they risk the wrath of many stakeholders, devs, the business etc.; if they allow release of that functionality, they risk the well-being of the server, as well as other issues, such as security, that you mention.
If there is a tool out there that I can parse the assembly through it, and it can tell me what kind of risks I am dealing with, that would be great.
Is there something like this?
(I know of the PEVerify tool (peverify.exe), but this is for EXTERNAL_ACCESS assemblies, is it not?
http://msdn.microsoft.com/en-us/library/ms403273.aspx)
(2) Rights-escalation issues:
I don't think we have this problem, but can you be more specific as to what I should be looking for?
(3) Is there a means whereby you could have a service outside of SQL Server consume the web services and then interact with SQL Server, instead of calling the consumption from within?
I'm not sure that would work for us; the advantage of SQL-CLR is that web-services can be called from within the T-SQL code, and that is a big plus. We are using web services to facilitate communication between database servers in our environment.
Thank you again for the input!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 8, 2010 at 1:50 pm
Geoff A (12/8/2010)
i have run into these type of issues before. COTS products and 3rd party vendors are always the culprits.the only thing i ever demanded was that for those types of apps, they must reside on a seperate server. Cost more, but will protect other apps in a true shared enviroment or multiple instance server.
Thanks, this is certainly something for us to think about.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 8, 2010 at 2:08 pm
On the subject of what's in the DLLs, you may not be able to verify it yourself, but you can insist on a level of code-review and sign-off by the management of the dev team. Of course, depending on the size of the shop, that may be the same person as the one who wrote the code, but at least a signed attestation that the code does only what it's supposed to. The idea isn't just CYA, though. It's best if there's an actual review, because issues the original dev may have overlooked might be spotted at that point.
The point of rights escalation is that an unsafe assembly injected from an outside source, or a maliciously or ignorantly created internal assembly, can access things like AD and the file system, web services, et al. If it can access web services, and the file system, it may be able to download malware. Thus, you need to make sure that nobody can create assemblies without the kind of review I mentioned above. No ex-dev, no random outside person who hacked through the firewall. Correct assignment of rights with regard to object creation will prevent this kind of thing. It's just basic security policy, but it means you don't let devs into the server with sa rights, that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 2:32 pm
GSquared (12/8/2010)
On the subject of what's in the DLLs, you may not be able to verify it yourself, but you can insist on a level of code-review and sign-off by the management of the dev team. Of course, depending on the size of the shop, that may be the same person as the one who wrote the code, but at least a signed attestation that the code does only what it's supposed to. The idea isn't just CYA, though. It's best if there's an actual review, because issues the original dev may have overlooked might be spotted at that point.The point of rights escalation is that an unsafe assembly injected from an outside source, or a maliciously or ignorantly created internal assembly, can access things like AD and the file system, web services, et al. If it can access web services, and the file system, it may be able to download malware. Thus, you need to make sure that nobody can create assemblies without the kind of review I mentioned above. No ex-dev, no random outside person who hacked through the firewall. Correct assignment of rights with regard to object creation will prevent this kind of thing. It's just basic security policy, but it means you don't let devs into the server with sa rights, that kind of thing.
You are raising some good points; certainly, insisting on a proper review process is a must, and I will follow up on that with our dev team.
As a DBA, I want to learn more on the following topics:
(a) web-service security
(b) .NET and web services
(c) wcf services and security
Would you recommend any good resources on these topics?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 8, 2010 at 2:33 pm
Start with Wikipedia. Go from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply