Invoking REST APIs with SQLCLR (for Azure SQL MI)

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

In this article we are going to cover the process of building CLR User-defined Function (UDF) for Azure SQL Managed Instance, that that relies on usage of REST API and Newtonsoft’s JSON library to fetch and parse the output. We will also cover the process of adding 3rd party libraries to Azure SQL Managed Instance (SQL MI), and will discuss permission sets needed in order to make network calls from CLR hosted on SQL MI. Our how-to example will be based on building a function that provides a currency exchange conversion.

For the sake of clarity, let’s start first by discussing the end-result and what we’d like to achieve.

The end-result

Let’s create the User-defined Function that does currency conversion. This example should be simple enough to follow, while providing the opportunity to discuss some important aspects that need to be taken care of (setting proper permission sets, adding required libraries, etc.). You can find the full code at the end of the article.

Here is how’d like to invoke this function from SQL MI instance:

-- Convert 50 USD to EUR 
SELECT ConvertCurrency(50, ‘USD’, ‘EUR’)

Pretty simple, right?! You can also use it against the data in your table:

SELECT  
    amount as OriginalAmount, 
    currency as OriginalCurrency, 
    ConvertCurrency (amount, currency, ‘EUR’) as “Amount in EUR” 
FROM sample_values

However, before we can do this, there are a couple of pre-requisites that need to be satisfied. Specifically – we need to import Newtonsoft’s JSON library to SQL MI and in order to be able to do that, we need to add some libraries that it requires. So let’s address that process now.

Adding .NET libraries to your Managed Instance

SQL Server and 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 there are times when you’d want to add some additional libraries, like Newtonsoft’s JSON. This library has some dependencies on its own which need to be imported prior to importing the library. Specifically, the following assemblies are required to be present in your SQL MI instance:

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

If you have 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 are couple of more steps that need to be taken:

1. First, if you haven’t done so already, you need to enable CLR and disable the strict security. Here’s the code that does that:

EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE; 

EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE; 

EXEC sp_configure 'clr enabled', 1; 
RECONFIGURE;

But we are not done yet. SQL MI really takes a great effort to ensure that no malicious CLR assemblies are imported into your instance. Given the level of requirements that these two DLLs have, it’s no surprise that you must take some extra steps to have them imported.

By default, all assemblies are created with a SAFE permission set level. This means that what they actually can do is quite limited. Operations such are making network calls or reading data from the disk are not considered SAFE and are out of the question. However, if you really do trust the code that you’d want to import, there are two more flexible levels – EXTERNAL_ACCESS and UNSAFE. The former allows you to execute I/O operations and make Network calls, while the latter pretty much gives you unconstrained access. As you can imagine, due to the risks involved, you really need to take extra care when assigning these security levels. You can read more about it in this article.

System.ServiceModel.Internals.dll and System.Runtime.Serialization.dll require UNSAFE level of access. In order to import such assembly into SQL MI, you need to make them “trusted”. Since all the ways to make code trusted are out of scope of this article, we will stick to the simplest and most straightforward way.

The simplest way, assuming you have a privilege to do so, is to make the entire database trustworthy. Do keep in mind that, although being an easiest solution, you need to practice it with caution and have a full understanding of the consequences of what you are about to do. If you enable trustworthiness during the initial import of CLR assemblies, make sure to disable it afterwards.

Here is how to make a database trustworthy:

ALTER DATABASE DB_NAME SET TRUSTWORTHY ON;

Now, we can import the required assemblies. As a reminder, in SQL MI, you are not allowed to create assemblies from file paths, and you have to use the assembly bits directly. Here’s a simple way to do it from your PC:

# Execute the following code in your PowerShell terminal 
$assembly = "C:\path\to\System.ServiceModel.Internals.dll"

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard

The above command will convert your DLL into hexadecimal notation and have it stored inside your clipboard.

2. Second, go to your SQL MI instance and execute the following T-SQL:

# Execute the following on your SQL MI instance

CREATE ASSEMBLY System_servicemodel_internals 
FROM 0x(paste the content of your clipboard here)  
WITH PERMISSION_SET = UNSAFE;

Do the same for SMDiagnostics.dll:

# Execute the following code in your PowerShell terminal 
$assembly = "C:\path\to\SMDiagnostics.dll" 

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard

# Execute the following on your SQL MI instance

CREATE ASSEMBLY SMDiagnostics 
FROM 0x(paste the content of your clipboard here)  
WITH PERMISSION_SET = UNSAFE;

And finally do the same for System.Runtime.Serialization.dll :

# Execute the following code in your PowerShell terminal 
$assembly = "C:\path\to\System.Runtime.Serialization.dll"  

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard

# Execute the following on your SQL MI instance

CREATE ASSEMBLY System_runtime_serialization 
FROM 0x(paste the content of your clipboard here)  
WITH PERMISSION_SET = UNSAFE;

Finally, you should be able to import Newtonsoft’s JSON assembly itself. Since it comes with binaries for multiple platforms. Do make sure to choose the one built for .NET 4.5:

# Execute the following code in your PowerShell terminal 
$assembly = "C:\path\to\Bin\net45\Newtonsoft.Json.dll" 

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard

# Execute the following on your SQL MI instance

CREATE ASSEMBLY Newtonsoft_json 
FROM 0x(paste the content of your clipboard here)  
WITH PERMISSION_SET = UNSAFE;

If all goes well, reading data from sys.assemblies should show all three assemblies being present on your SQL MI:

You are now ready to import our own UDF.

Importing our own CLR UDF

Here is the C# code for the UDF function that we’d like to create:

using System; 
using System.Data; 
using System.Data.SqlTypes; 
using System.IO; 
using System.Net; 
using Newtonsoft.Json; 
using Newtonsoft.Json.Linq; 
using Microsoft.SqlServer.Server; 

public class CurrencyConverter 
{ 

  [SqlFunction(DataAccess = DataAccessKind.Read)]  
  public static SqlDouble Convert(SqlDouble amount, SqlString fromCurrency, SqlString toCurrency) 
  { 
    // Output contains list of currency parities 
    string jsonResponse = GetCurrencyParities(fromCurrency.ToString()); 

    JObject parities = JObject.Parse(jsonResponse);

    SqlDouble parity = SqlDouble.Parse(parities[toCurrency].ToString()); 

    return amount * parity; 
  } 

  /// 
  /// Returns parities for specified currency. 
  /// Invokes a fictional Currency API that takes currency name as an input 
  /// and returns dictionary where keys represent target currencies, and 
  /// values represent the parities to source Currency. 
  /// 
  /// e.g. for GetCurrencyParities("EUR"), the response would be: 
  /// 	{ "USD": 1.2, "CAD": 1.46, "CHF": 0.96 } 
  /// 
  private static string GetCurrencyParities(String fromCurrency) 
  { 
    string url = String.Format("https://example-api.com/currency/{0}.json", fromCurrency); 

    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); 

    HttpWebResponse response = (HttpWebResponse)request.GetResponse(); 

    StreamReader reader = new StreamReader(response.GetResponseStream()); 

    string responseData = reader.ReadToEnd(); 

    return responseData; 
  }
}

Keep in mind that code has been simplified and is aimed at showcasing the example, rather than being production-ready. It purposefully omits any validations whatsoever.

Once you have the code compiled into a DLL, the next step is to have it imported into your SQL MI instance. One difference to previously imported libraries is that our code can be imported with EXTERNAL_ACCESS permission set, meaning that it can execute Network operations.

# Execute the following code in your PowerShell terminal 
$assembly = "C:\path\to\YourRestApiClient.dll" 

(Format-Hex $assembly | Select-Object -Expand Bytes | ForEach-Object { '{0:x2}' -f $_ }) -join '' | Set-Clipboard

# Execute the following on your SQL MI instance

CREATE ASSEMBLY converter Currency_converter 
FROM 0x(paste the content of your clipboard here)  
WITH PERMISSION_SET = EXTERNAL_ACCESS;

Your DLL should now be imported without issues. Time to celebrate!

The next step is to have the UDF created. Here’s how to do it:

# Execute the following on SQL MI instance: 

CREATE FUNCTION ConvertCurrency 
( 
    @amount FLOAT, 
    @fromCurrency NVARCHAR(3), 
    @toCurrency NVARCHAR(3) 
)
RETURNS FLOAT 
AS EXTERNAL NAME [Currency_converter].[CurrencyConverter].[Convert];

And that’s all! Assuming that the API you are calling works properly, the following function should yield the actual results:

Congratulations!

Couple of words on Permission Sets

Before we wrap up this article, I’d like to do a brief overview of the various permission sets – SAFE, EXTERNAL_ACCESS and UNSAFE.

These three levels, each one being less restrictive than the one before, rely on a .NET feature called Code Access Security (CAS). Do keep in mind that CAS is officially deprecated and is not deemed to be a security boundary anymore. However, even though it’s not considered to be a complete safety boundary, SQL CLR still forces you to specify the permission set level for each assembly that you create. Let’s discuss each level.

SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. Generally speaking, unless you are absolutely certain you need network and/or I/O access, you should stick to SAFE level.
EXTERNAL_ACCESS grants you what SAFE does, plus the additional ability to access external system resources such as files, networks, environmental variables, and the registry. Since our example needed to access external API and as such had to use network features, we granted the EXTERNAL_ACCESS to it.
Finally, UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can also call unmanaged code. Needless to say, that you should be very careful if and when you use this level. Only use it with assemblies that you trust 100% or else you might put your SQL MI instance at great risk.

Do keep in mind that even though SQL MI will allow you to create an assembly with a more restrictive level than is needed, trying to execute the code will result in an error.

Invoking REST APIs with SQLCLR (for Azure SQL MI)

The end-result

Adding .NET libraries to your Managed Instance

Importing our own CLR UDF

Couple of words on Permission Sets

Further reading

Leave a Reply Cancel reply