Welcome to the Azure Synapse Connector setup guide! This document serves as a comprehensive guide for configuring Azure Synapse to integrate seamlessly with Azure SQL Database using the below mentioned methods.

Features

Read data from Azure Synapse Analytics
Writing analysis outcome to Azure Synapse Analytics

Requirements

Following requirements must be met to establish the connection between Savant and Azure Synapse database.

Access to the Azure Portal
Necessary permissions to register applications and manage Azure SQL databases
Basic understanding of SQL and Azure services.
Savant IP addresses whitelisted.

Connection Methods

Savant supports multiple connection methods including the below mentioned for secure access:

Database User
Azure AD OAuth

This step-by-step guide ensures you can efficiently set up your environment for optimal data integration.

Database User Method

Step 1: Create a Login and User

Create the Login: Replace {savant_login} with the desired login name and {password} with a strong password.
Create the User: Replace {savant_user} with the desired user name.

CREATE LOGIN {savant_login} WITH PASSWORD = {password};
CREATE USER {savant_user} FOR LOGIN {savant_login};

Step 2: Grant Permissions in Azure Synapse

Add User to Role: Add a role to the user created in the above step.
Grant Specific Permissions: Grant the Savant user privileges to access all tables.

EXEC sp_addrolemember 'db_datawriter', {savant_user};

GRANT CREATE TABLE, CREATE SCHEMA, SELECT, INSERT, ALTER, UPDATE, DELETE, ADMINISTER DATABASE BULK OPERATIONS TO {savant_user};

Step 3: Check Permissions

Run these commands: Run the commands to check privileges to the Savant role:

SELECT   roles.principal_id                          AS RolePrincipalID
    ,    roles.name                                  AS RolePrincipalName
    ,    database_role_members.member_principal_id   AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members
JOIN sys.database_principals AS roles
    ON database_role_members.role_principal_id = roles.principal_id
JOIN sys.database_principals AS members
    ON database_role_members.member_principal_id = members.principal_id;
GO

Step 4: Getting Started in Savant

Once you’ve completed the steps above, follow these steps in Savant:

Go to the Systems page and click Add System
Select the Azure Synapse connector and click Next
Enter your system information and credentials from above
Click Authenticate and then Confirm

Azure AD OAuth

Step 1: Application Registration in Azure

Login to the Azure Portal.
Select App Registration and click on New registration.
Fill in the following details:
- Name: Savant Synapse Integration
- Supported account types: Choose Accounts in this organizational directory only.
- Redirect URI:
- For EU applications: https://app-eu.savantlabs.io/api/callback
- For others: https://app.savantlabs.io/api/callback

Note Important IDs like the client ID and tenant ID
Create a Client Secret by clicking on Certificates & secrets under Manage
Note the client secret.

Step 2: Set Up Permissions

Navigate to Manage and click on API permissions followed by Add a permission.
In APIs my organization uses, select Azure SQL Database.
Choose delegated permissions and check user_impersonation.

To Grant Permissions (Optional) go Enterprise Applications
Locate the registered application.
Select Security followed by Permissions, and grant permissions for all users in your tenant.

Step 3: Configure Active Directory Admin

Set Active Directory Admin for Azure SQL Server:
Navigate to your Azure SQL Server (associated with Synapse).
Configure the Active Directory Admin.
Refer to the Configure Microsoft Entra Authentication

Important Note:

For non-admin users needing access, create an external user:

CREATE USER [<email_address@company>] FROM EXTERNAL PROVIDER;

Step 4: Connecting Savant to Azure Synapse using following details

Server: savant-dev.sql.azuresynapse.net
Port: 1433 (default)
Database: savant_test (optional)
Directory (tenant) ID: [Your Tenant ID]
Application (client) ID: [Your Client ID]
Client Secret: [Your Client Secret]
Click on Authenticate to get to Microsoft's login page
After authorising the OAuth app’s access, you will be redirected back to Savant.

Troubleshooting

Follow these steps to resolve some of the common issues listed below

If you encounter issues during authentication, double-check the client ID and secret.
Verify that users have been properly created in SQL and that permissions are correctly assigned.
Check the Azure portal for any service outages or issues related to Azure SQL Database.
Ensure that all sensitive information, such as client secrets, is stored securely.
Regularly review and update permissions for users and applications.
Use role-based access control to minimize permissions to only what is necessary.

Don’t see what you’re looking for? Contact us in the Community or reach out in Chat Support

Azure Synapse Setup