Error Creating an Assembly in SQL 2008 from a DLL

Question

Iam creating a simple Assembly in SQL Server 2008 as follows:

CREATE ASSEMBLY AsmGetDateTimeOffset FROM 'C:\getDateTime.dll';

By default the  PERMISSION_SET is supposed to be "SAFE"

When I create a function to consume this Assembly:

CREATE FUNCTION dbo.clrDateTimeTest

( @DateTime1 datetime, 
  @Timezone nvarchar(100)
)   
RETURNS DatetimeOffset(3)
AS EXTERNAL NAME AsmGetDateTimeOffset.[getDateTime.dateTimeNow].datetimeoffset1;

Iam able to create the function, but on calling the function with parameters

SELECT dbo.clrDateTimeTest(GETDATE(), 'Eastern Standard Time')

Iam getting the following error:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clrDateTimeTest": 
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
 The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort
 System.Security.HostProtectionException: 
at getDateTime.dateTimeNow.datetimeoffset1(DateTime DT, String TimeZone)

On setting the PERMISSION_SET to "UNSAFE" while creating the assembly, iam not getting any error and my function seems to work fine.

How can I avoid using the PERMISSION_SET "UNSAFE" option and get it to work?

Answer 1

Hello,

I don't know your code, but by the assembly & parameter names I guess you use the CLR to do some timezone conversion and the Windows timezone informations are stored in registry and so the CLR has to access the registry, which is an external source for the SQL Server, and that's the reason why it don't work with permission "SAFE". You have to grant at least "EXTERNAL_ACCESS".

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Apparently your function performs some action that is not deemed safe by SQL Server. That is, the procedure could have menacing effects on SQL Server if it is not implemented properly. Since you did not post the source for you CLR function it's hard to tell exactly what's unsuitable. But MayLeakOnAbort suggests there is some memory-allocation problem.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Thanks.

You are actually right. The DLL (or assembly in SQL Server), accepts Date and timzone (string) as parameters, and returns back a datetimeoffset value with the correct offset.

Iam not sure if the CLR has to access the registry for this? Is this the case?

Answer 4

Iam not sure if the CLR has to access the registry for this? Is this the case?

Yes, the timezone information are stored in Registry, see e.g. TIME_ZONE_INFORMATION structure and Time Zone Registry Settings

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 5

Also it is difficult to implement the "UNSAFE" option on Production environments? I could see that for the "UNSAFE" option to work, the database owner on which the Assembly is being created, needs to be the same as the owner for the "Master" database.

Answer 6

Both UNSAFE and EXTERNAL ACCESS assemblies requires server-level permission. A "simple" way is to make the database trustworthy, however this comes with a security risk that could permit a user who is db_owner to elevate his privileges on the server.

A safer way is to sign the assembly with a strong name. The in SQL Server create an assymetric key from the assembly in the master database, and from the assymetric key create a login (which is only a technical login) that is granted the appropriate of the permissions of UNSAFE ASSEMBLY or EXTERNAL ACCESS ASSEMBLY.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

There isn't any relation between a CLR and the "master" database; otherwise you could detach the db and attach it to an other server.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 8

Well, it turns out I had to change the DB owner to one who has UNSAFE previligies or in other I had to change it to an Owner who probably has all Sysadmin previliges.

Answer 9

I agree. 

Microsoft recommends the second option you have mentioned.

"We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLYpermission."

They have also given the code to create the asymmetric key:

USE master; 
GO  
 
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\getDateTime.dll'   
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey   
GRANT UNSAFE ASSEMBLY TO SQLCLRTestLogin; 
GO 
But when I tried using my DLL to create the Assymetric Key, it does not work and it gives me an error:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

Answer 10

Did you sign the assembly when you built it?

Create the key pair:

   sn -k keypair.snk

Then use

   /keyfile:keypair.snk

When you build the assembly.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

BTW, there is a dedicated forum for CLR in SQL Server available: .NET Framework inside SQL Server

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 12

Iam not sure I follow you, dont think I know how to do that.....

Answer 13

Thanks.

Answer 14

Iam not sure I follow you, dont think I know how to do that.....

With Strong Name tool (sn.exe from Windows SDK) you can create a strong name key (snk) file to sign your assembly, see MSDN How to: Sign an Assembly with a Strong Name and Creating and Using Strong-Named Assemblies ; your assembly will be more secure and therefore more trustworthy.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 15

Iam not sure I follow you, dont think I know how to do that.....

Well, this is a newsgroup for T-SQL, not for using C#. You use sn from a command-line window and /keyfile is a command-line switch which you apply to the CSC command. If you are using Visual Studio to build your assembly and you don't know how to add command-line options I am afraid I can't help you - I don't know how to build C# programs/DLLs at all. (I tried once, but I gave up. Using the command-line proved to be a lot easier.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 16

Just to clarify that I do not know how to create c# DLLs etc.. I am a pure SQL Server programmer, and this CLR assembly too is something new to me that Iam using for the first time.

Thanks for your responses.

Answer 17

If you create a regular safe assembly you say (in a command-line window):

csc /target:library myassem.cs

And then you load the DLL with CREATE ASSEMBLY.

When you need to create a privileged assembly (EXTERNAL_ACCESS or UNSAFE) you say:

sn -k keypair.snk
csc /target:library /keyfile:keypair.snk myassem.cs

---

Erland Sommarskog, SQL Server MVP, [e-mail]