Just released: Developer preview of Microsoft Teams bot integration for Now Assistant. Try with your Teams ×

Create an MSSQL Connector

In this guide, we will walk through how to set up a Connector which is able to communicate with an MSSQL database, and therefore allow you to build Cards which display information from that database.

Before beginning this guide, you will need a working installation of SQL Express. A walkthrough of performing the setup is available in our SQL Express installation guide.

You will then need to install the Northwind example database onto your SQL server, which is available here.

Create the Connector folder

Navigate to Development → Manage Spaces → Global → Connectors. Select Create Folder, and on the following screen, choose the template MSSQL Connector and give the Connector a name, such as MSSQL.

The Connector creation screen

Create the Connector configuration

Now, navigate to Content Manager → Service Connectors, and select Create New Connector. Choose a unique name and a title for the Connector - for example, mssql and MSSQL.

We also need to set the connection string, which points the Connector at your SQL server, specifies the database to access, and provides your login information. The SQL connection string takes the following format:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Here, replace myServerAddress with the address at which your server can be reached, myDataBase with Northwind, then User Id and Password with the login information for your server instance. When finished, scroll down and press Save.

The Connector configuration screen

To access the database using an instance name in the Server attribute of the connection string, you will need SQL Server Browser Service to be enabled and running. More information on the SQL Server Browser Service can be found here.

Test the connection to your SQL server

Navigate to Development → API Documentation, and select adenin.Connector to expand the list of operations. Find the ping entry for your Connector, which should be listed as something like:

GET /api/adenin.GateKeeper.Connector/mssql/Ping

The API Documentation screen

Click this entry to expand it, and press Try it out! If your SQL server and connection string are configured correctly, you should see the following Response Body appear:

{
  "Id": null,
  "ErrorCode": 0,
  "Data": {
    "success": true
  }
}

Once we have confirmed our Connector is communicating with the database, we can start adding a custom service to the connector to submit and return queries.

Creating a service to query the database

Navigate back to Development → Manage Spaces → Global → Connectors and enter the folder you created for your Connector. Then, select New File, choose a filetype of js, select the MSSQL actionable card template, and name the file customercount. Press Create.

The file creation screen

This will generate a template file for performing SQL queries via your Connector, and the corresponding _service.customercount.yaml file that defines the service the Connector will expost. Select Edit on the customercount.js file that was just created, then replace the query value on line 3 with the following:

const query = "SELECT COUNT(*) AS CustomerCount FROM [Customers]";

Editing customer count script

For this guide, this is all that is required to demonstrate the completion of a query - but you may wish to inspect customercount.js to familiarise yourself with how the script functions for when you want to create your own custom services. Be sure to save customercount.js before continuing.

Test the service

The implementation is now complete and we are ready to test our Connector's custom service. Navigate back to Development → API Documentation, and again select adenin.Connector to expand the list of operations. Find the customercount entry for your Connector:

GET /api/adenin.GateKeeper.Connector/mssql/customercount

Click it to expand, and press Try it out! If the Connector and service are correctly configured, you should see something like this under Response Body:

{
  "Id": null,
  "ErrorCode": 0,
  "Data": {
    "CustomerCount": 91
  }
}