A very frustrating issue with Microsoft SQL Express

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
• TCP/IP

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.

Last but not least, is not the workaround, but the actual fix:

• 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).  

Note that in the year 2013, Microsoft still can't decide on one SQL tool to rule them all, so you've still got SQLSCM, SSMS, SSDT,  Visual Studio, and more ways to "look at your database", and no sign of one integrated tool to rule them all.    As a free product, SQL Express is amazing, and Microsoft has done great things with it, SQL Express 2012 is particularly a fantastic product.  But it has some warts, and this is one of them.   Another is that the SSMS (management studio) that comes with all SQL 2012 editions is based on the new and disgustingly slow and bloated version of visual studio shell.  I am going to stick with the old SQL 2008 R2 management studio until someone at microsoft visual studio team gets their head out of the sand.  A slow and bloated IDE shell is my favorite thing to use all day. Said no one.  Ever.