Azure Synapse SQL

The following details are required per target instance configuration:

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.

1. Create a login

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

Generate strong environment specific passwords and store them securely.

2. Create a master key

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

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

3. Create a role

CREATE ROLE ADE_ROLE;

4. Grant permission to the role

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

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

Assign the role to the user:

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.

The following details are required per target instance configuration:

Configuration

Set up a separate Azure SQL Database with Entra Id Service Principal for each Agile Data Engine Runtime. Within the databases, you need to set up the following objects:

• USER

• MASTER KEY (optional)

• ROLE

• EXTERNAL DATA SOURCE (optional)

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

1. Create user

CREATE USER [ade-ci-sql-database-user] FROM EXTERNAL PROVIDER;

2. Create a master key

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

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

3. Create a role

CREATE ROLE ADE_ROLE;

4. Grant permissions to the role

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. Assign role for the user

EXEC sp_addrolemember 'ADE_ROLE', 'ade-ci-sql-database-user';

6. (Optional) Create an external data source

For loading source data into Azure SQL database, create an external data source by following the Microsoft documentation. For more information, please refer to Loading Source Data.

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