Connections to the database failing with error: 'TCP Provider: No connection could be made because the target machine actively refused it.

Question

There is a SQL Server Database connecting to a .NET application but producing the error 'TCP Provider: No connection could be made because the target machine actively refused it' exceptions. It was working earlier but there was a change to the password. It works connecting from SSMS. Any suggestions?

Answer 1

@lawrencet-msft Thanks for using Microsoft Q&A !!

There could be multiple different causes of this error as stated here however, this can also happen if there were several attempts made to login to the database with incorrect credentials like empty passwords (error 18456, state 123), invalid user (error 18456, state 5) or wrong password (error 18456, state 8) being passed to the SQL connection string while establishing the connection. Please refer to the Error documentation for error state and description.
Due to this high number of login failures from the same client IP, the DoS guard kicks in and block all connections from that IP to the database for a few minutes (error 18456, state 113) and thus you start getting the stated error. For more information about DoS Guard, please check this documentation.

You cannot disable DosGuard, but if It is being triggered by a client error that you cannot control, you can suppress DosGuard for a given client IP by explicitly adding the IP to the Azure SQL Server firewall rules or, when using Service Endpoints, adding the subnet explicitly in the firewall.
The recommendation for now is to validate the connection strings you are using to connect to SQL server, to make sure there are no empty passwords. If you are using an application which connects to a SQL Server instance it would be beneficial to add the application name in the connection string, so it’s easier to identify which application is causing the errors when looking in the audit logs. For more information, please check the documentation here.

You can see the login errors in the audit log. It would be easier for you if the audit information and diagnostics are sent to a Log Analytics workspace in these cases since there would be a high number of login failures.
In this blog post, there is a step by step on how to set up audit and diagnostics to Log Analytics and also an example of a Kusto query to get the failed logins:

AzureDiagnostics
| where TimeGenerated >= ago(5d) //Events on last 5 days
| where action_name_s == "DATABASE AUTHENTICATION FAILED"
| extend additional_information_xml=parse_xml(additional_information_s)
| extend Error_Code=additional_information_xml.login_information.error_code
| extend Error_State=additional_information_xml.login_information.error_state
| project
TimeGenerated, event_time_t,
ResourceGroup, LogicalServerName_s, database_name_s,
session_id_d, client_ip_s, application_name_s, session_server_principal_name_s,
Error_Code, Error_State
| order by TimeGenerated desc

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.