A basic but crucial part of a successful catch involves putting your bait near active fish. It sounds really simple, but often we as fisherman spend way too much time zipping around the lake trying to find fish that are in a mood to feed. When muskie fishing, both great excitement and disappointed frustration are felt when a large fish lazily follows your bait to the boat but turns around and swims away instead of trying to eat. If you want to make that connection with a large fish, it needs to be ready.
The same is true of SQL Server. SQL Server is able to use different network protocols to “listen” for incoming connection requests. The three main protocol types are Shared Memory, TCP/IP, and Named Pipes. The most widely used type is TCP/IP because it provides support for computers with diverse hardware architectures and various operating systems. Here are definitions of each type, parafrased from Microsoft Technet:
– Shared Memory: The simplest to use, shared memory has no configurable settings. This can only be used when connecting to a SQL Server instance on the local computer. This will often be used for troubleshooting when there are problems with other protocols.
– TCP/IP: This is the most common protocol used over the Internet. TCP/IP includes standards for routing network traffic and offers advanced security features. Configuration can be complicated, but most networked computers are already correctly configured.
– Named Pipes: This protocol was developed for local area networks (LAN). A part of memory is used to pass information from one process to another. This is comparable performantly to TCP/IP in a fast LAN, but can function poorly in environments with a slow network. Generally, TCP/IP is preferred in a slow LAN, WAN (wide area network), or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.
I recently ran into an issue where I was unable to connect to a remote instance of SQL Server that I had setup on a sandbox machine within our local network.
I could connect from SQL Server Management Studio (SSMS) from the local sandbox machine, so I knew I needed to check a few settings. My first step was to check that the instance was correctly configured to allow remote connections. I went into the properties of the instance and found that it was set up to allow remote connections:
Next I checked the SQL Server Configuration Manager to see what network protocols were enabled. I found that that while Shared Memory was enabled, both Named Pipes and TCP/IP were set to disabled.
I know that in our network we use TCP/IP for our connections to SQL Server, so I enabled this option, but I was still unable to successfully connect from the remote computer. My next step was to check the SQL Server Browser. This can be found in the SQL Server Configuration manager under SQL Server Services.
This was also stopped, so I started it, and was then able to connect via SSMS from the remote computer.
Other items to consider checking include Windows Firewall and making sure the SQL Server service is running.
So next time you can’t connect to a remote instance of SQL Server, check these two settings. And next time a fish looks at your bait indifferently before turning around and swimming away, practice persistance – it’s got to eat sooner or later!