Sql server error 10060

I can’t access my MySQL database remotely, it’s throwing “cannot connect to mysql server (10060)” error. Please help.

That was a recent support ticket received at our Server Management Services department where we manage our customer servers.

Website owners usually face this error due to firewall restrictions, MySQL service downtime, and more.

Today, we’ll discuss the top 5 reasons for this error and how our Server Administrators fix them.

“Cannot connect to mysql server (10060)” error – Causes and Fixes

Customers usually receive this error when they try to access the MySQL server remotely.

“Cannot connect to mysql server (10060)” error means that the connection established failed because the host connected has failed to respond in time.

Let’s now see the main reasons for this error and how our Server Experts fix them.

1) Service downtime

One of the common reasons for this error is MySQL service not running on the server. And, the reason for service downtime can be due to heavy traffic spikes, resource outages, DDoS attacks, and more.

How we fix?

So, our Support Engineers confirm whether the MySQL service is running in the server with the netstat command.

netstat -plan | grep :3306

If the service fails or doesn’t respond, we’ll kill the dead process and restart the service once again. In addition to that, we check the server logs to find the reason for the downtime and fix it permanently.

[Do you face intermittent MySQL downtime issues? Our experienced Server Administrators can fix this for you.]

2) MySQL configuration issues

By default, MySQL listens for connections only from localhost.

bind-address 127.0.0.1

So, if MySQL only supports local connections, users will receive “Cannot connect to mysql server (10060)” error when establishing a remote MySQL connection.

How we fix?

Firstly, our MySQL experts check the MySQL configuration file /etc/mysql/my.cnf and confirm that MySQL remote access is enabled.

If not, we change the value of bind-address to 0.0.0.0 in /etc/mysql/my.cnf to accept all connections.

bind-address 0.0.0.0

Alternatively, we comment the bind-address parameter to enable remote connections. Similarly, in some cases, we give the bind-address as the IP address of the server where MySQL service listens.

bind-address database-IP

Here, MySQL will only listen connections to the server’s IP. Most importantly, we restart the MySQL service to reflect the changes.

We always recommend taking the backup of this file before making any modifications. Because, a small typo error in this file can break your MySQL service.

[Do you want to enable Remote MySQL access on your server? Click here, one of our Server Admins can do this for you.]

3) Firewall restrictions

The next possible cause is MySQL port 3306 not opened in the server firewall. In other words, MySQL server can’t accept any connection from the outside world due to firewall.

How we fix?

In such cases, our Support Engineers temporarily disable the firewall to determine whether the firewall is the culprit. Later, we check the MySQL port from /etc/mysql/my.cnf and use the telnet command to check the external connectivity to MySQL port.

telnet xx.xx.xx.xx 3306

If any firewall rules interfere with connectivity, we set the firewall rules to allow connections on the MySQL port.

For example, if MySQL server runs on Windows, we whitelist the MySQL port from Start > Control Panel > System & Security > Windows Firewall > Exceptions > Add Port.

Similarly, in Ubuntu servers we open the MySQL port in UFW firewall using the below command.

ufw allow 3306

4) No privileges to the remote user

Similarly, this error can occur if the MySQL user doesn’t have enough privileges to remotely access the MySQL server.

In such cases, users will get an error “cannot connect to mysql server (10060)”.

How we fix?

Here, our Hosting Engineers grant the user proper privileges to remotely access the database. Most importantly, we whitelist the remote hostname or IP address.

GRANT ALL ON *.* to 'user'@'host_name' identified by 'Password';
FLUSH PRIVILEGES;

Because, if a user connects as root user from a different machine, MySQL server can easily identify whether it’s the root user in the local server or remote server.

For example in cPanel servers, we whitelist the remote host or IP address from cPanel > Databases > Remote MySQL .

[Worried about MySQL user permissions. One of our MySQL Experts can help you here.]

5) Wrong MySQL connection string

Last but not least, wrong connection string used to access the MySQL server remotely may lead to this error. A small typo in the MySQL server name, database name, username, port, etc. can block the connection to the MySQL server.

How we fix?

In such cases, our Support Experts get the MySQL connection string details from the client. After that, we configure an ODBC connection to the server. If that works, we compare the new string with the connection string. And, if any mismatch is noted, we’ll correct it immediately.

Conclusion

In short, error “cannot connect to mysql server (10060)” can happen due to firewall restrictions, MySQL configuration issues, MySQL service downtime, and more. Today, we’ve discussed the top 5 reasons for this error and how our Support Engineers fix them.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

LinuxData.RU

Причин возникновения ошибки 10060 при подключении к серверу MySQL может быть несколько:

1. Проверьте настройки межсетевого экрана (файрвола). В них обязательно должен быть открыт порт 3306 по протоколам TCP и UDP.
2. Проверьте, настроен ли ваш MySQL-сервер на работу по сети. У вашем конфигурационном файле не должно быть директивы skip_networking.
3. Проверьте, что ваш MySQL-сервер слушает нужный IP адрес, т.е. подключение к серверу возможно с тех IP адресов, с которых вы производите подключение. Иногда сервер слушает только внутренний сетевой интерфейс локальной сети, а попытка доступа осуществляется с другого, например, внешнего интерфейса.
4. Проверьте, что ваш MySQL-сервер слушает именно нужный порт 3306, а не какой-то другой, иначе в клиентской программе нужно указывать именно этот, другой порт.
5. Ну и наконец, что MySQL-сервер просто запущен

Простейшим способом убедиться, что пункты 2-5 работают, является выполнение команды:

в результате которой вы должны получить, что-то такое:

в данном случае, MySQL-сервер слушает на порту 3306 соединения по любому IP-адресу (0.0.0.0). Также мы видим, что сам MySQL сервер запущен и его идентификатор процесса: 2472.

• Войдите или зарегистрируйтесь, чтобы получить возможность отправлять комментарии
• 9164 просмотра

Copyright(C) 2014. Все материалы сайта, если не указано иначе, распространяются под лицензией CC-BY-NC-ND

Источник

Remote MySQL connection fails (10060)

When I am trying to connect to a MySQL database from a remote computer I get a prompt saying: Connection Failed: [HY000] [MySQL][ODBC 5.1 Driver]Can’t Connect to MySQL server on ‘XXX.XXX.XX.XX’ (10060)

I have created a user account in the MySQL Administrator and added a host to enable remote access, I have also made an exception for my Windows Firewall on port 3306 but the connection still fails.

What is the problem?

8 Answers 8

I suppose your mysql server is running on Windows. thus open a DOS window and type:

You should find a row like this:

TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

This tell you that mysql server is running and listening on TCP port 3306.

Another test you can do is to telnet (from another pc) to your mysql server:

telnet ip_mysql_server 3306

This should open a telnet session:

The error 10060 means that you are not allowed to access the web server remotely. Therefore

1. Make sure that you are using the correct IP-address of the MySQL server.
2. The rest of the parameters of your connection are the same.
3. Use the % wildcard for the host part of the user account to allow connect from any host (see Adding User Accounts article).

First of all, it’s a very bad idea to have a worldwide open port to your database server. Especially if it’s on the MySQL default port. You’re just inviting people to try to break in to your database.

Have you tried using a port scanning tool like nmap on your from your remote computer to scan the database server? It will tell you if the port is open with nmap -PN -p 3306 XXX.XXX.XX.XX ?

1) iptables access

2) my.conf

3) mysql

4) mysqld

Check the mysql configuration: /etc/my.cnf

and comment out:

Now it will be listening to TCP/IP ports, however you have usually blocked direct access to MySQL via the firewall anyway, so it shouldn’t be a problem. You can now SSH in over TCP/IP as » localhost » to the MySQL server.

e.g., Windows bat file, PuTTY tunnel:

If you have XAMPP/MySQL running on your local machine, change the above port to 3307 , ie:

But that’s not a good idea. If you are going to access the database from another remote server, why don’t you place a file inside your site with some security such htpasswd protection or some authentication module.

If you are going to get data of a mysql server to another server, be precise with the requirement.

If it’s just for data synchronization, go for db server concepts.

The likely culprit that people often forget is:

This step is needed to be able to connect from the computer 123.456.789.123 More information and a more complete guide can be found at Holicreature’s link.

I had exactly the same issue communicating between a MSSQL Server database on one (Win2008) server and a MySQL database on another.

I had already created an ODBC connection between one MSSQL Server and the MySQL server a couple of years ago; primarily to allow me to create a Linked Server object on the MSSQL Server.

When trying to create an ODBC connection from a different server I receive the same error:

After following some of the suggestions in the answers found here, I decided to check the firewall rules on the (Win2008) server hosting MySQL.

I had an inbound rule set up that locked port 3306 down to be accessible only from a list of IP addresses. Once I added my latest server’s IP to the list, I was able to connect.

Источник

How to fix Mysql Connector Error 10060 —> Error?

Click here follow the steps to fix Mysql Connector Error 10060 and related errors.

To Fix (Mysql Connector Error 10060) error you need to follow the steps below:

Instructions

• Hi all,

  I am trying to setup an ODBC connection to a SQL Server,

  Control Panel —> Admin Tools —> ODBC —> User DSN —> Type in Host Name —> SQL Authentication

  Then I get this error,

  Connection Failed:

  SQL State: ‘01000’

  SQL Server: Error: 10060

  [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())

  Connection Failed:

  SQL State: ‘08001’

  SQL Server Error: 17

  [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

  Things I have tried:

  1) Enabled TCP/IP and Named-Pipes

  2) Did the same on the server machine hosting SQLServer, able to log in with the same host name, and same SQL Authentication.

  3) I can ping the server

  4) I can Remote Desktop Connection using my local computer

  Can anyone help?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  Sounds like you need to enable remote connections. If value_in_use is 0 for this:

  SELECT name,

  value_in_use

  FROM sys.configurations

  WHERE name = N'remote access'

  Then you’ll want to log into the server again, from the local server, and run this:

  EXEC sys.sp_configure

  N'remote access',

  N'1'

  GO

  RECONFIGURE WITH OVERRIDE

  GO

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  I checked the value, it was set to 1.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  You posted in a SQL 2008 forum, so to be sure, is it a SQL 2008 or 2008R2 instance we’re talking about? If so, your error message says DBNETLIB which points to you may be using a SQL 2000 driver. Do you have the SQL Native Client installed? If so, you should choose that driver when setting up the DSN, i.e. «SQL Server Native Client 10.0», not «SQL Server». The driver version (scroll right in the dialog where you pick the driver) should start with «2009.».

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  Actually my SQL Server is 2012 (I guess I posted on the wrong section, should I remove this post?), I just checked my ODBC connection, it has

  SQL Server 2000.85.1132.00

  There is no SQL Server Native Client, what should I do next?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  No worries on the 2012 thing, now that we know, it’ll be easy to get you some help.

  You need the SQL 2012 Client Development Tools. You can get them here: SQL SERVER 2012 EXPRESS EDITION

  I think you could go with SQL Server Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express). If you do not want to install a local instance of the database engine you can get the client tools only by choosing SQL Server Management Studio Express (Tools only).

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  In my company, we have this offered to be downloaded.

  Microsoft SQL Server Native Client 10.0 MUI

  Is this enough?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  I do not think you can connect to a SQL 2012 instance with SSMS 2008.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  I see you mentioned the Native Client 10.0, not SSMS…you may be able to use the SQL 2008 Native Client to create an ODBC DSN that will connect to 2012, you just may not have access to all connection-functionality that 2012 offers.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  Unfortunately, when I installed the SQL Server Express with Tools, it popped up an error message saying that my computer does not meet the minimum requirement for SQL Server 2012.

  So, I downloaded the SQlocalDB.msi file, it said I must install a Windows service pack that contains a newer version of the Windows Installer Service.

  So..in essence…if there are no other ways, I willl have to to install 10.0?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  Bummer…the operating system requirements for SQL 2012 are the following:

  > Windows Vista SP2 or above

  > Windows 7 SP1 or above

  > Windows Server 2008 SP2 or above

  > Windows Server 2008R2 SP1 or above

  If you do not have one of those, then you could try installing the SQL 2008 Native Client (10.0) that you are offered in your org but that would only give you the ability to create an ODBC DSN or connect through an app language using ADO.NET or OLE DB. The Native Client does not include the Management Studio or Data Tools IDEs.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  Sigh, I will get back to you after they finish installing for me (which may take a few days). Thank you for all your help so far!

• Alan Kwan

  SSCommitted

  Points: 1722

  I tried connecting with my newly installed SQL native Client 10.0, No luck 🙁

  New error message though.

  Connection failed:

  SQLState: ‘08001’

  SQL Server Error: 10060

  [Microsoft][SQL Server Native Client 10.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed ot respond.

  Connection failed:

  SQLState: ‘HYT00’

  SQL Server Error: 0

  [Microsoft][SQL Server Native Client 10.0]Login timeout expired.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  it could be a lot of things…but it sounds like your SQL connection attempt is not even making it to the SQL instance.

  For starters, verify:

  1. You are providing the correct server name and instance name (if applicable)

  2. Your server name resolves to the correct IP address

  3. If you are using an instance name make sure the SQL Browser Service is running on the server

  4. If you are using an instance name and the browser service is running, make sure you have network access from your machine to UDP port 1434 on the server

  5. Make sure you have access from your machine to the port number of the SQL instance. You can use portqry for that. If you are not sure which port the SQL Server is listening on you can see it in the SQL Server Error Log as an entry that starts with «Server is listening on…» (that’s the message logged in SQL 2008…I do not have a 2012 instance handy to verify that is what will be logged in 2012 so look for similar if not found).

  Hopefully one of those will get things going for you!

  There are no special teachers of virtue, because virtue is taught by the whole community.
  —Plato

• Alan Kwan

  SSCommitted

  Points: 1722

  1) 100% sure the instance name, I just double-checked by going to SSMS —> properties of the server —> name

  For starters, verify:

  2) I can remote connect with the IP I have been using.

  3) SQL Browser is on

  4) How do I do this?

  5) How do I do this?

  By the way, when I connect the through ODBC, the server drop down list is blank. Not sure if this is relevant.

[SQLSTATE 42000] (Error 10060)  OLE DB provider «SQLNCLI10» for linked server

Error Message
Executed as user: MyServerMyaccount. TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [SQLSTATE 42000] (Error 10060)  OLE DB provider «SQLNCLI10» for linked server «» returned message «Login timeout expired». [SQLSTATE 01000] (Error 7412)  OLE DB provider «SQLNCLI10» for linked server «MYSERVER» returned message «A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.». [SQLSTATE 01000] (Error 7412).  The step failed.
Open Component Services via

 Run then dcomcnfg

To resolve this problem, follow these steps:

1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
3. Right-click My Computer, and then click Properties.
4. Click the MS DTC tab, and then click Security Configuration.
5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
6. Under Network DTC Access, click Network Transactions.

   Note If you installed Service Pack 1 for Windows Server 2003, you can click Allow Inbound and Allow Outbound.

7. Make sure that DTC Logon Account is set to NT AuthorityNetworkService.
8. Click OK.
9. In the message box, click Yes to continue.
10. In the DTC Console Message dialog box, click OK.
11. In the System Properties dialog box, click OK.
12. Reboot the computer for these changes to take effect.

    Note In some cases, you must start the DTC service before you start the SQL Server service so that the linked server distributed queries work well.

13. Add above DTC configuration on both server
14. You need  add XACT_ABORT ON on your stored procedure