Importing .NET FX and 3rd party DLLs into Azure SQL MI

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .


One question that usually pops up, especially while trying to migrate from on-prem to cloud (i.e., from SQL Server to Azure SQL MI) is how to import assemblies into the Azure SQL MI, given that there is no option to reference actual files on the hard drive.


Another common question that we observe is – “how to import assemblies created outside of our organization”. For example, many struggle with the question of how to import Newtonsoft.JSON library into their SQL MI instance, given that it has some external dependencies. We will try to answer both questions.


.NET libraries available in Azure SQL MI 


SQL Server and Azure SQL MI have the following libraries readily available to be used:

  • CustomMarshalers
  • Microsoft.VisualBasic
  • Microsoft.VisualC
  • mscorlib
  • System
  • System.Configuration
  • System.Core
  • System.Data
  • System.Data.OracleClient
  • System.Data.SqlXml
  • System.Deployment
  • System.Security
  • System.Transactions
  • System.Web.Services
  • System.Xml
  • System.Xml.Linq

This is good enough for most use-cases, however some libraries like Newtonsoft’s JSON require some additional assemblies, like:

  • System.ServiceModel.Internals.dll,
  • SMDiagnostics.dll, and
  • System.Runtime.Serialization.dll

If you have a version of .NET 4.x SDK installed on your system, you should be able to locate these DLLs in there. If not – you will likely have to install the .NET SDK first and then import these two.


Beware that before we can import these two, there is one other important thing we need to take into consideration – CLR Strict Security.


CLR Strict Security


CLR Strict Security was introduced in SQL 2017. Like all security related options, it was introduced in order to guarantee additional safety and protection against potentially-malicious assemblies.


What it does is effectively ignores the PERMISSION_SET option, specified with each assembly, and treats each assembly as UNSAFE. If you want to read more about PERMISSION SETS, you can do so here.


What it means for us is that each assembly that we try to import into our Azure SQL MI, has to be signed either with Certificate or Asymmetric key. However, the problem with 3rd party assemblies is that we do not have access neither to Certificate nor Asymmetric Key that was used to sign them.


In order to solve that, we will rely on usage of sp_add_trusted_assembly. What this stored procedure does is that it tells the SQL Server that we have full trust into 3rd party assembly and that we are 100% sure that we want to have it imported into our Azure SQL MI. Do keep in mind that this command requires sysadmin server role or CONTROL SERVER permission.


Now that we are equipped with knowledge of how to make external assemblies trusted, next step is to see how to actually add them.


Importing external assemblies


If this is the first time you are using SQLCLR on your Azure SQL MI, you will need to have it enabled first: 


EXEC sp_configure ‘clr enabled’, 1; 


Once you have the SQLCLR enabled, the next step is to import the actual assembly. Given that Azure SQL MI is a PaaS offering and that you do not have access to the underlying hard drive, what you need to do is convert the binary file into hex literal. 


Here is the list of all steps that need to be taken:

  1. Identify the location of the DLL on your local machine
  2. Invoke sp_add_trusted_assembly and let Azure SQL MI know that you fully trust this DLL
  3. Invoke CREATE ASSEMBLY command and import the actual DLL

For the sake of this example, I will import System.ServiceModel.Internals.dll, which is one of three DLLs required by Newtonsoft.JSON library. Be mindful that the process is exactly the same for any other external assembly (i.e., assembly not owned by yourself).


Start by executing the following in your PowerShell:


$assembly = “C:\path\to\System.ServiceModel.Internals.dll” 

(Get-FileHash -Path $assembly -Algorithm SHA512).Hash | Set-Clipboard 


Now go to your Azure SQL MI instance and execute the following command:


exec sp_add_trusted_assembly 
@hash = 0x(Paste content of your clipboard here), 
@description = N'System.ServiceModel.Internals.dll' 


If all goes well, you should receive the following success message:


Finally, execute the following on you Azure SQL MI: 


CREATE ASSEMBLY System_servicemodel_internals 
FROM 0x(paste the content of your clipboard here)  


If all goes well, you should be seeing a success message similar to the one above. Great job!


For convenience's sake, here’s a PowerShell script that you can use for the whole process:


Add-Type -AssemblyName System.Windows.Forms 

$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{  
    InitialDirectory = [Environment]::GetFolderPath('Desktop') 
    Filter           = 'DLL files (*.dll)|*.dll' 
    Title            = 'Select DLL file you wish to import' 

$Null = $FileBrowser.ShowDialog() 
$Name = (Split-Path $FileBrowser.FileName -Leaf) 
$SafeName = $Name -replace '\.', '_' 

Write-Host "Converting DLL to hex literal. This might take a while ..." 

$Hash = (Get-FileHash -Path $FileBrowser.FileName -Algorithm SHA512).Hash 
$HexLiteral = (Format-Hex $FileBrowser.FileName | Select-Object -Expand Bytes | ForEach-Object { ‘{0:x2}’ -f $_ }) -join ‘’ 
$ShortenedHexLiteral = "0x$HexLiteral".Substring(0, 10) 

$Cmd = "exec sp_add_trusted_assembly 
@hash = 0x$Hash, 
@description = N'$SafeName'" 

$Cmd2 = "CREATE ASSEMBLY $SafeName 
FROM 0x$HexLiteral 

$Cmd2Short = "CREATE ASSEMBLY $SafeName 
FROM 0x$ShortenedHexLiteral..(content trimmed due to length) 

Set-Clipboard $Cmd 

Write-Host "The following content has been added to your clipboard:" 
Write-Host $Cmd 

Write-Host "Open your Azure SQL MI instance, paste and execute the command there." 
Read-Host "Press ENTER key to continue once finished ..." 

Set-Clipboard $Cmd2 

Write-Host "The following content has been added to your clipboard:" 
Write-Host $Cmd2Short 

Write-Host "Open your Azure SQL MI instance, paste and execute the command there." 
Read-Host "Press ENTER key to continue once finished ..." 

Write-Host "All done!" 


Word of caution


Remain mindful of the fact that, even though it’s convenient, sp_add_trusted_assembly requires high privileges for a reason! And the reason is that you are making a very bold statement that a specific assembly is 100% secure.


We strongly suggest that you execute this ONLY against the assemblies that are signed by Microsoft, or that originate from publishers whose identity you can verify.


For assemblies created by your own organization, you should follow the regular process of ensuring that they are created with proper authorization.


Further reading 


Here are some of the additional resources you might find useful: 

We would love to hear your feedback! If you’ve enjoyed this article or think there might be some improvements to be made, please leave your comment below. Thanks for reading!   

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.