locked
error occurred while establishing a connection to the server RRS feed

  • Question

  •  

    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)
    Monday, January 14, 2008 5:01 AM

Answers

  • You probably need to configure SQL to allow remote logins. By default the remote logins are turned off. You will need to go into the ‘Surface Area Configuration’ (SAC) manager and turn them on. Then restart the service.

     

    Here are some basic steps, based on a default installation:

     

    1)      Start ‘Surface Area Configuration’ manager

    a.       Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration

    2)      Change the “SAC” setting

    (on the left pane)

    a.       Click on the “Surface Area Configuration for Services and Connections” hyperlink

    b.      Expand the node for the database you are trying to connect to (i.e. “MSSQLSERVER”)

    c.       Expand the node for the ‘Database Engine’

    d.      Click on “Remote Connections”

    (on the right pane)

    e.      Select the radio option of the type of remote connections you wish to allow (i.e. “Using named pipes only”)

    f.        Click on “OK” (NOTE: You’ll get a message saying changes don’t take effect until you restart the SQL service, click “OK”)

    g.       Close the “SAC” dialog box

    3)      Restart the SQL Service (you’ll use: SQL Server Configuration Manager, ‘SSCM’)

    a.       Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

    b.      Double-click on the instance of SQL you had just modified (NOTE: You want to ensure the name in the parenthesis matches exactly to the name of the database you just changed; the name you used in step # 2-B).

    c.       Restart the service (You can select ‘Restart’; but, I’ve had problems with it stopping and starting…I’d suggest following the sub-set of instructions included below)

                                                                   i.      Click on “Stop” (if you get a warning about stopping the SQLSERVERAGENT service, then click on “Yes”…it’ll get restarted when you restart SQL)

                                                                 ii.      Wait for the service to stop (this can take a few moments…all depends on connections and services; it’s usually best to ensure no one else is connected and you have SQL Management Studio closed)

                                                                iii.      Click on “Start”

                                                               iv.      Wait for the service to start

    d.      Click on “OK”

    e.      If SQLSERVERAGENT is restarted, or you didn’t get a warning about SQLSERVERAGENT then proceed to step # f, else…

                                                                   i.      Check to ensure the SQLSERVERAGENT was restarted (should say under the 'State' column: "Running", you'll see a green arrow in the icon). If the 'State' column says "Stopped", and you see a red square then it is still stopped..you can restart it by following this:

    1.       Right-click on ‘SQLSERVERAGENT’

    2.       Click on ‘Start’ (wait a few moments for the service to start)

    f.        Close the “SSCM”

     

    That’s all there is; it can be tideous operation the first time. You only have to do this once to make the remote connections enabled. You’ll have to do this again if you want to disable the remote connections.

     

    I’d personally suggest starting with using only ‘named pipes’; this basically means the client connection must be on your network when connecting to the remote server. I’d only use TCP/IP if an application requires it, or you MUST have access outside of your network. As with anything else, this will open vulnerabilities within SQL should you not have proper security or patches applied. Allow remote connections with caution and great considerations.

     

    I hope the above helps!

     

    Thanks,

    James

    Database Administrator

    Monday, January 14, 2008 10:32 PM
  • Monday, January 14, 2008 10:57 PM

All replies

  • You probably need to configure SQL to allow remote logins. By default the remote logins are turned off. You will need to go into the ‘Surface Area Configuration’ (SAC) manager and turn them on. Then restart the service.

     

    Here are some basic steps, based on a default installation:

     

    1)      Start ‘Surface Area Configuration’ manager

    a.       Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration

    2)      Change the “SAC” setting

    (on the left pane)

    a.       Click on the “Surface Area Configuration for Services and Connections” hyperlink

    b.      Expand the node for the database you are trying to connect to (i.e. “MSSQLSERVER”)

    c.       Expand the node for the ‘Database Engine’

    d.      Click on “Remote Connections”

    (on the right pane)

    e.      Select the radio option of the type of remote connections you wish to allow (i.e. “Using named pipes only”)

    f.        Click on “OK” (NOTE: You’ll get a message saying changes don’t take effect until you restart the SQL service, click “OK”)

    g.       Close the “SAC” dialog box

    3)      Restart the SQL Service (you’ll use: SQL Server Configuration Manager, ‘SSCM’)

    a.       Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

    b.      Double-click on the instance of SQL you had just modified (NOTE: You want to ensure the name in the parenthesis matches exactly to the name of the database you just changed; the name you used in step # 2-B).

    c.       Restart the service (You can select ‘Restart’; but, I’ve had problems with it stopping and starting…I’d suggest following the sub-set of instructions included below)

                                                                   i.      Click on “Stop” (if you get a warning about stopping the SQLSERVERAGENT service, then click on “Yes”…it’ll get restarted when you restart SQL)

                                                                 ii.      Wait for the service to stop (this can take a few moments…all depends on connections and services; it’s usually best to ensure no one else is connected and you have SQL Management Studio closed)

                                                                iii.      Click on “Start”

                                                               iv.      Wait for the service to start

    d.      Click on “OK”

    e.      If SQLSERVERAGENT is restarted, or you didn’t get a warning about SQLSERVERAGENT then proceed to step # f, else…

                                                                   i.      Check to ensure the SQLSERVERAGENT was restarted (should say under the 'State' column: "Running", you'll see a green arrow in the icon). If the 'State' column says "Stopped", and you see a red square then it is still stopped..you can restart it by following this:

    1.       Right-click on ‘SQLSERVERAGENT’

    2.       Click on ‘Start’ (wait a few moments for the service to start)

    f.        Close the “SSCM”

     

    That’s all there is; it can be tideous operation the first time. You only have to do this once to make the remote connections enabled. You’ll have to do this again if you want to disable the remote connections.

     

    I’d personally suggest starting with using only ‘named pipes’; this basically means the client connection must be on your network when connecting to the remote server. I’d only use TCP/IP if an application requires it, or you MUST have access outside of your network. As with anything else, this will open vulnerabilities within SQL should you not have proper security or patches applied. Allow remote connections with caution and great considerations.

     

    I hope the above helps!

     

    Thanks,

    James

    Database Administrator

    Monday, January 14, 2008 10:32 PM
  • Monday, January 14, 2008 10:57 PM