How do I access the .NET TimeZoneInfo class from SQL Server 2005?

Question

The TimeZoneInfo class has a Host Protection Attribute of MayLeakOnAbort.

This seems to prevent me accessing it from the SQL Server CLR. But is there a workaround?

Answer 1

Yeah sure. You encapsulate it in another .NET class and then reference from your .NET assembly in SQL Server. Then you complete disable the security in your SQL Server database so you can run the second class.

If that does not work, you can create a COM wrapper and then an Interop wrapper and still destroy security in SQL Server to run the thing.

Now, if security actually matters to you, you can create a WCF service that wraps the bits you need and use it as a service from your SQL code. It is a bit of latency and cannot work against objects in SQL Server, per se, but it is cleaner.

Sorry for the snarky sounding answer, but I am in a strange mood right now. :-)

Answer 2

Deploy your CLR procedure as Unsafe:

Right click project in Visual Studio > Properties > Database tab > Permission Level

Then, to deploy it, you need to configure the database so it will accept unsafe assemblies. Two options:

the database owner (DBO) has UNSAFE 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 UNSAFE ASSEMBLY permission.