This article explains how to connect Azure SQL with Pigment to retrieve any SQL query result into a Pigment Block.

As with all other native Pigment Integrations, you first need to configure a connection in the Integration library page. When this is configured, this connection is usable in any Pigment Application that is authorized as a data source. The authorized Applications are defined during the Import Data configuration step. Now, let’s see how to configure and use this native integration!

Configuration setup in Azure

This section covers how to configure Azure to connect with Pigment. The process is done in Azure and is done in three steps.

1. Create a service account by creating an Application and its secret key.
2. Grant the required Permissions to the application in your Azure SQL database.
3. Make your SQL Server visible to Pigment.

Step 1. Create an Application

For more information about these steps, you can review Azure’s documentation.

In Azure Active Directory > App registrations,  click + New registration .

Name your application, for example Pigment-app, and select Default Directory only - Single tenant option in the Supported Account types section.

When the application is created, copy the following: 

• Application (client) ID
• Directory (tenant) ID

Next, open the Certificates & secrets page of the application which was just created, and then click + New client secret.

Once created, copy the Secret Value and keep it in a safe place! You need to share this secret value with Pigment in the next steps of the process.

Step 2. Grant access to the Database

Method 1: Manually granting access to the application:

Execute the following two commands on the database to which you want the application to have access.

Replace user_name] with the name of the application created during the previous step.

CREATE USER puser_name] FROM EXTERNAL PROVIDER
GO


EXEC sp_addrolemember 'db_datareader', ruser_name]
GO

Method 2: Grant access to the application through Group:

Instead of creating a user in the database for the application, it’s also possible to manage access through Groups. 

First create a group. In the Azure Active Directory > Groups, click on New group. Create a security group.

When the group is created, add the application to the group.

Execute the following two commands on the database that you want the application to have access to.

Replace hgroup_name] with the name of the Group created before step.

CREATE USER GO


EXEC sp_addrolemember 'db_datareader',  group_name]
GO

Step 3. Configure the SQL server Network access

To let Pigment reach your SQL server, go to the SQL server configuration page and go to Security > Networking.

In the Public access tab, check that Public network access is set to Selected Network.

Then in Firewall rules, click on + add firewall rule and allow the following Pigment server IPs to access the SQL server: 

• 35.242.251.111
• 34.145.54.113
• 34.163.209.119
• 35.202.142.12

Setting up the Connector in Pigment 

Within Pigment, navigate to the Integration page from your Workspace homepage, and click + Add on the Azure SQL integration. Next, fill out the information about the connection.

Name: Enter the name of your connection.   Application access: Select the applications to allow using this connection. Application (Client) ID: The application ID previously generated.  Directory (Tenant) ID: The Directory ID of the application previously generated.  Secret value:  The secret value previously generated.  Server name: The name of the server with which you want to connect. If your Azure SQL database is mycompany.database.windows.net, then the server name is mycompany.  Database name: The name of the database where the queries are executed.

Load Azure SQL data into Pigment

When the Azure SQL Connection is configured, open an Application with the accessible connection and open the Import Data interface for the object you want to import data into.

For example, if you are importing data into a Transaction List, you would open up List and then select Import and Download and then Import .

Instead of Upload file, select the Integration option.  From here, select the Azure SQL Connection you just configured

Now, you need to enter the SQL query you want to load and click on Start Import.

The result of your SQL query will load in Pigment within seconds!