Many times we need to test the connectivity & authentication between a machine and a database server. A very fast and easy tool to do this is the ODBC Data Source Administrator. You can open it from the Control Panel, Administrative Tools.
Open the ODBC Data Source Administrator and click ADD
Select “SQL Server” and click Finish
At the Name field enter test or anything you want, its just a label
At the Server field add the name or IP of the SQL server you want to test
Click Next
Now we can choose Windows authentication or SQL authentication. If we choose SQL authentication we need to enter Login ID (username) and password.
Click next
If the SQL server is available and the authentication is successful then we will proceed to the next step that we can select a specific database.
If the connectivity with the database server is unsuccessful we will see the message ” Connection failed… SQL Server does not exists or access denied”. This may mean that the TCP port is disabled or a firewall is preventing the connection, generally it is a connectivity error.
If the authentication is unsuccessful then we will see the message “Connection failed… Login failed for user ‘username’. This means that the user doesn’t have login access to the SQL Server.
Finally we can press “Test Data Source”, it the Test completes successfully then there is no connectivity or authentication issue
Pantelis Apostolidis is a Cloud Solutions Architect at Microsoft and an ex Microsoft Azure MVP. For the last 15 years, Pantelis has been involved to major cloud projects in Greece and abroad, helping companies to adopt and deploy cloud technologies, driving business value. He is entitled to a lot of Microsoft Expert Certifications, demonstrating his proven experience in delivering high quality solutions. He is an author, blogger and he is acting as a spokesperson for conferences, workshops and webinars. He is also an active member of several communities as a moderator in azureheads.gr and autoexec.gr. Follow him on Twitter @papostolidis.
