Data360 Analyze

Hello, have been using the JDBC query node to load files to a SQL Server table, it requires windows authentication. We started getting the error below. Does it mean that Data3Sixty sees the DB as untrusted, or is that the message from the DB indicating that Data3Sixty is an untrusted site?

Unable to connect to jdbc:jtds:sqlserver://txalle2brmtld04.itservices.sbc.com;instance=pd_brmcxx01;useNTLMv2=true;domain=itservices: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Thanks,

Brad

Correction, we are using the JDBC Store node.

The error is being generated from the DB rather than from Data360 Analyze.

Often, this error is generated because the client is in a different domain to the domain of the DB server.

Your post indicates the node had been working and is now generating the error. Can you confirm with your DBA and/or corporate security team whether anything has changed in the configuration of the DB machine that has changed domain memberships or whether they have recently tightened up network security policies that would prevent access to the DB machine from the server hosting Data360 Analyze. Can you also confirm the account used by Analyze is not locked.Is the DB configured to accept both Windows and SQL Server accounts?

We inquired with the DBA for the SQL Server yesterday, we're awaiting an answer. Some additional information, I'm able to connect to a different SQL server DB with the JDBC Store node and the JDBC node. I'll check the domain of the one to which I'm connecting.

Regarding the the last questions, "Can you also confirm the account used by Analyze is not locked.Is the DB configured to accept both Windows and SQL Server accounts?" I understand file locking, but I'm not sure what you are asking.

Thank you! Brad

SQL Server can be configured to lock a user's account if there are more than x invalid login attempts. Can you still access the DB using a third party SQL client using the credentials that would be used by Analyze?

The second part was a separate question "Is the DB configured to accept both Windows and SQL Server accounts?" and was just to confirm that the SQL Server is configured to use 'Windows authentication' rather than 'SQL Server authentication'.

The user can successfully connect to the account using another SQL client and the same credentials. The DB only accepts Windows authentication. Does SQL Server lock credentials too many attempts with invalid credentials based only on the credentials? Could it be configured to lock by the credentials and server machine? It's common for users to used multiple tools or platforms like tools that automate process flow, etc. Thanks for clarifying those questions.

Are you using the SQL Server that is shipped with analyze?

See the Help documention

https://doc.infogixsaas.com/analyze/Default.htm#h-tech-guides/database-connection-urls.htm

I missed your last reply (I was replying to stale browser page for the post). If it works with one client then it should be unlocked for all clients that use those credentials.

Regarding the post with the information for connecting with Windows Authentication. We have successfully connected with Windows authentication. We connected to the DB in the past with it and I'm able to connect with my credentials to a second SQL Server DB not related to the one we are having the trouble with.

I wanted to understand whether you were using the mssql-jdbc driver shipped with Analyze or the jtds driver.

But I now realize that you included the info in the error message

jdbc:jtds:sqlserver.....

thanks

Did you manage to get any information on whether the connection to the DB is cross domain from the Analyze server?

Re. "The user can successfully connect to the account using another SQL client and the same credentials." - was the client program installed on the Analyze server or the user's PC? 

Regards

Adrian

Have you tried changing the specified domain option in the connection URL to be the FQDN? i.e.

domain=itservices.sbc.com

Has the DB been updated to require SSL(TLS)  connections? You may want to try adding the ssl option:

ssl=request

(though some posts report problems with TLS1.2 when using the jTDS driver)

Thanks for the suggestions. Tried the full domain name, didn't work.

Is this what you meant regarding the ssl:

jdbc:jtds:sqlserver://txalle2brmtld04.itservices.sbc.com;instance=pd_brmcxx01;useNTLMv2=true;domain=itservices;ssl=reqeust

Thanks for the suggestion, I gave it a try. We got this message:

Unable to connect to jdbc:jtds:sqlserver://txalle2brmtld04.itservices.sbc.com;instance=pd_brmcxx01;useNTLMv2=true;domain=itservices;ssl=reqeust: Network error IOException: Connection reset

I understand you modified the connection string and that it is now working for you. If possible, it would be useful to the community to know the format that worked for you.

Update: We recommend using the default microsoft driver for SQL Server instead of the jTDS driver. Starting from Data360 Analyze release 3.6.8 the Microsoft driver can be used with Windows Authentication. The DbURL should be similar to the following format:

jdbc:sqlserver://<hostname>:<port>;authenticationScheme=NTLM;domain=INFOGIX;integratedSecurity=true