Ошибка 40 не удалось открыть подключение к sql server - Исправление ошибок и поиск оптимальных решений проблем

TL;DR; Your SQL Server instance is using dynamic ports which is not working. Force SQL Server to use static port # 1433.

Complete Details: First of all this problem is more likely if you’ve a mix of default and named instance or named instances only(which was my case).

Key concept: Each instance of Microsoft SQL Server installed on a computer uses a different port to listen for incoming connection requests. Default instance of SQL Server uses port # 1433. As you install named instances then they will start using dynamic ports which is decided at the time of start-up of Windows service corresponding to named SQL Server instance.

My code was failing (with error code 40) to connect to the only named SQL Server instance that I had on my VM. You can try below possible solutions:

Solution # 1: Client code trying to connect to SQL Server instance takes help from SQL Server browser service to figure out port number at which your named instance is listening for incoming connections. Make sure SQL browser service is running on your computer.

Solution # 2: Check the port # (in yellow color) your named SQL Server instance is using from SQL Server configuration manager as shown in the snapshot below:

Use that port number explicitly in your connection string or with sqlcmd shown below:

sqlcmd -s mymachinename,11380 -i deleteDB.sql -o SQLDelete.txt

Solution # 3: Force your named instance to use port # 1433 which is used by default instance. Remember this will work only if you don’t have any default SQL Server instance on your computer as the default SQL Server instance would be using using port # 1433 already. Same port number can’t be uses by two different Windows services.

Mark TCP Dynamic ports field to blank and TCP Port field to 1433.

Change the port number in your connection string as shown below:

sqlcmd -s mymachinenameinstanceName -i deleteDB.sql -o SQLDelete.txt

sqlcmd -s mymachinename,1433 -i deleteDB.sql -o SQLDelete.txt

Note: Every change in TCP/IP settings requires corresponding Windows service restart.

Interestingly enough after resolving the error when I went back to dynamic port setting to reproduce the same error then it didn’t happen. Not sure why.

Please read below interesting threads to know more about dynamic ports of SQL Server:

How to configure SQL Server Port on multiple instances?

When is a Dynamic Port “dynamic”?

When to use a TCP dynamic port and when TCP Port?

I got leads to solution of my problem from this blog.

Источник

TL;DR; Your SQL Server instance is using dynamic ports which is not working. Force SQL Server to use static port # 1433.

Complete Details: First of all this problem is more likely if you’ve a mix of default and named instance or named instances only(which was my case).

My code was failing (with error code 40) to connect to the only named SQL Server instance that I had on my VM. You can try below possible solutions:

Solution # 2: Check the port # (in yellow color) your named SQL Server instance is using from SQL Server configuration manager as shown in the snapshot below:

Use that port number explicitly in your connection string or with sqlcmd shown below:

sqlcmd -s mymachinename,11380 -i deleteDB.sql -o SQLDelete.txt

Mark TCP Dynamic ports field to blank and TCP Port field to 1433.

Change the port number in your connection string as shown below:

sqlcmd -s mymachinenameinstanceName -i deleteDB.sql -o SQLDelete.txt

sqlcmd -s mymachinename,1433 -i deleteDB.sql -o SQLDelete.txt

Note: Every change in TCP/IP settings requires corresponding Windows service restart.

Interestingly enough after resolving the error when I went back to dynamic port setting to reproduce the same error then it didn’t happen. Not sure why.

Please read below interesting threads to know more about dynamic ports of SQL Server:

How to configure SQL Server Port on multiple instances?

When is a Dynamic Port “dynamic”?

When to use a TCP dynamic port and when TCP Port?

I got leads to solution of my problem from this blog.

Источник

TL;DR; Your SQL Server instance is using dynamic ports which is not working. Force SQL Server to use static port # 1433.

Complete Details: First of all this problem is more likely if you’ve a mix of default and named instance or named instances only(which was my case).

My code was failing (with error code 40) to connect to the only named SQL Server instance that I had on my VM. You can try below possible solutions:

Solution # 2: Check the port # (in yellow color) your named SQL Server instance is using from SQL Server configuration manager as shown in the snapshot below:

Use that port number explicitly in your connection string or with sqlcmd shown below:

sqlcmd -s mymachinename,11380 -i deleteDB.sql -o SQLDelete.txt

Mark TCP Dynamic ports field to blank and TCP Port field to 1433.

Change the port number in your connection string as shown below:

sqlcmd -s mymachinenameinstanceName -i deleteDB.sql -o SQLDelete.txt

sqlcmd -s mymachinename,1433 -i deleteDB.sql -o SQLDelete.txt

Note: Every change in TCP/IP settings requires corresponding Windows service restart.

Interestingly enough after resolving the error when I went back to dynamic port setting to reproduce the same error then it didn’t happen. Not sure why.

Please read below interesting threads to know more about dynamic ports of SQL Server:

How to configure SQL Server Port on multiple instances?

When is a Dynamic Port “dynamic”?

When to use a TCP dynamic port and when TCP Port?

I got leads to solution of my problem from this blog.

Источник

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

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:

Things to check:

</connectionStrings>

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.

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

Marked as answer by
Maggie Luo
Monday, January 21, 2013 3:02 PM

Источник

Problem

Unable to save template after making changes.

Symptom

Error message when attempting to hit save:

Template [Document] failed to save in DR
.
Problem description
Clarity.10026 Template [Document] failed to save.

System.Data.SqlClient.SqlException 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)

Cause

The cause for this issue is that the cube’s relational connection source is pointing to a relational database on a separate server environment.

Diagnosing The Problem

Reconfirm all connection/detail to ensure that both the relational database and olap cube connection strings are from the same server.

— Open up Analysis Services

— Select the database that is being used and expand

— Select Data Sources, and further expand.

— Select the datasource and right click and select properties.

— Examine the Connection String information to see if the relational database used is on the same server as Analysis Services.

Resolving The Problem

In this case, the issue lies in the connection string for the cube. Analysis Services was on one server and the corresponding relational database was on another. The customer forgot to repoint the database after moving the database from a production environment.

Edit the connection string to use the same server as the Analysis Service and this should correct the problem.

If the databases are required to be on separate servers, please refer to technote # 1583743

[{«Product»:{«code»:»SSMVH7″,»label»:»Clarity 7″},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»—«,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»7.2.1;7.2;7.0;6.2;6.1;6.0″,»Edition»:»Standard»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Источник