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


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.


image


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.
image

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
image
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.
image

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.

Advertisements

4 thoughts on “#0215-SQL Server-Testing your connection strings using SSMS

  1. Pingback: #0366 – SQL Server – SSMS – Simulating a TCP/IP connection on the same machine as the server | SQLTwins by Nakul Vachhrajani

  2. Pingback: #0366 – SQL Server – SSMS – Simulating a TCP/IP connection on the same machine as the server - SQL Server - SQL Server - Toad World

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s