SQL Server Error 4060, often searched as 4060 error SQL Server or 4060 SQL Server error, displays messages such as “Cannot open database. Login failed” or “Server rejected the connection; Access to the selected database has been denied.”
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
Users often encounter SQL Server Error 4060 after a database migration, restore, or while connecting through an application or website. 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:
- Missing database user mapping
- Insufficient database permissions
- Incorrect database name in the connection string
- Database offline, restoring, or unavailable
- Login’s default database is unavailable
- Protocols disabled
- Firewall blocking SQL Server traffic
Lets us now look into each of these reasons in detail and the possible fix for them.
How to Fix SQL Server Error 4060
Lack of Permission
In many applications, this may also appear as a connection failed SQL Server Error 4060, even though the SQL Server instance is running correctly. 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.
The error can also occur if the SQL Server login exists, but it is not mapped to a user in the requested database. In such cases, SQL Server authenticates the login but cannot open the database. This is commonly seen after a database migration or restore, where the user mapping needs to be recreated.
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 the SQL Server Database Engine, at least one network protocol must be enabled. TCP/IP is the most commonly used protocol for local and remote connections. If TCP/IP is disabled, it can trigger SQL Server Error 4060. Named Pipes should be enabled only if your application or environment requires it.
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.
- After enabling the required protocol, save the changes before restarting the SQL Server service.
- Finally, from SQL Server Management Studio, restart the server instance.
Firewall blocking Access
Firewall restrictions can also trigger Error 4060. To fix it, we need to allow access through the firewall. By default, SQL Server uses TCP port 1433 for the default instance. If you are using a named instance, UDP port 1434 is used by the SQL Server Browser service. Make sure these ports are allowed through the firewall if remote connections are required. Generally, a closed port is the most common restriction that a firewall places. To fix it, we need to open the required port in the firewall for the client.
Another possible reason is that the requested database is unavailable. Verify that the database exists on the SQL Server instance and that it is online. If the database was recently migrated or restored, confirm that the restore completed successfully and that the database name in the connection string matches the actual database name.
[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”.
