Upgrade SQL Server Assemblies .NET version

(This is a personal post, just a reminder)

note-to-self

Damn! :/

Each time a new version of .NET Framework is installed (or upgraded) in our SQL Server I need to update some assemblies we’re using in some projects.

Related posts

HowTo: Send Notifications from SQL Server using SignalR

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)

The issue

Executing the send message to hub stored procedure throws an exception:

EXEC clr.SendMessageToHub 'http://THESERVER:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

‘Could not load file or assembly ‘System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC.

Solution (it works for me):

1 – Remove clr stored procedure

2 – Remove assemblies (removing Falcon_SqlProxy should remove other dependences, if not, remove manually)

3 – Go to the GAC folder.: C:\Windows\Microsoft.NET\assembly\GAC_MSIL

4 – Find the assemblies (in this case only ‘System.Runtime.Serialization.dll and System.Neh.Http.dll were needed) and copy them to the SQL Assemblies folder (C:\Falcon\SqlServerAssemblies)

5 – Create assembly Falcon_SqlProxy again:

CREATE ASSEMBLY Falcon_SqlProxy from
 'C:\Falcon\SqlServerAssemblies\Falcon.NotificationsClient.SqlProxy.dll'
WITH PERMISSION_SET = UNSAFE

6 – Create clr stored procedure again:

CREATE PROCEDURE [clr].[SendMessageToHub]
 @url [NCHAR](125),
 @hubName [NCHAR](75),
 @hubMethod [NCHAR](75),
 @message [NVARCHAR](MAX)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].[SendMessageToHub]
GO

7 – Check if SP is working:

DECLARE @jsonmsg VARCHAR(MAX)
SET @jsonmsg = '{"message": "GenerateProposalProgress", "description": "Resolving Portfolios", "userid": "' +
 REPLACE(ORIGINAL_LOGIN(), '\', '\\') +
 '", "formName" : "Falcon.Client.Strategies.Proposals.Forms.ObjectSelectorForm", "value" : '
 + CAST(5 AS VARCHAR(MAX)) +'}'
EXEC clr.SendMessageToHub 'http://elsuper:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

Hope this helps to my future me :P


Note: Today (12/07/2017) after moving some databases to other disks I had to do it again, so I’ve discovered other related issue.

It seems sometimes the USER SID changes after a backup/restore and throws an error:

The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘Falcon’. You should correct this situation by resetting the owner of database ‘Falcon’ using the ALTER AUTHORIZATION statement.

So, before create the assemblies you (I mean me) you’ll need to change the database owner to sa.


EXEC sp_changedbowner 'sa'

ALTER DATABASE [database_name] SET TRUSTWORTHY ON
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)

 

sr

The Story

Hi again,

Some time ago I published a post about how to send notifications from SQL Server using SignalR. We are using SignalR self-hosted in a Windows Service in a separated server, and it had been running fine until now.

This week I had to set up a new SQL server which will replace the old one, which will replace the current ‘preprod’ one, and (entering recursive mode…).

After configuring the new SQL Server settings, I tried to update the previously imported assemblies to the database to the latest versions I’m currently using in the .NET project (Newtonsoft.Json & Microsoft.AspNet.SignalR.Client).

References map:

  • MyAssembly
    • Microsoft.AspNet.SignalR.Client
      • Newtonsoft.Json

As you can see, the app references the SignalR client, which references Newtonsoft dll.

So, in order to redeploy the assemblies it’s necessary to:

  • Remove all the CLR Stored Procedures that are using these assemblies, otherwise you cannot delete the assemblies.
  • Remove the main assembly and then all its dependencies in the correct order (Note: It’s not necessary to remove Framework assemblies if it’s not changed).
  • At this point you’ll be able to create the assembly again via CREATE ASSEMBLY
CREATE ASSEMBLY Falcon_SqlProxy FROM
'C:\Falcon\SqlServerAssemblies\Falcon.NotificationsClient.SqlProxy.dll'
WITH PERMISSION_SET = UNSAFE

All dependent assemblies will be created again in the database (in this case Newtonsoft.Json & Microsoft.AspNet.SignalR.Client). Easy…

BOOM!

However, when tried to import the assembly to SQL Server I’ve got this error:

“Assembly ‘Microsoft.AspNet.SignalR.Client’ references assembly ‘newtonsoft.json, version=6.0.0.0, culture=neutral, publickeytoken=XXXXXXXXX.’, 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.”

I decided to import the assemblies one by one, from down to top.

Firstly I imported Newtonsoft.Json with no problems, but when I tried to import the SignalR client I received this error:

ie1

It seems a versions issue, because I’ve imported Newtonsoft.Json version 9, not 6! Let’s see what’s in the assemblies view:

select * from sys.assemblies

ie2

It’s correct. I’ve imported version 9 of the library… so why the hell SQL is looking for version 6? Well, if you inspect Microsoft.AspNet.SignalR.Client you’ll see that this library has a reference to Newtonsoft.Json version 6.0.0.0 :S

ie3

This version number is used to determine the minimum version number (the library won’t work with previous versions), but it seems SQL Server is trying to match this version number with the assembly version number in its catalog, and if the number is different is unable to import the assembly.

If I’m wrong, please correct me! Thx :)

Solution?

After spending some hours trying different solutions , the only way to solve that -at this time- is register a previous version of Newtonsoft.Json instead of the latest one.

Any version? Nope.

The only version it worked for me is 6.0.8, previous ‘6’ versions didn’t work because of this error:

“‘Method not found: ‘Newtonsoft.Json.Linq.JValue Newtonsoft.Json.Linq.JValue.CreateNull()’.'”

Finally, once I’ve copied the 6.0.8 version of the Newtonsoft.Json to the server path the import process has worked like a charm.

ie4

Now I’m looking for a better solution, because I don’t like using old versions of assemblies. I will update this post with a better solution as soon as possible :D

In the meanwhile, hope this helps.

HowTo: Send Notifications from SQL Server using SignalR

sr

Overview

In our current project we’ve to push some notifications directly from the database server (SQL Server 2014) to different apps. For example, we’ve scheduled some SSIS (SQL Server Integration Services) projects that pre-calculates large amounts of data. And well, should be nice notify all the apps when new data is available without unnecessary round trips to database. And you know? we’re already using SignalR in our apps… so, why not try to build a lightweight client for sending SignalR notifications?

Dear architects, please, don’t judge me :P

The idea

  1. I finally decided to create a small project in C# that connects to our SignalR (which is self-hosted in a Windows Service). That project contains a single method for sending notifications with a message (just a string for simplify).
  2. Once the project is ready and tested, the next step is create an ASSEMBLY, that is, a module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
  3. And finally, create a Stored Procedure that use our previous assembly CLR function (for isolation purposes and decoupling), and then use it in our SSIS projects with a simply ‘Execute SQL Task‘ when the process finalizes.

Creating the C# project

Create a blank class Library project and add the SignalR client (Nuget will add depending assemblies -like Newtonsoft.Json- automatically)

addSignalRClient

Once we added SignalR Client references to the project we need to create the C# method, so let’s add a class item to the project with this code:

public class SqlServerNotificationsClientService
{
    internal static HubConnection hubConnection = null;
    internal static IHubProxy hubProxy = null;
    [SqlFunction()]
    public static void SendMessageToHub(
        string severURL, string hubName, string hubMethod, params object[] args)
    {
        try
        {
            if (hubConnection == null || isConnectionClosed())
                hubConnection = new HubConnection(severURL.Trim()) 
                { Credentials = CredentialCache.DefaultNetworkCredentials };
            if (hubProxy == null || isConnectionClosed())
                hubProxy = hubConnection.CreateHubProxy(hubName.Trim());
            if (isConnectionClosed())
                hubConnection.Start().Wait();
            if(args.Length == 0)
                hubProxy.Invoke(hubMethod.Trim()).Wait();
            else
                hubProxy.Invoke(hubMethod.Trim(), args).Wait();
        }
        catch (Exception exc)
        {
            SqlContext.Pipe.Send(string.Format("{0}.{1} error: '{2}'{3}", 
                hubName.Trim(), hubMethod.Trim(), exc.Message, Environment.NewLine));
        }
    }

    private static bool isConnectionClosed()
    {
        return !(hubConnection.State == ConnectionState.Connected
            || hubConnection.State == ConnectionState.Reconnecting
            || hubConnection.State == ConnectionState.Connecting);
    }
}

Note: Before continue, take a look to the last parameter ‘args’. This parameter allows you to pass a different number of arguments to this method, which is nice because when we invoke the hub’s method we can use the same argument type. Unfortunately this argument type is not compatible with SQL CRL and will crash when creating the Stored Procedure in the database :( So we need to change this argument to a simple string.

This code is just an example, maybe would be a better idea split it into different methods (remember the ‘S’ of SOLID?) :)

Let’s assume the code is ok and is able to connect and send the notification. Now…

Create the SQL Assembly

In order to deploy your assemblies to a SQL Server you need the same version of the .NET Framework installed on the server. Yep, it’s too obvious but…

Create a folder in the server called ‘C:\SQLServerAssemblies’ (or wherever you want) and copy all the assemblies generated by the previous project.

signalRClientBaseAssemblies

But we’ve not finished yet… Now we’ve to add some related assemblies (assemblies dependencies). Otherwise, assembly creation will fail.

signalRClientFullListAssemblies

You can find these assemblies in the same server, in the correct version of .NET Framework. In my case:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319 -> Microsoft Framework 4.5

Once we’ve all the necessary assemblies copied, let’s create the assembly. First we need to activate some things in the server:

ALTER DATABASE Falcon SET trustworthy ON

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

CREATE ASSEMBLY Falcon_SqlProxy from 
  'C:\SQLServerAssemblies\Falcon.NotificationsClient.SqlProxy.dll' 
WITH PERMISSION_SET = UNSAFE 


signalRSqlAssemblies

Yes! Assemblies created successfully in SQL Server ;)

Creating the SP

Finally we only need to create the Stored Procedure. I recommend you use a custom schema. I’ve created one called ‘clr’

CREATE PROCEDURE clr.SendMessageToHub
@url nchar(125) ,
@hubName nchar(75),
@hubMethod NCHAR(75),
@message NVARCHAR(MAX)
AS
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].SendMessageToHub

Remember the note? Have you changed the last argument of the method  SendMessageToHub to a string?

If you did it and copied all the assemblies to the folder it should have worked like a charm ;)

Now it’s time to test our SP… Success!

signalR_SPOk

Bonus:

If you wanna use this SP from a SSIS project to send notifications to your apps, just add a Execute SQL Task to your project and configure the SQL statement:

signalRExecuteSQLTask

Hope this helps! :)

Curiosity: What happens if you remove all the Trim() in the c# code? Why connection to SignalR fails? ;)

See you,