One Star

[resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi there. This could be simple but I cannot get it. How do you connect to SQL Server using Windows Authentication? It looks like I Profiler wants me to use SQL Server authentication only... Am I missing something? Thanks.

19 REPLIES
Employee

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi Darius,
did you try to connect without setting a login and password?
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi Darius,
did you try to connect without setting a login and password?

Hi. I have found a reply on this forum: Currently SQL Server 2008 Windows Authentication is not supported.
I have created a standard SQL Server login and used it instead of win auth.
Best
Darek
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi Darius,
Could you elaborate how you got Talend to work with SQL Server 2005? I've tried using SQL Server authentication and that hasn't work for me.
Thanks
Jack
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi,
I do not reach to make run my TOS or TOP with Windows 7 and SQL Server 2008 R2.
When I try to create a database connection, and after I click on button "Check" following error message appears "Check connection failure:login failed for user 'damien'.
damien is a SQL Server user.
Even with windows authentification user it's not running
THanks in advance for your support.
Damien.
Community Manager

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hello Darius
I have found a reply on this forum: Currently SQL Server 2008 Windows Authentication is not supported.

Yes, TOP/TDQ change to use Microsoft JDBC driver and it don't support windows Authentication, to work round this, try to use MS SQL authentication. For TOS/TIS component, it use driver JTDS and it support windows Authentication by defaut.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi,
After your advises to connect to my SQLServer 2008 with JDBC Driver with a SQLServer user, I still have some issues to make it run.
Perhaps the configuration is not the right, here below the configurations I filled in :
Driver JAR : C:\Program Files\Java\jdk1.6.0_15\jre\lib\rt.jar;
Driver class name : sun.jdbc.odbc.JdbcOdbcDriver
URL : jdbc:sqlserver://localhost:1433;DatabaseName=Test;zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8
Error message is "Check connection failure:Can't create the connection!
Thanks in advance.
Damien.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I don't use MS SQL Server 2008 or Windows 7, but have you tried using an ODBC connection? Setting it up as a systems DNS might work. The windows authentication through an ODBC connections works perfectly for me with earlier verions of MS SQL server.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Thanks for your quick answer.
Can you please precise how you configure your ODBC connection ? and how do you set it up as a systems DNS ?

Thanks in advance for your support.
Regards,
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

in Windows: open Control Panel -> Administrative tools -> Data Source (ODBC)
add your connection information to the System DNS section (tab System DNS). On 1st screen select SQL Server. Then on the next screen give it a name and provide the server information - remember the name you give your connection. You can specify on the 3rd screen the Windows Authentication.
in TOP: open Metadata -> Db Connections
add a db connection to the reporsitory. On the 2nd screen (step 2/2) select Generic ODBC in the DB Type field. Then enter the ODBC connection name to the DataSource field. Enter Login parameters in the form \ and don't forget your password. I'm not sure whether the last step is really needed, but it works for me.
Good luck.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Greetings,
I have success using SQL2008R2 on Windows 7, and the ODBC creation as you stepped through above (per usual), does work for Windows 7. However, in Talend there is no success using this ODBC/DSN or SQL Server direct for building a metadata connection.
The error:
Connection failure. You must change the Database Settings.
Invalid string or buffer length
I have tried both using SQL and Windows Authentication. ODBC connects in both cases and tests successfully, Talend is not successful.
I do not have any problems connecting with SQL 2005 in Windows 7
Any other thoughts? @dledizes, have you found success?
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I have exactly the same error message !!
Invalid string or buffer length
Regards,
Damien.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I am getting the same error as well. I am on Windows 7 64 bit/SQL Server 2008 R2. I tried both 32 bit ODBC as well the 64-bit Native Driver.

Connection failure. You must change the Database Settings.
Invalid string or buffer length

Any Solutions????
Employee

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Which version of Talend Open Profiler are you using?
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

fyi
I once also struggled with sql connection and windows authentication then I found information and ended up downloading the ntlmauth.dll file and copied it to my
windows/sytem32 directory which enabled windows auth from that computer. I used a normal jdbc connection from talend.
Maybe this can help somebody
Regards,
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I am on Windows 7 64 bit/SQL Server 2008 R2. I tried both 32 bit ODBC as well the 64-bit Native Driver.

There is a bug in 64-bit ODBC for SQL Server with Java - see http://www.talendforge.org/bugs/view.php?id=14149.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I'm trying to connect to MS SQL Server 2008 via the DB Connections form in Talend DQ Version 5.0.2.r78237.
I first tried selecting MS SQL Server, MS SQL Server 2005/2008 as well as ODBC in the DB type and none of these options work. ODBC connection tests correctly.
I tried both Windows and SQL Server credentials but neither functions. I tried moving the ntmauth.dll to C:\Program Files (x86)\Java\jre6\bin but that does not work.
The DB Connections functionality allows me to connect to the same DB on TOS - MDM and TOS Data Integration but not with DQ.
Any advice would be welcome.
Thanks
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

I'm trying to connect to MS SQL Server 2008 via the DB Connections form in Talend DQ Version 5.0.2.r78237.
I first tried selecting MS SQL Server, MS SQL Server 2005/2008 as well as ODBC in the DB type and none of these options work. ODBC connection tests correctly.
I tried both Windows and SQL Server credentials but neither functions. I tried moving the ntmauth.dll to C:\Program Files (x86)\Java\jre6\bin but that does not work.
The DB Connections functionality allows me to connect to the same DB on TOS - MDM and TOS Data Integration but not with DQ.
Any advice would be welcome.
Thanks

Same here.
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Hi Everyone,
It is my first day on Talend and I feel as if I have been living under the 3rd rock from the sun. What an amazing product. Kudos to everyone who made this happen. I am busy with an independent review for power users and this product is right up there...
I have found a way around the SQL 2008 connectivity, but its kind of longwinded although it does work...
Firstly, there is a problem with SQL Server (ODBC) right now - TOS returns an error message that the string buffer is incorrect. Using Generic ODBC also throws an error - Invalid String or Buffer Length. With SQL Server connection, it authenticates find but if you try and import the schema it fails on authentication. The same happens on Generic JDBC.
That was the bad news.
Now for the good news...
If you download Microsoft SQL Server JDBC Driver 3.0 from their site, you could specify a generic JDBC driver. When you download the file, extract the jar via the executable.
Your JDBC URL will look something like this jdbc:sqlserver://yourinstancename:1433;databaseName=yourdbname
When specifying the generic JDBC driver ensure that you select the jar you just extracted. Click on the elipses by the class name which should populate the info for you - for in case it doesn't the class name is "com.microsoft.sqlserver.jdbc.SQLServerDriver". Enter your userid and password. Here comes the next trick --> select the Export as context button. I cannot even call myself a greenhorn on this amazing technology but I think its almost like config files. This will get TOS to remember the credentials you supplied.
TADA - when you import the schema it will be there. In my opinion generic JDBC will in any way be faster than ODBC although I wouldnt want to be quoted.
Good luck
One Star

Re: [resolved] Cannot connect to SQL Server 2008 - Windows Athentication

Setting up a System DSN in ODBC and connecting in Talend via the Generic ODBC driver works. Thanks for helping this newbie!