Friday, May 17, 2013

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.



8 comments:

  1. Indeed, the slowness of the new Management Studio is ridiculous. I can't beleive that this made it through QA.

    ReplyDelete
  2. Did you check that SQL Browser service is running? That service returns connection info to clients. Forcing 1433 on single server can work only if you are running only single SQL Server instance on that one computer.

    ReplyDelete
  3. Ah. Perhaps the SQL Browser service is supposed to handle this dynamic situation properly?

    ReplyDelete
  4. SQL Browser solves exactly that issue. But it is not enabled by default on Express. More here:

    http://msdn.microsoft.com/en-us/library/hh510203.aspx

    ReplyDelete
  5. Yep, SQL Browser should be set to auto-start. IMO, the reason it isn't by default is reducing attack surface, the same as allowing only local connections and Windows authentication at first. Also don't forget to check Windows firewall for both *\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQL\binn\sqlservr.exe and *\Microsoft SQL Server\NNN\Shared\sqlbrowser.exe are allowed programs there.
    You might also want to consider running SQL Server service under LocalSystem account to reduce possibility of SPN (setspn -L) record not created/deleted on service start.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. I had to install SQL Express 2008 R2 as a prereq to an application. Struggled with local connection to the database until I discovered TCP/IP enabled and named pipes needed to be enabled. I was wondering if there could be something during install that would allow you to enable these.

    ReplyDelete
  8. basically you have to understand that SQL Express is FREE and is configured as a single-system-workstation database out of the box on purpose. When you need TCP/IP you configure it. They are not being mean, just thinking about reducing the surface area for attackers. It is good technical sense that they do it this way. These manual steps are part of a way of protecting systems from abuse.

    ReplyDelete