Azure SQL Entra Account

Overview

You can use the Azure SQL Entra account type to connect Azure SQL Snaps with data sources that use Azure SQL Entra account.

Prerequisites

Valid Database to connect.
Valid JDBC JAR file.

Limitations

When you select the Disable Connection Pooling checkbox, the account forces the session state to close the existing connection and reopen another connection to the database for each Snap until the Pipeline is executed, which might affect the performance. Therefore, we recommend that you select the Disable Connection Pooling checkbox only when there is clear isolation between Snap executions within a Pipeline.

Known issues

Supports only reading JSON files.

Account settings

Note: Learn about the common controls in the Snap settings dialog.


Field/Field set	Description
Label* `String`	Required. Unique name for the account. Default value: N/A
Account properties `Fieldset`	Required. Enter the information to create a connection to the database.
Hostname `String/Expression`	Required. Specify the server's address to which you must connect. Default value: N/A Example: test.database.windows.net
Port number `Integer/Expression`	Required. Specify the port number of the database server to which you must connect. Default value: 1433 Example: 1433
Database name `String/Expression`	Required. Specify the name of the database to which you must connect. Default value: N/A Example: DB1
Authentication mode `Dropdown list`	Required. Select one of the following options for Azure SQL Authentication: Active Directory Password: Provide Username and Password for authentication. Active Directory Service Principal: Provide Client ID and Client Secret from service principals for authentication. Active Directory Service Principal Certificate: Provide Client ID and Keystore values for client certificate-based authentication. Note: An Azure Active Directory (Azure AD) service principal is the local representation of an application object in a single tenant or directory. Service principals define who can access the application, and what resources the application can access. Default value: Active Directory Password Example: Active Directory Service Principal
Client ID `String/Expression`	Appears when you select Active Directory Service Principal Certificate as the Authentication mode. Required. Specify the ID of an Azure Active Directory application. Default value: N/A Example: d4cbb84cbd-cv87-bc75-78yv-90fcd-b319c51
Keystore `String/Expression`	Appears when you select Active Directory Service Principal Certificate as the Authentication mode. Required. Specify the location of the keystore file. The location can be SLDB or any other unauthorized endpoint. Default value: N/A Example: CertificateWithoutPwd.pfx
Keystore password `String/Expression`	Appears when you select Active Directory Service Principal Certificate as the Authentication mode. Specify the password for the keystore file. Default value: N/A Example: 1234test
Key alias `String/Expression`	Appears when you select Active Directory Service Principal Certificate as the Authentication mode. Required. Specify the alias of the private key used from the keystore file. Default value: N/A Example: thisisanalias
Private key passphrase `String/Expression`	Appears when you select Active Directory Service Principal Certificate as the Authentication mode. Specify the passphrase for the private key. You can leave the field empty if the passphrase is the same as the keystore password. Default value: N/A Example: 1234test
Username `String/Expression`	Appears when you select Active Directory Password as the Authentication mode. Specify the username that is allowed to connect to the database. This username is used as the default username when retrieving connections and must be valid to set up the data source. Default value: N/A Example: sladmin
Password `String/Expression`	Appears when you select Active Directory Password as the Authentication mode. Specify the password used to connect to the data source. This password is used as the default password when retrieving connections and must be valid to set up the data source. Default value: N/A Example: P#2,nxu0oiX2&?
Trust all certificates `Checkbox`	Select if all SSL certificates are to be accepted for connecting to the database. Default value: Deselected
Advanced properties `Fieldset`	Use this field set to specify the advanced properties.
Auto commit `Checkbox`	Select this checkbox to commit each of the batches immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. Deselect this checkbox to commit the output only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view. Default value: Selected
Disable connection pooling `Checkbox`	Select this checkbox to prevent the sharing of session state. Note: Connection pooling keeps the connections active. When the Snap uses a connection and creates a table during execution, the table gets locked and is not released even after the Snap is executed and the connection is returned to the pool. If another Snap or the same Snap uses the same connection from the pool and reads the metadata of the same table, it displays an error that the resource is locked. We recommend you select this checkbox for Snaps that use session state, for example, creating a temporary table. Note: When you select the Disable Connection Pooling checkbox, the account forces the session state to close the existing connection and reopen another connection to the database for each Snap until the Pipeline is executed, which might affect the performance. Therefore, we recommend that you select the Disable Connection Pooling checkbox only when there is clear isolation between Snap executions within a Pipeline. Default value: Deselected
Batch size `Integer/Expression`	Required. Specify the number of statements to execute at a time. Note: Select queries are not batched. Using a large batch size could use up the JDBC placeholder limit of 2100. Default value: 50 Example: 60
Fetch size `Integer/Expression`	Required. Specify the number of rows to fetch at a time when executing a query. Note: Large values could cause the server to run out of memory. Default value: 100 Example: 80
Max pool size `Integer/Expression`	Required. Specify the maximum number of connections a pool will maintain at a time. Default value: 50 Example: 60
Max lifetime (minutes) `Integer/Expression`	Required. Specify the maximum lifetime (in minutes) of a connection in the pool. Note: Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed. Default value: 30 Example: 50
Idle timeout (minutes) `Integer/Expression`	Required. Specify the maximum amount of time (in minutes) a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool. Default value: 5 Example: 8
Checkout timeout (milliseconds) `Integer/Expression`	Required. Specify the timeout (in milliseconds) to wait for a connection to be available when the pool is exhausted. Note: If you specify `0`, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify `0` for Checkout Timeout. Default value: 10000 Example: 100
URL properties `Fieldset`	Use this field set to define URL properties to use if any.
URL property name `String/Expression`	Specify a name for the URL property if any. Default value: N/A Example: MAX_CONCURRENCY_LEVEL
URL property value `String/Expression`	Specify a value for the URL property name. Default value: N/A Example: 3

Note: Click Validate after entering the required details to ensure that all fields have been filled accurately. Click Apply to save the settings.