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;
RECONFIGURE;
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:
- Identify the location of the DLL on your local machine
- Invoke sp_add_trusted_assembly and let Azure SQL MI know that you fully trust this DLL
- 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)
WITH PERMISSION_SET = UNSAFE;
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 ..."
Write-Host
$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
WITH PERMISSION_SET = UNSAFE;
"
$Cmd2Short = "CREATE ASSEMBLY $SafeName
FROM 0x$ShortenedHexLiteral..(content trimmed due to length)
WITH PERMISSION_SET = UNSAFE;
"
Set-Clipboard $Cmd
Write-Host "The following content has been added to your clipboard:"
Write-Host
Write-Host $Cmd
Write-Host
Write-Host "Open your Azure SQL MI instance, paste and execute the command there."
Write-Host
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
Write-Host $Cmd2Short
Write-Host
Write-Host "Open your Azure SQL MI instance, paste and execute the command there."
Write-Host
Read-Host "Press ENTER key to continue once finished ..."
Write-Host "All done!"
Write-Host
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:
- Embed C# in the heart of the SQL Query Engine
- Common Language Runtime Integration
- Architecture of CLR Integration
- CLR Integration Code Access Security
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!