Skip to main content

Connecting to Azure SQL Databases using Entra ID authentication

This guide will walk you through setting up Entra ID authentication for connecting to Azure SQL Databases from ConnectMyApps Canvas.

Adding Entra ID App Registration​

Log into your Azure portal as an administrator account. If you have multiple organizations available, please ensure to select the correct organization before proceeding to the next step.

  1. From the service menu go to "Microsoft Entra ID".

  2. Go to "App Registrations".

  3. Click "New Registration".

  4. Give your registration a name e.g. "ConnectMyApps SQL App".

  5. Under "Supported Account Type" select "single tenant only".

  6. Leave "Redirect URI" blank and click "Register".

Collect Required Values​

In the app registration overview screen copy the "Tenant ID" and "Client ID".

Create Client Secret​

  1. Click on "Certificates and secrets" from the left-hand menu.

  2. Click "New client secret". Give the secret a name and select an expiry time.

  3. Copy the secret.

Important

Make sure to add a reminder for yourself in advance of the secret expiry date. You must generate a new secret and update your connection information in ConnectMyApps Canvas platform to prevent service interruption.

Note

It is not required to grant "API permissions" to the app registration.

Granting Access to your Azure SQL Database​

The next steps assume you already have the database you wish to connect to. This guide does not include steps to create a new database.

Configure Database Access​

  1. While still in the Azure Portal, go to "Azure SQL Database" from the main menu.

  2. Click on your database and go to "Overview".

  3. Copy the "server name". This is the host name of your database server.

Configure Firewall Rules​

You must ensure that ConnectMyApps can connect to the database by whitelisting the IP address 54.217.222.87.

  1. Go to "Security" from the menu, then "Networking" and under firewall rules.

  2. Add a rule for the IP address and ensure that "Allow Azure services and resources access to this server" is checked.

  3. Save your firewall and networking rules.

Grant Database Permissions​

  1. Connect to your SQL Server database using SQL Management Studio. You must connect as a user with database administrator rights.

  2. Open a new query window on your database.

  3. Run the following query, replacing {app name} with the name of your app registration from the previous steps:

CREATE USER [{app name}] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [{app name}];

ALTER ROLE db_datawriter ADD MEMBER [{app name}];
Important

The above SQL statement assumes you wish for read and write access to the database. You may adjust role permissions to fit your use case, but read access should be granted as a minimum as it will be used to test connectivity.

Testing Database Connection​

To test the database connection, follow these steps:

  1. Go to https://utility.connectmyapps.com/web/db

  2. Select "MSSQL" as the "Dialect". Enter your server name in "Host" and set port to 1433 (default MSSQL port).

  3. Enter the database name in "Database" and select "Auth Type" as "Entra ID – Service Principal".

  4. Enter the "Tenant ID", "Client ID" and "Client Secret" you copied from the previous steps.

  5. Ensure "Force Encryption" is selected.

  6. Enter a query into the "SQL Query" box and click "Execute".

  7. You should see the query output in the "Response" box. You have now successfully verified the connection.

Next Steps​

Speak to your ConnectMyApps technical contact to register your SQL connection in the ConnectMyApps Canvas Platform.