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

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

Cross post: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/2deb9bf5-6dac-425e-b90c-ebb81c00f854

---

Sean Gallardy | Blog | Twitter

Answer 5

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..,

Answer 6

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

Answer 7

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, [e-mail]

Answer 8

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.

Answer 9

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, [e-mail]

Answer 10

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, [e-mail]

Answer 11

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, [e-mail]

Answer 12

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.

Answer 13

*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, [e-mail]

Answer 14

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.

Answer 15

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, [e-mail]

Answer 16

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