I have a server with two instances of SQL Server 2016 SP1 CU7 running, one is the default instance (on a custom port, 9433) and the second is a named instance (on another custom port, 9434).

I've already had minor issues on this setup with the SQL Server Agents (where I had to manually set each SQLServerAgent ServerHost registry key to "hostname,9433" and "hostname,9434" respectively, which I've never had to do before). Now, the outstanding issue is that we use Ola Hallengren's maintenance scripts which by default make use of sqlcmd and the (SRVR) SQL Server Agent token and I saw the scripts were failing on the named instance. Testing it manually, (SRVR) maps to @@SERVERNAME (hostname\instance) so running something like this directly on the server hosting the SQL Server instances showed the issue:

sqlcmd -E -S hostname\instance -d database -Q "SELECT @@SERVERNAME" -b
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.

Same error is seen when trying to set up an ODBC connection manually, again locally on that server. It does work if I use the port, or if I use either the FQDN or IP address without the port, for example these are all fine when executed locally on the database server and return the expected result:

sqlcmd -E -S hostname\instance,9434 -d database -Q "SELECT @@SERVERNAME" -b
sqlcmd -E -S fqdn\instance -d database -Q "SELECT @@SERVERNAME" -b
sqlcmd -E -S ip\instance -d database -Q "SELECT @@SERVERNAME" -b

So I can work round this (a further option is to create a local alias, which also works) I just want to know why it's not working as expected, on its own simply trying to connect to it locally using hostname\instance.

It works in any combination with or without the port when running the command from a remote machine (for example another SQL Server machine running the same level of software, and using the same version of ODBC to make its connection - Microsoft ODBC Driver for SQL Server Version 13.00.4466 - will happily connect to this remote hostname\instance without need for a port number, presumably it's making use of the SQL Browser successfully), it's only a problem locally. I can also log into the named instance locally from SSMS just using server\instance without the need for the port, though. SQL Browser is running, UDP port 1434 is listening and open in the firewall. It's not a clustered instance. Running an nslookup on the hostname resolves to the expected FQDN and IP address.

I just can't work out why this one combination isn't working and only when running locally on the server itself, the worry being that without understanding this there may be other related issues currently unseen. Hopefully it's something simple and overlooked (or changed but valid behaviour), but this sort of thing has worked as expected on SQL2012 and others and I can't see what's been done differently with respect to the setup here.

  • Is the browser service running? – Tara Kizer May 15 '18 at 14:36
  • What protocols are enabled in Configuration Manager > SQL Server Network Configuration > Protocols for [each instance]? – Aaron Bertrand May 15 '18 at 15:15
  • Does it work if you do this: sqlcmd -S tcp:hostname\instance -Q "SELECT @@SERVERNAME"? (you don't need the -E, -d, or -b switches for this test) – Solomon Rutzky May 15 '18 at 17:25
  • @TaraKizer - Yes, SQL Server Browser is running. As I said, you can connect remotely without the port with no issue., Stop the browser and it won't connect remotely either (with the same ODBC error, interestingly) – user150985 May 16 '18 at 14:20
  • @AaronBertrand - For both instances, Shared Memory is disabled and both Named Pipes and TCP/IP are enabled. That's how we tend to set up all our instances. – user150985 May 16 '18 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.