SQL Server Dynamic Account
In this article
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.
Prerequisites
None.
Limitations and Known Issues
None.
Account Settings
Field Name* | Field Type | Description | |
---|---|---|---|
Label* Â Default Value: N/A | String | Specify a unique label for the account. | |
Account properties* | Use this fieldset to enter in the information to create a connection to the databse. | ||
Hostname* Â Default Value: N/A | String/Expression | Specify the server's address to which you must connect. | |
Port number  Default Value: 1433 | Integer/Expression | Specify the port number of the database server to which you must connect.
| |
Database name* Â Default Value: N/A | String/Expression | Â Specify the name of the database to which you must connect. | |
Username  Default Value: N/A | String/Expression | 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.  | |
Password  Default Value: N/A | String/Expression | 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. | |
JDBC JARs   | Use this fieldset to specify the list of JDBC drivers to connect to the SQL Database. The default driver bundled with the SQL Server Snap Pack is | ||
JDBC driver  Default Value: N/A | String | 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. SQL Server Snap Pack does not support jTDS driver. | |
JDBC driver class*  Default Value: com.microsoft.sqlserver.jdbc.SQLServerDriver | String | Specify the JDBC driver class name to use. | |
Advanced properties | Use this fieldset to specify the advanced properties for connecting to the database. | ||
Auto commit  Default value: Selected | Checkbox/Expression | 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. | |
Batch size* Â Default value: 50 | Integer/Expression | Specify the number of statements to execute at a time. Enter the number of statements to be executed within a batch operation. | |
Fetch size* Â Default value: 100 | Integer/Expression | Specify the number of rows to fetch at a time when executing a query. Â | |
Max pool size* Â Default value: 50 | Integer/Expression | Specify the maximum number of connections a pool will maintain at a time. Â | |
Max lifetime (minutes)* Â Default value: 30 | Integer/Expression | Specify the maximum lifetime of a connection in the pool, in seconds. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit.
| |
Idle timeout (minutes)*  Default value: 5 | Integer/Expression | 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. | |
Checkout timeout (milliseconds)* Â Default value: 10000 | Integer/Expression | Specify the number of milliseconds to wait for a connection to be available when the pool is exhausted. | |
URL properties | Use this field set to define URL properties to use if any. | ||
URL property name  Default Value: selectMethod | String/Expression | Specify a name for the URL property if any. | |
URL property value  Default Value: cursor | String/Expression | Specify a value for the URL property name. |
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.
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 |
No valid credentials provided | The Service Principal Name (SPN) is not setup properly. | Ensure that you have setup the SPN for |
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 |
Snap Pack History
Related Content
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.