2

I have a .Net assembly (64 bit environment) which has P/Invoke method to load an unmanaged assembly. I want to consume the method available in the .Net assembly in to the SQL stored procedures. purpose of this is for error logging of SQL realated exceptions and errors.

Below is the method in .Net assembly to include in to the SQL. MyLogger.Logger.LogInfo method is defined in the MyLogger assembly which internally has P/Invoke method to load an unmanaged assembly.

public class SQLLogger
{
    [Microsoft.SqlServer.Server.SqlFunction()]
    public static void LogInfoMessage(SqlString infoMessage)
    {            
        MyLogger.Logger.LogInfo(infoMessage.ToString());          
    }

I built a .Net assembly and run the below query in SQL Server 2008 R2.

 CREATE assembly SQLLogger from 'C:\tmp\SQLLogger.dll' WITH PERMISSION_SET = SAFE

I kept the referenced MyLogger.dll in the same folder as well, and I get the below exception.

CREATE ASSEMBLY failed because type "MyLogger.Logger" in safe assembly "MyLogger" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.

I tried to create assembly in sql manually using the values Unrestricted, External Access for the Permission Set and I failed to create.

I am getting the below error during the manual operation for the External Access Permission Set & Unrestricted Permission Set.

Create failed for SqlAssembly 'SQLLogger'. (Microsoft.SqlServer.Smo) ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Assembly 'mylogger, version=1.0.0.0, culture=neutral, publickeytoken=b8f8bb225641c4a3.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)

There are some relevant posts but I didn't get any solution out of it,

Calling unmanaged C/C++ DLL functions from SQL Server 2008

http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/4a032c4b-1402-4c53-b34e-8c8bd724f904

Thanks for your help!!!

Community
  • 1
  • 1
Dev
  • 960
  • 2
  • 15
  • 35
  • *I tried to create assembly in sql manually using the values Unrestricted, External Access* And what happened? – ta.speot.is Mar 25 '13 at 12:24
  • I am getting errors during the manual operation. I updated the error details above. – Dev Mar 26 '13 at 03:00
  • Here is the MSDN link for SQL CLR Integration http://msdn.microsoft.com/en-us/library/ms345106.aspx – Dev Apr 03 '13 at 07:46

2 Answers2

2

So it's complaining it can't automatically load the referenced assembly, what happens if you try loading the other assembly too, like this:

CREATE ASSEMBLY MyLogger FROM 'C:\tmp\MyLogger.dll' WITH PERMISSION_SET = UNSAFE

Then try your referencing assembly:

CREATE ASSEMBLY SQLLogger FROM 'C:\tmp\SQLLogger.dll' WITH PERMISSION_SET = UNSAFE
steoleary
  • 8,968
  • 2
  • 33
  • 47
  • I am getting below error, there is a post in msdn regarding this but it didn't help me, CREATE ASSEMBLY for assembly 'MyLogger' failed because assembly 'MyLogger' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: 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. – Dev Mar 26 '13 at 13:48
  • You need to be a sysadmin in order to create unsafe assemblies. – steoleary Mar 27 '13 at 09:16
0

did you try using PERMISSION_SET=UNSAFE? this is requires so the assembly can use unmanaged resources.

Shahar Gvirtz
  • 2,418
  • 1
  • 14
  • 17
  • 1
    I am getting the below error during the PERMISSION_SET=UNSAFE . Assembly 'SQLLogger' references assembly 'myloggerlogger, version=1.0.0.0, culture=neutral, ', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request. – Dev Mar 26 '13 at 03:11
  • You're assembly referencing another assembly (myloggerlogger) that sql server failed to find automatically. you can add it manually, and see if it works out. – Shahar Gvirtz Mar 26 '13 at 16:41