SQL CLR - Cannot run SQL CLR from assembly

1

I have a SQL CLR database project in VS 2013. I am deploying to SQL Server 2008 R2. I have done the below steps:

USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

USE [MyDb]
GO

ALTER DATABASE [MyDb] SET TRUSTWORTHY ON

use master; 
grant external access assembly to [Domain\MyUser];

use master; 
create master key encryption by password = '*******';

CREATE ASYMMETRIC KEY MyDbExtKey AUTHORIZATION dbo
FROM FILE = 'C:\MyDataKey.snk'
ENCRYPTION BY PASSWORD = '*******';

create login MyDataExtLogin from asymmetric key MyDbExtKey;

GRANT UNSAFE ASSEMBLY to MyDataExtLogin

Also, I have made the assembly with Permission Level "EXTERNAL_ACCESS" & signed it.

When I run one of the SQL CLR procedures, I receive the following exception:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 

System.IO.FileLoadException: Could not load file or assembly 'mydata.sqlclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=151e208e169e0447' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

System.IO.FileLoadException: 
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)
sql-server
sqlclr
asked on Stack Overflow Apr 2, 2014 by Bill

1 Answer

0

Yes, the main problem is that the assembly needed a PERMISSION_SET of UNSAFE instead of EXTERNAL_ACCESS.

However, there are a few things being done in the script shown in the question that are unnecessary, and in one case, undesirable:

  1. ALTER DATABASE [MyDb] SET TRUSTWORTHY ON:

    This is both unnecessary (should be unnecessary, at least) and undesirable and it opens up the SQL Server instance to potential security issues. It is unnecessary due to creating the Asymmetric Key, the Login based on that Key, and granting that Login the UNSAFE ASSEMBLY permission.

  2. grant external access assembly to [Domain\MyUser];

    This is unnecessary as it doesn't link to the Assembly. It might be something to do if [Domain\MyUser] was the database owner of [MyDb] and you were relying upon TRUSTWORTHY ON, but that should not be the case here.

  3. create master key encryption by password = '*******';

    This is unnecessary due to using ENCRYPTION BY PASSWORD when doing the CREATE ASYMMETRIC KEY. The Master Key is only needed when you don't supply your own password.

So, as long as C:\MyDataKey.snk is the same key file used to sign the assembly, then the following statements should be all you need (all of which are in the question, with the unnecessary statements removed):

USE [master];

CREATE ASYMMETRIC KEY [MyDbExtKey]
  AUTHORIZATION [dbo]
  FROM FILE = 'C:\MyDataKey.snk'
  ENCRYPTION BY PASSWORD = '*******';

CREATE LOGIN [MyDataExtLogin]
  FROM ASYMMETRIC KEY [MyDbExtKey];

GRANT UNSAFE ASSEMBLY TO [MyDataExtLogin];

Then you can either:

USE [MyDb];

CREATE ASSEMBLY [mydata.sqlclr]
  FROM ...
  WITH PERMISSION_SET = UNSAFE;

or:

USE [MyDb];

ALTER ASSEMBLY [mydata.sqlclr]
  WITH PERMISSION_SET = UNSAFE;
answered on Stack Overflow Aug 23, 2015 by Solomon Rutzky • edited Jun 20, 2020 by Community

User contributions licensed under CC BY-SA 3.0