SQL Server Account
In this article
Overview
You can use this account type to connect SQL Server Snaps with data sources that use SQL Server database accounts. Account details specified in this account (at a Snap level) are called Service Accounts.
Limitations
Authentication of SQL Server Account using NTLM Protocol is limited to Windows Snaplexes.
Account Settings
Asterisk ( * ): Indicates a mandatory field.
Suggestion icon ( ): Indicates a list that is dynamically populated based on the configuration.
Expression icon ( ): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon ( ): Indicates that you can add fields in the fieldset.
Remove icon ( ): Indicates that you can remove fields from the fieldset.
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. If you need to connect to an on-premise server, specify the domain name or the IP address. For example, test.mydbserver.com or 190.159.0.124. | |
Port Number*
Default Value: 3306 | 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. | ||
JDBC Driver
Default Value: com.microsoft.sqlserver.jdbc.SQLServerDriver | String | The SQL Server Snap Pack is bundled with | |
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 | 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. | |
Batch size*
Default value: 50 | Integer | Specify the number of statements to execute at a time. | |
Fetch size*
Default value: 100 | Integer | Specify the number of rows to fetch at a time when executing a query.
| |
Max pool size*
Default value: 50 | Integer | Specify the maximum number of connections a pool will maintain at a time.
| |
Max life time*
Default value: 30 | Integer | Specify the maximum lifetime of a connection in the pool. | |
Idle Timeout*
Default value: 5 | Integer | 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*
Default value: 10000 | Integer | 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 | Specify a name for the URL property if any. | |
URL Property Value
Default Value: cursor | String | 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. This SQL Server account must have the required privileges to run the operations seamlessly defined for the Snaps in this Snap Pack.
Windows (Active Directory-based) Authentication
Service Account Authentication
Ensure that Snaplex is running on Windows and an AD user with required access privileges has logged into the Snaplex.
To use the default jar bundled with the Snap Pack, place the latest version of
sqljdbc_auth.dll
in$SL_ROOT/ldlib (c:\opt\Snaplogic\ldlib)
. You do not have to add the custom jar file to the account. You can find the latest version of the DLL on the Microsoft SQL Server JDBC Driver website.
Service Account Authentication implies that the active AD user session on the Snaplex is leveraged to obtain access to the SQL Server instance. Therefore, you need not provide values in the Username and Password fields.
Do not specify a port number.
SnapLogic recommends using the default JAR. You can download the latest JAR file from here.
Configure the following properties for JDBC under URL properties:
Url property name | Url property value |
---|---|
| Your domain name |
|
|
Connecting to an MSSQL Instance
You can connect to a specific instance of MSSQL by using the instance name along with the server name in Hostname <server_name>/<instance_name>.
Alternatively, you can define the instance name by using the URL property name, instanceName.
User Impersonation
In addition to providing the account properties such as Hostname, Database name, Username, and Password, ensure that the following settings/configurations are done for Active Directory Kerberos or NTLM Protocol (as the case may be) to implement User Impersonation with the SQL Server instance.
Authentication Using Active Directory Kerberos
SnapLogic supports (tested and certified) Active Directory authentication for SQL servers on the following driver JAR version for Java 11:
mssql-jdbc-12.2.0.jre11.jar
Refer to Microsoft JDBC Driver for SQL Server for details.
Ensure that you have installed the correct JAR file. Also, use the following configurations in Account Settings to configure Active Directory authentication:
JDBC Driver class:
com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Connection URL:
jdbc:sqlserver://ServerNameFQDN:portNumber;databaseName=DBNAME
SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. The prerequisites are as follows:
In the account settings, add the following to Url property name and Url property value:
URL property name | URL property value |
---|---|
|
|
|
|
|
In the account settings, enter your Active Directory Username and Password.
Authentication Using NTLM Protocol
To authenticate Windows SQL Server connection with the NTLM protocol, follow these steps:
Configure your SQL Server Account settings as shown below:
JDBC Driver Class:com.microsoft.sqlserver.jdbc.SQLServerDriver
Url propertiesDomain =
<Your Active Directory domain name>
integratedSecurity = true
authenticationScheme = NTLM
Note: After adding the domain name as an Url property (as mentioned above), do not include it again when providing the SQL Server username.
Click Validate and select the Snaplex when prompted. The "Account validation successful" message is displayed.
Click Apply to finish setting up NTLM-based authentication.
Linux (Active Directory-based) Authentication
The SQL Server Account supports Linux-based authentication in Groundplexes. The setup instructions must be configured on the Groundplex to align with the Kerberos configuration, and then updating the connection settings provides an integrated authentication. Refer to Microsoft documentation for more information: Using Kerberos integrated authentication to connect to SQL Server - JDBC Driver for SQL Server
Troubleshooting
Error | Reason | Resolution |
---|---|---|
| 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.