Connection to MSSQL 2008 on Win2K8

One Star

Connection to MSSQL 2008 on Win2K8

Hi,
I've just installed TOS 5.0.2.r78327 on Windows 2008 Server SP1 64-bit with SQL Server 2008 64-bit on the same host. I'm using JRE version is 1.7.0_03 64-bit. I start TOS by executing TOS_DI-win-x86_64.exe.
I've downloaded a copy of jtds-1.2.5.zip and from the x64/SSO folder, copied ntlmauth.dll into c:/windows/system32 and C:\Program Files\Java\jre7\bin.
I've re-started TOS. I then try and create a connection to the database with the following information:
DB Type = Microsoft SQL Server
Server = localhost
Port = 49527
Database = copdb
Schema = copdb
I click on Check and get "Connection Successful".
I click on Check again and I get "Connection failure. You must change the Database Settings." The details have the following:
Connection failure. You must change the Database Settings.
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
Firewall is off.
I checked http://jira.talendforge.org/browse/TDI-18787 and looked at my configuration but I don't have any http proxy settings.
I know this was an issue on v4.2 hence the decision to upgrade to 5.0 but I still seem to be running into this issue. Any ideas?
Thanks and Regards,
--
mohammed
One Star

Re: Connection to MSSQL 2008 on Win2K8

Hi
We need to fix this issue step by step.
Step1: Login with SQL Server Management Studio to check whether you have started up SQL Server Service.
Step2: Open Sql Server Configuration Manager-->SQL Server Protocal-->TCP/IP-->IPALL and get dynamic port.
Step3: Connect to SQL SERVER in SQL Server Authentication mode in TOS to check whether host and port are correct.
Step4: Connect to SQL SERVER in Windows authentication mode.
- No.1: Download the driver here. http://sourceforge.net/projects/jtds/files/
- No.2: You will find in the folder SSO a file called ntlmauth.dll. Put it in c:\windows\system32 and restart TOS. (Delete ntlmauth.dll in %JAVA_HOME%\bin)
- No.3: Create a new MSSQL connection and leave user & password fields empty.
Or jtds 1.2.5 isn't compatible with JDK 1.7?

Regards,
Pedro
One Star

Re: Connection to MSSQL 2008 on Win2K8

Hi Pedro,
Thank you for your response. I tried those steps again and re-downloaded the jtds-1.2.5.zip from the link you had provided. I removed JRE 1.7 and installed JRE 1.6 and removed ntlmauth.dll from %JAVA_HOME%\bin. ntlmauth.dll is now only installed in c:\windows\system32. I've also re-installed SQL Server 2008 and the database is listening on 1433.
I'm able to connect using a username and password but not able to connect using Windows Authentication Mode. It allows a one time connection followed by the error:
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
The error stack from the log is shown below (Help > About Talend Studio > Installation Details > Configuration > View Error Log):
!ENTRY org.talend.platform.logging 4 0 2012-04-17 12:04:09.573
!MESSAGE 2012-04-17 12:04:09,564 ERROR org.talend.commons.exception.CommonExceptionHandler - java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
!STACK 0
java.lang.RuntimeException: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:108)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:715)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:1677)
at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:141)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm.checkConnection(DatabaseForm.java:847)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm.access$19(DatabaseForm.java:798)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$4.widgetSelected(DatabaseForm.java:1013)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:234)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4066)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3657)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
at org.eclipse.jface.window.Window.open(Window.java:801)
at org.talend.repository.ui.actions.metadata.CreateConnectionAction.doRun(CreateConnectionAction.java:204)
at org.talend.repository.ui.actions.AContextualAction$2.run(AContextualAction.java:562)
at org.talend.repository.RepositoryWorkUnit.executeRun(RepositoryWorkUnit.java:84)
at org.talend.core.repository.model.AbstractRepositoryFactory.executeRepositoryWorkUnit(AbstractRepositoryFactory.java:217)
at org.talend.repository.localprovider.model.LocalRepositoryFactory.executeRepositoryWorkUnit(LocalRepositoryFactory.java:2592)
at org.talend.core.repository.model.ProxyRepositoryFactory.executeRepositoryWorkUnit(ProxyRepositoryFactory.java:1740)
at org.talend.repository.ui.actions.AContextualAction.run(AContextualAction.java:567)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:411)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4066)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3657)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2640)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2604)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2438)
at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:671)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:664)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at org.talend.rcp.intro.Application.start(Application.java:129)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:619)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:574)
at org.eclipse.equinox.launcher.Main.run(Main.java:1407)
at org.eclipse.equinox.launcher.Main.main(Main.java:1383)
Caused by: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:615)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:39)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:100)
... 49 more
Caused by: java.io.IOException: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1893)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:588)
... 54 more
Thanks
Regards,
--
mohammed
One Star

Re: Connection to MSSQL 2008 on Win2K8

Hi Pedro,
Just wanted to add that I also just tried using 4.2.4.r76583 on Windows7 and JRE 1.6 trying to connect to SQL Server 2008 on Windows 2008 and got the same error.
Regards,
--
mohammed
One Star

Re: Connection to MSSQL 2008 on Win2K8

Hi
We can't reproduce this issue.
Try the last way I know.
Copy ntlmauth.dll into <Talend studio install dir>\.

Regards,
Pedro
One Star

Re: Connection to MSSQL 2008 on Win2K8

Hi Pedro,
That didn't work either. At this point I assume it's a bug. I'll switch to mixed authentication as that seems to work without any issue. Maybe the SQL Server version is too new?
TOS 5.0.2 r78327
JRE 6u31
Windows Server 2008 R2 with SP1
SQL Server 2008 R2
I appreciate your help.
Thanks and Regards
--
mohammed
One Star

Re: Connection to MSSQL 2008 on Win2K8

For those people encountering problems connecting to 64 bit SQL Server 2008 R2 on a 64 bit Windows 7 machine, test / try the following :
Run SQL Server Configuration Manager
> SQL Server Network Configuration
> Protocols for MSSSQLSRVER
> Enable TCP/IP (if disabled)
In my case, only the SQL Server Native Client (32 bit) TCP/IP protocol was enabled. Logging on via SQL Server Management Studio (32 bit) was working using SQL Authentication but creating a DB connection via TOS wasn't.