Azure Synapse SQL

Agile Data Engine supports:

Azure Synapse Analytics dedicated SQL pool

Required connection details

The following details are required per target instance configuration:

Detail	Example	Description
Host	`clouddw-dev.sql.azuresynapse.net`	Fully qualified host name of the Synapse SQL endpoint.
Database name	`clouddw_dev`	Name of the database within the dedicated SQL pool.
Default schema	`dbo`	Default schema context for the user.
Port	`1433`	Default port for Synapse SQL endpoint.
Username*	`ade_login@clouddw-dev`	Login name in the format: `login@host` (host is the server name, not DB).
Password	`••••••••`	Password for the login user. Securely stored and managed.

*Microsoft Entra authentication currently not supported.

Configuration

Set up a separate database for each Agile Data Engine Runtime. Within the databases, you need to set up the following objects:

LOGIN (master database)
MASTER KEY
ROLE
USER

Repeat below steps in each environment. Feel free to name objects according to your naming conventions.

Execute step 1 in the master database.

Execute steps 2-5 in the target database.

1. Create a login

SQL

CREATE LOGIN ADE_LOGIN WITH PASSWORD = '***';

Generate strong environment specific passwords and store them securely.

2. Create a master key

SQL

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '***';

Refer to Microsoft documentation for more information about the master key.

3. Create a role

SQL

CREATE ROLE ADE_ROLE;

4. Grant permission to the role

SQL

GRANT CONTROL ON DATABASE::[clouddw_dev] TO ADE_ROLE;
GRANT ALTER ANY SCHEMA TO ADE_ROLE;
GRANT CREATE TABLE TO ADE_ROLE;
GRANT CREATE VIEW TO ADE_ROLE;

Replace clouddw_dev with the target database name.

5. Create a user for the login

SQL

CREATE USER [ADE_USER] FOR LOGIN [ADE_LOGIN] WITH DEFAULT_SCHEMA = dbo;

Assign the role to the user:

SQL

EXEC sp_addrolemember 'ADE_ROLE', 'ADE_USER';

6. Configure firewall rules

It is strongly recommended to configure firewall rules that allow access only from known and trusted IP addresses. Agile Data Engine uses a tenant-specific outbound IP address, which you can request via the ADE Support Portal.

For guidance on setting up firewall rules, refer to the official Microsoft documentation.

If your organization requires private connectivity, see Private Connectivity and VPN.