Skip to main content
All CollectionsSystem Setup Guides
Azure Synapse Connector
Azure Synapse Connector

Guide to connecting Azure Synapse Analytics

Updated this week
Azure Synapse Integration

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. For more information, Please refer to the document

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

  • 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

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


Did this answer your question?