Testing your connection to a SQL Server instance can be difficult for non-technical users, but there is an easy way for you to create a tool for them to test the connection.
- Create a blank text file using Notepad and save it with a meaningful file name with a “.udl” extension. When saving the file make sure to click on the drop down in Notepad and choose “All Files” instead of “.txt” under “Save As Type:” option.
- Double-click the saved file and the main UDL configuration window will open. On the Provider tab and choose “Microsoft OLE DB Provider for SQL Server.”
- Click “Next”, or click on the “Connection” tab to enter the data required to connect to your instance you want to test. Under the “1. Select or enter a server name:” dropdown, just type the name of your target server.Under “2. Enter information to log in to the server:”, choose the login option required to access your server. I usually select the “Use Windows NT integrated security” option, which makes the login test easier. Make sure you have the correct database selected under the “3. Select the database on the server:”.
- Now you can click on the “Test Connection” button to verify you have the correct options selected.
- Save the file by clicking the “Ok” button.
Now you can test the connection to the database very quickly, without SSMS or other technical tools. This makes it very easy for a non-technical user to verify the database is available without calling you first. They can now double-click the saved UDL file and click the “Test Connection” button to verify the database is operational.