How to Use Third Party Dll Reference in a SQL CLR Function

Posted on: 2015-09-09

In a previous article, I was using a SQL CLR function written in C# to be executed by SQL server during a migration of account. The reason was to use the hashing code from Asp.Net Identity that is used by Entity Framework and Asp.Net. In that article, instead of using a copy of the code of the real implementation, we are going to use the real Asp.Net Identity dll. Our C# Dll will just be a proxy to the third party.

 [Microsoft.SqlServer.Server.SqlFunction] public static SqlString HashPassword2(string plainPassword) { var hasher = new Microsoft.AspNet.Identity.PasswordHasher(); return hasher.HashPassword(plainPassword); } 

The code is way simpler, and reuse the Microsoft Asp.Net code which is interesting. The problem is that SQL project does not allow to use NuGet. It's not possible to get the reference from a NuGet package. So, we need to select the DLL from the browse window of the reference. The simple way to do it is to select the one in the Bin folder of you Asp.Net MVC project. Once done, we still need to do something to allow SQL to read it. If you do not, it will compile but not publish.

 Creating [MySqlToMsSql]... (63,1): SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'microsoft.aspnet.identity.core, version=2.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.' was not found in the SQL catalog. (63,0): SQL72045: Script execution error. The executed script: CREATE ASSEMBLY [MySqlToMsSql] AUTHORIZATION [dbo] FROM 0x4D5A9000... An error occurred while the batch was being executed. 

To fix that issue, we need to go in the property of the third-party DLL added to the project.

The property about Model Aware, Is Visible and Generate Sql Script must be set to true.