Resolving an IP address from a SQL Server Instance Name

[Guest]

I have a code snippet that searches for available SQL Server Instances on the
network and returns the instance Name(s). (See Bill Vaughn's comments in
"Finding Instances of SQL Server for DropDownList" below - it's a great demo
of new .NET 2.0 features).

I have found that in some cases the connection will fail if I assign the
value of Name to DataSource in the connection string but will succeed if I
use the IP address of the named instance instead.


(Actually, when I use the Name in the connection string an error is
returned: "An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)". This is odd because the connection opens
correctly when I use the IP address instead of the instance Name.)

To work around this (until I figure out the above problem), I was hoping
that there was a way to resolve the IP address from a SQL Server instance
Name.

 

[Guest]

Strange but not new.

The instance names are simple? I mean ServerName instead Server Name?
If so try putting the names in ‘.
Second try this connect with the explorer (to a share) to that machine. And
try then to connect.
If it will not work I have other ideas, but these are the most common
problems.

 

[Guest]

The instance names are in fact simple. I tried surrounding in single quotes
but again without success. Also couldn't get it to work after connecting to a
share on the machine. Any other clues (or ways to resolve the IP address from
the instance name)?

Thanks

 

[Guest]

One step further: check if on the offending servers the named pipes protocol
is enabled…
Here is how:
http://kb.discountasp.net/article.aspx?id=10381
http://dnnblog.venexus.com/DNN+40+And+SQL+Server+2005+Error+Using+Named+Pipes.aspx

You could also add a line in the a line in the host file, not a nice
solutions…
My magic hat is not empty, yet, check is the named pipes protocol is enabled
and we will see then…

 

[Guest]

In the SQL Server Network Utility, Named Pipes (ssnmpn70.dll) is enabled.

 

[Kevin Yu [MSFT]]

Hi Michael,

Can you try to disable Named Pipes and try to connect using TCP/IP?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Since in this manner we will not going fast I will try to spill the bucket of
ideas.
Please take time to read it all this post, which will be rather big.
And please let me know if it worked.

· First

From msdn blog
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)
Troubleshoot:
1) Make sure your sql service is running, use either "net start" or "sc
query <InstanceName>" or run services.msc, check status of the server; If
server start fail, go to ERRORLOG to see what happened there, fix the problem
and restart server.
2) You might explicitly use "np:"prefix which ask for connect through named
pipe. However, client can not connect to server through the pipe name that
specified.Double check the server is started and listening on named pipe if
you enabled Named Pipe. One way is that see the ERRORLOG of the server,
search follow keywords:
Server named pipe provider is ready to accept connection on [
\\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]
Notice that "sql\query" is the default pipe name, so you need to know server
is listening on which pipe name. eg: if you specify server pipe name is
"sql\query1", then you would see in the errorlog that server listening on [
\\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click
client Named Pipe properties, see whether the pipe name is same with the one
server listening on.
3) You might specify named pipe protocol in connection string, but did not
enable named pipe on the server, check ERRORLOG.
4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when
only shared memory was enabled, you can change to <machinename> to resolve
this.
5) You might explictly specify "lpc:" prefix in your connection string, but
shared memory was not enabled. To resolve this, either remove the prefix as
long as named pipe or tcp was enabled or enable shared memory.

· Second
Add the ip and computer to the host file located in
C:\WINDOWS\system32\drivers\etc.

· Third
To get the ip from computer name, import the System.Net namespace there from
Dns namespace you have the method GetHodtByName. That method resolves host
names in to IPs. You have of usage samples in the help.