This part explains how to connect to Azure Cosmos DB from your Business Central extension using AL Language and how to get some values from the database.
——————————————————————————————————————
This is the second part of series of three articles about integration between Microsoft Azure Cosmos DB and Microsoft Dynamics 365 Business Central. This part explains how to connect to Azure Cosmos DB from your Business Central extension using AL Language and how to get some values from the database.
The first part expains how to create Azure Cosmos DB environment (Database, Container), how to add new values to your newly created database and how to obtain your API keys that will be used later for API authorization.
The third and last part describes how to send your own SQL queries using POST request from AL Language to get filtered results based on variable requests made in AL Language.
——————————————————————————————————————
How to connect to Azure Cosmos DB
SAS Token
First of all, we have to create secured connection from AL project to Azure Cosmos DB. The authorization is based on SAS token (A shared access signature provides secure delegated access to resources in your storage account. With a SAS, you have granular control over how a client can access your data, see Microsoft Docs).
The SAS token is created based on three parts.
First two parts are contants, the “type=master” and “ver=1.0″.
The most interesting part is “sig” part. Let’s see how to create it.
Sig part (HMAC)
The SECURITYHMACTOKEN from the previous part is a string created using a cryptographic technique that combines public keys, private keys, and a hash (more details about HMAC could be found here). As said, we have to define three things: string to encode, private kay and hash.
Luckily we do not have to care about creating HMAC itself as it is already prepared by Microsoft directly in the Business Central. The whole magic about this cryptographic technique is wrapped into Codeunit 1266 “Cryptography Management”, procedure GenerateBase64KeyedHashAsBase64String().
This procedure accepts three parameters: Input String, Sign Key and Hash Algorithm. See the picture below.
As you can see there is a warning for the third parameter (“An implicit conversion is being performed from a value of type ‘Enum “Hash Algorithm”‘ to a value of type ‘Option’. This conversion can lead to unexpected runtime issues. This warning will become an error in a future release.”). Fortunately, we can solve it simply by adding .AsInteger() to the third parameter (at least until Microsoft upgrade Cryptography Management codeunit to use the corresponding Enum instead of Option data type…).
Ok, but what are the Input and SignKey parameters?
The SignKey is easy to get – it is the Read-only or Read-Write key you can get from your Azure Cosmos DB environment (see the previous part where I described how to obtain this key).
The Input is a bit more complicated. The input is a string that contains all information about the entity we want to access.
1) Method
The method is GET or POST. In this article, we will start with the GET method, and the POST method will be covered in the next part of this series. It’s important to remember that method must be defined in lower-case!
2) ResourceType
The standard Resource Types are dbs, colls, docs. The resource type specifies which entity you want to get from Cosmos DB. By using dbs you specify a database, colls means collections and docs are documents.
In other words, with dbs you can get a list of/specific database(s), with colls you can get a list of/specific collection(s) (= containers) and with docs you can get a list of/specific document(s) (= record(s) from the specific container).
3) ResourceId
Using Resource Id you specify which database (and container) should be used for obtaining data. The format of this parameter depends on specified ResourceType but is usually like “dbs/{0}/colls/{1}/docs/{2}” where {0} is database name, {1} is container name and {2} is document name.
The rule is: Use only the part of this string that ends with the thing you want to get. For example, to get a list of all databases use “dbs” only, to get a list of all collections use “dbs/{0}/colls” and to get specific collection use “dbs/{0}/colls/{1}”.
4) Current Time
Time is used for security reasons to set the time validity of your requests. The value must be specified in RFC1123 (“D, d M Y H:i:s O”, for example “Mon, 01 Jan 2021 14:00:00 GMT”). Fortunately, again, the Type Helper codeunit has the procedure we need! GetCurrUTCDateTimeAsText() is what we need!
See the image below with a full example of how to get SAS Token for your GET request to get all documents from KeptyCZTestContainer container in KeptyCZTestDB database.
Get data from Azure Cosmos DB
Now we know everything necessary to be able to get values from Azure Cosmos DB. See the image below to see how to do it.
A few notes:
1) URL
URL is constant and contains your environment name and the resource you want to get. In this case, the environment name is “bcmarket”, you can just replace it with your own. The resource always ends with the resource type (even if it is not part of the SAS token!). As you can see in the picture, I want to list all documents (so the SAS token is built with Resource Id ending with Container name, but the URL ends with docs).
2) Headers
All headers that are in the picture are mandatory.
3) Time
Time defined in SAS Token must correspond to the time defined in the HTTP header. The validity of x-ms-date header is 15 minutes.
And that is all! You can compile your app and try it with your environment and your own data. In the next (and last) part of this series, we will look at how to create queries and POST requests. It will allow you to filter the results at the CosmosDB before downloading them to the Business Central.
