Solved

Management Studio cannot find my SQL but I can connect to the SQL server by IP address or by name

Posted on 2010-11-26
17
948 Views
Last Modified: 2013-11-05
Hi
I'm Install new SQL server 2008 enterprise addition on windows server 2008 enterprise addition. I can connect to SQL server from outside company using Public IP address and I can connect using private IP address from inside also I can connect to SQL server by name from inside . But the problem is the SQL management studio cannot find SQL when I try to search it in instance

I do not know the problem I check the browser in SQL service and its work

Please any Idea

Regards

0
Comment
Question by:nasemabdullaa
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 3

Expert Comment

by:GSGDBA
Comment Utility
HI,

Please check with you windows sys admin.
Whether the SQL Server name hasbeen in sync with DNS.

If you created the server newly. It would take some time for the DNS to get sync.
0
 
LVL 8

Expert Comment

by:WesWilson
Comment Utility
Yes, the system admin would need to associate an external name to that IP in DNS.
0
 
LVL 5

Expert Comment

by:adamsjs
Comment Utility
If the issue is that you do not see the SQL Server when you click the "Browse for more" option in the server name drop-down when connecting in SSMS, then it may be that the instance has been configured to be hidden (not enumerated by the Browser service).  See http://msdn.microsoft.com/en-us/library/ms179327.aspx, which details how to hide an instance.  You would change the Hide Instance option to No if this is the case with your instance.

If possible, you may also want to see if browsing for the instance works from a host on the same subnet/VLAN as the instance host, but not from hosts (such as your workstation, perhaps) which are on different subnets/VLANs from the instance.  If you only have an issue from hosts not on the same subnet as the SQL Server host, then UDP port 1434 traffic may not be forwarded off the SQL Server host's subnet.  See http://msdn.microsoft.com/en-us/library/ms188200.aspx and  http://msdn.microsoft.com/en-us/library/ms181087.aspx for information on the "Browse for server" dialog box and the Browser service.
0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi

Thank you for all reply
Please I use internal DNS I mean my DNS is not registered and I use my ISP DNS as second DNS. Also I do not want to connect from outside using name I will use IP address and its work now. But I need to find the SQL server when I click"" Browse for more"". Please now I can connect to the server from outside using public IP address and that is OK and I can connect from inside using private IP address or name server but the problem that management studio cannot find the instance when I click "" Browse for more"" I need the management studio to find the SQL because  I have program need to find the server in management studio to connect to it

Regards



>>>>If the issue is that you do not see the SQL Server when you click the "Browse for more"
yes this is the issue. Please I check and the instance has been configured to be visible (not hidden)
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
It is still not clear whether your want to find MSSQL using the  private or public IP. The latter is impossible.
0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi
Thank you for your reply

Please I want to see the SQL server in instance (Management studio)(brows for more) from inside I mean private IP address

Regards


0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
If you don't see any MSSQL Server when browsing, check your firewall settings if they allow for incoming udp/1434. Best to switch it off for a test.
0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi

Thank you for your reply

Please I can see the other SQL server I have three SQL server but I can not see this server

Regards
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Expert Comment

by:GSGDBA
Comment Utility
HI,
As you said the you are able to connet to SQL server using IP address.
So it is not firewall problem.

Now i understand your ponit, That you want to see the SQL Server instance name in the SSMS.

Is the system where you are using SSMS to connect is in the same domain or  diffreent domain or it in workgroup?

If it is in workgroup.I would say you have to try connectserver using IP address.



0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi
Thank you for your reply

Please the computers in other domain and I use mixed mode when I install SQL server therfore I connect using IP address but I need to find SQL server in Mangment studio. Plesae can I find it or I need to enter all computer using this SQL server to the same domain

Regards
0
 
LVL 3

Expert Comment

by:GSGDBA
Comment Utility
HI,
Would  you please tell me the requirement of your program. What it wants to interact with the SSMS.
0
 
LVL 3

Expert Comment

by:GSGDBA
Comment Utility
0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi

Please I have Archive program and this program design by VB.net. This program allowed me to connect to SQL server by below way
1- From outside using IP address (if I'm outside company use public IP address and if I'm in out in any branch of our company connect use privet IP address all branch connect together by VPN)

2- From inside I have search bottom to find the SQL server and if I cannot see it I cannot connect ti the server

3- Because the SQL management studio cannot find the instance the program cannot find it) the search bottom in the program work as same the management studio when click "brows for more)

Regards

0
 
LVL 5

Accepted Solution

by:
adamsjs earned 120 total points
Comment Utility
Please I use internal DNS I mean my DNS is not registered and I use my ISP DNS as second DNS. Also I do not want to connect from outside using name I will use IP address and its work now. But I need to find the SQL server when I click"" Browse for more"". Please now I can connect to the server from outside using public IP address and that is OK and I can connect from inside using private IP address or name server but the problem that management studio cannot find the instance when I click "" Browse for more"" I need the management studio to find the SQL because  I have program need to find the server in management studio to connect to it
Based on this information, I'll assume that you only want the "browse for more" to work on your internal network.  It is unlikely your network admins will allow the necessary network traffic from your internal servers to the public network.

Here is a question:  You say you need the "Browse for more" option to work so that some other program can find the server in Management Studio.  Why is this necessary?  You've stated that you can connect if you specify an IP address. Why can't you configure your additional program to connect to the SQL Server by IP address?  I'm not criticizing, just trying to understand why this isn't an option, since it is known to work.
yes this is the issue. Please I check and the instance has been configured to be visible (not hidden)
Can you try any of this troubleshooting which I had suggested?:
If possible, you may also want to see if browsing for the instance works from a host on the same subnet/VLAN as the instance host, but not from hosts (such as your workstation, perhaps) which are on different subnets/VLANs from the instance.  If you only have an issue from hosts not on the same subnet as the SQL Server host, then UDP port 1434 traffic may not be forwarded off the SQL Server host's subnet.  See http://msdn.microsoft.com/en-us/library/ms188200.aspx and  http://msdn.microsoft.com/en-us/library/ms181087.aspx for information on the "Browse for server" dialog box and the Browser service.
Based on experience, I suspect the issue you are having is related to network behavior.  After re-reading what I suggested above, I had an an additional thought, which I think is more likely than UDP 1434 being blocked internally:  Multicast network traffic may not be forwarded between your network subnets/VLANs.

Using the "Browse for more" option would generate a multicast request from your client.  If multicast traffic is being blocked between subnets, the SQL Browser service on your SQL Servers hosts won't receive the requests to enumerate the instances.

I found some additional, useful documentation on networking requirements for SQL Server is here:  http://technet.microsoft.com/en-us/library/cc646023.aspx.  It is an article on Windows Firewall configuration, but details what ports are used by SQL Server components.
0
 
LVL 3

Expert Comment

by:GSGDBA
Comment Utility
0
 

Author Comment

by:nasemabdullaa
Comment Utility
Hi

Thank you for all reply I raed and try all above but its stll not working

and help

Regards
0
 
LVL 5

Assisted Solution

by:adamsjs
adamsjs earned 120 total points
Comment Utility
Since you started troubleshooting this issue, have you tried restarting the SQL Browser service for you new SQL Server host?  I generally only see this resolve issues caused by having changed IP and port bindings for a SQL Server after the Browser service was started.  But, as you are still trying to resolve your issue, I would recommend doing this when able.

I'll give some more suggestions.  These will continue to be weighted towards networking-related causes, as this has been the problem when I have experienced similar issues.

Looking back at previous comments, you stated you have three SQL Servers, and that when you click the "Browse for more" button in SQL Server Management Studio you cannot see this new server.  You see two of of your three SQL Server instances, correct?  Have you looked at what (if anything) those two instances have in common, or how they or their hosts are different from your new SQL Server instance and its host?  Are they on the same subnet as the new SQL Server host?  Are the a different version of SQL Server than your new instance?  Did you configure or secure your new SQL Server instance differently than the other instances?  Is the Windows OS different on your new system than the other two SQL Server hosts?  I can't list everything you should look at, but keep looking for what the working scenarios have in common, and how they differ from the nonworking scenario.

When you are trying "Browse for more" from Management studio, is the host from which you are doing this on the same subnet as the hosts for the SQL Server instances you expect to see in the list?  If not, and you don't see the instance you want, have you tried repeating the test using SSMS from a host which is on the same subnet as the SQL Server instance (connect your computer to the same subnet as your SQL Server host, or use another server with SSMS that is already on the same subnet)?  Can you see the new instance in this scenario?

Have you discussed this issue with your network and server administrators?  Have they verified that UDP traffic on port 1434 is not being blocked at routers, the SQL Server host, or the machine from which you are attempting to connect?  Can they verify that this is occurring?  This will require the ability to sniff/trace the network traffic between (and possibly at) the SQL Server and the host running SSMS.  

For the "Browse" function to work, UDP traffic on port 1434 must be allowed, both directions, from the machine where you are using Management Studio.  The request coming from the Management Studio host will be a broadcast request (I had previous said multicast, but believe broadcast is actually correct here).  If the Management Studio host and the SQL Server host are not on the same subnet, the broadcast traffic will need to be routed.

Additionally, in regards to Windows Firewall (if enabled on your SQL Server host), there are settings which would affect responses to UDP 1434 broadcast requests.  In the Books Online entry "Configuring the Windows Firewall to Allow SQL Server Access", there is a section on Additional Ports and Services.  In that section, there is an item regarding Management Studio's browse button which details a firewall setting that impacts responses to the browse requests.  Also note that even if this setting is configured to allow the responses, responses may still fail:
If the setting is FALSE (default), unicast responses are permitted for 3 seconds. The length of time is not configurable. in a congested or high-latency network, or for heavily loaded servers, tries to enumerate instances of SQL Server might return a partial list, which might mislead users.
In previous comments you also stated the following when asked about the program you are trying to connect to your SQL Server instance:
1 - From outside using IP address (if I'm outside company use public IP address and if I'm in out in any branch of our company connect use privet IP address all branch connect together by VPN)

2- From inside I have search bottom to find the SQL server and if I cannot see it I cannot connect ti the server

3 - Because the SQL management studio cannot find the instance the program cannot find it) the search bottom in the program work as same the management studio when click "brows for more)
So, you can get the program to work if you specify the internal IP address if you are on your internal network (either in main or branch location), correct?  This may not be the answer you want to hear, but if you are unable to make the Browse feature work, at least your application will still function.

You might also want to contact the developer of your the application and see if can be updated or patched so that you can just enter the SQL Server instance name without having to browse for it, especially if this is a commercial, third-party application.  There are many cases where it is not possible to browse for a SQL Server instance, either due to coincidental configuration and state of the infrastructure, or a purposeful design and securing of the environment.  If the SQL Server instance name is known, it would be simpler to just be able to enter it directly.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now