SQL error 4060 “Server rejected the connection; Access to the selected database has been denied” is a common error usually seen after website migration in Windows servers.
The reasons for this error include permission issues, firewall restrictions, etc.
As a part of our Server Management Services, we help our customers to fix SQL related errors regularly.
Let’s today discuss the possible causes and fixes for this error.
What causes SQL error 4060?
Often after a migration, some users notice SQL error 4060 while applications or websites. This error states that the “Server rejected the connection; Access to the selected database has been denied“. As the error message states, access to the database was denied as the server rejected the connection.
To list out, the common reasons for this error include:
- No permission to access the database using the credentials
- Protocol not enabled
- Firewall blocking access
Lets us now look into each of these reasons in detail and the possible fix for them.
Lack of Permission
The most common reason for the error 4060 is the lack of permission for the user to access the database. After migration, it is important to ensure that all the users in the previous environment are set up in the new environment as well with the required privileges. However, this is often a frequently missed point.
Correct permissions can be granted to the users with a proper TSQL command. A general format of the command is
GRANT <permission> [ ,...n ]
TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
[ AS <database_principal> ]
For instance, to grant SHOWPLAN permission on the Example database to application role Moderator, TSQL command to be used is:
USE Example;
GRANT SHOWPLAN TO Moderator;
GO.Similarly, we could grant the required privileges to the user after comparison with that of the permission in the old environment.
Protocol not enabled
To connect to SQL Server Database Engine you must have a network protocol enabled. If those are not enabled, it may trigger the error 4060.
The steps to enable them are:
-
First, select Start, and in your list of programs, select SQL Server Configuration Manager.
-
Next, navigate to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <machine instance>.
-
Then, double-click Named Pipes. The Named Pipes Properties screen appears.
-
From Enabled, select Yes. Then click OK.
-
Similarly to enable TCP/IP, we can select TCP/IP as in step 2. The TCP/IP Properties screen appears.
-
Next, on the Protocol tab, ensure Yes is selected for Enabled. On the IP Addresses tab, ensure that Yes is selected for the appropriate IP Address. Also, ensure that the appropriate TCP Port is indicated. Then click ok.
-
Finally, from SQL Server Management Studio, restart the server instance.
Firewall blocking Access
Firewall restrictions can also trigger the error 4060. To fix it, we need to allow access in firewall. Generally, a closed port is the most common restriction that Firewall places. To fix it, we need to open the port in the firewall for the client.
Apart from the common reasons mentioned above, some other reasons can also trigger this error. In a rare case scenario, this error could also indicate that the corresponding database is not copied over to the new environment. Thus it is not a bad idea to cross-check if the database is actually copied over before we go for any other complex fixes.
[Need help to fix SQL errors? We are available 24×7]
Conclusion
In short, lack of permission for users to access database, firewall restriction etc trigger the error 4060. Today, we discussed how our Support Engineers fix the error “Server rejected the connection; Access to the selected database has been denied”.
0 Comments