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
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