Unsafe?

Question

Unsafe?

LerxtDBA

SSCrazy

Points: 2190
More actions
September 30, 2008 at 2:11 pm

#191560

I'm just getting into CLR and I think I probably did a bad thing. It was certainly expedient though.
We're an insurance company and we use a scanning solution for claim forms. We wanted to make the form images available on our web-based claim processing app. The java app that we use to display the forms requires us to specify the number of pages in the file (.tif files). Unfortunately there was no good way to do this. Our web app is CF based and our web developers really don't do .net or COM or any real Microsoft-based coding.
I found the system.drawing.image .net object, added it to a db as an assembly and wrote a 10 line VB clr that accepts a UNC file name and returns a page count.
The downside here is that it requires me to add a .net object to SQL Server that isn't included by default. Because who would want to do image work through SQL, right? Also, it would only deploy as "unsafe".
The upside is now we run what looks like a sql function and get information about our image file. It's fast and I think I've trapped all the potential errors so it won't generate a SQL-level error for the web app.
I understand potential issues around loading a tif image into memory on my sql box and I'm willing to accept them. Memory use, network traffic, UNC path not available, etc. Are there any issues around having to set a database as "Trustworthy" and running CLR code in "Unsafe" mode that I should be aware of? The real question being should I push one of my web developers to figure it out on their end or am I ok with the quick solution I came up with for them?
Kent

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I wouldn't turn trustworthy on just to run a External_Access or Unsafe assembly. Trustworthy allows numerous other things to occur security wise as well, and you don't need those allowed to use this CLR Assembly. It also creates complexity when you have to restore the database, either in a disaster scenario or to a testing environment. Instead you should sign the assembly with a certificate, and use the certificate to create a login with unsafe assembly rights in SQL Server which is the Microsoft recommended best practice. An example of how to do this is:

http://www.sqlclr.net/Articles/tabid/54/Page/2/ShowHiddenExpired/False/Default.aspx

As for other problems, you need to monitor your VAS freespace because if it becomes overly fragmented or runs out, you will experience AppDomain unloads which could cause errors in execution if timed properly. This probably won't be that big of an issue if you are on a 64 bit server, but on 32 bit SQL Servers, SQLCLR runs in a finite memory reservation called the MemToLeave area. By default, most SQL Servers with 4GB of RAM will only have 384MB of MemToLeave space. Of this 128 is used for managing worker threads, and the rest is used by Large Memory allocations > 8KB, Linked Servers, OLEDB calls, OLE Automation routines, SQL Server itself, extended stored procedures, and SQLCLR.

I personally would make the web guys figure out how to calculate the page count, and then store that in a column of int datatype called NumPages in the table. That or create an external service in .NET that uses the SQLDependency class on the table holding your image data. When data changes in the table, the Service Broker will trigger an event in the external service which can then load the image, and update the NumPages column in the database table for any updates, or inserts. This at least keeps the process outside of SQL Server, and the MTL memory allocation. The other thing to consider is that reading larger files using SQLCLR may result in false Non-Yielding scheduler notifications and mini-dumps if the process fails to yield for 60 seconds or more. This can at times cause a SQL Service failure if the dumper fails or encounters problems. I have seen this a number of times on testing servers, though I have yet to definatively prove the problem where I would say absolutely that it is caused by CLR. However, if I disable CLR, or stop the process that reads from external files, then the problem goes away in every single case.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

"TRUSTWORTHY" is a Security issue, it means that TRUSTWORTHY databases should be trusted by other databases and not to worry about the possibility that some user could hack that DB, give themselves DBO or DB_OWNER rights, then use the Schema/Role/Rights of some User in that DB to gains access to their corresponding Server Logon identity and then use that to get into a different database with privileges.

"UNSAFE" is a Reliability issue and has nothing in particular to do with Security. UNSAFE means that Microsoft has not added one or more of the DLLs your CLR Assembly is using, either directly or indirectly (and it is usually indirectly), to their very short list of SAFE and EXTERNAL_ACCESS dlls/assemblies. What's the risk? No one knows because Microsoft has never published a concrete breakdown of what the actual risks are nor their likelihoods.

What are they worried about? As far as I can tell, two things: one that they allude to in their FUD speeches, and one that they do not talk about. The one that they allude to is memory problems: explicitly memory leaks (and implicitly memory corruption). They worry that some of that ancient COM/DCOM/RPC/DDE code that is still running at the core of the nice new .Net DLL's might mess up SQL Server's memory management.

If you find that odd, given that the whole point of .Net and Managed Code was specifically to prevent that, then you are not alone.

The other problem, that they rarely talk about, but which can be inferred by reading between the lines is: Synchronization Incompatibilities that may result in SQL Server hanging. It seems that the synchronization primitives of SQL OS (which still uses cooperative scheduling) are not really compatible with those of .Net/Windows (which uses preemptive scheduling) and it appears that they are concerned that uncooperative CLR code could cause SQL Server performance to suffer or even hang the Server.

What are the real chances of this? I couldn't tell you since MS never talks about it directly. Maybe some of our MVPs can get better info on this? ...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

LerxtDBA SSCrazy Points: 2190 More actions · Answer 3

Thanks so much. I think I understand now. You can see how I might have gotten the idea that anything other than SAFE was tied to TRUSTWORTHY when you see what lead me down that road:

the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on

The issue now becomes how to deploy an assembly that has external access or unsafe permission level without turning on trustworthy. I've signed the assembly (with a .snk) but I'm trying to find how to meet these criteria:

the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

Obviously, the login I'm using has external access assembly permissions - I can turn trustworthy on and it works.

I'll do some looking around to answer this one.

Kent

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Kent,

I documented how to do this with screenshots on the link I provided above. You can access the article directly though using:

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Yes, as Jonathan shows, Certificates allows you to get around the TRUSTWORTHY requirement, because you cannot use DB level authorities to use another Server-level Certificate-based Login.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

LerxtDBA SSCrazy Points: 2190 More actions · Answer 6

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx

Two words:

You rock!

That helped tons. Now the follow-up question. Can I use that login based on that Cert for other CLRs as well?

Kent

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

As long as you use the same certificate key to sign the other assemblies.

LerxtDBA SSCrazy Points: 2190 More actions · Answer 8

You know what? I don't think I even care about this one that much. I'll push my web guys to come up with a replacement for this so I can just remove the assemblies that do this. It was a neat trick and it gave us an immediate solution to the problem at hand but I'm going to let them know it's just an intermediate solution rather than a permanent one.

This discussion has helped me with the other thread I had going as well so it's not been a waste of time. You all are really great to spend the time to educate me on this.

Kent

Last one for a while, I promise.
So I've got system.drawing.image imported as an assembly in SQL. Unfortunately I didn't create it so it has no key. That pushes me back to making a database TRUSTWORTHY in order to be able to use it.
Here's what I've done:
ALTER DATABASE myDB SET trustworthy ON;
GO
CREATE ASSEMBLY MicrosoftSystemImagingLibrary
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.drawing.dll'
WITH PERMISSION_SET = UNSAFE
This only works when the database is set to TRUSTWORTHY. Then I have to change the owner of the assembly MicrosoftSystemImagingLibrary to my newly created login. Once that is done, the .net-based assembly and my custom one have the same owner and will work together.
So the question becomes: Is there a way to take what is essentially a 3rd party DLL and register it as an assembly with my own key so that TRUSTWORTHY doesn't have to be turned on?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Since the BCL consists of signed assemblies, you can still do it without Trustworthy:

CREATE ASYMMETRIC KEY SYSTEM_DRAWING_KEY

FROM EXECUTABLE FILE = 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.drawing.dll'

GO

CREATE LOGIN SYSTEM_DRAWING_Login FROM ASYMMETRIC KEY SYSTEM_DRAWING_KEY

GO

GRANT UNSAFE ASSEMBLY TO SYSTEM_DRAWING_Login

GO

USE [SQLCLR_Examples]

GO

-- Add a database user in the SQLCLR_Net Database for the Login

CREATE USER SYSTEM_DRAWING_Login FOR LOGIN SYSTEM_DRAWING_Login

GO

CREATE ASSEMBLY MicrosoftSystemImagingLibrary

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.drawing.dll'

WITH PERMISSION_SET = UNSAFE

LerxtDBA SSCrazy Points: 2190 More actions · Answer 10

Makes sense but then I'd have to use that login for my assembly as well, yes? It seems my custom assembly has to be owned by the same owner as the .net-based assembly.

Kent

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

I would have to play with this to know. I don't register unapproved assemblies in my servers, so I have never encountered this. I'll have to play with creating an assembly that references an unapproved one to figure out if there is another option to trustworthy on.

LerxtDBA SSCrazy Points: 2190 More actions · Answer 12

Yeah, I'm running into circular problems with this if I were to go that route. Seems I'd have use the same login that was created using the key from the .net dll. But then I don't have the original key file from that dll so my assembly wouldn't be able to do it. It seems like more trouble than it's worth at this point for something that sounds like a generally bad idea.

Kent

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

kent.kester (10/1/2008)
It seems like more trouble than it's worth at this point for something that sounds like a generally bad idea.

That would be my thoughts on this exactly, but I can play with it a little and see if there is something that can be done. I don't recommend to anyone that they register an unsupported BCL assembly in SQL Server. RBarryYoung has the only example that is the exception to this, and that is the OracleClient, which I have opened a connect with Microsoft for. If the SQLClient can be safe I don't get why the OracleClient is Unsafe. They work very similarly.

LerxtDBA SSCrazy Points: 2190 More actions · Answer 14

If you want to do for academic reasons I'd be interested in the results. I lost a coder today leaving me neck deep in deadlines or I'd try it myself and let you know what I found out. Like I said our web guys aren't really good with MS or I wouldn't have put this in place. It was a matter of the fastest way to solve a problem and I had the tools in front of me.