First published on MSDN on Mar 31, 2007
This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message.
First, take a look at below MSDN forum link lists about this topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1287189&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1334187&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1292357&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1
The various causes fall into five categories:
1 Incorrect connection string, such as using SqlExpress.
2 NP was not enabled on the SQL instance.
3 Remote connection was not enabled.
4 Server not started, or point to not a real server in your connection string.
5 Other reasons such as incorrect security context.
Let’s go throught the detail one by one:
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. 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 filesmicrosoft sql servermssql.1mssqllog, notepad ERRORLOG, see whether Server is listening on NP. You should see «Server named pipe provider is ready to accept connection on [
.pipesqlquery
] or [.pipemssql$<InstanceName>sqlquery]»
3) Notice that «sqlquery» 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 «sqlquery1», then you would see in the errorlog that server listening on [
.pipesqlquery1
], 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:
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-Issu…
IV. Server not started, or point to not a real server in your connection string.
Check out:
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.
Summary, give checklist:
1. Is your target server started?
2. Is your target server listening on NP? Which Pipe?
3. Has your client enabled NP? Use the same pipe to connect as Server?
4. Are you making local connection? If so, what is the instance, default or remote?
5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.
6. Did you enable remote connection? Firewall? IPSec? «File and Printer Sharing» opened? Can access server?
7. Can you make basic connection by using <servername> or <servername><instancename>? Use sqlcmd or osql.
8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?
MING LU
SQL Server Protocols
Disclaimer: This posting is provided «AS IS» with no warranties, and confers no rights
- Remove From My Forums
Named Pipes Provider, error: 40 — Could not open a connection to SQL Server
-
Question
-
This was originally posted in the SSIS Forum, but a member of the IS team suggested it be moved here. «Most recently I got this error (Named Pipes Provider, error: 40 — Could not open a connection to SQL Server) so does this mean that SQL Server is still trying to use named pipes even if I only have TCP/IP enabled in SQL Server Configuration Manager?»
I’m having the same issue, and here’s our scenario:
- Installed SQL Server 2005 Developer Edition on a machine with WinXP SP2 and enabled remote connections over TCP/IP
- Installed SQL Server 2005 Standard Edition on a machine with Win2003 SP1 (remote connections over TCP/IP enabled by default)
- Attempted to ‘Copy Database’ from Developer Edition TO Standard Edition using ‘Detach and Attach‘ method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server
- Attempted to ‘Copy Database’ from Developer Edition TO Standard Edition using ‘SQL Management Objects‘ method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server
- Reinstalled Standard Edition on the Win2003 SP1 machine
- Checked all the settings on both machines several times, restarted services etc., and read every post I could find referencing the error.
- Same error
- Lost my last hair
Thanks in advance,
Steve
Answers
-
OMG u guys saved me 20000 hrs of work. I had the same problem and now it works.
Thanks for all the help;)
Also, if this still doesnt fix ur problem , try these tips- Enable the TCP/IP protocol using the Surface Area Configuration Utility
- Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
- Make sure the SQL Server browser is started. Note this step
is optional. It is possible to set the SQL Server instance to use a fixed IP address — but this is
non-standard for named instances - Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine.
This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the
windows firewall. - Note: In order to get things to work. You might need to completely reboot the
server machine after making the changes. There have been reports (thanks P.C.) that starting and stopping the
SQL Server and Browser software is not enough.
resource:www.datamasker.com
-
Marked as answer by
Wednesday, March 4, 2009 10:10 PM
-
The error is reported by client library. While your server is listeing on remote TCP, client will still try TCP and NP connection in order. So the error client behavior is expected. From what you have described, I believe that even though you enabled the remote TCP connection on the XPSP2 machine, you didn’t make the TCP listening port an exception of XPSP2 personal firewall. You should follow steps below to resolve this issue.
1. check the SQL Server Errorlog to make sure SQL Server is now listening on TCP/IP and confirm which port it is listening on. Usually 1433. In the Errorlog, you will see several lines that discuss what SQL Server is listening on. Below is an example:
2006-01-04 01:41:07.65 server SQL server listening on 10.254.1.150: 1433. <—Shows the IP Address and the port.
2006-01-04 01:41:07.65 server SQL server listening on 127.0.0.1: 1433. <—Shows another IP Address and the port.
2006-01-04 01:41:07.69 server SQL server listening on TCP, Shared Memory, Named Pipes.
2006-01-04 01:41:07.69 server SQL Server is ready for client connections
2, Make sure on Windows XP that the firewall is not blocking that port.
3, go to your client machine and run the client network configuration tool (cliconfg.exe) Make sure TCP/IP is enabled, click properties and make sure the port number is the same one as SQL Server is listening on. Here you can enable NP or disable client NP as well.
Once both the client and the server are using TCP/IP with the same port number and the firewall on server machines is not blocked, you should be able to connect.
Hope this helps.
-
Marked as answer by
John C GordonMicrosoft employee
Wednesday, March 4, 2009 10:08 PM
-
Marked as answer by
-
From the error message, your scenario is broken on connection stage and I would like to know if you have tried 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 -Stcp:servernameinstancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.
-
Hi Steve,
I just ran the Copy Database Wizard myself on my SQL Server 2005 and I suspect the problem you are running into is due to the account that SQL Agent runs under. You need to create a SQL Agent Proxy account that can login to the remote machine and then use this account. By default the SQL Agent service account does not have remote access.
When you get to the page thay says: «Schedule the Package» at the bottom you will see the «Integration Services Proxy Account» selection, you need to create an account that can login to the remote machine (standard account is easiest) and then select this account rather than «SQL Server Agent Service Account»
See ->
How to: Create a Proxy (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms190698.aspx
Matt
-
Marked as answer by
John C GordonMicrosoft employee
Wednesday, March 4, 2009 10:09 PM
-
Marked as answer by
In this SQL Server tutorial, we will learn how to resolve the “provider: named pipes provider, error: 40 – could not open a connection to sql server” error. So, in this tutorial, we will discuss the root cause of this error and will also discuss how to solve the error, could not open a connection to sql server error 40.
Recently, I have encountered this error while connecting to the Database Engine using SQL Server Management Studios. The complete error message is shown in the image below.
Cannot connect to
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. error 40 – could not open a connection to sql server.
Let’s first discuss some of the main reasons behind this error message.
- We might be using wrong server name to connect due which the failure has occured.
- The SQL Server Service is not running properly or it might have wrong default settings.
- The SQL Server do not have permission to allow an remote connection.
- The host machine might have wrong firewall settings.
Don’t worry if you don’t know how to resolve these issues to overcome this error. Here are some of the steps that we can follow to overcome these issues.
Fix 1: Use Correct Server Name
Many times the main reason behind this error is the use of the wrong server name while connecting. To connect to the Database Engine in the SQL Server, we need to provide some login details such as username, password, and server name.
Now, it is important to use the correct server name. By default, the server name is in the following format – “ComputerNameInstanceName“.
In this format, the ComputerName is the name of the machine or computer. Now, if you are using Windows 10 OS, you can get this name by opening the About page in your System settings. An example for this is shown below.
Next, in the server name is InstanceName which is the name of your SQL Server instance. Now, to get the instance name, we need to open the Services setting in our system.
And from the list, we have to find the SQL Server Service. The instance name is there in the brackets of the service name. The example is demonstrated below.
In our case, the name of the instance is SQLEXPRESS. So, the server name will be similar to “DESKTOP-XXXXSQLEXPRESS“.
Fix 2: SQL Server should be up and running
The second root cause for this error can be that your SQL Server instance is not running. Now, to check wheater the instance is running or not. First, we need to open the “SQL Server Configuration Manager“.
In SQL Server Configuration Manager, first, click on SQL Server Services then, a list of services will appear on the right pane. From the list check the state of the “SQL Server” service. And we can also right-click the service and click on “Start” to start the service.
Next, we should also confirm whether the SQL Server Browser is running or not in the same way.
Fix 3: Enable TCP/IP in SQL Server Configuration
Whenever multiple SQL Server instances are connected across a network, they all use TCP/IP for communication. So, to resolve this error, we have to confirm whether the TCP/IP is enabled or not. For this, again we will use the “SQL Server Configuration Manager“.
In SQL Server Configuration Manager, first, expand the “SQL Native Client 11.0 Configuration” then, click on “Client Protocols“. A list of protocols will appear on the right pane. From the list check the state of TCPIP. And we can also right-click it and click on the “Enable” option to enable it.
Next, we can also confirm whether TCPIP is working on the default ports or not. For this, first, right-click TCPIP and click on Properties. Next, from the General category, we can confirm whether the default port is 1433 or not.
Fix 4: Allow Remote Connections
Other than enabling the TCPIP, we have to enable the remote connection settings from the SQL Server properties. For this task, we can use SQL Server Management Studio. Here are steps to enable the property using SQL Server Management Studio.
- From the Object Explorer, right-click on the server name and click on “Properties” option.
- Next, open the Connection properties section and tick mark the “Allow remote connections to this server” option. In the end, clcik on “OK” to save changes.
Fix 5: Allow SQL Server in Firewall Settings
The Windows Firewall is quite effective at safeguarding the operating system from various dangerous threats. By default, the firewall prevents numerous ports and services from running.
To overcome this issue, we can add a firewall exception for TCP/IP ports 1433 or 1434. So, the SQL Server can run without any issue.
The steps to add the exception in the Windows firewall are as follows.
- First, search for “Windows Defender Firewall with Advanced Security” in our start and open it.
- Next, from the left pane click on “Inbound Rules” and then, click on “New Rule“. It will open a new “Inbound Rule Wizard” window.
- In the new window, first, select “Port” and then click on “Next“. After this, select the “TCP” option and specify the port number as 1433.
- Next, we need to select the “Allow the connection” option and click on the “Next” button.
- Next on the Profile page, tick marks the options as per your requirements and again click on the “Next” option.
- On the last Name page, specify the name and description for the exception and click on the “Finish” button.
With this, we have added the new firewall which will allow having a remote connection in SQL Server.
By following all the given solutions, we can resolve the error and we can easily connect to the SQL Server instance.
So, in this tutorial, we have learned how to resolve the “provider: named pipes provider, error: 40 – could not open a connection to sql server” error. In this, we have discussed the root cause of this error and also the possible solution for it.
You may also like to read the following SQL Server tutorials.
- Full-text search in SQL Server
- SQL Server find text in stored procedure
- SQL Server check user permissions on table
- How to execute stored procedure in SQL Server
- How to check if SQL Server is running
- How to get list of users in SQL Server
- What is a stored procedure in sql server
- SQL Server create stored procedure
- Remote procedure call failed in SQL Server
- msg 3609 the transaction ended in the trigger
- Create Foreign Key in SQL Server
I hope this will help to fix the below errors:
- named pipes provider, error: 40 – could not open a connection to sql server
- a network-related or instance-specific error in sql server 2014 error: 40
- a network-related or instance-specific error in sql server 2019
- named pipes provider: could not open a connection to sql server (53)
- could not open a connection to sql server error 40
- error 40 – could not open a connection to sql server
- error 40 could not open a connection to sql server error 53
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
Named Pipes Provider, error: 40 — Could not open a connection to SQL Server :
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 filesmicrosoft sql servermssql.1mssqllog, notepad ERRORLOG, see whether Server is listening on NP. You should see «Server named pipe provider is ready to accept connection on [ \.pipesqlquery ] or [\.pipemssql$<InstanceName>sqlquery]»
3) Notice that «sqlquery» 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 «sqlquery1», then you would see in the errorlog that server listening on [ \.pipesqlquery1 ], 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 -Stcp
ervernameinstancename. 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.
Check the following article:
If you get the following SQL Server 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 machinenameinstancename
• Check your connection string as well
<connectionStrings>
<add name=»SampleConnectionString» connectionString=»Data Source=machinenameinstancename;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 ServerMSSQL.xMSSQLBinn, 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.
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.
-
Marked as answer by
Maggie Luo
Monday, January 21, 2013 3:02 PM
Check the following article:
If you get the following SQL Server 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 machinenameinstancename
• Check your connection string as well
<connectionStrings>
<add name=»SampleConnectionString» connectionString=»Data Source=machinenameinstancename;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 ServerMSSQL.xMSSQLBinn, 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.
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.
-
Marked as answer by
Maggie Luo
Monday, January 21, 2013 3:02 PM