Not able to connect to SQL Server Remote Database

Hi All,

I am a new user; trying to make a connection with SQL server remote database.
Its failing always with following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host XXXXXX, named instance YYYYYY failed. Error: “java.net.SocketTimeoutException: Receive timed out”. Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:3589)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:1225)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:972)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:86)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:46)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:70)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:41)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getRawConnection(DataSourceImpl.java:96)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getNonTransactionalConnection(DataSourceImpl.java:64)
at com.ataccama.dqc.gui.ui.wizards.database.DatabaseConnectionModel.testConnection(y:954)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage$1.run(y:1147)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

-> Please see I can connect to this remote database using SQL Server Management system. Thus no authorization/authentication issue with the remote database.

Not sure. Any help please.

Thank you so much.

Regards,
Tapasya

Hi Tapasya,

There might be several causes for the error.

It may be caused by the configuration of SQL Network Configuration.

Please check the option below:

Open SQL Server Configuration Manager and go to Protocols for SQLEXPRESS under SQL Server Network Configuration. Right-click on TCP/IP and choose Properties. Check whether it is Enabled. If NO, set Enabled = YES. Then click on IP Addresses tab, find the section IP All and check what port is used. It should match the port you are using in DQA.
¨
I have some additional questions for you:

  1. Do you start SQL Server Management Studio on the same machine where you run DQA? Or is it the machine where you have SQL Server installed?

  2. It can be related to the authentication method. Can you share more information about which one you are using?

  3. If it’s possible can you share a screenshot of the Database Connection window of DQA? Mask the sensitive information.

Thank you so much for your response Inna!

1.Do you start SQL Server Management Studio on the same machine where you run DQA? Or is it the machine where you have SQL Server installed?
– Yes, I am starting my SQL Server Management Studio on the same machine as of DQA.

2.It can be related to the authentication method. Can you share more information about which one you are using?
– This database I am trying to connect to is a Dev Database server. When I connect through SSMS, I use Windows Authentication. To open the DQA, I tried to create a batch file, which will automatically pick the Windows Authentication, like I am doing for other tools like Tableau, to access the database. But some how it is not working in the case of DQA.

3.If it’s possible can you share a screenshot of the Database Connection window of DQA? Mask the sensitive information.
Screen shot in separate reply.

Thank you so much for all your effort to help me resolve this.

In addition:

  1. Also I tried to check SQL Server Configuration Manager settings, as you had mentioned, but I couldn’t even find the option.

Hi Tapasya,

Thank you for the input.

If you are using the windows authentication method, you need to add the integratedSecurity=true; parameter to the Connection String. Thus, your Connection string should look as follows:

jdbc:sqlserver://<host>:<port>;databaseName=<database_name>;integratedSecurity=true

Please, let us know whether it worked.

Thank u for your reply and help.
My connection string now is:
jdbc:sqlserver://XXXX;databaseName=YYYY;integratedSecurity=true
Now, I am getting a different error:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host XXXX, port 1434 has failed. Error: “connect timed out. 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:190)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:86)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:46)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:70)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:41)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getRawConnection(DataSourceImpl.java:96)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getNonTransactionalConnection(DataSourceImpl.java:64)
at com.ataccama.dqc.gui.ui.wizards.database.DatabaseConnectionModel.testConnection(y:954)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage$1.run(y:1147)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

What is this port number and how can I get this for my remote SQL server? - by default it is taking 1434

Hi Tapasya,

The default instance of SQL Server listens on Port 1433. Port 1434 is used by the SQL Browser Service which allows connections to named instances of SQL Server.

We suggest you try the following:

  1. Use the following Connection String
    jdbc:sqlserver://<host>:1433;databaseName=<database_name>;integratedSecurity=true

Please note the port 1433 in the Connection string

  1. Go to SQL Server Configuration Manager
  • Navigate to SQL Native Client 11.0 Configuration (32 bit) -> Client Protocols. Verify whether the TCP/IP is enabled. Double-click on it and check the configuration.

  • Navigate to SQL Native Client 11.0 Configuration -> Client Protocols. Verify whether the TCP/IP is enabled. Double-click on it and check the configuration.

Thank you so much Said.

I cross checked, what’s mentioned and all looks fine.
When I tried using 1433, I am getting following error:
Unable to connect to the database: Unable to test connection: null

Details:

java.lang.reflect.InvocationTargetException
at org.eclipse.jface.operation.ModalContext.run(ModalContext.java:421)
at org.eclipse.jface.dialogs.ProgressMonitorDialog.run(ProgressMonitorDialog.java:507)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage.nj(y:3403)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage.Ǔ(y:1830)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage.ǚ(y:646)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage$7.widgetSelected(y:289)
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 com.ataccama.dqc.gui.ui.navigator.actions.wizards.PurityNewWizardAction.run(y:1365)
at org.eclipse.ui.actions.BaseSelectionListenerAction.runWithEvent(BaseSelectionListenerAction.java:168)
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 com.ataccama.dqc.gui.application.AtcmApplication.start(y:1465)
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:620)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:575)
at org.eclipse.equinox.launcher.Main.run(Main.java:1408)
Caused by: java.lang.UnsatisfiedLinkError: com.microsoft.sqlserver.jdbc.AuthenticationJNI.SNISecGenClientContext([B[I[BI[B[I[ZLjava/lang/String;ILjava/lang/String;Ljava/lang/String;Ljava/util/logging/Logger;)I
at com.microsoft.sqlserver.jdbc.AuthenticationJNI.SNISecGenClientContext(Native Method)
at com.microsoft.sqlserver.jdbc.AuthenticationJNI.GenerateClientContext(AuthenticationJNI.java:80)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2691)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:86)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:46)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:70)
at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:41)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getRawConnection(DataSourceImpl.java:96)
at com.ataccama.dqc.commons.sql.DataSourceImpl.getNonTransactionalConnection(DataSourceImpl.java:64)
at com.ataccama.dqc.gui.ui.wizards.database.DatabaseConnectionModel.testConnection(y:954)
at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage$1.run(y:1147)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

Tapasya,

There are several available options.

By default, SQL is using dynamic ports for TCP/IP connections.
In order to connect to SQL using DQA, you need to define the exact port number.
The first option, ask your dba (or the responsible person) to check the following:

If the port is not defined, please define the port number and use it in the Connection String.
If you have a predefined port already, specify this port number in the Connection String

The second option is that if you have a firewall, it may block connections on the specific port. You need to allow the TCP/IP connections to SQL on this specific port.

Please, let us know if it helped you.

I tried all of these but couldn’t connect.
I am exporting the table in a txt file and proceeding ahead with Column analysis.

I will update the thread once I will be able to connect.

Tapasya,

In order to help you, we can organise a web-session.
Please, let us know if you would like to proceed this way.

Sure. When can we do that. That would be a great help.
-Thanks

Hi. Was this issue resoslved? If it was, can you please post the solution? I am experiencing the same issue.

Hi Carmel,

I would like to ask you for more information.
Could you please send the details of your Database Connection configuration (Right click on the database connection in the File Explorer panel --> Edit Connection…)? If it’s possible, please share a screenshot (sensitive information can be masked).

Also could you please send the entire error log you get while you are trying to connect to the database?

Inna

Hi Inna,

I sent a reply email to community@ataccama.com with the subject: RE:[Ataccama Community] [DQ Analyzer] Not able to connect to SQL Server Remote Database. Thanks.

Carmel

• I connect to a remote server via Microsoft SQL Server Management Studio Version 12 via Windows Authentication.
• Driver used: sqljdbc41.jar.
• TCP/IP is Enabled.

java.rmi.RemoteException: Unable to connect to the database

; nested exception is:
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘SMI\clibrero’. ClientConnectionId:09481835-dd8f-404a-b45d-ce84888ab0bc
at com.ataccama.dqc.gui.model.internal.database.DBDatabaseImpl.connect(y:533)
at com.ataccama.dqc.gui.ui.navigator.actions.database.DatabaseConnectionAdapter.connect(y:2814)
at com.ataccama.dqc.gui.ui.navigator.actions.connection.ConnectOperation.execute(y:1202)
at com.ataccama.dqc.gui.ui.navigator.actions.connection.OperationConnectionAction$1.run(y:1877)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘SMI\clibrero’. ClientConnectionId:09481835-dd8f-404a-b45d-ce84888ab0bc
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:251)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:81)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:3077)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2360)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2346)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1404)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1068)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:904)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:451)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1014)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:87)
at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:71)
at com.ataccama.dqc.internal.commons.sql.ConnectionCreator.createResource(ConnectionCreator.java:36)
at com.ataccama.dqc.internal.commons.sql.ConnectionCreator.createResource(ConnectionCreator.java:24)
at com.ataccama.dqc.commons.resourcepool.ResourcesPool.createResource(ResourcesPool.java:110)
at com.ataccama.dqc.commons.resourcepool.ResourcesPool.createResources(ResourcesPool.java:241)
at com.ataccama.dqc.commons.resourcepool.ResourceCreatorTask.run(ResourceCreatorTask.java:21)
at java.lang.Thread.run(Unknown Source)

Hi Carmel,

Which authentication method you are using?
If you are using the Windows authentication method, try adding the integratedSecurity=true; parameter to your Connection string.

Thus, your Connection string should look as follows:

jdbc:sqlserver://<host>:<port>;databaseName=<database_name>;integratedSecurity=true

Do i need to fill out the user name and password after i put integratedSecurity=true in the connection string?

It is necessary to fill out the credentials in the Username and Password fields.