The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Have you ever checked to see what your options are when connecting to a server with SSMS? Give the options box a click and see what pops up. Today is day 13 – let’s go through some more options today, specifically those associated with connections.
SSMS Connection Options:
Below is the first tab you’ll see when you click options on the connection dialog. Similar to the way the GUI is actually passing T-SQL behind the scenes it’s good to remember that most of the options you have here are simply modifying the connection string being used by SSMS to connect to your server. In fact, the other tab – “additional connection parameters” is just a way to specify those connection string options that the first tab doesn’t give you in the form of a GUI.
Connect to database:
SQL Logins have a default database associated with them. This is the way to over-ride that default and specify another database on the server that the connection should be connected to be default. Obviously this will not override security defined for the login. Setting a database that the login does not have rights to will generate an error.
Network packet size:
There could be a whole series of posts on network packets and why you might want to change from the default of 4096. I’ll just quote the MSDN article related to this option. “This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician“.
Ensuring that SSMS is using an encrypted connection is as easy as checking the ‘Encrypt Connection’ box on the connection properties tab. The caveat however is that the database server you are connecting to is properly set up for encrypted connections i.e, has all the certificates and providers in place; if it isn’t you see something like this.
Setting up encrypted connections is out of the scope of this SSMS series but you can get a complete setup walkthrough on MSDN.
Custom Connection Colors:
If you specify a color for a connection than the status bar of any query windows that are established against that connection will utilitze your custom color instead of the SSMS default. This is a great visual aid that can assist with identifying classifications of servers you are connected to. At least, that is what I use custom colors for. I use the following for example: red connections are production, yellow are certification and UAT, green are dev and test. Nothing is stopping you from classifying your colors across business functions or other means of separation however.
Hint: If you are using connection colors as a visual identifier… placing the status bar at the top of the query window instead of the default at the bottom is a great way to not only change the color but make the color more prominent and visible near where your query text is. You can move the status bar to the top by editing the option in Tools->Options.
Note that SSMS will remember the color chosen for each connection and that color will be used in future sessions as well. One thing worth mentioning is that once you begin using custom colors – SSMS will always use the last custom color specified for all new connections that do not already have a custom color specified.
Additional Connection Parameters:
As stated above – modifying connection options is really just changing the underlying connection string that SSMS is using. Any ODBC connection string option you’d like to use can be specified manually in this box and SSMS will implement it. In fact, if you specify something here that overrides another setting in the other tab – the manual option wins. See MSDN for the complete list of ODBC connection string options. If you are not familiar with connection strings you should google your way to knowledge on that topic.
Why would you want to modify your connection string in SSMS this way? Primarily I have seen it used to mimic the connection string settings of an application (or proposed application) to test and develop in like for like conditions.
There has been a somewhat controversial past (read comments in that link) with regards to Microsoft’s preferential provider and SQL Server. Microsoft’s commitment to Azure and the cloud cemented ODBC as the clear winner so it’s not surprising that ODBC via the .Net SqlClient provider is the standard for SSMS connections. We’ll talk a little bit more about ODBC vs OLEDB and the nuances that can arise between the two etc. when we cover SQLCMD on day 24 but for the time being keep in mind that SSMS is using ODBC even in SQLCMD mode.