SQL Server Dynamic Account

Overview

You can use this account type to connect SQL Server Snaps with SQL Server database by specifying the Account properties as expressions referencing Pipeline parameters. Account details specified in this account (at a Snap level) are referred to as Service Accounts.

You can also connect to a SQL Server high-availability (HA) deployment, such as an Always On availability group or a failover cluster. Select User Provided for the JDBC URL Source and specify a JDBC URL with the required HA parameters in the Custom JDBC URL field. Your pipelines then reconnect during a failover event without any manual intervention. Accounts that use Snap Generated JDBC URLs continue to work without any changes.

Important: When you use a multi-host account, ensure that retry is enabled. Retries are disabled by default, so set Number of Retries to a value greater than 0 in the SQL Server Snaps that use this account. Learn more: FAQ: Retry Mechanism in SQL Server Snaps.

Note: Expression-enabled authentication fields, such as Username, Password, and Client Secret, support Secrets Management, a SnapLogic add-on that allows you to store endpoint credentials in a third-party secrets manager, such as AWS Secrets Manager, Azure Key Vault, or HashiCorp Vault. During validation and execution, pipelines obtain the credentials directly from the secrets manager. Learn more: Configure Accounts to use secrets.

Account settings

Legend:

Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
Upload : Uploads files. Learn more.

Learn more about the icons in the Snap settings dialog.


Field/Field set	Type	Description
Label `String`	Required.Specify a unique label for the account.
JDBC URL Source `Dropdown list`	Required. Select the source of the JDBC URL. Snap Generated: The JDBC URL that SnapLogic generates using the details you provide in the Account Settings dialog. If you choose this option, the Hostname, Port number, and Database name fields are displayed. User Provided: Allows you to specify a custom JDBC URL, including a multi-host URL for a high-availability deployment. When you choose this option, the Custom JDBC URL field appears. Existing accounts continue to use Snap Generated, so the single-host accounts that you created before this field was introduced work without any changes. Default value: `Snap Generated` Example: `User Provided`
Account properties	Required. Use this fieldset to enter in the information to create a connection to the database. This field set contains the following fields:
Hostname `String/Expression`	Required. Appears when the JDBC URL Source is Snap Generated. Specify the server's address to which you must connect. Default value: N/A Example: `54.98.196.248`
Port number `Integer/Expression`	Optional. Appears when the JDBC URL Source is Snap Generated. Specify the port number of the database server to which you must connect. Note: Remove the port number for Windows-based authentication when connecting to SQL Server account with multiple or named instances. Else, SnapLogic tries to connect to the specified port without checking with the Server Browser service list. Default value: `1433` Example: `1433`
Database name `String/Expression`	Required. Appears when the JDBC URL Source is Snap Generated. Specify the name of the database to which you must connect. Default value: N/A Example: `ReportServer`
Username `String/Expression`	Optional. Specify the username 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: `snaplogic`
Password `String/Expression`	Optional. 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&?`
Custom JDBC URL `String`	Required. Appears when the JDBC URL Source is User Provided. Specify the JDBC URL that you want to use to connect to your SQL Server account. Use this option if you want to use complex JDBC URLs (involving multiple hosts and advanced options) to connect to your SQL Server instance. To connect to a SQL Server high-availability deployment, such as an Always On availability group or a failover cluster, add the SQL Server HA parameters that you want to apply. For example, if you want to connect to a failover pair, you can enter a JDBC URL like the one below: `jdbc:sqlserver://server1:1433;databaseName=TestDB;failoverPartner=server2;trustServerCertificate=true;` Important: When you use a multi-host account, ensure that retry is enabled. Retries are disabled by default, so set Number of Retries to a value greater than 0 in the SQL Server Snaps that use this account. Otherwise, the Snaps do not reconnect during a failover event. Learn more: FAQ: Retry Mechanism in SQL Server Snaps. The Microsoft JDBC driver supports the following HA parameters: `failoverPartner`: The address of the standby server to use when the principal server is unavailable in a database mirroring configuration. `multiSubnetFailover`: Set this parameter to `true` to enable faster failover for an Always On availability group or a failover cluster instance that spans multiple subnets. `applicationIntent`: Set this parameter to `ReadOnly` to route read-only workloads to a secondary replica that is configured for read-only routing. Note: Use the `jdbc:sqlserver:` scheme and ensure that the URL is valid for the driver that the account loads. When you use a custom JDBC URL, specify the database in the URL using the `databaseName` property. The account displays a validation error if the URL format is invalid. Default value: N/A Example: `jdbc:sqlserver://server1:1433;databaseName=TestDB;failoverPartner=server2;trustServerCertificate=true;`
JDBC JARs	Use this fieldset to specify the list of JDBC drivers to connect to the SQL Database. Note: The default driver bundled with the SQL Server Snap Pack is `mssql-jdbc-12.2.0.jre11.jar` This field set contains the following fields:
JDBC driver `String`	Optional. Specify the JDBC driver to use. Do not use the same name if you provide multiple drivers. If this property is left blank, a default JDBC driver is loaded. Note: SQL Server Snap Pack does not support jTDS driver. Tip: Ensure that the JDBC Driver is available in a project directory that you can access. The JDBC Driver Class should default to com.microsoft.sqlserver.jdbc.SQLServerDriver. Default value: N/A Example: `mssql-jdbc-12.2.0.jre11.jar`
JDBC driver class `String`	Required. Specify the JDBC driver class name to use. Default value: `com.microsoft.sqlserver.jdbc.SQLServerDriver` Example: `com.microsoft.sqlserver.jdbc.SQLServerDriver`
Advanced properties	Use this fieldset to specify the advanced properties for connecting to the database. This field set contains the following fields:
Auto commit `Checkbox/Expression`	Optional. Select this check box 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. When deselected, the Snap execution output is committed 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`
Batch size `Integer/Expression`	Required. Specify the number of statements to execute at a time. Enter the number of statements to be executed within a batch operation. Tip: `SELECT` queries are not batched. When using million rows of data, the bulk copy program (bcp) utility (at the backend) is likely to fail due to lack of space in the database's transaction log. In such cases, use Batch size for better utilization of database resources. However, if you specify a Batch size, the Snap's performance is degraded. Hence, we recommend you to provide Batch size only if your environment is less resource-intensive. 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 of a connection in the pool. Ensure that the value you enter is 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 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 number of 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	Use this field set to define URL properties to use if any. This field set contains the following fields:
URL property name `String/Expression`	Optional. Specify a name for the URL property if any. Default value: `selectMethod` Example: `selectMethod`
URL property value `String/Expression`	Optional. Specify a value for the URL property name. Default value: `cursor` Example: `cursor`

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

SQL Server Authentication

In the Account Settings, provide the Username and Password as defined for the user on the SQL Server instance. See the Create Account screen for reference. This SQL Server account must have the required privileges to run seamlessly, the operations defined for the Snaps in this Snap Pack.

Tip: DO NOT use the default sa account for SQL Server Authentication. See SQL Docs: Security: Authentication mode to learn how to choose between SQL Server Authentication and Windows Authentication.

Windows (Active Directory-based) Authentication

See Windows (Active Directory)-based Authentication in SQL Server account for details of the different authentication methods supported.

Troubleshooting


Error	Reason	Resolution
`java.lang.UnsatisfiedLinkError: Native Library C:\\opt\\snaplogic\\ldlib\\sqljdbc_auth.dll already loaded in another classloader.`	You have uploaded customized JDBC JAR files to the directory.	Remove all JDBC JARs from the SQL Server account and restart the JCC node.
`com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.`	The correct sqljdbc_auth.dll file is not available in the ldlib folder.	Download the appropriate `sqljdbc_auth.dll` file and place it in `$SL_ROOT/ldlib`.
No valid credentials provided OR Server not found in Kerberos database.	The Service Principal Name (SPN) is not setup properly.	Ensure that you have setup the SPN for `MSSQLSvc` service properly.
Defective token detected	Kerberos fails when a mismatch occurs between the SPN for the SQL Server service and the hostName. For example, if FQDN of the machine is used to setup SPN, the hostName must be an FQDN and not an IP address, while establishing a connection.	Provide a value for `hostName` that matches with the SPN in context. Also ensure that the properties `authenticationScheme`, `domain` and `integratedSecurity` are defined for the account.