I was recently trying to troubleshoot why my custom SharePoint application could not connect to a database, and I found this great tip on the ASP.Net forums that I wanted to share for when you can’t or don’t want to install SQL Management Studio onto the server.
First a little back story: My application is pulling some financial data from an SAP database to display to the users. The farm is set up with a SharePoint web front end server inside the firewall for internal employees to log in to, and another web front end is located in the DMZ which is what their customers log into using forms based authentication.
The client recently moved their SAP database to a different server so we thought, no problem, we’ll just update the connection string and all will be good.
Everything worked fine from the internal server, however from the DMZ server, I was getting the following error message (mind you it’s the exact same codebase, using the exact same connection string in the respective web.config files):
Login failed for user ‘[user name]’
The DBA was convinced that it was an issue with the code passing an empty security token to the database. In my attempt to prove him wrong, I wanted to check whether the DMZ server itself could even connect to the database in question.
Since this was a production web front end server, I couldn’t install SQL Management Studio on it to test the connectivity. That’s when I ran across this awesome tip:
- Create an empty text file in Windows Explorer and rename it to X.UDL
- Double click on it and the Data Link Properties dialog will appear.
- Select the Connection tab. Enter the server and credentials.
- Test the connection.
Following are my results from the interal server:
And from the DMZ server:
Which proves that the server itself could not connect to the database and that it wasn’t my application passing an invalid security token.