Home Dashboard Directory Help
Search

Unable to connect on localhost using FQDN machine name by Holm_Capa


Status: 

Closed
 as By Design Help for as By Design


2
0
Sign in
to vote
Type: Bug
ID: 589901
Opened: 8/27/2010 4:31:41 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

Using FQDN I am unable to connect to databases located on local machine.

If I try and connect using only machine name there is no problem at all.

FQDN example
DEVPC.domain.com

Details
Sign in to post a comment.
Posted by harsha432 on 2/13/2012 at 8:46 AM
hi
i am using sql server 2005 ,i got errors in db.mirrroing when i starts the mirroring that "speify the FQDN for each server and start mirroring again " , can you please expalin what is FQDN and how to configure it
---hari krishna.B
Posted by Microsoft on 11/5/2010 at 1:11 PM
Hi,

I want to apologize for the typo in the previous posting: I've meant to say "Thanks a lot for the update and I am sorry about the delayed answer".

Thanks and sorry once again,
Jivko
Posted by Microsoft on 11/3/2010 at 11:13 AM
Hi,

Thanks a lot for the update and the delayed answer. Yes, that makes sense and matches the behavior which I have described. BTW - our SNI component is common for SNAC, SQL Server and .NET SqlClient, so both native and managed clients should behave the same.

If you have any further questions, please don't hesitate to contact me again.

Thanks,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Posted by Holm_Capa on 9/6/2010 at 1:26 AM
Let me give a brief story of this issue.

Not long ago we had to change the way we connect to sql servers. Our customers demanded crossdomain support which meant that we had to start using FQDN names for shared folders and also for connectionstrings.

To make this change we made the connection code convert sql logon infomation to include FQDN. However lately our test and development departments had reported cases where our application was unable to connect to the database but if they used regedit to strip fqdn they could connect.

Investigating further I found that this happened when using NamedInstance and connecting locally using FQDN. Remote connection using machine or FQDN have always worked. It was first when converting to FQDN we noticed the problem.

Usually when we have connection issues we use SQL Server Management Studio because if it can connect then .net can connect as well. In code we are using System.Data.SqlClient.SqlConnection to make the connection to the server.

So thats the story.


After reading your reply I went and enabled SQL Browser Service (which is disabled by default) and now I can connect locally using FQDN on a names instance.

Does this make sense?
I dont know too much about what actually happens when connecting to an sql server via .net but as said we have always been able to connect remotely (even with FQDN) even when that service is not running..

Posted by Microsoft on 9/3/2010 at 2:47 PM
Hi,

Thanks a lot for the details and the screenshots. I think this makes the picture clear:
- when you use wstst05\sqlexpress as a server name, the client code separates the machine name from the instance name and the wstst05 is compared against the netbios name. I see no problem for them to match and the connection is considered local. From there, we retrieve the needed information *WITHOUT* contacting SQL Browser and connect to the SQL instance via Shared Memory without any problem.
- when you use wstst05.capatest.local\sqlexpress, the client code fails the comparison of the name (wstst05.capatest.local) to the netbios name (wstst05) and considers the connection "remote". This is by design and we will definitely consider improving this in the future. Anyway, due to considering the connection remote and the fact that it is a named instance, client decides that it needs to use SQLBrowser for name resolution. It attempts to contact SQL Browser on wstst05.capatest.local (UDP port 1434) and apparently that part fails. Hence the error you get.

Please verify the following:
- is SQL Browser service enabled and started?
- do you have any SQL Browser-related issues reported in the Event Logs?
- are you able to connect to the instance from a remote machine using wstst05.capatest.local\sqlexpress? If you are, then SQLBrowser is probably functional and sending correct name resolution information. Then the problem will be limited to the inability of the local client to connect the local UDP::1434 listening service, which would be quite puzzling.

Does this explanation make sense? Do you have any questions? Please let me know what the results from the checks above are.

Thanks,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Posted by Holm_Capa on 9/2/2010 at 1:57 AM
Oh and both NamedPipes and TCP is enabled on both default and holm instance
Posted by Holm_Capa on 9/2/2010 at 1:14 AM
Nope. Rob is not my alter ego :)

Ive tried to attach a screenshot to this issue.

Turns out I may have missed some info. It seems this issue happens when trying to connect to an instance.

In the screenshot you can se I try to connect to wstst05.capatest.local\sqlexpress and that Im presented with a nice error dialog.
As proof Ive added some extra into on the screenshot.
As you can see I have no problem connection to wstst05\sqlexpress
And you can also see that my domain is called CAPATEST.LOCAL and that my machinename is WSTST05

Hope this helps in figuring out if there is an issue.

Ive even tried on another machine where I can connect to devhfh\holm with no problem but if I try devhfh.capasystems.com\holm I get the same error and in the screenshot.

adding the lpc: has no effect.
Posted by Microsoft on 9/1/2010 at 12:24 PM
Hi,
As part of the connection process, the current SQL Server client drivers verify if you are attempting to connect locally. In the current implementation, we check for ".", "(local)", "localhost" or for a match to the local netbios-style machine name. If the server name provided is different than any of these (and this is the case with the FQDN), then the client does not consider the connection local and does not try to use the Shared Memory network provider.

To prove the concept, you can try to force the shared memory by passing serve name like this:
lpc:DEVPC.domain.com

This would result in an error like the following:

============================================================================
HResult 0x57, Level 16, State 1
SQL Server Network Interfaces: Cannot open a Shared Memory connection to a remote SQL Server instance [87].
Sqlcmd: Error: Microsoft SQL Server Native Client 11.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. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
============================================================================

If you do not enforce Shared Memory, the client will treat the connection as remote and will try to connect with Tcp and Named Pipes. For most of the SKUs (e.g. SQL Express), these protocols are disabled. However, if you have them enabled, the connection should succeed. If it still fails, please provide the exact error you get.

Regarding your last comment with the named instance: named instance connectivity requires name resolution and we have a complex code which either contact SQL Browser or attempts to detect if this is a local connection to retrieve the instance connectivity information. The process goes throught a different code path and may retrieve the shared memory connection path from registry. That would explain why you are able to connect to the named instance.

Please let me know what you think. Do both Holm76 and Rob Volk aliases belong to you? If you have Tcp enabled on the local instance and still unable to connect, please post the exact connection failure error. Also, please try with sqlcmd/osql.

Thanks,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Posted by Rob Volk1 on 9/1/2010 at 11:22 AM
Hate to say it, but it works on my machine. I'm using 2008 R2 under a named instance, don't know if that matters.
Posted by Holm_Capa on 9/1/2010 at 7:06 AM
Just to make it clear. Im talking about SQL Server 2008. This works with SQL Server 2005.
Posted by Holm_Capa on 9/1/2010 at 7:04 AM
Yes. It is possible to connect to the server from all other machines on the network using FQDN except when trying to connect on the same machine the sql server is installed in.

If you are on a domain you can easily test it. Just install a local sql server and try to connect to it using your machines full qualified machine name and then try and connect with just machine name. And then try and connect to that server from another machine.

As you will notice you cannot connect to a local sql server using FQDN.
Posted by Rob Volk1 on 8/27/2010 at 6:11 AM
Have you checked that the TCP/IP network library is enabled on your local machine?
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
SQLServerConnectionIssue2.png 9/2/2010 150 KB
SQLServerConnectionIssue.png 9/2/2010 158 KB
SQLServerConnectionIssue.png 9/2/2010 158 KB