Articles

Current Articles | Categories | Search | Syndication

By Jonathan Kehayias @ Thursday, April 10, 2008 8:35 PM :: 42737 Views :: 0 Comments

Determining whether to set the Trustworthy Property of a database to ON or to sign your assembly with a certificate might be a confusing thing.  Simply searching online for how to run UNSAFE or EXTERNAL permission Assemblies in SQL Server makes it seem as if setting the Trustworthy Property to ON is the right way to go.  This however is not the Microsoft recommended practice.

When I built my first CLR Assembly it was just a simple UDF that required nothing more than SAFE permissions to run.  Since CLR solves a number of problems with String and Data validation, these were the only types of objects I initially created.  However, as I got more into CLR, I found that I wanted to Read/Write to a file.  This requires that the Assembly be registered as EXTERNAL in SQL Server.  However, when you try to create the assembly:

CREATE ASSEMBLY SQLCLR_ExternalAccess
AUTHORIZATION
dbo
FROM 'D:\SQLCLR_Examples\SQLCLR_ExternalAccess.dll'
WITH
PERMISSION_SET = EXTERNAL_ACCESS
GO

SQL Server will raise an error similar to the one below:

CREATE ASSEMBLY for assembly '%' failed because assembly '%' is not 
authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized
when either of the following is true: the database owner (DBO) has EXTERNAL
ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database
property on; or the assembly is signed with a certificate or an asymmetric
key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

Searching online will return numerous posts on blogs and web pages detailing how to turn Trustworthy On for the database so that you can create this Assembly using DDL similar to the following:

ALTER DATABASE SQLCLR_Examples
SET TRUSTWORTHY ON
GO

This will resolve the problem and allow you to create the Assembly and ultimately execute the code inside of it.  However, it doesn't follow the Microsoft Recommendations in the Books Online.  When creating an Assembly, the Microsoft recommendation is not to set the database to Trustworthy just for the sake of using non-SAFE CLR Assemblies.  The best practice is instead to sign the Assembly with a certificate and that an assymetric key be created from the Assembly in the master database.  Then a login can be created mapped to this key and granted the EXTERNAL_ACCESS permission.

Since this is the recommended path by Microsoft, one might ask why so many people, including MSDN Blogs use the Trustworthy property instead?  The only answer I can provide, based on my experience is that it is far simpler to set the Trustworthy Property to On that it is to create the certificate, sign the assembly, and then add the assymetric key to the SQL Server.  It is far easier to set Trustworthy to On, and deploy the CLR Assembly from inside of Visual Studio directly to the database.

In the end the decision is ultimately up to the DBA who allows CLR to run inside their database server.  One thing I have learned over the years, is to generally speaking follow the published Microsoft recommendations for security on my servers.  It wasn't to long ago that web applications used dynamic SQL and sysadmin level rights to talk to SQL Servers, which is completely against the rules today.  Ultimately the level of security that you have using CLR is based on whether or not you allow anything but SAFE Assemblies on your server.  There are however many features that don't exist unless you are willing to expand into at least the EXTERNAL_ACCESS area.

 

Posted @ Thursday, April 10, 2008 8:35 PM by Jonathan Kehayias
Previous Page | Next Page
Comments
Currently, there are no comments. Be the first to post one!
You must be logged in to post a comment. You can login here

Survey

Which of the following CLR objects are you currently using in SQL Server?




Submit Survey  View Results

Links


Copyright 2007 by SQLCLR.net Terms Of Use Privacy Statement
Website graphics provided by Matt Green Designs
Page generated in 0.1092028 seconds.

All information and example code on this site is provided 'as-is', without any express or implied warranty. In no event will the authors be held liable for any damages arising from its use.

This site is in no way affiliated with Microsoft. Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.