How to fix database MSSQL server error 4060 ?
If you’re using a SQL database, then you might encounter the SQL Error 4060 after a database migration. This is one of the many SQL Errors admin have to deal with. SQL Error 4060 is a server-side error. Therefore, the client can do little about it. You need a quick fix. In this tutorial, we’ll show you how to fix the SQL Error 4060.
What causes the SQL Error 4060?
After migrating a website or an application, users sometime see the SQL Error 4060 that reads “Connection Failure: Server rejected the connection, Access to selected database has been denied”. This error flashes because:
No valid credentials, or no permission
The biggest issue with the SQL Error 4060 is that the user did not have the permission to view the database objects. They did not face this issue before migration because they had the required permission. Therefore, after migration, you, as an administrator, should set up permission for them.
To grant the privileges, you need to use the Transact-SQL or TSQL command. This command looks like:
As you can see, the first line of code grants the required permission, and the second line to a specific user.
In real-world application, the TSQL Command looks like:
Likewise, you can grant permission to other users as per their roles.
This should fix the error. If not, look for the next problem.
Protocol not enabled
All connection to the SQL Server Database Engine requires a network protocol, which should be enabled. If not, then the database will throw the 4060 error.
Here are the steps involved to solve this:
Following the above method, you also need to enabled TCP/IP.
Firewall block
If both of the above solutions fail to resolve the problem, then it’s more likely a firewall blocking the access to the database. To resolve this, you simply need to tell firewall to grant the access for the user. Navigate into the installed firewalls and check for the permissions. If it has not been granted to the user, grant it.
The SQL Error 4060 can also be triggered by some other issues. For example, a corresponding database might not have been correctly copied into a new environment. Other there are problems with rows or objects. Although, these are rare, we encourage you to check into these if the SQL 4060 error persists.
If you’re using a SQL database, then you might encounter the SQL Error 4060 after a database migration. This is one of the many SQL Errors admin have to deal with. SQL Error 4060 is a server-side error. Therefore, the client can do little about it. You need a quick fix. In this tutorial, we’ll show you how to fix the SQL Error 4060.
What causes the SQL Error 4060?
After migrating a website or an application, users sometime see the SQL Error 4060 that reads “Connection Failure: Server rejected the connection, Access to selected database has been denied”. This error flashes because:
- There was lack of valid credentials for accessing the database, hence access was denied
- Protocol was not enabled
- The access was blocked by a firewall
No valid credentials, or no permission
The biggest issue with the SQL Error 4060 is that the user did not have the permission to view the database objects. They did not face this issue before migration because they had the required permission. Therefore, after migration, you, as an administrator, should set up permission for them.
To grant the privileges, you need to use the Transact-SQL or TSQL command. This command looks like:
Code:
GRANT <permission> [,...n]
TO <database_principal> [,...n] [ WITH GRANT OPTION]
[AS <database_principal>]In real-world application, the TSQL Command looks like:
Code:
GRANT SHOWROW
TO Moderator;
GOThis should fix the error. If not, look for the next problem.
Protocol not enabled
All connection to the SQL Server Database Engine requires a network protocol, which should be enabled. If not, then the database will throw the 4060 error.
Here are the steps involved to solve this:
- Go to the programs list by clicking on “Start”
- From the list, select the “SQL Server Configuration Manager”
- Then navigate into SQL Server Network Configuration and Protocols for <machine instance>.
- Double click on “Named Pipes”. This will open the Named Pipes Properties tab
- Select “Yes” for the Enabled option and then OK to confirm.
Following the above method, you also need to enabled TCP/IP.
- Go to the list of programs by click on Start and select “TCP/IP”.
- From the properties screen, select “Yes” on the Enabled parameter.
- Now go to the IP Addresses tab and select “Yes” for the IP address.
- Also check for the correctness of TCP Port.
- Finally click “OK”
Firewall block
If both of the above solutions fail to resolve the problem, then it’s more likely a firewall blocking the access to the database. To resolve this, you simply need to tell firewall to grant the access for the user. Navigate into the installed firewalls and check for the permissions. If it has not been granted to the user, grant it.
The SQL Error 4060 can also be triggered by some other issues. For example, a corresponding database might not have been correctly copied into a new environment. Other there are problems with rows or objects. Although, these are rare, we encourage you to check into these if the SQL 4060 error persists.
