locked
Problem connecting to local server RRS feed

  • Question

  • I'm totally a newbie here. I just installed everything on my PC. When I tried to connect to the local server(on my computer), I got this error. I guess I'm totally lost at the message of the red characters since all I try to do is to connect to the local server. CORPLLIDA1 is the name of my computer. Can anyone help me here? Thanks in advance.

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

    Cannot connect to CORPLLIDA1.

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

    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) (Microsoft SQL Server, Error: 2)

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

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

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


     

    Thursday, May 22, 2008 5:17 AM

Answers

  • Make sure sql is running.  Open up either the SQL Server Surface Area Configuration or the Sql Server Configuration Manager.  In the Sql Server Configuration Manager in services section check and make that sql state is running, check the network configuration section and try enabling tcp and named pipes.
    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 5:36 AM
  • click computer->management->service, check whether there is service named as "SQL SERVER (MSSQLSERVER or instancename)", if there is, start it; else you should return to the setup.exe and choose database service and  install it.

    Also you can goto Sql server surface area configuration to enable  both remote TCP connection and name pipe connection.

    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 2:50 PM
  • Okie try the below

    • Go to RUN
    • Type services.msc
    • Once Service control manager opened check whether SQL SERVER(MSSQLSERVER or instancename) service is available as said by Jet Li
    • If its available then check whether the service is running fine
    • If its not available, then you have not installed database engine service!
    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 11:40 PM
  • David, please try these steps:

     

    I.   Incorrect connection string, such as using SqlExpress.

    Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

    The typical error when dealing with Express includes:

    a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

    b. Np was disabld by default after installing SqlExpress.

    c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

    Please read the following blog for best practice of connecting to SqlExpress.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

     

    II. Named Pipes(NP) was not enabled on the SQL instance.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

    Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

    1) Go to SQL Server Configuration Manager, See Server has NP enabled.

    2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

    3) Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

    5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

     

    III. Remote connection was not enabled. 

    Check out: when you right click on the Server in SQL Server Management Studio, in Connections, the Remote server connections part, you have enabled the "Allow remote connections to this server" check box

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

    If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

    a. "File and Printer Sharing" was opened in Firewall exception list.

    b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

    http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    IV. Server not started, or point to not a real server in your connection string.

    Check out: Open SQL Server Surface Area Configuration and ensure all the required services are started, Remote Connections are configured.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

    a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

    b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

     

     V. Other reasons such as incorrect security context.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

    Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

    a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

    b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

     

    VI. Please try basic connectivity tests between the two mahcines you are working on. One simple way to verifty connectivity is to use command line tools, such as osql.exe. For example, osql -E -StcpTongue Tiedervername\instancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.

     

    Here are some blogs which could be helpful: just follow the basic connectivity troubleshooting guidelines on the SQL Protocols blog, see:

    SQL Server 2005 Connectivity Issue Troubleshoot - Part I

    http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    and

    SQL Server 2005 Connectivity Issue Troubleshoot - Part II

    http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

     

    Hope this helps.

     

     

    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:48 PM
    Saturday, May 24, 2008 8:15 AM
    Answerer

All replies

  • Make sure sql is running.  Open up either the SQL Server Surface Area Configuration or the Sql Server Configuration Manager.  In the Sql Server Configuration Manager in services section check and make that sql state is running, check the network configuration section and try enabling tcp and named pipes.
    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 5:36 AM
  • There's no entry in services section in Sql Server Configuration Manager. Is this the reason?

    Thursday, May 22, 2008 6:05 AM
  • If you are running sql 2005 then yes it should be listed here.  Is this from a fresh install?  Maybe you installed the client tools and not the server?

    Sql Server Configuration Manager
    http://msdn.microsoft.com/en-us/library/ms174212.aspx
    Thursday, May 22, 2008 6:10 AM
  • You need to install Sql Server 2005 database engine to connect to Sql Server. If you have'nt installed it you cannot connect. So rerun the setup.exe and choose database services and install it. Also if you are trying to connect from your workstation you need to enable remote connection in Sql server surface area configuration as shown in the below link,

    http://support.microsoft.com/kb/914277

     

    - Deepak

    Thursday, May 22, 2008 6:52 AM
  • click computer->management->service, check whether there is service named as "SQL SERVER (MSSQLSERVER or instancename)", if there is, start it; else you should return to the setup.exe and choose database service and  install it.

    Also you can goto Sql server surface area configuration to enable  both remote TCP connection and name pipe connection.

    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 2:50 PM
  • I know I have installed everything -- I selected all available components when I ran the set up.

    I can connect to remote server with no problem. However, when I try to connect to the Server on the local matchine(aka, my workstation), I got the error as I listed above. What frustrated me is that -- it's local connection and has nothing to do with the remote connection, yet the message sounds like I try to connect remotely.

    Thursday, May 22, 2008 10:13 PM
  • Jet Li,

    Where is computer->management->service?

    Thursday, May 22, 2008 10:18 PM
  • Okie try the below

    • Go to RUN
    • Type services.msc
    • Once Service control manager opened check whether SQL SERVER(MSSQLSERVER or instancename) service is available as said by Jet Li
    • If its available then check whether the service is running fine
    • If its not available, then you have not installed database engine service!
    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:49 PM
    Thursday, May 22, 2008 11:40 PM
  • If this is sql 2005 you should see the service listed in Sql Server Configuration Manager. If its not there then its not installed.  I think you just installed the client tools.  The server tools and client tools are two seperate setup programs.  At least in all the distribs I have there is a servers and tools folder.  And I have a lot of them Smile

    Developer
    Developer IA64
    Developer X64
    Enterprise
    Enterprise IA64
    Enterprise X64
    Standard
    Standard IA64
    Standard X64
    Workgroup

    Thursday, May 22, 2008 11:46 PM
  • sorry for type error.

    the correct one is : computer->manage->services

    Friday, May 23, 2008 5:23 AM
  • Thanks for you guys' help! Now, I'm pretty sure I only installed the client version of the setup. Darn, I didn't know they have two versions for the set up...

    Friday, May 23, 2008 3:52 PM
  • David, please try these steps:

     

    I.   Incorrect connection string, such as using SqlExpress.

    Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

    The typical error when dealing with Express includes:

    a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

    b. Np was disabld by default after installing SqlExpress.

    c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

    Please read the following blog for best practice of connecting to SqlExpress.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

     

    II. Named Pipes(NP) was not enabled on the SQL instance.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

    Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

    1) Go to SQL Server Configuration Manager, See Server has NP enabled.

    2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

    3) Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

    5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

     

    III. Remote connection was not enabled. 

    Check out: when you right click on the Server in SQL Server Management Studio, in Connections, the Remote server connections part, you have enabled the "Allow remote connections to this server" check box

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

    If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

    a. "File and Printer Sharing" was opened in Firewall exception list.

    b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

    http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    IV. Server not started, or point to not a real server in your connection string.

    Check out: Open SQL Server Surface Area Configuration and ensure all the required services are started, Remote Connections are configured.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

    a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

    b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

     

     V. Other reasons such as incorrect security context.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

    Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

    a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

    b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

     

    VI. Please try basic connectivity tests between the two mahcines you are working on. One simple way to verifty connectivity is to use command line tools, such as osql.exe. For example, osql -E -StcpTongue Tiedervername\instancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.

     

    Here are some blogs which could be helpful: just follow the basic connectivity troubleshooting guidelines on the SQL Protocols blog, see:

    SQL Server 2005 Connectivity Issue Troubleshoot - Part I

    http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    and

    SQL Server 2005 Connectivity Issue Troubleshoot - Part II

    http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

     

    Hope this helps.

     

     

    • Marked as answer by pituachMVP Thursday, January 9, 2014 10:48 PM
    Saturday, May 24, 2008 8:15 AM
    Answerer