Post reply

Unable to connect to one of Production database through SSMS

  • July 12, 2012 at 1:54 am

    #263688

    Hi ALL,

    I need an urgent help on this issue.

    I got the problem that I and team not able to connecto the product server giving following error..

    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

    I have checked following

    1) Instance is online sql service running

    2) Browser service running

    3)TCP/IP and named protocol enabled

    4)Remote connection to server is enabled.

    If I directly log on to the server through RDP and connect to SQL instance locally it works.

    If I use the IP address of the server in SSMS then also it works.

    Every thing was working day before yesterday. Suddenly got this issue user complaining server is not allowing to connect through SSMS.

    Please guide where to diagnose for this issue.

    I used sqlcmd -L on production box it gives the name of the instance. If I browse for network intance on production box it show that instance. But If I browse for the same instance from DEV box it dont give that instance name but gives the name of the prodouct box server through which we can connect.

    Please guide

    Thanks

  • July 12, 2012 at 2:13 am

    #1511904

    - Check SQL Server Configuration to ensure port settings haven't changed (default: 1433).

    - Execute SQLCMD -Sservername -E -A to see if you can connect at the command line.

    (If you have a named instance, specify it - MYSERVER\MYINSTANCE at the -S parameter).

    - Check all SQL Server services are running (services.msc).

    - Are you connecting directly from the server? Or from another server? If from another server confirm Windows Firewall settings aren't blocking connectivity. Confirm the server you are on is able to contact the production server.

    - Check the system logs for any automatic Windows Updates applied recently.

    - Check the system and application error logs for anomalies.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • July 12, 2012 at 2:47 am

    #1511916

    IP Address changed on the server, DNS not updated?

    If you can connect via the IP locally but not the name it sounds like DNS to IP translation isnt functioning correctly, possibly due to a stale DNS record.

  • July 12, 2012 at 3:40 am

    #1511949

    Hi,

    Thanks for the response, Firewall is off the server. The port is 1433.

    As suggested by you, I use sqlcmd and here is message.

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Server Network Interfaces: An error occurred while obtaining the dedicated a

    dministrator connection (DAC) port. Make sure that SQL Browser is running, or ch

    eck the error log for the port number [xFFFFFFFF].

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in

    stance-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 i

    f SQL Server is configured to allow remote connections. For more information see

    SQL Server Books Online..

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

  • July 12, 2012 at 3:42 am

    #1511952

    I think IP is not changed.

    I can connect through IP from any where but can not connect through server\InstanceName.

    Thanks

  • July 12, 2012 at 4:07 am

    #1511961

    vinod.saraswat (7/12/2012)

    Hi ALL,

    I need an urgent help on this issue.

    I got the problem that I and team not able to connecto the product server giving following error..

    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

    I have checked following

    1) Instance is online sql service running

    2) Browser service running

    3)TCP/IP and named protocol enabled

    4)Remote connection to server is enabled.

    If I directly log on to the server through RDP and connect to SQL instance locally it works.

    If I use the IP address of the server in SSMS then also it works.

    Every thing was working day before yesterday. Suddenly got this issue user complaining server is not allowing to connect through SSMS.

    Please guide where to diagnose for this issue.

    I used sqlcmd -L on production box it gives the name of the instance. If I browse for network intance on production box it show that instance. But If I browse for the same instance from DEV box it dont give that instance name but gives the name of the prodouct box server through which we can connect.

    Please guide

    Thanks

    Did you check server name ?

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

  • July 12, 2012 at 4:16 am

    #1511963

    Hi,

    I checked the server name from computer properties using @@servername.

    The computer name shows xxx-xxxx-PROD

    @@servername = xxx-xxxx-PROD\SQL2008P.

    We are able to connect through xxx-xxxx-PROD or IP address.

    But we are not able to connect through xxx-xxxx-PROD\SQL2008P, we were able to connect to through this till day before yesterday. Don't know what has changed.

    Thanks

  • July 12, 2012 at 5:14 am

    #1511977

    Try adding the hostname entry for the production server in the host file on dev box & check. It seems DNS resolution is not working properly.

    Sujeet Singh

  • July 12, 2012 at 8:12 am

    #1512080

    How it will help if I put entry in the host file which on dev?

    My question is We are not able to connect to xxx-xxxx-PROD\SQL2008P instance through SSMS to which earlier we can connect.

  • July 12, 2012 at 8:27 am

    #1512096

    vinod.saraswat (7/12/2012)

    How it will help if I put entry in the host file which on dev?

    My question is We are not able to connect to xxx-xxxx-PROD\SQL2008P instance through SSMS to which earlier we can connect.

    Earlier in the thread you wrote:

    vinod.saraswat (7/12/2012)

    I think IP is not changed.

    I can connect through IP from any where but can not connect through server\InstanceName.

    Thanks

    It suggests that you are able to connect using the IP from anywhere but you are not able to connect using the name. If this is the case, making a host file entry can help resolving the issue.

    p.s. the host file entry should be made on the machine from which you are connecting to the server (dev or whatever pc).

    Sujeet Singh

  • July 12, 2012 at 8:37 am

    #1512109

    Hi Divine flame,

    Please correct me if I am wrong, I am not able to connect to Production box so for this do I need to make entry in Production box's host file or DEV box host file?

    Some confusion..

    I checked the host files both on prod and DEV there were no such entries available and in fact those files have not changed since last two years. Will it be good to edit those files?

    Thanks for all you reply..

  • July 12, 2012 at 8:39 am

    #1512111

    Have you checked your DNS servers to ensure that the IP on the name record match and there fore resolving correctly?

  • July 12, 2012 at 8:45 am

    #1512117

    vinod.saraswat (7/12/2012)

    Hi Divine flame,

    Please correct me if I am wrong, I am not able to connect to Production box so for this do I need to make entry in Production box's host file or DEV box host file?

    Some confusion..

    I checked the host files both on prod and DEV there were no such entries available and in fact those files have not changed since last two years. Will it be good to edit those files?

    Thanks for all you reply..

    Since you are opening the SSMS on dev box to connect to production server, you should make the entry on dev box.

    Sujeet Singh

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply