When connecting to a SQL Server instance in SSMS there are a fair number of options. Most DBAs I know have only ever looked beyond the basics so I thought I would mention a few of the others.

First open a connection window in SSMS: BOL

This is what most people see and we do have a few options right here.

1. Server type: This is, as it says, the list of server types that SSMS can connect to. I have to admit, with rare exceptions the only one I use is Database Engine. But then again I’m a fairly plain DBA with very little BI (SSIS only) and no Reporting.
   1. Database Engine
   2. Analysis Services
   3. Reporting Services
   4. SQL Server Compact
   5. Integration Services
2. Server name: Again, very descriptive. This is the name of the server to be connected to.
3. Authentication: This area is dependent on the Server type. If for instance you are connecting to a Database Engine then the options are Windows and SQL Server Authentication. If on the other hand you are connecting to a Reporting server then your options are Windows, Basic and Forms Authentication. Etc.

Having gone through the basic options hit the “Options>>” button.

The page is now not only larger but we have two additional tabs. Connection Properties and Additional Connection Properties.

The Connection properties tab is dependent on what “Server Type” you are connecting to. I’m only going to go over the Database Engine properties here. If you use other connection types I highly recommend reviewing what options you have.

Database Engine: BOL

1. Connect to database: This allows you to change the database you initially connect to. This overrides the default database.

   This is helpful if your default database has been dropped preventing you from easily connecting to the server or when using a contained database and a loginless user.

2. Network protocol: TCP/IP, Named Pipes etc. Allows you to be specific about what network protocol you use to connect to the server. This not only overrides the order set by SSCM (SQL Server Connection Manager) but picks one specific network protocol and if it doesn’t work the connection fails.

   I’ve used this a number of times when testing a specific protocol so that I can actually see the failure rather than have the connection fail over to another protocol.

3. Network packet size: Change the size of the network packets sent.

   I’ve never used this but I imagine it has it’s uses also.

4. Connection time-out: Change the number of seconds before a connection timeout. The default is 15 seconds.

   This is rather helpful if some network latency is making it difficult to connect.

5. Execution time-out: Change the number of seconds before an execution timeout. The default is 0 or never.

   Again I’ve never used this but I could see how it would be helpful if you are going to walk away from a long running query and want it to time out if it goes to long.

6. Encrypt connection: Forces the connection to be encrypted.

   Useful if you need the connection to be encrypted (duh).

7. Use custom color: Pick a background color for the status bar.

   I typically set this in my registered servers so I don’t mess with it here.

Additional Connection Parameters: BOL

This allows you to add additional connection parameters to the connection string. Personally I’ve never seen a use for this myself although Balmukund Lakhani did.

These connection options are not a constant need but something that DBAs should be aware of for when they are needed!

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, SSMS Tagged: connection problems, microsoft sql server, SSMS, TCP/IP