Connect Pigment with Azure SQL

This article explains how to connect Pigment to an Azure SQL database. It outlines the required configuration steps in Azure, including setting up access, permissions, and connection details, to establish a secure integration with Pigment.

Configure setup in Azure

⚠️ Important

To connect to your Azure environment, Pigment requires a Service account. The SQL server must accept Azure Active Directory (Azure AD) authentication.

This section covers how to configure Azure to connect with Pigment. The process is completed 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 or Azure Fabric Data Warehouse.

3. Make your SQL Server visible to Pigment.

Step 1. Create an application

For more information about these steps, see Azure’s documentation. To create an application in Azure:

1. In Azure Active Directory > App registrations

2. Select + New registration.

3. In the Register an application pane, enter a name for your application (for example, Pigment-app), and select Accounts in this organizational directory only (Single tenant) under Supported account types.

4. When the application is created, copy the following:

• Application (client) ID

• Directory (tenant) ID

5. In the newly created application, open the Certificates & secrets page, then select + New client secret.

6. Once the client secret is created, copy the Secret Value and store it securely. You will need to provide this value to Pigment in the next steps.

🛡️ Warning

Client secret values can only be viewed immediately after creation. Ensure you save the secret value before leaving the page.

Step 2. Grant access to the database

ℹ️ Note

Granting database access to the service account can be done in many different ways. Check with your server administration to understand how they prefer to grant access to the application. The methods documented below are examples.

Method 1. Manually granting access to the application

To manually grant access to the application:

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

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

CREATE USER [user_name] FROM EXTERNAL PROVIDER
GO


EXEC sp_addrolemember 'db_datareader', [user_name]
GO

Method 2. Grant access to the application through Group:

You can manage access through Groups rather than creating a user record in the application database.

You must create a security group first for this method:

1. Go to Azure Active Directory, then Groups and select New group. Create a security group.

2. Add the application to the security group.

3. Execute these statements in the database the application needs access to.

   Replace [group_name] with the name of the Group created before step.

CREATE USER [group_name] FROM EXTERNAL PROVIDER
GO


EXEC sp_addrolemember 'db_datareader', [group_name]
GO

Step 3. Configure the SQL server network access

To allow Pigment to access your SQL server:

1. Open your SQL server resource in Azure.

2. Select Security, then Networking. In the Public access tab, check that Public network access is set to Selected Network.

3. Go to Firewall rules, select + 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

Set up the Connector in Pigment

⚠️ Important

You must be a Pigment Workspace Admin to configure the connector. For more information, see Account Types and Management.

To set up the connector in Pigment:

1. From the Workspace, select Settings, then choose Integrations.

2. Select + Add on the Azure SQL integration. Enter the required information for 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. If your SQL analytics endpoint in Microsoft Fabric is at mycompany.datawarehouse.fabric.microsoft.com, then the server name is mycompany. Database name: The name of the database where the queries are executed. Service type: Select either Azure SQL Database, Azure SQL Managed Instance or Microsoft Fabric Data Warehouse.

ℹ️ Note

The Service Type selection affects the port used to reach your Azure SQL instance:

• with Azure SQL Database and Microsoft Fabric Data Warehouse, Pigment uses port 1433

• with Azure SQL Managed Instance, Pigment uses port 3342

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 .

To access the new connection:

1. Select the Integration option.

2. Select the recently configured Azure SQL Connection from the drop-down menu.

ℹ️ Note

If you don’t see the connection, navigate to the Integrations page, and verify that the Application is on the Application Access list.

3. Enter the SQL query you want to load and select Start Import. The results of your query will be available in Pigment after the import process completes.