locked
Can't Log in from Management Studio RRS feed

  • Question

  • 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
    ------------------------------

    Friday, June 19, 2015 12:50 AM

Answers

  • 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




    Friday, June 19, 2015 4:03 AM
  • This is general error message please have a look at below article

    How to troubleshoot connecting to SQL Server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, June 19, 2015 4:38 AM

All replies

  • 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

    Friday, June 19, 2015 3:17 AM
  •  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


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Friday, June 19, 2015 3:40 AM
  • 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

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, June 19, 2015 3:42 AM
  • 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

    Friday, June 19, 2015 3:49 AM
  • 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




    Friday, June 19, 2015 4:03 AM
  • 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


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Friday, June 19, 2015 4:24 AM
  • This is general error message please have a look at below article

    How to troubleshoot connecting to SQL Server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, June 19, 2015 4:38 AM