How to Test MS SQL Connectivity

This is a fairly complicated subject, as SQL is a fairly complicated application.

Maybe you need to test connectivity to your SQL server as part of preparations for a failover.

Maybe you’re having some problems with getting a client application to connect and you want to make sure your SQL server is responding.

Maybe it seems to work locally but not remotely, and you want to gather more information on where you can connect from and where you cannot.

There’s no really simple methodology for testing this, because MS SQL can be configured in many different ways – using Windows authentication, using SA authentication, using TCP/IP, or Named Pipes, on different ports, even on dynamic ports.

So rather than present a step-by-step approach that will only work for one specific configuration, I’ll point you to some articles that will help you determine which way SQL is configured first – and then how to test it using that information.

This one is about enabling remote connections, but in the process it tells us where to look for the actual port number being used:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

This one helps us figure out whether the server is using TCP/IP or Named Pipes, whether or not Dynamic Ports are bring used, etc:

http://support.microsoft.com/default.aspx/kb/265808

This one helps us use SQLCMD to connect to x instance with y port, giving a number of syntax examples:

http://msdn.microsoft.com/en-us/library/ms188247.aspx

If any of these links go missing, please send me a comment so I can replace them.

Tags: Connectivity, SQL