"Error Locating Server/Instance Specified [xFFFFFFFF]". SQLSRV32 to the rescue.
When connecting to SQL Server and when the connection fails the reason for the error may sometimes be a bit unclear.
For this example I have a SQL Server 2008 instance called Spike2008 running on a machine called SpikeSrv2008 (running on Windows Server 2008).
So the full name of the server is SpikeSrv2008\Spike2008.
If trying to connect to it from a command prompt using sqlcmd it will show the same error for both incorrect server name and incorrect instance name.
Incorrect server name:
C:\Users\maspeng>sqlcmd -S SpikeSrv2009\Spike2008
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
Incorrect instance name:
C:\Users\maspeng>sqlcmd -S SpikeSrv2008\Spike2009
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
As we can see, the errors are identical. However, this is an ideal situation since we know that either the server name or the instance name is wrong.
But if you are not sure which one is incorrect (we are assuming that we know that the server is up and running and can be connected to from other machines).
What you can do to get a bit closer to what is happening is to create an ODBC connection to the server that you cannot connect to.
Click “Start”, select “Run” and type odbcad32.exe or run it directly from C:\Windows\System32\odbcad32.exe
Select “Add” (for a new DSN) then select the Sqlsrv32 driver (“SQL Server”) not the sqlncli or sqlncli10 driver (these are the newer drivers and will give the same error as above)
Then enter anything in the Name textbox and then enter the info for the Server in that textbox.
In the case of the incorrect server name (SpikeSrv2009\Spike2008):
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
In the case of the incorrect instance name (SpikeSrv2008\Spike2009):
SQL Server Error: 67
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
So here you notice that the SQL Server Error is 53 if the server name is wrong and 67 if the instance name is wrong.
This still doesn’t answer why the error occurs, but it should help narrow down what could be causing this.
For example, 67 tells us that the server can be found (i.e. there is no DNS error) but that the instance can’t be connected to.
This could be, again as an example, a blocking in the firewall on the client side. As we know, the SQL Browser service is returning the port number for the particular instance name.
And this particular port could be blocked.
If the error is 53, then it could be that the DNS can’t be resolved (try with IP instead of FQDN) or that there is a general network issue.
So, this is not intended to resolve the issue, but hopefully get you closer to figure out where the error occurs.