locked
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. (p RRS feed

  • Question

  • I've just installed sql server 2008 r2 and i restored my DB, and i receive this error:

    I have got the above 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

    Please Help Me.

    Thanks,

    Harish


    • Edited by d.Harish Thursday, December 13, 2012 1:52 PM
    Thursday, December 13, 2012 1:51 PM

Answers

  • If you are running Visual Studio on your local machine, we can forget all about firewalls and enabling of TCP/IP. That matters when you connect from a different machine.

    So let's focus on how you connect from Visual Studio? Can you include a screenshot.

    There is some puzzling information in your post. I assume that your machine name is CEO-PC and not CEO-PC/SQLSERVER? When you connect to a named instance, you should use a backslash (\) as the separator, not a forward slash. Furthermore, the default name for an instance of Express Edition is SQLEXPRESS, and this is confirmed by your output from SQL Server Configuration Manager.

    If you are attempting to connect from Visual Studio with CEO-PC/SQLSERVER, try CEO-PC\SQLEXPRESS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 17, 2012 10:30 PM
  • I am connecting with DATA SOURCE as CEO-PC/SQLEXPRESS. This is my connection string.

    Data Source=CEO-PC/SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;

    Tilt the slash.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 18, 2012 7:58 AM

All replies

  • Hi,

    I have just installed Micorsoft SQLSERVER 2008 r2. 

    When I am trying to connect from Visual studio for web.., I got the above ERROR..,

    I have goner through Many Suggestions like.., Suggestions by Pinal Dave.

    1. For sqlcmd -L command I got 

    Servers:

    (Just like this Nothing else)

    2. For osql -L: i got

    Servers:

    --NONE--

    3. When i checked SQL configuration manager.

    only these were running

    Name State Start Mode Log On As Process ID Service Type
    SQL Server (SQLEXPRESS) Running Automatic NT AUTHORITY\NETWORKSERVICE 1724 SQL Server
    SQL Server Agent (SQLEXPRESS) Stopped Other (Boot, System, Disabled or Unknown) NT AUTHORITY\NETWORKSERVICE 0 SQL Agent
    SQL Server Browser Stopped Other (Boot, System, Disabled or Unknown) NT AUTHORITY\LOCALSERVICE 0 SQL Browser

    Help me please.

    Thanks,

    Harish

    Thursday, December 13, 2012 2:18 PM
  • Hi, Check the SQL server service for that instance in SQL server configuration manger. If it is stopped right click on the service and start the service. Check the network protocols too from SQL server configuration manger . Enable tcp protocol if it is disabled. And if the instance is named , use servername\instancename in the connection string or from SQL server management studio. If it is default instance use the server name for the connections. Hope this will help. Thanks, SKB.
    • Proposed as answer by Iamyourmonkey Tuesday, May 12, 2015 8:44 PM
    Thursday, December 13, 2012 2:38 PM
  • Hello,

    The SQL Server Browser service is stopped and needs to be started. While SQL Server isn't dependant on the browser service, it is what responds to the udp checks that are sent out with sqlcmd and the older deprecated osql. Since it wasn't running you wouldn't get a result. The other part of this is that the browser service does the port lookup for a named instance of which sql server express by default installs as. Named intances default to dynamic ports, since the default 1433 is not used and the browser service isn't running, the connection should fail.

    Start by turning on the browser service and attempting to connect locally to the Machine_Name\SQLEXPRESS instance where Machine_Name is teh name of the server or computer it is running on. If you can connect locally then you'll need to make sure your firewall rules and network connectivity are setup (along with remote access in sql express) to allow and pass remote connections.

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, December 13, 2012 3:08 PM
  • Thanks for your response.

    I have done what you said.

    The thing is when I am trying to connect SQL Server Management studio.., I am ABLE to connect and create databases and Tables.

    But I am Inserting from Visual Studio Express 2012 for WEB..., I am getting Similar ERROR again and again.

    Will you suggest me please..,

    Friday, December 14, 2012 8:57 AM
  • If you've set express edition to allow remote connections and enabled the tcp/ip protocol which is in the FAQ section, then you have a networking issue that needs to be resolved. Make sure you're not blocking the TCP port sql server is listening on - since it's a named instance it won't be 1433, but a random port - and 1434 UDP for the browser service.

    Make sure you're using the correct connection string when attempting to connect - Machine_name\SQLEXPRESS

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, December 14, 2012 1:07 PM
  • Do you run Visual Studio and SQL Server on the same machine?

    When you attempt to connect to SQL Server what do you specify for the server?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 14, 2012 9:59 PM
  • Hello All,

    I am running Visual Studio and SQL Server on the same machine.

    Server name is CEO-PC/SQLSERVER.

    In my SQL Server Network Configuration.., I have only one Protocol Running. "Protocols for SQLEXPRESS"  No more protocols.

    1. I have set Windows Firewall >> Inbound Rules >> New Rule  

    2. SQL Server configuration manager >> Protocol for SQLEXPRESS >>  TCP/IP Enabled >> (right click) Properties >> Protocols >> Ip Address>> Everything Enable is Set to "Yes"

    3. When I run Command prompt As:

    i) sqlcmd -L: CEO-PC/SQLSERVER

    ii) Osql -L: CEO-PC/SQLSERVER     is my output.

    Monday, December 17, 2012 1:42 PM
  • If you are running Visual Studio on your local machine, we can forget all about firewalls and enabling of TCP/IP. That matters when you connect from a different machine.

    So let's focus on how you connect from Visual Studio? Can you include a screenshot.

    There is some puzzling information in your post. I assume that your machine name is CEO-PC and not CEO-PC/SQLSERVER? When you connect to a named instance, you should use a backslash (\) as the separator, not a forward slash. Furthermore, the default name for an instance of Express Edition is SQLEXPRESS, and this is confirmed by your output from SQL Server Configuration Manager.

    If you are attempting to connect from Visual Studio with CEO-PC/SQLSERVER, try CEO-PC\SQLEXPRESS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 17, 2012 10:30 PM
  • Hello,

    Thanks for your response,

    Ya My server name is CEO-PC\SQLEXPRESS itself, I typed it wrong.

    I am connecting with DATA SOURCE as CEO-PC/SQLEXPRESS. This is my connection string.

    Data Source=CEO-PC/SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;

    But I am encountered with same Error.

    I am connecting with Windows Authentication.

    When I am Open SQL Server Management it works. But I could not connect through Visual studio.

    Thanks,

    Harish.

    _________________________________________________________________

    Harish, jr. Software Engineer, dharish8005@gmail.com

    Tuesday, December 18, 2012 5:39 AM
  • I am connecting with DATA SOURCE as CEO-PC/SQLEXPRESS. This is my connection string.

    Data Source=CEO-PC/SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;

    Tilt the slash.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 18, 2012 7:58 AM
  • Hi Harish,

    There are  many resons for why your are getting this error. The following are the few resolutions for this error:

    If you get the following SQL Server 2005 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) ”

    Things to check:
    1. Make sure your database engine is configured to accept remote connections
    • Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
    • Click on Surface Area Configuration for Services and Connections
    • Select the instance that is having a problem > Database Engine > Remote Connections
    • Enable local and remote connections
    • Restart instance

    2. Check the SQL Server service account
    • If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

    3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
    • Usually the format needed to specify the database server is machinename\instancename
    • Check your connection string as well

    <connectionStrings>

    <add name="SampleConnectionString" connectionString="Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName="System.Data.SqlClient"/>

    </connectionStrings>

    4. You may need to create an exception on the firewall for the SQL Server instance and port you are using
    • Start > Run > Firewall.cpl
    • Click on exceptions tab
    • Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn, check your installs for the actual folder path), and port (default is 1433)
    • Check your connection string as well

    5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

    6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

    7. Check that you have connectivity to the SQL Server.
    Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
    • Start > Run > cmd
    •netstat -ano| findstr 1433
    •telnet myserver 1433
    •ping -a myserver

    Check what ports are IP addresses are being returned.

    If you still can’t get any connection, you may want to create a SQL account on the server, a corresponding SQL user on the database in question, and just use this username/password combo in your web application.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Tuesday, December 18, 2012 12:47 PM
    Tuesday, December 18, 2012 12:47 PM
  • *1. Make sure your database engine is configured to accept remote connections

    Basit, while I realise that you post a list that you have lying around, please observe that we have already concluded that Harish is trying to connect locally. We don't to confuse anymore with talk about remote connections etc. Your post had been appropriate as an initial response, when all we knew that Harish got this error. But at this point your post is less-than-helpful.

    I also note that you have proposed your own response as answer, which in my book is inappropriate in all days of the week.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 18, 2012 10:35 PM
  • Hello All,

    very thankful for your response,

    I have tried all Above. I am Using Sql Server 2008R2 and visual Studio2012 for web.

    Hello Erland Sommarskog, as you said,

    I have tried to Tilt the slash. Getting an error as: Unrecognized escape sequence I did not resolved the problem yet.

    And the thing I dont understand is, When I try to connect from Studio Management, I am able to do that and also I am able to connect to local database using Gridview with Sqldatasource.

    I have tried like copying the ConnectionString from web.config file and pasted to connect.

    Then I found an error like "Format of the initialization string does not conform to specification starting at index 138".

    connection string is(Default Connection in my Web.config file)

    "Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-WebApplication1-20121205143521;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-WebApplication1-20121205143521.mdf"

    And for "ConnectionString" In my web.config file

    Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-WebApplication1-20121205143521.mdf;Integrated Security=True;Connect Timeout=30

    Error: Format of the initialization string does not conform to specification starting at index 59.

    If you find any other errors or modifications That I has to do or Tricks you find to Fix my Error please share.

    Thanks To All,

    Harish.

    • Edited by d.Harish Saturday, December 22, 2012 7:52 AM
    Saturday, December 22, 2012 7:39 AM
  • I have tried to Tilt the slash. Getting an error as: Unrecognized escape

    sequence I did not resolved the problem yet.

    Then you might be using a programming language where backslash is a special
    character in string literals and in which case you need to double it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 22, 2012 9:54 AM
  • When you create a service-based database in Visual Studio 2012, it uses the SQL Server Express LocalDB engine to access the database file (.mdf), where earlier versions of Visual Studio used the SQL Server Express engine. Since you are using SQL server Express and visual studio 2012, you have to make a minor change in Visual studio settings. In Visual Studio 2012, you can use SQL Server Express by default instead of SQL Server Express LocalDB. On menu bar, choose Tools, Options. Under the Database Tools node, choose Data Connections. In the SQL Server Instance Name text box, enter SQLEXPRESS. As an alternative, you can enter other values for the SQL Server instance name (for example, SQL2008).

    Check out this link: http://msdn.microsoft.com/library/ms233817%28VS.110%29.aspx

    Friday, February 15, 2013 3:27 PM
  • thank
    Saturday, June 22, 2013 5:28 AM
  • I have tried to Tilt the slash. Getting an error as: Unrecognized escape

    sequence I did not resolved the problem yet.

    Then you might be using a programming language where backslash is a special
    character in string literals and in which case you need to double it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    This make my issue to be solved, "\\".. Thankyou Erland
    Monday, August 12, 2013 12:18 PM
  • Thank!
    Thursday, August 22, 2013 3:57 PM
  • I ran into this issue for some reason my SQL server was in a failed state. I went to my "View Local Services" and scrolled down to SQL. I right clicked on SQL Server(MSSSQLSERVER) and clicked start. This started the SQL server back up because for some reason it failed. Hope this helps.
    Friday, August 23, 2013 5:53 PM
  • hi. a blind student developer. installed sql management studio express 2012. and want to use it, independent of visual studio 2013 ultimate 32 bit, running windows 7 professional on a toshiba laptop. so. will provide the errors, and will provide my computer name. how do i connect to this. and also i use a screen reader jaws for windows from htttp://www.freedomscientific.com. and how do i connect. what stupid thing am i doing. tried several times, and i do not have any registered servers. so how to create a local server. and went to database engine, local server groups, tasks, register local servers, and says i have none. also if i go to the local servers tab, and it tells me to select a local server, my two or three screen readers, does not say what the item is, and just says tree view. can any one help. this is frustrating me, just want to connect. or do i need to do the local db command. and then will this allow me to connect to the ssme 2012. or do i need to get ssme 2014. please help. will paste below.  marvin.  ps: pasting the error below and will paste the computer name and what is the command for the sql express instance. please help. Connect to Server
    Cannot connect to Marvin-PC-PC\sqlexpress.
    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)
    OK Control Panel All Control Panel Items System
    Control Panel Home View basic information about your computer
    Device Manager Windows edition
    Remote settings Windows 7 Professional
    System protection Copyright © 2009 Microsoft Corporation.  All rights reserved.
    Advanced system settings Service Pack 1
    Get more features with a new edition of Windows 7
    System
    Rating: Windows Experience Index
    Processor: Intel(R) Core(TM)2 Duo CPU     T8100  @ 2.10GHz   2.10 GHz
    Installed memory (RAM): 4.00 GB (3.00 GB usable)
    System type: 32-bit Operating System
    Pen and Touch: No Pen or Touch Input is available for this Display
    Computer name, domain, and workgroup settingsSee also Computer name: Marvin-PC-PC Change settingsAction Center Full computer name: Marvin-PC-PCWindows Update
    Computer description:Performance Information and
    Tools Workgroup: WORKGROUP

    http://startrekcafe.stevesdomain.net http://groups.yahoo.com/groups/JawsOz

    Thursday, May 22, 2014 10:45 AM
  • The situation is not entirely clear, but it seems that you only installed SQL Server Management Studio, and not an actual engine. Or does that one come with Visual Studio 2013? I'm not updated on what ships with VS.

    In the Program Menu, do you have SQL Server Configuration Manager? If so, if you open it and go the Services node, are there any services listed?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 22, 2014 9:26 PM
  • In addition to all these other things,

    If your named instance is not using a default port, then you have to indicate the port when you connect. This caught me today and cost me an hour.

    the format is

    myServer\myInstance,62394     (the last part is the port you are using)

    --this post talks about connections
    http://msdn.microsoft.com/en-us/library/ms188642.aspx

    --this page runs you through how to find the port. Essentially   ConfigMgr->Protocols->IP->Properties

    http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

    Wednesday, October 15, 2014 2:39 PM
  • If your named instance is not using a default port, then you have to indicate the port when you connect.

    Not if the SQL Server Browser service is running. But if it is not running, then you need to specify the port number. That is correct.

    myServer\myInstance,62394     (the last part is the port you are using)

    You don't have to specify the instance name in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, October 15, 2014 9:13 PM
  • I was having hard time to connect, but your answer saved me. Thank you! I was wrongly put http:// befroe server name. 

    Tesfaye

    Sunday, November 29, 2015 2:15 AM
  • Tip: make sure to provide servername\instancename in full. (so that it matches the database you are tring to conect). Example: I found mistake in configue file like its mentioned like MYSERVERNAME\UAT2 instead of MYSERVERNAME\UAT2P1 caused connection problem after moving to UAT env... 
    Wednesday, January 20, 2016 11:59 AM
  • thanks sir  
    Tuesday, October 25, 2016 9:44 AM
  • Hi,

    I am getting this error as well with Visual Studio Community 2017.  Not sure how this could be a config issue if the data storage is built into Visual Studio on install.  I tried connecting to MySQL (WAMP) and got the same error message.  Is this a bug?

    Monday, March 20, 2017 9:07 PM
  • Don't piggyback to five year old thread, but start a new thread to describe your problem properly.

    What I can say is that if you got that error message when trying to connect to MySQL, you are using the wrong client library.

    Monday, March 20, 2017 10:30 PM