SQL server error 40 occurs while we try to connect to the SQL Server.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix SQL related issues.

In this context, we shall look into the steps to fix this sql error.

What triggers SQL server error 40 ?

Here are the different causes of this error to occur.

i. Connection to the server instance fails.

ii. Using wrong default settings.

iii. SQL Server doesn’t have enough permissions to allow the remote connection.

iv. The problem in firewall settings.

How to fix SQL server error 40 ?

Follow the steps given below to resolve this error.

1. If the SQL Server instance is not running.

Now go to Services option and click on SQL Server installed in your system. If its status is not ‘Started’, then start it by clicking on the START option.

2. Enabling TCP/IP

Ensure that the TCP/IP is enabled on the server. In case, if it is not enabled then follow the below steps to enable it;

i. First, click on the Configuration Manager of SQL Server.

ii. Check if the TCP/IP port status is enabled or not. If not, then you would need to enable it and then click on the status to change the port properties.

iii. Now enter the Default Port number 1433 and click on the OK button.

3. Allow Remote Connections enabled under Connections in SQL Server Properties.

Below are the steps to enable it;

i. First, open the SQL Management Studio and then right-click on the Server Name.

ii. Now, click on the Server Properties. Then under the Connections Options, check the box of ‘Allow remote connections to this server’ and then click on the OK button.

4. Allow SQL Server in Firewall Settings:

Add a Windows Firewall exception on the server for SQL TCP ports 1433 and 1434, so that SQL Server will run.

Here are the steps to do the same;

i. First, go to Control Panel then System and Security, or directly search it on your system search as Windows Firewall.

ii. Now, click on the Firewall. Here you can see the Action tab as Allow for Firewall. You can change it through SQL Server Properties to allow or block.

5. Verify whether the SQL server is reachable using ping

Ensure that you are able to ping the physical server where SQL Server is installed from the client machine. If you are not able to then you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance.

6. Verify whether SQL Server Browser service is running or not

It is important for the SQL Server Browser service to be running well. So make sure that it is running well.

However, if you have installed a SQL Server named instance and not configured a specific TCP/IP port then the incoming requests will be listening on a dynamic port.

In order to resolve this, you would need to have the SQL SQL Server Browser service enabled and running. Moreover, you can check the status of browser service using either SQL Server Configuration Manager or the SC command as below:

sc query sqlbrowser

Also, make sure to add an exception for sqlbrowser.exe in the firewall.

7. Confirm that you are using the right SQL Server instance name

Ensure that you are using the right instance name. Whenever you connect to a default instance, machinename is the best representative for the instance name. However, when you connect to a named instance such as sqlexpress, you would need to specify the instancename as machinename\instancename where you enter the SQL Server instance name for instancename.

8. Verify the port number that SQL Server is using

In order to verify the port number, locally connect to the SQL Server and check the error log for the port entry. You can verify this in SSMS. 

Below are the steps for the same;

i. Access the Management in SSMS

ii. Now, go to SQL Server Logs and select the Current log.

iii. Scroll down to the bottom which will be the first entry in the error log and look for entries to check whether Named Pipes and TCP/IP are enabled and the port used for TCP/IP.

[Need urgent assistance in fixing SQL errors? – We're available 24*7. ]