topic Re: Cannot connect to a SQL server database in Data Quality

Cannot connect to a SQL server database

Anonymous — Thu, 28 Feb 2019 22:16:56 GMT

So have tried on a number of occasions and re-installs of SQL Server 2018 and cannot for the life of me get a successful connection. All are on my local machine. I know that there have been threads before but they didn't assist much at all so....

The error I'm getting is:

Connection failure. You must change the Database Settings.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host LAPTOP-ABCDEFG, port 1433 has failed. Error: "Connection refused: connect. 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.".
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:204)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1133)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:316)
at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:290)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:4138)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:1)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host LAPTOP-ABCDEFG, port 1433 has failed. Error: "Connection refused: connect. 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.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:242)
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2369)
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:551)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1963)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:195)
... 9 more

The Talend connection details are:

Db Version: Microsoft

String of Connection: jdbc:sqlserver://LAPTOP-ABCDEFG:1433;DatabaseName=courseloop;

Password:

Server: LAPTOP-ABCDEFG

Port: 1433

DataBase: courseloop

Schema:

Additional Parameters:

Any thoughts would be great.

Re: Cannot connect to a SQL server database

vapukov — Fri, 01 Mar 2019 06:06:07 GMT

Hi,

by default when you install SQL Server it installed with disabled TCP/IP protocol

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

it is not important on remote server or on local machine - protocol is disabled by default

Re: Cannot connect to a SQL server database

Anonymous — Fri, 01 Mar 2019 10:10:12 GMT

Thanks tried that, but really don't know why I get password error when there is no password for the local instance.

Connection failure. You must change the Database Settings.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'LAPTOP-ABCDEFG\aitch'. ClientConnectionId:db04ab4f-b364-40b2-b753-01e1701cf908
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:204)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1133)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:316)
at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:290)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:4138)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:1)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'LAPTOP-ABCDEFG\aitch'. ClientConnectionId:db04ab4f-b364-40b2-b753-01e1701cf908
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:195)
... 9 more

Re: Cannot connect to a SQL server database

vapukov — Fri, 01 Mar 2019 10:49:17 GMT

it is JDBC, you must enable Native authentification for JDBC by connection parameters

jdbc.url=jdbc:sqlserver://hostname:1433;databaseName=dbname;s;sendStringParametersAsUnicode=false;integratedSecurity=true

also possible you need add dll (in case of JTDS jdbc)

it is not a Talend related issue, it is common for JDBC + SQL Server

Re: Cannot connect to a SQL server database

Anonymous — Mon, 04 Mar 2019 21:38:11 GMT

unfortunately, still not working - so really not sure why that's the case

Re: Cannot connect to a SQL server database

Anonymous — Mon, 22 Jun 2020 16:36:45 GMT

Hi All,

getting same error in postgres connection. Yesterday it was working.