As part of my normal development activities, I use my trusted developer instance on my local machine. However, one of the main things I realized was that while I could connect to the instance using SQL Server Management Studio (SSMS), I could not connect to my instance via some of my SSIS packages.

I realized shortly afterwards that this was because the instance only has “Shared Memory” enabled as the protocol for connections by default. I turned on TCP/IP as well and was able to complete my work. However, I thought it best to document the steps I took for future reference.

What is my current connection using – Shared Memory or TCP/IP?

Before we go any further, let us investigate the default connection mechanism used by client applications running on the same machine as the SQL Server instance.

To do this, I have opened connections to the SQL Server via both – SSMS and SQLCmd and am then querying the DMV (sys.dm_exec_connections) to investigate the protocol being used for the connection. Session Ids used by each connection have been highlighted in the image below.

Now, because my connections are active, I can take their session Ids and query the DMV – sys.dm_exec_connections which will give me the physical transport protocol that is used by this connection.

If I explicitly try to connect to the instance using TCP/IP, note that I get an error #26 (Error Locating Server/Instance Specified):

Enabling TCP/IP

In order to change the connection, one needs to use the SQL Server Configuration Manager.

In the Configuration Manager, when we navigate to the SQL Server Network Configuration -> Protocols for <SQL Server Instance>, we notice that TCP/IP and Named Pipes are disabled – only the Shared Memory protocol is enabled.

Now, all that needs to be done is to enable TCP/IP from the Protocol properties (right-click -> Enable or simply double-click to open the properties window) and restart the SQL Server service.

Specifying Transport Protocol when connecting to the database

Now that I have reconfigured the SQL Server instance, I can now specify the protocol when connecting to a SQL server:

1. In SSMS, when connecting to a SQL Server, click on “Options”
2. Under “Connection Properties”, choose “TCP/IP” as the connection protocol

When I use the DMV (sys.dm_exec_connections) to check the session, I can see that it is now using TCP/IP and not Shared Memory.

How to disable Shared Memory?

One of the questions that we started with was how to disable “Shared Memory” for connections?

This can be achieved in the same way as we enabled TCP/IP. Simply use the SQL Server Configuration Manager to disable the “Shared Memory” protocol.

I hope you will find this post helpful.

Disclaimer: Please DO NOT try this on your production SQL Server instances.

References:

• DMV: sys.dm_exec_connections [MSDN Link]
• SQL Server Configuration Manager [MSDN Link]

Until we meet next time,

Be courteous. Drive responsibly.