SQL Server can't find my function in my DLL / Assembly

Question

I created a library class in C# with this code as you can see:

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        public BaseFunctionClass()
        {
        }


        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString
        strMessge)
        {
            strMessge = "Welcome," + strName + ", " + "your code is getting executed under CLR !";

        }
    }
}

I built this project with the UNSAFE Permission Set property, and I added the DLL to SQL Server using this code:

use master;
grant external access assembly to [sa];
use SampleCLR;
CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'd:\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

It added the assembly as part of my database.

I want to call the function as you can see:

CREATE PROCEDURE usp_UseHelloDotNetAssembly
@name nvarchar(200),
@msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage
GO

But I get this error:

Msg 6505, Level 16, State 2, Procedure usp_UseHelloDotNetAssembly, Line 1
Could not find Type 'ManagedCodeAndSQLServer.
BaseFunctionClass' in assembly 'ManagedCodeAndSQLServer'.

@BradleyUffner i added this code .is it enough?use master; grant external access assembly to [sa]; use SampleCLR; — Ehsan Akbar, Feb 17 '17 at 16:29
Please don't use `UNSAFE` there is absolutely no reason for this code to not be marked as `SAFE`. Also, you never need to grant permissions to `sa` as that Login always has all permissions. — Solomon Rutzky, Feb 17 '17 at 16:35
@BradleyUffner yes i did that ,based on this article https://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server — Ehsan Akbar, Feb 17 '17 at 16:37
Also, please do not set your database to `TRUSTWORTHY ON`. That is an unnecessary security risk. Also, that is just at the DB level, not at the server level. — Solomon Rutzky, Feb 17 '17 at 16:37
@srutzky i execute this :ALTER DATABASE TestingCLR SET TRUSTWORTHY Off GO but same error — Ehsan Akbar, Feb 17 '17 at 16:39
I'm going to delete my comments, as people who know more about sql security than me are saying my suggestions are security risks. I don't want to be giving anyone bad advice. — Bradley Uffner, Feb 17 '17 at 16:39
At first glance the code looks just fine. Usually this error happens if you either don't specify the namespace name or if you don't have the class marked as "public". but you have done both of those. Are you sure that this is the _exact_ code that was in that Assembly? — Solomon Rutzky, Feb 17 '17 at 16:44
@srutzky i compile my code without unsafe feature and i execute this code as you said but same error :CREATE ASSEMBLY ManagedCodeAndSQLServer AUTHORIZATION dbo FROM 'd:\ManagedCodeAndSQLServer.dll' GO alter database testingclr set trustworthy off; GO CREATE PROCEDURE usp_UseHelloDotNetAssembly name nvarchar(200), msg nvarchar(MAX)OUTPUT AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer. BaseFunctionClass].GetMessage GO — Ehsan Akbar, Feb 17 '17 at 16:44
@srutzky could you please take a look at this ,i am trying to implement this article https://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server — Ehsan Akbar, Feb 17 '17 at 16:45
Just FYI: for these comments, you can enclose code in single back-ticks for better `formatting` :). Also, I just looked at that article and it is _horrible_. It should be removed from codeproject. Please forget you ever saw it. If you are learning SQLCLR, please see the series I am writing on that topic on SQL Server Central: [Stairway to SQLCLR](http://www.sqlservercentral.com/stairway/105855/) (free registration is required to read their content). I am trying your code on my system. Will let you know. — Solomon Rutzky, Feb 17 '17 at 16:52
@srutzky thank you very very much . i am trying to develop my code based on your article :) — Ehsan Akbar, Feb 17 '17 at 16:53
Ok, so I tried your code from a simple copy and paste from here. When I executed the T-SQL, I got the same error. Then I noticed that when I copied and pasted, it kept the newline between the namespace name and the class name that are enclosed in square brackets. I removed the newline so it appeared as `[ManagedCodeAndSQLServer.BaseFunctionClass]` and it worked. I had assumed the newline in your SQL and even in the error message was from formatting done here on S.O. But it looks like it really is there in your code. Remove the newline and it should work. If so, I will post an answer for it. — Solomon Rutzky, Feb 17 '17 at 17:03

Solomon Rutzky · Accepted Answer · 2017-02-17T17:33:34.277

The problem is subtle, and in an online format such as here, it even appears to simply be a matter of formatting. But the issue is entirely found here, within the square brackets:

AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage

There is an actual newline character after ManagedCodeAndSQLServer. that should not be there. It is even reflected in the error message:

Msg 6505, Level 16, State 2, Procedure usp_UseHelloDotNetAssembly, Line 1
Could not find Type 'ManagedCodeAndSQLServer.
BaseFunctionClass' in assembly 'ManagedCodeAndSQLServer'.

which again looks like a matter of word-
wrapping ;-), but isn't. If the newline is removed such that the T-SQL appears as follows:

AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage;

then it will work just fine.

Some additional notes:

No need to ever grant sa anything. Any member of the sysadmin fixed server role already has all permissions.
There is no need for the code shown in the question to be marked as anything but SAFE. Please do not mark any Assembly as UNSAFE unless absolutely necessary.
There is no need to set the Database to TRUSTWORTHY ON. That is a security risk and should be avoided unless absolutely necessary.
If you are learning SQLCLR, please see the series I am writing on that topic on SQL Server Central: Stairway to SQLCLR (free registration is required to read their content, but it's worth it :-).

SQL Server can't find my function in my DLL / Assembly

1 Answers1