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).
Indeed, the slowness of the new Management Studio is ridiculous. I can't beleive that this made it through QA.ReplyDelete
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
Ah. Perhaps the SQL Browser service is supposed to handle this dynamic situation properly?ReplyDelete
SQL Browser solves exactly that issue. But it is not enabled by default on Express. More here:ReplyDelete
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.ReplyDelete
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.
This comment has been removed by a blog administrator.ReplyDelete
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
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