CLR Stored Procedure can't access Windows registry on SQL Server 2008 Express R2 - No Problem on Other SQL Server Versions

Question

Hi.

I hope this is the right place to post this question.  We have a C# application that uses SQL Server as a back end and employs the use of CLR stored procedures (also coded in C#)  to handle certain operations.  Our users operate on various SQL platforms (2005, 2005 Express, 2008, 2008 Express, 2008 R2 and more recently, 2008 Express R2), all of which we have to support.  Some of the stored procedures in our custom CLR assembly must read and write to the Windows registry.  As a result, we compile and install our assembly with Permission Level 'Unsafe' and we make certain that the 'SystemManagement' assembly is properly installed on the user's SQL Server.  (The latter is necessary to support the use of the Microsoft.Win32.RegistryKey class, which is what we're using to access the registry.)  Additionally, during our install, we enable CLR and SET TRUSTWORTHY ON.  

Over the past months, the CLR stored procedures have installed and functioned perfectly ... until SQL Server 2008 Express R2 that is.  Of all the SQL versions that I list above, 2008 Exp. R2 is the only platform on which our stored procedures are failing. (We've tried installing on various operating systems, but when 2008 Exp. R2 is used, our stored procedures fail consistently on all of them.) 

Specifically, when we try to execute any of the stored procedures that access the registry, we get:

A .NET Framework error occurred during execution of user-defined routine or aggregate "XXXXXXXXX":
System.Exception: Unable to open registry ---> System.UnauthorizedAccessException: Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\XXXXX is denied.

We've scrutinized every permission setting that we're aware of (or have read about) and have confirmed that the assemblies are installed correctly and that everything appears to be intact.  Yet, our stored procedures simply aren't being allowed to access the registry when the SQL Server is 2008 Express R2.  In fact,  in our tests, we started with clean images of various operating systems, installed 2008 Express (and on some,  2008 R2) and then installed our assembly.  Our CLR stored procedures worked fine on all.  We then reset the OS images, installed 2008 Express R2 and our assembly.  No errors were reported during the CLR installation but when we tried to execute the stored procedures, they again failed.  Note that the stored procedures within our CLR assembly that don't access the registry continue to work fine.  In short,  the only difference between our failures and successes seems to be the version of SQL Server that's being used.

It's quite possible that we're just missing some step that's required for express R2 as opposed to any of the other versions ... but at the moment, it's eluding us.

Anyone have any thoughts or suggestions about this?

Thanks.

Tom

---

Tom T.

Answer 1

Hi Tom,

Since the CLR stored procedure accesses external system resources, please check if the service account of the SQL Server 2008 R2 Express instance has sufficient permissions to access the registry key.

References:

CLR Integration Code Access Security
http://msdn.microsoft.com/en-us/library/ms345101.aspx

Setting Up Windows Service Accounts
http://msdn.microsoft.com/en-us/library/ms143504.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.