We’ve all been there before. We set up our new web or application server with the latest code, update the connection strings with the proper SQL Server instance and password and fire up the application only to see all sorts of SQL connection/login issues. These issues can be very frustrating to troubleshoot because you are not sure if the Login is incorrect, the db is spelled incorrectly or if there are network issues preventing you from seeing the SQL Server. On windows machines, there is a simple way to validate that the SQL server is accessible from the server.
- Remote desktop into the server having connection issues
- Click on “Start” and search for Folder Options. Click on Folder options, go to the View tab and validate that the “Hide extensions for known file types” is unchecked:
Go to the desktop, right click and select New –> Text Document
- Rename the file connection.udl and accept the rename warning
- Double click on the udl file and it will open the Data Link window.
- In the Connection tab, enter the information for the SQL server and click the “test Connection” button:
- Now that you are successful connecting to the server, you can close the window and right click the connection.udl file and chose “Open with”. Select “Notepad” to view the proper connection string.
It’s a simple and effective way to troubleshoot SQL connection issues between servers and the SQL DB.