#0366 – SQL Server – SSMS – Simulating a TCP/IP connection on the same machine as the server


Most development environments that I have worked in have a developer edition of Microsoft SQL Server on the local machine of the developer. Hence, both the client (SSMS) and the database server are often on the same machine.

What this means is that the default connection mechanism used for all connections will be “Shared Memory”. However, when working on features like auditing, the ability to simulate a TCP/IP connection becomes important. Today, I document a mechanism that you can use to initiate a TCP/IP connection even when both server and client (SSMS) are the on the same machine, provided the server has been configured to allow TCP/IP connections in the Configuration Manager.

SQLTwins, Post #0366 - Ensuring that TCP/IP connections to the server are enabled via the SQL Server Configuration Manager

SQL Server Configuration Manager – ensuring that TCP/IP connections to the server are enabled

When initiating a new connection using the SQL Server Management Studio, simply click on the “Options” button in the “Connect to Database Engine” window, and navigate to the “Connection properties” tab.

SQLTwins, Post #0366 - SQL Server Management Studio (SSMS) - The "Connect to Database Engine" window

SQL Server Management Studio (SSMS) – The “Connect to Database Engine” window

SQLTwins, Post #0366 - SQL Server Management Studio (SSMS) - Using the Connection Properties tab to change the Network protocol when connecting to a SQL Server instance

SQL Server Management Studio (SSMS) – Using the Connection Properties tab to change the Network protocol

In the “Network” group, locate the “Network Protocol” drop-down. Change the value from “<default>” to “TCP/IP“.

Finally, verify the credentials in the “Login” tab and click “Connect“. That’s it!

Use the CONNECTIONPROPERTY() function described in my previous post to confirm that you are indeed connected via TCP/IP.

Further Reading

  • Testing your connection strings using SSMS [Link]
  • Using CONNECTIONPROPERTY to determine connection properties [Link]

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

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