Prerequisites

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Before you install the Azure SQL DB OCF connector, ensure that you have performed the following:

Configure Network Connectivity

​​Open the outbound TCP port 1433 to Azure SQL DB (default).

Note

Azure SQL DB instances can listen on non-standard ports. Open the appropriate outbound TCP ports from the Alation server.

Create Service Account

Currently, only SQL authentication has been certified for this data source type by Alation. Kerberos authentication, NTLM, and SSL support will be certified with upcoming connector versions.

SQL Authentication

For SQL authentication, create a service account for Alation on the master database.

Example:

CREATE LOGIN [login_value] WITH PASSWORD = [password];
CREATE USER [service_account] FOR LOGIN [login_value];

Create the corresponding user account in Azure SQL DB.

Example:

CREATE USER [login_name] FOR LOGIN [login_name];

Azure Active Directory

Note

Azure Active Directory is now called Microsoft Entra.

Set an admin account on the Azure portal. Login to the Azure SQL DB using your admin credentials.

For more information on setting up an admin account, refer to Set up an admin account.

Create a service account for Alation on Azure SQL DB.

Example

CREATE USER [<Microsoft_Entra_principal_name>] FROM EXTERNAL PROVIDER;

The Microsoft_Entra_principal_name can be the user principal email id of a Azure Active Directory (Microsoft Entra) user.

For more information on Azure Active Directory (Microsoft Entra) user creation, refer to Create Users.

Authentication with Active Directory is supported through basic authentication (username and password). Use the following format for the username when configuring the data source settings in Alation:

  • For Username, use Microsoft_Entra_principal_name, where Microsoft_Entra_principal_name represents the email id of a Azure Active Directory (Microsoft Entra) user.

  • For Password, use the Active Directory password of the Microsoft_Entra_principal_name.

Grant Required Permissions

The service account you want to use requires a specific set of permissions on Azure SQL DB.

Grant Permissions for Metadata Extraction

Grant the service account the VIEW DEFINITION rights on all schemas you want to extract into the data catalog. This permission allows Alation to extract schema, table, and view metadata.

Example:

GRANT VIEW DEFINITION ON SCHEMA :: schema_name TO [login_name];

Grant Permissions for Table Profiling

If you want to use sampling and profiling, grant the SELECT rights on schemas and tables you want to retrieve samples from.

Grant Permissions for Query Log Ingestion

For query log ingestion, the service account requires the CONTROL permission to access the sys.fn_get_audit_file file.

Example:

GRANT CONTROL TO [login_name];

JDBC URI

Minimal Format

sqlserver://<hostname_or_ip>:<port>

Example

sqlserver://test.database.windows.net:1433;

JDBC URI for Azure Active Directory

To obtain the JDBC URI for Azure Active Directory, perform the following steps:

  1. Go to Azure Portal and locate the the Azure SQL DB resource that you want to connect to.

  2. Go to Settings > Connection strings > JDBC.

../../../_images/SQLDB_OCF_AzureAD_JDBC_URI.png

  1. Copy the JDBC URI.

    Example:

jdbc:sqlserver://<datasource-ip/host-name>:<port-number>;database=SqlServerAzDB_<database-name>;user=<username>;password={password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

The connector requires only the host , protocol, and port. Database is optional. Use this parameter if you want to fetch an object from a particular database.

Incremental MDE from Compose

When users create tables and views in Compose, Alation triggers a background extraction job to make the new table and view objects available in the catalog. As a result, users will see the corresponding table or view metadata in the data catalog without re-running MDE on the Settings page of the data source.

Azure SQL DB does not allow the database name in the CREATE VIEW queries. For the incremental MDE from Compose to work, add the database name as the database parameter to the JDBC URI.

Format

sqlserver://<datasource-ip/host-name>:<port-number>;database=<database-name>;
Example
sqlserver://<datasource-ip/host-name>:<port-number>;database=<database-name>;

Note

If the main connection URI does not include the database name, however, in Compose users add the database name to their Compose connection URI, incremental MDE from Compose will fail with exception.