Troubleshooting what should have been a working ODBC connection

Had a case where the customer was setting up a test database and the GP client server stopped connecting.  The troubleshooting steps in this blog can be used for testing when a connection should be working but just isn't.  In this case there were no errors just GP would sit and not connect.  When testing the OBDC connection nothing would come up.  We would normally see a pop-up saying connection was successful or failed but saw neither.

They could ping the server by name which indicated DNS and networking between the workstation and the server were working.  We did the regular troubleshooting of temporarily turn off the antivirus software, removed/added the ODBC connection, made sure the latest updates were installed, checked for pending reboots, temporarily disabled the firewall, and still if you clicked on Test Data Source nothing happened. 

The customer thought perhaps the SQL server was blocking connection to this workstation but nothing in the SQL logs or the Event Viewer on the SQL server showed anything indicating a blocked connection.  I was sure with the troubleshooting we had done so far nothing should be blocking the connection to the SQL server but the customer wanted to verify that.

To verify connectivity to the SQL server I used a UDL (Universal Data Link) file.  This is a great tool to help identify if there are SQL browser issues, to check if a protocol is turned off, or if a firewall/antivirus is blocking access. 

How do you download a UDL file?  You don’t.  This is a built-in feature of the OLE DB infrastructure in Windows.  To create a UDL create an empty text file on the desktop named testing “odbc.txt” and change the extension from txt to udl.  I have a screenshot of the before and after of the file rename.

If you double click on the “testing odbc.udl” file the following will appear.  I wanted to test connection to the SQL server without using the ODBC connection first, so I went to the “Provider” tab.

On the provider tab highlighted the “Microsoft OLE DB Provier for SQL Server”.  The "Microsoft OLE DB Provider" will connect on any SQL server from SQL 2000 to SQL server 2022.  Once selected clicked Next.

Clicking Next brings us back to the Connection tab.  Entered the SQL server name, sa for the user, and the sa password, and then clicked “Test Connection”

At this point the connection to the SQL server is made successfully.  That tells us there is no issue connecting to the database from the client machine using the OLE DB connection.  To confirm this further we can click on the dropdown for the “Select the database on the server”.  The list included the DYNAMICS and the TWO databases.

The next step was to use the UDL file to test the ODBC from this computer.  To do that we went back to the Provider tab and selected the “SQL Server Native Client 11.0” and clicked next.

On the connection tab entered the server's name, sa user and the sa password and clicked on Test Connection.

After about 20 seconds the the following error occurred.

After verifying the connection to the SQL server could be made from another test machine and rechecked this machine and verified, we got the same error from the UDL but still got nothing when testing from the ODBC connection itself.

At this stage since it was a test environment, and they were just setting it up we could have just blown the test machine away and did a new one.  But fascination kept me moving forward on this machine.  Once machines that behave weirdly such as this one of the first things that I do is check for missing OS files.  I do that with a sfc /scannow from an elevated cmd prompt.  The full use of the sfc command is detailed in the following link.

Use the System File Checker tool to repair missing or corrupted system files (microsoft.com)

To make a long story short the sfc did find corrupt and/or missing files and we were able to correct them.  Once we did the connection was able to be made to the database.  What caused the corruption I cannot say.  The steps outlined in this blog can be used to troubleshoot a wide variety of connectivity issues.