Recently I had some customers complaining that they could not connect with our Delphi-powered database backed application, to their database. They were new customers and (although I did not know it) the source of their trouble is that SQL Express (like the full SQL Server product) allows you to configure incoming connection types, those being:
- Shared Memory
- Named Pipes
The first type (Shared Memory) is fine for local connections only when the SQL server is on the same machine as the client application. Across a Windows domain, named pipes and TCP/IP are commonly used. The problem is that SQL Express not only ships with TCP/IP and Named Pipes disabled, a condition that all of us who use it are so accustomed to that it is second nature for us to enable them, but also, TCP/IP is misconfigured out of the box, for some versions of SQL Express. Instead of having its "static TCP port" set to 1433 and "dynamic TCP port" option set to blank (a proper default), they are set with "static TCP port" set to blank, and "dynamic TCP port" set to zero.
Now here's where that gets really fun:
- On some client computers, using an SQL connection string that does not specify named pipes explicitly, Named pipes will be chosen first, and everything will work.
- On some client computers on the same local area network and domain, the same SQL connection string that does not explicitly specify named pipes, TCP/IP will be chosen first, and will fail.
Several workarounds are possible:
- Hack the connection string so it forces named pipes. This must be made configurable in your Delphi application though or you'll be stuck where I was stuck at the beginning of this blog post, with some of your clients (who are unable to use Named Pipes) also unable to use your application. If this is a "set it and forget it hack" done in the background with no UI to select this hack on or off, this will solve one problem while creating another one, so you're just sweeping the issue "under a carpet" and making it harder for someone else in the future.
- Make your connection string configurable. This is flexible, but it makes configuring your application harder.
- Disable the misconfigured TCP/IP option completely on the server, this will make the negotiation (use named pipes? use tcp/ip? Let's check what's available and try one) go faster, but for many users Named Pipes are significantly SLOWER than working with TCP/IP.
- Fix the TCP/IP configuration on the server, using SQL Server Configuration Manager. Make sure that your SQL Server static port is set to 1433, and that your dynamic port option is set to blank (not configured).