Unable to make SQL DB connection using "Connection Profile" with Windows Integrated Security
search cancel

Unable to make SQL DB connection using "Connection Profile" with Windows Integrated Security

book

Article ID: 189013

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are trying to set up TDM Portal using Integrated Security with our SQL Server Database. However, the connection to the SQL Server Database is failing. We have verified the account we are using does have access and can connect to the SQL DB / Instance via Windows Integrated Security, and are using this same account to log into CA TDM when trying to set up the Connection Profile in TDM Portal. We contacted our DBA, who confirmed the connection is not even making it to the DB server, so nothing to see.

The Connection Profile error displayed on Testing the connection:

The server replied: Failed to obtain JDBC Connection; The connection to the host LOUSQLWQSxxx.domain.com, named instance LOUSQLWQSxxx 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.

We are unable to find any log entries on the CA TDM server that could assist us, and this is our first attempt to utilize integrated security to make a DB connection, but is quickly becoming the standard for our company. 

Environment

Release : 4.9 and later

Components: TDM Web Portal and DataMaker

Cause

Each of the TDM Portal microservices generates its own log in the %ProgramData%\CA\CA Test Data Manger Portal\logs directory. The log that will most helpful in this situation is the TDMConnectionProfileManager.log

Resolution

From the startup.log I can see the TDMConnectionProfileManager service for Portal is running on 'LOUAPPWQSxxxx'.

There are two errors that we see thrown in the TDMConnectionProfileManager.log.

  1. c.c.t.c.e.h.GlobalExceptionConvertor: HTTP Error - SERVICE UNAVAILABLE: Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'DOMAIN\LOUAPPWQSxxxx$'. ClientConnectionId:5d46e911-a643-49d1-9695-f39806f8b11b
  2. c.c.t.c.e.h.GlobalExceptionConvertor: HTTP Error - SERVICE UNAVAILABLE: Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host LOUSQLWQSxxx, named instance LOUSQLWQSxxx 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.


I know host LOUAPPWQSxxxx is the TDM Portal server. I assume host LOUSQLWQSxxx is the SQL Server hosting the database you are trying to connect to, and the connection requests from LOUAPPWQSxxxx are failing because the socket we are trying to communicate across is timing out.

There are many things that can cause this, but the most common are:

  • A misconfigured connection profile used by TDM, such as using the wrong instance name.
  • A network issue, such as a firewall blocking the communication port.
  • A problem with the SQL Server host, such as the SQL Server Browser Service is not running.


I would like for you to try to create a new Connection Profile using GT DataMaker, and see if you are able to connect o the SQL Server database. This will remove TDM Portal from the picture.

To create a new Connection Profile:

  1. Launch DataMaker and log in
  2. Click the 'Create New Profile' (looks like a folder with a +) in the Profiles' dialog window.
  3. Select 'Other' when asked "What type of database do you want to connect to?"
  4. Give the Connector a name
  5. Choose 'DNS-less ODBC'
  6. Complete the profile dialog with your SQL Server details.
  7. Enable the option 'No login required', and leave the username and password blank.
  8. Specify a default Schema
  9. Click the Test button (looks like a green gear)
  10. If successful, click the save button (Looks like a green check mark)


The new profile will be visible, and usable from CA DataMaker, as well as Portal.

  • If you can connect from DataMaker, launch Portal and try connecting from the new Connection Profile.
  • If DataMaker and Portal fail to connect, you will want to install SSMS (SQL Server Management Studio) on the Portal server and try connecting using SSMS. This will take TDM completely out of the picture, allowing you a way to test the communication port between the Portal server and the SQL Server.
  • If SSMS can connect, then there definitely is a problem with TDM. Notify Broadcom Support, and provide a copy of the TDM Portal logs. It would be best to have TDM Portal in debug at the time you gather the logs.
  • If SSMS cannot connect, the issue is most likely a network issue, such as a firewall blocking the communication. You will need to work with your local Network team, and/or your SQL Server DBA to further troubleshoot the issue.

Additional Information

Powered by Wolken Software