SQLTwins by Nakul Vachhrajani

Home | About Nakul Vachhrajani | Archives

#0215-SQL Server-Testing your connection strings using SSMS

November 22, 2012 9:00 AM

Earlier, I wrote a post about how adding the Application Name and the Workstation ID in a connection string are recommended best practices because these would help system administrators troubleshoot problems faster by being able to identify which application and which workstation actually initiated a SQL Server connection. In most teams, the connection string is constructed and provided to the development team by the administrators/DBAs and therefore, as soon as the post was up, I received a couple of questions from these DBAs, one of which included:

Is there a way to test connection strings before an application is built to consume the connection string?

This is a very interesting question. A connection string has many different parameters which can influence the overall behaviour of the application and therefore, testing the connection strings before actually using them makes complete sense. In today’s post, I explore the answer to this question.

Using the “Connect To…” window of SSMS to test connection strings

If you work frequently with the SQL Server Management Studio (Tutorial on “Getting Started with SSMS”), you would notice that one of the first windows that the user encounters is the “Connect To” window. By default, the window looks similar to the screenshot shown below and allows the user to choose the SQL Server instance to connect to, the mode of the connection (windows authentication/SQL Server authentication) and the user credentials.

This window collects what is the “bare-minimum” information to establish a connection. Clicking on the “Options” button exposes a window that enables the user to tweak the connection parameters in greater detail. As you can see, the window consists of three tabs:

Login
Connection Properties
Additional Connection Parameters

Login Tab:
This is essentially the same as the compact version of the window.

Connection Properties Tab:
Allows a user to:

Define an initial catalog/database
Modify the network protocol to be used and choose the network packet size
Modify the timeout parameters

Additional Connection Parameters:
This allows a user to free-form type connection string options that are not available on the UI (for example, the Application Name and the Workstation Id)
NOTE: Please note that any information entered here would override the information specified in the UI on the other tabs of the “Connect To…” window.

When preparing the demonstrations shown in my earlier post on connection strings, I used these options to test out the changes and tweaks to the connection strings. In a similar way, administrators/DBAs can now use the SSMS to test out their connection strings before they hand them over to the application development teams.

I trust you found this feature of the SSMS useful – I use it on a regular basis, and would like to know if you have ever used it. Do drop in a line as you go.

Until we meet next time,

Be courteous. Drive responsibly.