Hello,

I often face problem can't log in to database from Management Studion as below :

TITLE: Connect to Server
------------------------------

Cannot connect to 10.3.0.13\BPM.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Hi,

Please check the link below for a possible solution

http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Thanks

Bhanu

 Here are the steps:
1) Make sure your server name is correct, e.g., no typo on the name. 
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true). 
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

Refer

1. Make sure SQL Server Service is running
2. If a named instance, make sure SQL Server browser service is running
3. Make sure SQL Server is configured to allow remote connections
4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
5. Test server connectivity with PING from the client machine
6. Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine.  For example
a. TELNET <server-name> 1433
b. PowerShell: 1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }
7. Check firewall settings if step 5 or 6 connectivity test fails

Hello,

When we face this problem, we just restart the SQL Broswer service then it working fine,

but I still want to find the root cause

Hi,

SQL Server browser service is required to connect to named instances of SQL Server.

Looks like you have to change the Start Mode of the browser service  to automatic using the SQL Config Manager as shown below

This will let the SQL Server browser service to start automatically every time your machine re-starts

Hope This helps

Thanks

Bhanu

• Edited by bhanu_nz 2 hours 58 minutes ago

The basic purpose of the SQL Server Browser service is to provide instance and port information to incoming connection requests.

To configure a better and safe access mechanism for SQL Server, a DBA should have proper understanding of the SQL Server Browser service.

Best practices for using SQL Server browser service

The way you use SQL Server Browser service affects the access to your SQL Server instances, hence the security of installed instances. At one end, the most security conscious approach may be to use customized static ports for your instances and access SQL Server with fully qualified connection parameters. It would include IP + InstanceName + Port. In this configuration your instance would not be exposed to the network also more parameters are required in the connection string.

It would be a secure configuration compared to having the SQL Server Browser service running and serving the incoming connection requests with instance and port information. If you have no problem in using fully customized connection strings (IP + Name + Port), then you can stop the browser service permanently and access the instance through a fully qualified connection string.

In another type of configuration, if the instance is configured to use dynamic ports each time, then SQL Server Browser service should be running, otherwise there would be no way to keep track of the dynamic ports that are assigned.

If you have just a default instance installed (with default port) on your machine then SQL Server Browser service may be stopped to avoid overhead.

As part of best practices, always run SQL Server Browser service with a minimum privileged account. According to BOL any Windows user having the following rights would be capable to run the SQL Server Browser service.

• Deny access to this computer from the network
• Deny logon locally
• Deny logon as a batch job
• Deny logon through Terminal Services
• Log on as a service
• Read and write the SQL Server registry keys related to network communication (ports and pipes)

You can change the default account of the browser service (local system), to any other user having the above mentioned rights on the machine.

Refer

This is general error message please have a look at below article

How to troubleshoot connecting to SQL Server