Unable to connect to sql server using window authentication

One Star

Unable to connect to sql server using window authentication

I am trying to connect to Sql server using windows authentication. I created a new connection in metadata with all the required parameter for generic JDBC type. I am using Microsoft JDBC driver.
I keep getting the authentication failed error even after using the correct username and password.
Here is the screenshot for that.
Moderator

Re: Unable to connect to sql server using window authentication

Hi,
What's talend build version you are using? What's your OS and sql server? Could you please create a new connection in metadata with all the required parameter for  MSSQL DB (tMSSqlConnection) type instead of generic JDBC type to see if it is OK with you?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to connect to sql server using window authentication

Yes, It works fine for mssql server . I am even able to connect to SQL server using JDBC type if I set integratedSecurity to false and give user name and password to use sqlserver authentication. Its only not working if I set Integratedsecurity to true.
I am using Talend 5.3.1, Windpows server 2008 and Sql server 2008R2.
Seventeen Stars

Re: Unable to connect to sql server using window authentication

Yes because an Java program cannot have access to the integrated credentials. This is only possible with special designed Windows programs. The JDBC driver does not support the mode  integratedSecurity=true
Moderator

Re: Unable to connect to sql server using window authentication

Hi,
So far, talend don't support for that. Because integrated security=true is a parameter for the jars that microsoft distributes. Talend uses the jtds jar and jtds connection string.
Please take a look at:http://stackoverflow.com/questions/167464/can-i-connect-to-sql-server-using-windows-authentication-f...
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to connect to sql server using window authentication

What do you mean by "the jars that Microsoft supports". If you are refering to Microsoft JDBC drives , I am already using that.
Here are the conncetion parameters that I am using:
DB Type: General JDBC
JDBC URL : jdbc:sqlserver://TEST:1433;Database=TestDB;integratedSecurity=true
Driver jar : c:\program files\java\jre1.7.0\lib\sqljdbc41.jar  \\this is the jar file of microsoft jdbc driver. I have placed it in java lib folder.
class name : com.microsoft.sqlserver.jdbc.SQLServerDriver
username : domain\uname
password : unamepassword
mapping file : id_MSSQL
As per this link in jira.talendforge this should work. Please let me know if I am using the correct values and not missed anything.
Seventeen Stars

Re: Unable to connect to sql server using window authentication

Yes this way it should work. We actually thought you use the dedicated tMS* components.
I suggest you try the necessary properties in a separate tool like the SQLDeveloper and if you get it running there use the configuration in your Talend job.
It looks like your URL is wrong. The first parameter must be separated from the path with a question mark! Change the first ; with a ? and try again.
One Star

Re: Unable to connect to sql server using window authentication

I replaced ; with ?. But it still didnot work. I am still geting the follwing error:
    Connection failure. You must change the Database Settings.
    com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:5fe9820a-c18d-4af9-b19a-bca3103de2cc
The connection string is as per this Microsoft article.
I have copied the dll sqljdbc_auth.dll to java\bin and windows\system32 folders. I have also added the dll's path in Path environment variable.
Seventeen Stars

Re: Unable to connect to sql server using window authentication

Hard to say whats necessary. I would digger in the Microsoft knowledge base how to do this.
One Star

Re: Unable to connect to sql server using window authentication

This seems to be a BUG.
tjdbcconnection is working fine with the same set of values. But creating new generic JDBC connection in metadata is not working.
Moderator

Re: Unable to connect to sql server using window authentication

Hi,
Have you tried it in anther server(Win7) to see if this issue repro?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: Unable to connect to sql server using window authentication

Hi,
Can we consider this topic as resolved?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.