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.
"Connection is closed" error with MSSQL service account
I’m running into a strange problem connecting a pipeline to one of my company’s MS SQL servers. My pipeline requires two service accounts as I’m connecting to servers on two different domains. I tested it using accounts linked to lower environments in each domain, and it worked perfectly. Then I added accounts for our prod environments. One of these works fine, but the other keeps giving me an error “Connection is closed.” Here’s the stack trace: com.snaplogic.api.ExecutionException: Could not retrieve a connection to database. at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.acquireConnection(JdbcOperationsImpl.java:351) at com.snaplogic.snaps.sql.SimpleSqlSelectSnap.defineOutputSchema(SimpleSqlSelectSnap.java:433) at com.snaplogic.cc.util.SnapUtil.defineSchema(SnapUtil.java:286) at com.snaplogic.cc.snap.common.SnapRunnableImpl.configureSnap(SnapRunnableImpl.java:746) at com.snaplogic.cc.snap.common.SnapRunnableImpl.executeForSuggest(SnapRunnableImpl.java:624) at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:865) at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:436) at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:120) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Connection is closed at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:502) at com.sun.proxy.$Proxy172.getAutoCommit(Unknown Source) at com.zaxxer.hikari.pool.HikariProxyConnection.getAutoCommit(HikariProxyConnection.java) at com.snaplogic.snap.api.sql.connection.ConnectionProxy.getAutoCommit(ConnectionProxy.java:127) at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.handleAutoCommit(JdbcOperationsImpl.java:2460) at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.acquireConnection(JdbcOperationsImpl.java:346) ... 13 more Reason: Connection is closed Resolution: Please address the reported issue. This is a general service account for accessing this server, and it works perfectly well for another project. I copied all the configuration information exactly, and the account validates. Is this a problem within SnapLogic, or something I need to take up with my company’s database team?SQL Server Stored Procedure Snap hangs when returning data
Hello, I’m working with a Stored Procedure that has no input variables and returns about 4800 rows of data. When I build out my pipeline and validate, the procedure returns the first 50 rows successfully (as expected). However, when I go for an actual execution the pipeline hangs on that particular snap. Given that my pipeline works when validating, I don’t think there is a connectivity issue. I don’t know if there is some action I need to take in order to handle larger data sets? Any information or insight here would be very helpful Thank you Dan Edit: Here is a screen shot of the pipeline execution in the dashboard. It will hang at exactly 1024 docs each time
SQL Bulk load truncating data
Hi Community, I am trying to SQL Bulk Load with the Create Table option. When this runs it is erroring on string truncation. I do not understand how to use the Mapper with only one side as a second input into the pipeline. I do not want to have to map all the fields/objects I have 1900+ fields and 167+ tables. SQL Insert is inserting with Varchar(8000) and this doesn’t work because it is not supporting unicode characters. Thanks for any help.
Table Creation in SQL server with specific data type using data from excel file
Hi all, Thanks in advance for read and any help here. I have a pipeline where I had a excel file, with columns name and table name, so first I read this excel file and them make a parse, this is a sample of the excel file: So I want (if not exist) create a table AAA with the columns 0XX5, 0XX6, 0XX7, 0XX8, 0XX9 each one with the respective Datatype, how can I o that?SQL Insert from JSON change type and only type
Hi All, I have a Pipeline that takes data from a Rest API and converts it to a SQL database. This is all working and we are able to create the table without having to define the Schema in a mapper (we have hundreds of tables with thousands of fields and it would be very difficult to map them all individually). My current issue is that when converting from the JSON to SQL Server it is automatically converting the type from string to varchar(8000). We have Japanese characters etc that are being lost because of this conversion. I need to have it be nvarchar(4000) or if the string is >4000 be nvarchar max. The question is how do I make that conversion without having to map/define each and every field. And at the same time making sure I don’t truncate anything. Thanks for your help.
SQL Server account connection setup failing with multiple error
Hello Community, We are trying to setup SQL server account in Snaplogic, however, getting below error Invalid username password- Able to login with windows authentication in microsoft sql management studio Failed to validate account: Failed to retrieve a database connection. Cause: Login failed for user ‘\empid’. ClientConnectionId:27dc1f0c-b783-4327-8d10-c77c82f6c198 (Reason: Login failed for user ‘\empid’. ClientConnectionId:27dc1f0c-b783-4327-8d10-c77c82f6c198; Resolution: Ensure credentials are valid, multiple attempts with invalid credentials may result into account getting locked) If we use domainname/instancename in HOSTNAME, getting below error Failed to validate account: Failed to retrieve a database connection. Cause: The TCP/IP connection to the host /, port 1433 has failed. Error: “/. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”. (Reason: The TCP/IP connection to the host /, port 1433 has failed. Error: “/. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.; Resolution: Address the reported issue.) version - 13.0.5 Here are our account settings - Hostname: abc.bot.com/mydatabase port:1433 DBname:emp_name username: abc\123 password:<> Also, Please share sample account settings for our reference. Thank you in advance.SQL server snap - Unexpected Re-throwing connection error
Hi Community, I was trying to access SQL server via snaplogic but I wasn’t successful as I was facing an issue with the connection. Below are the two error messages when I tried with different plexes. Error while validating with groundplex 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.) Error while validating with cloudplex: Failed to validate account: Failed to retrieve a database connection. Cause: The TCP/IP connection to the host ***********************, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”. (Reason: The TCP/IP connection to the host ****************************, port 1433 has failed. Error: “null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.; Resolution: Address the reported issue.) JDBC drives that I used: mssql-jdbc-9.4.0.jre8 mssql-jdbc-7.4.1.jre8 mssql-jdbc-10.2.0.jre8.jar mssql-jdbc-7.4.1.jre11.jar* Details: I would really appreciate if anyone could help me on this. Thank you.
