Before ingesting Azure Synapse Analytics metadata into Dawiso, prepare your account for authentication by configuring a Azure Synapse Analytics database user and grant it the necessary permissions.

Connection configuration

In this guide, you will:

  • Create a new SQL login
  • Create a user for the login
  • Grant minimum required permissions to the newly created user

Create new login

Create a new SQL login (e.g., DawisoIntegration) using the following statement:

CREATE LOGIN [DawisoIntegration] WITH PASSWORD = 'password123'
  • Make sure to replace password123 with a strong password. Refer to the official Microsoft documentation for more details.
  • If you use a custom login, make sure to replace it in all following statements.

Create users for login

The login is created at the server level, which is why you also need to create a corresponding user in each database from which you want to ingest metadata.

Switch to the database context and create a new user (e.g., DawisoIntegrationUser). You can use the same username across all databases:

CREATE USER [DawisoIntegrationUser] FOR LOGIN [DawisoIntegration]

Grant permissions

The minimum required permissions you need to set for each user are:

GRANT VIEW DEFINITION ON DATABASE::[database_name] TO [DawisoIntegrationUser]

GRANT SELECT ON OBJECT::[sys].[sql_expression_dependencies] TO [DawisoIntegrationUser]
  • Replace  database_name with the name of your real database.

Sample script

You can copy and run the following script on your server. To execute it, you must have the ALTER ANY LOGIN permission or be a member of the securityadmin fixed server role.

USE [master]
GO

CREATE LOGIN [DawisoIntegration] WITH PASSWORD = 'Password123';
GO

-- run this in context of pool database (i.e. SQLPOOL1)

CREATE USER [DawisoIntegrationUser] FROM LOGIN [DawisoIntegration];
GO

GRANT SELECT ON OBJECT::[sys].[sql_expression_dependencies] TO [DawisoIntegrationUser]
GO

GRANT VIEW DEFINITION ON DATABASE::[SQLPOOL1] TO [DawisoIntegrationUser];
GO