Azure SQL Database

Agile Data Engine supports:

Azure SQL Database
Microsoft SQL Server (on-prem with VPN)

Required connection details

The following details are required per target instance configuration:

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

*Microsoft Entra authentication currently not supported.

Configuration

Set up a separate Azure SQL 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
EXTERNAL DATA SOURCE (optional)

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

Execute step 1 in the SQL server 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 permissions 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. (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.