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), 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.
Alternative:
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.
No related posts.
Thank you for a great breakdown! #6 was my problem!
awesome! great to hear the post helped!
i had the same problem.
i had to change the Logon creds. in the service properties.
Thank you
I’ve been seeing this error message, but intermittently – I will have the same connection with the same application code work 1000 times and on time 1001 it will throw this error. (Not always time 1000 and 1001, it does seem totally random when it will occur, I’m just using those numbers as an example).
Do you know what could cause this to occur intermittently, perhaps related to load on the server? I am using the activity monitor and there are not an unusual number of connections to the server, although I do have connections opening and closing rapidly they do not seem to be “leaking” (ie. all the ones that are opened are being closed and disappearing from the activity monitor). I wonder also if the network itself could be getting too busy to let the connection through?
Thanks in advance for any ideas you might have!
Thank you for #1, it solved my problems
It may potentially be your bandwidth or network card. Unless you have a really fast connection and huge bandwidth, your network connection might choke on the continuous connections. I know this happens with my networked printer.
What SQL Server version do you have?
You may also want to try enabling MARS (Multiple Asynchronous Result Set) to avoid hammering SQL Server with new connections.
I’ll try to dig in more to this issue. Maybe a good topic for my next post
Are the recommendations above server or client side?
Please fogive me, I no little of nothing about SQL.
server side
If you’re having trouble connecting via TCP/IP, check to make sure also that the instance you’re trying to connect to has the TCP/IP protocol enabled.
This is very useful. But I’ve an odd situation in which I can connect via the client api but not the smo api.
So this fails:
[Test]
public void SmoConnectionTest()
{
Server server = new Server(new ServerConnection(helper.ConnectionString));
server.Refresh(); //fails to open connection
}
But this succeeds:
[Test]
public void ClientConnectionTest()
{
using (SqlConnection connection = new SqlConnection(helper.ConnectionString))
connection.Open(); //succeeds in opening connection
}
Presumably this comes back to dataserver permissions? Here my SqlServer knowledge fails me – does one’s connection need to be related to the service account in some way?
You should also make sure SQL Server is installed on your server machine. I recently moved a web app to another machine and log-ins failed for this exact error. Turns out the machine didn’t have SQL Server installed! Dumb, but I’m just sayin’…
I’m getting this in 2008 trying to do a select * from otherdb..tablename. It worked fine in 2005 – and this is all from within SQL Server Management.
Help!
Hi Rich,
Not sure why you are getting this error, but it should still work in SQL Server 2008. I just tried it.
Three guesses:
- maybe you accidentally got disconnected when you tried this statement
- maybe your settings don’t allow for cross database queries (not sure which settings)
- maybe the default schema of the user you’re connecting as is different from the default schema it’s using in the other database
Alhamdulillah.
No 4 (firewall) was my problem.
Thank you for this useful posting.
Thank you
correct answer , direct to the point
Hi have the same problem , but have a differnt scenario. The scnario is that i am able to connect to the server with same connection string from Location say ‘A’ . but by using the same connection string i wont be connect to sql server from location say ‘B’ . My sql is hoting server is on differnt location say ‘C’. Can anybody help me ?
Also make sure your anti-virus/firewall is not blocking the Sql Server Service. McAfee was blocking mine.
Ganpro, try using sql server’s ip address instead of server name. first ping to make sure it works.
Anye, have you enabled the connection pool explicitly in connection string?
I have a website developed in visual web developer 2008 (with SQL 2008 express installed)in a XP pc, now I migrate the website to a web server which is windows 2k3 and SQL Server 2008 installed. I map to the web server from my XP pc, then copy the full website files from XP to the web server,connectionstrings changed accordingly by add connection in Visual web Developer in XP pc,IT guys help to transfer database files from APP_DATA to SQL SERVER,when run the website in IE, login page appears, but when login, the error appears as this thread shows, details as below:
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)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
I’d like to know what I need to do when I deploy a website from SQL express (XP PC) to a web server which is SQL server 2008 (Windows server 2k3)installed? I can not open ASP.NET administrator tool in VWD as well,it show me SQL error No 5110 ASPNETDB_TMP.MDF is on a network path that is not suppoted for database files.
anybody can help,thanks a lot!
Hi, #1 solved my problem. But may I know what causes this to happen?
Very good article. Correct Answers for problems. My problem is #1.
Thanks.
Anye
I’ve the similar problem that u have faced. Have u found the solution for it?
I have a console application which hits the DB continuously. The same error occurs while inserting record number 991.
I have switched of the firewall in the system.
All the services are running.
Any idea why it is showing error??
Hi friends
i am working on DotNet login controls,
I am getting error like this “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)”
As per your suggestion i wrote below logic in my web.config file…
but still i am getting same error…i am using SQL Server 2005 Standard Edition. please help me to solve this problem…
Thanks
Suneel Reddy
Sorry Suneel I cant see what you added to your web.config file ..
But before that, please make sure you can connect to SQL Server via Management Studio using the credentials you are using in your web.config. If you are able to connect, then at least you can eliminate possible credential issue from SQL Server end and focus on how SQL Server and your app is interfacing.
Also, it would be worthwhile to turn on Profiler when you try to connect from your app. It should also give you some clues as to what credentials are being passed to SQL Server, if at all.
A few other places to look at are the Windows Event Viewer, SQL Server log, and perhaps your app’s log.
hi All, im getting this error on my pc, while the same application does not give this error on my brother’s pc
. I amusing SQLServer2008 and VS2008.
“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) .Net SqlClient Data Provider.”
Any idea how to resolve it?
Thankyou very much
udayakumar/Anye,
I too am facing the same issue (intermittent loss of connection even if all the services are running).
Were you guys able to find any solution.
Please help.
Thanks,
Piyush
Hi , thanks boss , its working
Did anyone find any solutions to the intermittent loss of connection? I am also suffering from this problem but cannot find a solution.
Thanks.
Thanx for this article..
i have #1 problem…….now solved
really thanx
I have followed all the steps, from here and other places as well, but I can’t solve my problem.
I cannot connect to a local instance of SQL Server Developer edition running on a Windows 7 machine.
It’s a web application that for development purposes connects to the local machine only, it was working on a vista machine but on Windows 7 it doesn’t.
I can connect to SQl Server using Management Studio or Visual Studio’s Server Explorer, I can also connect from a Windows app. the problem is the web app, and the problem seems to be the named pipes, all connectivity tests work, but I believe (I might be wrong) that a web app. running locally and connecting to a local instance of sql server will use named pipes.
I must add that I have installed all updates to sql 2005 and that I am also running sql express 2005, but I cannot connect to either.
I guess is something with the named pipes in Windows 7 but I cannot find any information about it.
I also have the intermittent loss of connection. Completely frustrating. I can connect again and again, then all of a sudden get this error. 10-15 minutes later, all is working great again! When the connection fails, it fails both in my Visual Basic 2008 app AND in SQL Server 2008 Mgmt Studio. I dual boot XP and Windows 7 – both operating systems have this problem. Hope someone can help solve this!
Thanks!
Have you tried connecting using the machine/server name instead of relying on named pipes?
What version of SQL Server are you using (express, standard developer)? Have you looked at Event Viewer? There might be some clues there ..
Also, have a look at this http://blogs.msdn.com/mssqlisv/archive/2008/05/27/sql-server-intermittent-connectivity-issue.aspx, might have some tips for your issue
I believe I am using the machine/server name and instance name to connect to the database. But I am fairly new to SQL Server, so do not fully understand named pipes.
The version of SQL Server is the 2008 Developer Edition. It is running on an XP Pro workstation so that two developers (myself and a coworker) can work on the same database. Only my PC seems to have the intermittent connection problem – and over the past few months I have seen it happen in XP Pro, Vista Ultimate and Win 7 Ultimate – all 32 bit. My workstation is a fairly new Dell Studio XPS Desktop 435MT.
I am getting this error about every two minutes. For the most part everything is working OK, except users complain that things are a bit slow. This is a new server with 6 GB of RAM and less than 10 users. The only SQL-based program that is running is Backup Exec (other than the SBS monitoring, SharePoint, etc. from the SBS 2008 OS). All of these errors reference SharePoint Services 3. My main concern is that every few weeks the users mysteriously lose connectivity to the server. A reboot is the only way to get them reconnected to the shares on the server. One of the posts referenced network bandwidth and said that “your network connection might choke on the continuous connections.” Could this be what is happening? If so, how do I resolve the problem? I would prefer to disable any unneeded services or SQL instances since I have seen SQL using up a lot of RAM. I am not experienced with SQL so I don’t want to dive in without some advice. Any help would be much appreciated. Thank you!
If you run into connection problems on a 64 bit (say your new Windows 7 system, using SQL BI Development Studio) add your aliases under SQL Native Client 10.0 Configuration (32 bit)
hi,
I had this same error for Connection .
Thanks for your help.
Thank You very much.
Happy New Year To You All.
Thanks for the valuable article.
Thanks for the post. My first error message was “Cannot generate SSPI context” when trying to connect using Windows Authentication. Connecting via SQL Server Authentication worked. Rebooting the server then gave the “A network-related or instance-specific error ….” Scenario #2 stuck out and I checked the services, they weren’t running. Then I remembered a week earlier an administrator had been terminated and the Administrator password changed. Why it took AD a week to cause the first error with Windows Authentication is what I do not understand.
it might be a refresh issue .. perhaps your instance was restarted some time a week after, and when it tries to restart the services, it cannot anymore because the username/password is no longer valid. it could be a possible scenario..
Excellent ….
Thanx a ton…….too good………
I have been having this same error constantly. On my local development machine everything in my asp.net application worked perfectly fine then when I would copy my website files out to a hosted web server I would get the same error. I am using a MS SQL Sever 2008 that is hosted by discountasp.net hosting service.
After when searches trial and errors I have finally figured out how to solve this problem. In your web.config file you have your connection string stored. In my case I had it pointing to the external sql server hosted by discountasp.net. The thing that I didn’t have in my connection string section of the web.config file was that you need to put in the lines then below that add
then below that you can specify your own connection string that you actually use in your code to validate users, etc.
Here are the lines you need in your web.config section
remove name=”LocalSqlServer”
add name=”LocalSqlServer” connectionString=”"
I really appreciated this forum, I was looking for the answer to my problem and I didn’t find it anywhere, even at msdn, finally I did this steps and the one taht solved my problem was to include de server browser at firewall exceptions, thank you !!
My friend you have solved my problems. Many thanks
GRacias. Excelente guía.
Thank you so much!! I was trying to register a sql express installation from another pc on my management studio and this was the problem…
I had to allow remote connections via the surface area configuration manager.
Thank you very much this solved my issue immediately.
Excellent answer. I’ve struggled with this for many weeks. Thanks again.
By the way, I could connect to my default instances from a client machine, but could not connect to a named instants. I appied same setting to second instance. anything went wrong?
Robin
hello!
I am using sql server 2008 and visual studio 2010. while making connection b/w these two an exception is fired in my program…
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)
please help me
with regards
salil
My problem was resolved real quick. It was # 1.Thanks for arranging info. so Clear and clean. Great
Hi,
I have tried all options which have been stated here and some other websites but still i have the same error
Thanks in advance
#1 hit the mark!
Unreserved thanks – but WHY don’t I remember using the “Surface Area Config” utility before ?
Is there another way to allow remote access (express 2005)
that I must have forgotten ?
This is my 3rd instance to create on the server and the previous 2 DID allow remote access initial but I was stumped on sql3rd instance. Go figure !
R
Thanks for this post Belle. This post is the most comprehensive one for this error. Thanks again
Thank You……Start all Services in Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
Great post.
Just thought you might like to see someone who has scraped your work and passed it off as their own. Scumbags.
http://sqlservererror-info.blogspot.com/2010/09/network-related-or-instance-specific.html
Thanks Andy. Not sure what to do with the plagiarism. All I can do is to ask them to take those pages down. I saw another site that had a handful of my articles! Ironically they also passed off that they “also” work at Black Ninja …
Great post, the firewall exception was what I needed
Cheers,
SteveC.
#6 was my problem, also. Thanks so much for this post!
Same issue here, had DPM 2007 with sql 2005, upgraded to dpm 2010 and sql 2008. Ran a repair on sql 2008 shared components and error resolved.
Nothing is working, Ive tried to completly disable every firewall on my machine including the antivirus, every step you said. I cannot connect to my sql express.
I have it insatlled on my devellopement machine and I try to connect via the local network. It detects the server when I browse it but cant connect.
pls somebody help me i cannot instal sqlserver 2005 coz it cannot find the ipaddress. cannot connect to remote connection. i dunno whats lacking….
I believe
• Enable local and remote connections
was my answer.
Thanks you for great help.
Only Restart Server and login again…
THANK YOU Sir! It helps me VERY MUCH!!! Helpful, very Helpful INDEED! =)
Many thinks, number 3 got me.
Great post, the firewall exception was what I needed
Thanks a lot, that saved me stressing out about not being able to do my assignment.
Hi
I am having the problem to connect to SQL Server. I have developed an application in VB.Net one client computer where it is running. But on the other client it could not connect to Sql Server.
Fire wall is disabled on client. But enabled on server.
any one help me.
I was having the same issue with SQL 2005 Express on Windows 7. Issue was resolved by adding UDP port 1434 (SQL Browser) to my inbound and outbound firewall rules.
Thank you it helped me identify the problem. sqlservice was stopping.
Girum
In the SQL server configuration manager, there is an item called SQL server network configuration, under protocols, I selected the properties of the TCP/IP protocol. There is a tab “IP Addresses” there, and at the bottom of the list is an entry called IPAll. In my case the TCP port was empty. I entered the SQL standard port 1433 in there and I was able to connect.
This should work!
Hi. I had trouble with number 4. I assumed that once you have disabled the firewall, everything should work fine. Unknown to me, everyone will notice that in windows 7 and server 2008R2, disabling firewall does not stop the machine from blocking programs not on the allowed list.
Great help.. thanks.
awesome big clap!!!
thx a lot
Please help to me it is error how is it solove.
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) ”
75.Mohammed Mudassir – Sir you are my hero! I’ve tried all those steps mentioned none of it work except your method. I was able to connect remotely to my sql 2008 server withouth any error from other apps that connect to my sql 2008 server with no problem but for some reason why i try to connect to it from my system center service mananger server it doesn’t work but your suggestion is what makes it work. I’ve been working on this for over three days. Btw Iam not a dba guy, just starting to managing our sql environment.
I need to create a SQL Server 2008 Database programatically. And then add three tables in that database. And for connecting to sql server i need to use SQL Data client. I am unable to create a SQL Server Database programatically. Everyone it is showing how to create tables in a existing database. Please help!!
Thanks a lot in advance.
Apart from this, when migrating from development environment to another it can be simply that this error occurs due to the presence of incorrect connection string. I recommend going through all items that make use of connection strings. This is where programming best practices are best used. Basically “Use only one connection string”.
GO START -> ALL PROGRAMS -> SQL SERVER 2008/2005/2008 R2
->CONFIGURATION TOOLS->
->SELECT SQL SERVER CONFIGURATION MANAGER
->SELECT SQL SERVER NETWORK CONFIGURATION
->SELECT PROTOCOLS FOR MSSQLSERVER(YOUR INSTANT NAME)
->IN RIGHT TASK PANEL
->MAKE SURE MENTION BELOW:
DISABLE SHARED MEMORY AND VIA
ENABLE NAMED PIPE AND TCP/IP
—-MAKE SAME THING IN
->SQL NATIVE CLIENT 10.0(SERVER VERSION) CONFIGURATION – CLIENT PROTOCOLS
RESTART SQL SERVER(MSSQLSERVER [YOUR INSTANT NAME]) SERVICE
Hi
Can You Please help me? I get following error after Upgrading
SQL Server 2008 to SP2 , Unable to connect to SQL Server Named Insatnce after using SSMS . The Network Service Stops when I try to connect using SSMS
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: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)
Thanks for your help
Regards
Dave
Check the SQL services (Start –> Settings –> Control Panel –> Administrative Tools –> Services) and switch all “Disabled” services to “Automatic”
I hope this help you.
I get the same error msg after adding a login form in Microsoft Visual Studio 2008 but with SQL Server 2008. Confusing, there is somewhere it says “SQLExpress database file auto-creation error: ” while I don’t use Express version, I use Enteprise version. How can I solve this?
Wow Excellent Explanation.
Hi and tanx for your good recommendations!
Could you update this topic for sql server 2008 and windows7?
Thank you, it helped me connect to my SQL server
[...] to check: Resolving “A network-related or instance-specific error occurred while establishing a connecti… __________________ Florin Aparaschivei Iasi, [...]
I was getting numerous errors on my SharePoint farm after migrating to a new SQL 2008 R2 cluster (running on Windows 2008 R2). After about a month of troubleshooting, I decided to update my network card drivers on the new SQL cluster (Broadcom II). This fixed all the SQL “network-related” errors for me.
Issue with the connection to SQLExpress
This is the connectin string that I have given
SqlConnection myConnection = new SqlConnection(“Server=GINOJVISWAM/SQLEXPRESS;AttachDbFileName=D:/DotNet-Studies/SQLServer/WebSiteCheck/App_Data/PUBS.mdf;Database=PUBS;Trusted_Connection=False;”);
myConnection.Open();.
Is there any issue with the string. Any help would be appreciated.
when i am trying to send data into another computer sql server 2005 using windows forms application in .net ‘it shows error message “Login Failed fpr DEEPAK/guest” . please help me i am in trouble , please help me………
hi
great post
http://csharpektroncmssql.blogspot.in/2012/04/network-related-or-instance-specific.html
[...] syntaxes for e.g. ngmssql or ngmssql,1433 or dac:ngmssql, etc. ———– Ref: http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred... 1. Make sure your database engine is configured to accept remote connections • Start > All [...]
Hi,
Background:
SQLExpress 2012, Visual Studio 2012 running Win 7 Ent 64 bit, App in VB.Net
SQLconnStr = “Server=MYCOMPUTER\SQLEXPRESS;Initial Catalog=APPDB;Integrated Security=SSPI”
Both SQLExpress and Visual Studio are running on the same computer.
Problem:
I kept getting SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified whenever I tried to connect to the database.
Troubleshooting:
All network connections are enabled in the Server – named pipe, tcp, and shared memory.
Ran it with Win Firewall turned off – still the problem persist.
Ensure Win Firewall has port 1433 enabled – problem still persist.
Ping server – ok, sqlcmd -L – return the right server\instance
SQL Management Studio – connects with no problem
Ensure remote connection is enabled on the server
Install the SQLExpress 2012 on another machine running Win 8, ran same App – No problem
Very puzzled, can someone shed some light? Thanks in advance!
writing IP ADDRESS\MSSQLSERVER2008 solved my problem
Thanks, it is working..