Using Windows Authentication with SQL Server Accounts
The topic of "providing windows ID / Password to SQL Server has come up a few different times so below is a solution that I found to work in most instances. Scenario: Service accounts with Native SQL Server authentication is NOT supported (internal security rules, regulatory compliance etc.) Running SnapLogic using “IntegratedSecurity = true” would require every database to grant access to an individual robot / service account and didn’t allow specific user tracking Solution: 3rd party JDBC driver called jtds (http://jtds.sourceforge.net/) allows you to specify URL Properties and pass in account credentials. You will need to upload this jar file into the SLDB and reference that within the account settings (see screen caps below). On the SQL Server, you can see Windows Authentication selection for ID “SQLSvr_TESTING” Account settings within SnapLogic for “SQL Server Account” type: Account settings that need to be defined Referencing the JAR files uploaded to the SLDB The JDBC Driver Class should be set to net.sourceforge.jtds.jdbc.Driver URL Properties Defined: useNTLMv2 = true domain = [YourDomain] instance = [InstanceName-OPTIONAL] NOTE : It’s important to stipulate that if the users windows password changes, they will need to come into SnapLogic and adjust the password in the account as well. By default, the SQL Server snaps will attempt to reconnect 3 times so you may get into a situation where the ID becomes locked out.
Sharding SQL Server DB Table
Sharding a table is a type of partitioning that separates very large table the into smaller parts for loading data in parallel into target system. For Performance improvement the table was partitioned into multiple parts with Shard Offset snap and trigger parallel jobs for fast data transfer 1- Drag the SQL server execute snap on to the canvas and get the count of rows for a particular table. 2- Drag the Offset Snap and make changes to the Shard count setting. Step1 gets the total count of rows in a table and does a split by the Shard count. The O/P of the snap gives offset and the limit that are used to Query the DB table to fetch the rows. 3 - Use Pipeline Execute/For-each Snaps to send the offset and limit parameters to the child pipeline that queries the table for the data. 4 - In the Child Pipeline have the SQL Server execute snap query the table by taking the offset and limit as parameters. These kind of scenarios mostly come up when you are loading huge volume of data into Target Systems.
SQL server unable to validate
Hi Team I am getting error while validating SQL server account from snaplogic which will connect to SQL server with windows 2003. And getting error as below, can any one help me on this please. Failed to validate account: Failed to retrieve a database connection. Cause: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “Unexpected rethrowing”. (Reason: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “Unexpected rethrowing”.; Resolution: Address the reported issue.) Regards Kasi
