1

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?

Warren P
  • 1,195
  • 8
  • 21
  • 35

1 Answers1

1

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.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • So in my case, how do I explain how PING SERVERNAME works, and yet I can't get a TCP connection to SERVERNAME? I'm guessing LLMNR is the only functional thing here, because I have no windows-hostname-aware-DNS (activedirectory dhcp + dns + whatever-you-call-netbios-style-ms-name-resolution these days now that netbios is dead). I guess at the end of the day a real office network MUST have a reliable DNS service, right? Long story short? – Warren P May 24 '13 at 00:04
  • Two things: 1. I would say that a network should have a relaible internal DNS server if services, such as SQL, are being provided to clients on that network. Simple file and printer sharing between a few clients might be an exception where you wouldn't neccessarily install a DNS server but if you're providing more complex services like SQL then I would want to have an internal DNS server to provide reliable name resolution. - 2. I must have missed it in your question that you're able to ping by name but not connect to SQL by name. I would start with implementing internal DNS and go from there. – joeqwerty May 24 '13 at 00:11
  • It wasn't entirely a DNS issue, as it turns out, it was also an SQL Alias and SQL native client network library issue or set of issues. – Warren P Jul 23 '13 at 13:56
  • Same problem here. @Warren P: Could you post more about your solution!? – dwo Oct 06 '15 at 12:14
  • You'll have to read up about SQL Aliases, and just do a lot of googling on SQL Native Client Library issues. You'll probably find an old version of a client library you can remove, and a new one you can add, or you'll find a different connection string on your client PCs that solves your issue. You can force named pipes, or force TCP/IP using connection strings, and if your application doesn't let you completely edit your connection strings, you can usually set up aliases, and connect via an alias, that has the settings properly configured. Check your IPV4 subnet masks too. – Warren P Oct 06 '15 at 14:09