Sql-server – TCP/IP connection to SQL Express works on HALF of the clients in a WORKGROUP

sql serversql-server-2008-r2tcpip

So far I have learned the following facts about Microsoft SQL Server Express, including version 2012, and earlier versions like 2008R2, and 2008, and for the most part all the way back to SQL Server 2005. I am configuring an SQL SERVER on a "workgroup server" machine, that is to say, it's a dedicated server box, running Windows Server OS, but it's using a WORKGROUP, and it's only running Microsoft SQL Express.

Note the following background facts:

  • Configuring an SQL Express instance comes out of the box without TCP/IP configured.

  • Turning on the TCP/IP option (changing the SQL Server Configurator's protocols list item for TCP/IP from No to Yes) is not enough, sometimes you have to also choose to either reconfigure the Static and Dynamic ports options. For example, for a single instance, you could set the static port to 1433, and the dynamic port to blank (removing the 0 if present). This is done repeatedly, and on some Windows machines that have IPV4 and IPV6, and one physical, and several virtual ethernet adaptors, this might be 4, 8 or even 16 places where you have to set the static and dynamic port.

    • Alternatively you could configure the SQL Browser, and enable it, and this often helps resolve complex TCP/IP connectivity issues, but often it does NOT.

    • On a domain, I rarely have weird issues, so this question is not for Domain based deployments of SQL server, but rather for workgroup deployments. No I did not choose to run my office on a workgroup, but many of my customers/clients DO choose this option, and I am supposed to support SQL Express on workgroups.

All of the above is background and is given to make the next part that is my actual question clear:

  • Many WORKGROUP client systems in a small office network that has no domain, seem to have strange issues connecting via TCP/IP, or even just auto-negotiating a connection to an SQL Server database, that I cannot replicate in any of my own office or home networks, where SQL connections will not function, even when the SQL Browser is running, and SQL static and dynamic port options have been configured, firewalls have been turned off, and ping works just fine, yet TCP/IP connectivity using the SQL client that ships inside Windows 7 MDAC components, is NOT POSSIBLE. Note that it's not as simple as installing SQL Native Client v10 nor the SQL native Client V11, either. The only workaround for these clients is to disable TCP/IP using CLICONFG.EXE plus to disable auto-negotiation in the SQL database connection string by prefixing the server name with "NP:". Something about this client's workgroup LAN is fundamentally incompatible even with a static single SQL Server instance listening on TCP port 1433, and no SQL Browser or dynamic port.

  • These same clients often have 12 machines, and 6 out of the 12 machines can NOT connect via automatic (not forcing named pipes) connection strings, and can only connect when the connection type is forced to named pipes.

A typical connection string that forces named pipes might either put "NP:" in front of the
host and instance, like this: "NP:HOSTNAME\SQLEXPRESS", plus we would go to that client's CLICONFG.EXE and disable TCP/IP. Both steps appear necessary in some workgroup situations.

Again the question is, WHY do half of the Windows 7 client systems (6 out of 12 on average) at some of my client's sites have no ability to do SQL communications via TCP/IP over their workgroup local area networks.

Some qualifications and general facts:

  • Many of the systems that fail to connect are 32 bit windows 7 clients, that have no additional SQL Client connectivity components installed than whatever MDAC functionality is built into windows.

  • The applications which I am trying to connect with, are using ADO and work fine on the underlying MDAC components that ship in Windows 7.

  • Installing a "native client" component for the particular SQL 2012 or 2008R2 Server (SQL Native Client 11, SQL Native Client 10) has NO effect on these issues, does not fix them, or make anything any better, or any worse.

  • I suspect some underlying issues involving IPV4, IPV6, DNS, and lack of "trust issues" that then cause basic parts of TCP/IP + DNS connectivity to be "semi broken, but I have not found any accurate SQL Server documentation on this subject.

  • These customer sites have NO windows Active Directory domain, and no Windows-based DHCP services, and are typically getting their dynamic IP addresses via a DHCP server built into a low end commercial or residential grade WiFi+DSL+Router+NAT box, such as a Linksys WRT64G. I am giving a specific model so I can be clear that these users have NOTHING that you would recognize as an "IT style" local area network. These people are running small companies, and have no IT budget, no IT staff, and no Windows Network or Domain infrastructure. They have one SQL Server instance running on a Windows Server powered computer, purchased from a software vendor (me), to run a software application, and are not willing to switch to using a domain.

All of the above being true, I am mystified why SQL over TCP with IPV6 and IPV4 enabled (stock out of box Windows 7 32 bit network configuration) would be so difficult to get working. The only partial answer I have so far is that in SOME cases, it appears differences on the workgroup name could be responsible for some of the trouble. (Machine A has no problems, and is running Windows 7, has workgroup name set to WORKGROUP, Machine B has problems, and is running on Windows 7 and has workgroup name set to MYCOMPANY.)

Note that a related confusing issue some people might get stuck on is on the choice between SQL Authentication and Windows (integrated) authentication. We always use SQL authentication when configuring connections in a workgroup, and this appears to be completely necessary due to the lack of a domain, and is not what I am asking about. i am asking ONLY about TCP/IP versus named pipes, and the reason why I can't configure TCP/IP connections that work over a workgroup, and yet, I CAN configure them easily across most WANs, VPNs, and even across the internet.

Why is a WINDOWS WORKGROUP LAN a special case for TCP/IP connectivity to SQL Server Express?

Best Answer

I suspect some underlying issues involving IPV4, IPV6, DNS, and lack of "trust issues" that then cause basic parts of TCP/IP + DNS connectivity to be "semi broken, but I have not found any accurate SQL Server documentation on this subject.

  1. There is no "trust" that you speak of between DNS and TCP. There is a relationship between DNS and TCP in regards to DNS facilitating name based connectivity to TCP hosts but there certainly isn't any "trust". In order for one host to connect to another host by name that name has to be resolveable. That's called name resolution. Very often it's achieved by way of DNS but in the workgroup scenario you're describing there is no central DNS server to handle name resolution for the clients.

  2. In a scenario like you describe, where no common DNS server exists to provide name resolution, clients will resort to LLMNR or Broadcasting, depending on the client. Neither method guarantees that every host will be able to resolve the name of every other host.

  3. It's not magic and it's not a mystery. If you want to regularly and reliably resolve host names to ip addresses then you need to implement an internal DNS server and configure the clients accordingly. For workgroup clients that means having to manually configure their DNS suffix to match the DNS zone on the DNS server that you create for the workgroup and then configuring all of the clients to use that DNS server for DNS.

Related Topic