Ошибка can not access database

Read time 5 minutes

SQL database handling is complicated even for the technically sound administrators. The generally expected smooth behavior of SQL Server is often hindered by several SQL Server errors. The main effect of these SQL Server errors is upon the database accessibility which hinders all the SQL Server operation ultimately.

Our attempt in this blog is to know about a specific SQL Server error, its occurrence, reasons and solutions. The SQL Server error which we are going to discuss is “SQL database access denied error.” This error comes with the code 5123 and thus also known as “SQL Server Error 5123”. The appearance of the error is somewhat like this.

What Does This Error Message Say?

The above SQL error literally says that the SQL database file opening process has failed or access is denied to open/create this particular file. The operation has failed to retrieve data for this particular request.

The reasons behind “SQL database access denied error”

• SQL database files (MDF and NDF) are moved from its default location on the system drive.
• Administrator does not have permissions to access that particular SQL Server database file.

Both the reasons are highly probable for this “SQL Server Access Denied” error. Let us take two scenarios to understand this.

Scenario 1 – If you have intentionally or unintentionally moved the SQL database file from its default location, then it is obvious that any SQL administrator (even with the full permissions) while trying to access this database using the Windows Authentication can face this error.

Scenario 2 – In this scenario, we assume that an SQL administrator with full permissions over the SQL database file folder removes or detaches the database from the SQL Server. Then, when another SQL administrator with rights on the SQL Server instance tries to re-attach the same SQL database file to the server, this “SQL database access denied error” appears. And the cause behind it could be the lack of SQL database permissions to the second SQL administrator.

The SQL Server error 5123 can be resolved by addressing these reasons. We would provide you the manual tips to get through this error and access your SQL database again.

Manual Tips To Fix SQL Database Access Denied Error

Users facing this error must ensure that there SQL Server is running and that SQL Server instance is available on the network.

• Confirm the SQL Database Files Location

  Go to the location C:Program FilesMicrosoft SQL ServerMSSQL. 1MSSQLData and check whether both the MDF and NDF files is available or not. If not, try to paste the database files at this default location.

• Assign permissions to the administrator trying to attach the database
  As mentioned in the reasons, whenever any SQL administrator detaches the SQL database using his login credentials, the whole access permissions gets removed. In that case whenever any other SQL administrator with administrative rights on the same SQL Server instance tries to attach this database again, the error “>SQL database access denied error” is received.

  So, you need to assign the full access permissions to that SQL administrator in order to let him attach and access the SQL database. Follow these steps.

  a. Go to the default SQL database file location.
  b. Right-click on the .mdf file and click on the Properties option.
  c. In the Properties dialogue box, navigate to the Security tab.
  d. Select the Administrator name from whose credentials you want to access the database under Group or user names section.
  e. Click on Edit option and select all the permissions under Allow section.

  

  f. Click on OK and exit the Properties page.
  Note: Perform the same process for the .ldf file available at the same location.

  g. Now, try to access the database file with this SQL administrator login credentials.
  h. Hope you succeeds this time!

If you still cannot access the database, try this:

We expect that the above manual tip can help you access the SQL database again. But if it is not possible to open it, then we fear that your SQL database is corrupted. Sometimes the reason behind the corruption is not certain but it could lead to damage, loss and inaccessibility of SQL Server data.

In such a condition, we offer you an efficient SQL database recovery tool, the SQL Server Recovery software, which recovers corrupt and damaged MS SQL database files. The tool supports recovery of database files of all SQL Server versions, and provides the facility to view preview of the recovered data. You can save the recovered SQL database file either to live SQL Server location or to a batch file on your system. So, users can trust this tool to repair MDF file anytime and also learn about it using the free version.

Wrapping Up

The most common “SQL database access denied error” is discussed in detail along with some helpful manual solutions. In addition, a solution to deal with SQL database corruption is also suggested.

I have been trying to setup a local instance for a new application. I am new to MYSQL and I cannot understand how to actually connect this database to the actual web application sitting in the /var/www folder in my computer.

I have created a database in command line terminal:

mysql> create database mydatabase
    -> ;
Query OK, 1 row affected (0.00 sec)  

mysql> create user 'host'@'website.local' identified by 'hmypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'host'@'%' identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on database.* to 'host'@'%';
Query OK, 0 rows affected (0.01 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


mysql -u host -h 127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 52
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

How to actually connect this database to the website? I also don’t understand how to make a schema for this database. Whenever I try to refresh my web browser I get the following message:

Could not access database!

SQLSTATE[HY000] [2002] Connection refused

Several SQL database administrators have reported encountering SQL Server error 5123 with the message “operating system error ‘5 (Access is denied.)’”. The error occurs while trying to attach a database, which has been placed in different locations. The complete error message reads as:

“Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file ‘filepath‘. (Microsoft SQL Server, Error: 5123)”

Possible Reasons behind Operating System Error 5 (access is denied) Error and Their Solutions

Following are the main reasons that may cause MS SQL ‘access denied error’ when attaching SQL database, along with their solutions:

Reason 1 – Default Location of SQL Data Files Has Changed

SQL database files (MDF and NDF) are moved from their default location on the system drive to another location.

Solution – Check the Default Location of the Data File

Go to the default location of the file folder where the SQL database (MDF/LDF) files are stored. Check whether the database files are present in their default location or not. If they are not present, paste them in the default folder.

Note: The database should not be in use when copying the files into the default folder.

The default location for SQL data files is as follows:

C:Program FilesMicrosoft SQL ServerMSSQL{nn}.MyInstance

Here, ‘nn’ stands for SQL Server version.

For example, following is the default location of DATA file of MS SQL version 14.

C:Program FilesMicrosoft SQL ServerMSSQL 14.SQLTECH2017MSSQLDATA

Reason 2 – SQL Server Resets File Permissions when Database is Detached

Although SQL Server administrator credentials might grant you the privilege to perform several activities, some activities require a different set of permissions.

For instance, your log-in credentials might allow you to remove the database but will throw the “SQL Server Access denied” error message when you attempt to re-attach it. The error occurs because the SQL Server resets the file permissions when the db is detached. And so, the database file permissions must be granted explicitly to reattach the database.

After the database has been attached, the permissions are reverted to the Database Engine SID NT SERVICEMSSQLSERVER account, and all privileges for individual log-in credentials are removed. While you can use the Database Engine SID NT SERVICEMSSQLSERVER account to attach database files, it might not always be easy to do so.

Solution – Grant Full Access Control on the SQL Data (MDF) and Log (LDF) Files

Let’s take an example to understand and fix the problem. In this example, two administrators, Adm1 and Adm2, have sysadmin (system administrator) rights on an SQL server instance. Here, we will use the first administrator’s credentials to remove a database, followed by using the second administrator’s credentials to attach the same database.

• Step 1 – Create the Example Database

For this example, an example database ‘db1’ is created on the system. The name of the database file is db1.mdf, and the name of the database log file is db1.ldf.

• Step 2 – Check the Permissions of the db1.mdf and db1.ldf Files

Open the db1 Properties window. Under the Security tab, select the server name, and grant all permissions of the database file to all users on the server instance.

• Step 3 – Remove the db1 Database File

Use the administrator credentials of Adm1 to detach the ‘db1’ database file from the server.

• Step 4 – Check the permissions of the db1.mdf and db1.ldf files again

When you check the permissions of both db1.mdf and db1.ldf, you will notice that, under Security, full permissions are applied only to Adm1.

• Step 5 – Attach the db1 database file

Use the administrator credentials of Adm2 to attach ‘db1’ back to the server. As seen in Step 4, all privileges now lie with only Adm1, which is why, when Adm2 tries to re-attach the db1 database file to the server, the system throws the SQL Error 5123: “SQL Server Access denied”.

• Step 6 – Apply Full Permissions to Both db1.mdf and db1.ldf Files

For ‘Adm2’  to be able to re-attach the db1 database file, full permissions for both the ‘db1.mdf’ and ‘db1.ldf’ files must be granted to Adm2.
Alternatively, full permissions to ‘db1.mdf’ and ‘db1.ldf’ files can be granted to the Database Engine SID NT SERVICEMSSQLSERVER account. Under the Security tab, select ‘Adm2’ and grant it full permissions to the db1.mdf and db1.ldf files.

• Step 7 – Re-attach the db1 Database File

Use ‘Adm2’ credentials to attach the db1 database file.

• Step 8 – Check the Permissions of the db1.mdf and db1.ldf Files One Last Time

After the ‘db1’ database file has been attached, the system removes full permissions for both ‘Adm1’ and ‘Adm2’ credentials. Full permissions are now granted only to the Database Engine SID NT SERVICEMSSQLSERVER account.

Also read: Potential causes of the “SQL Server does not exist or access denied” error message

What To Do If the Error Persists?

If the above methods fail to fix the issue, problems within your SQL database, such as corruption in the MDF file, may cause the ‘Access denied error’. In such a case, either perform a full restore from a recent database backup or repair the damaged SQL database using reliable SQL database repair software, such as Stellar Repair for MS SQL.

Key Features of Stellar Repair for MS SQL Software

1. It fixes all corruption of SQL Server database and recovers inaccessible objects from MDF and NDF file.
2. It carries out the highest level of non-destructive repair algorithm to preserve database integrity while recovering tables, triggers, indexes, keys, rules, and defaults.
3. Trusted by SQL Server MVP
4. Auto-detects the corrupt database version.
5. Show a preview of the recoverable sql database.
6. Saved the repaired database in 4 formats: MSSQL (.MDF), HTML. CSV and, XLS.
7. Supports MS SQL 2019, 2017, 2016 , and all lower versions.
8. Compatible with Windows 10 / 8 / 8.1 / 7 / Vista / XP.

Read this: How to Repair MDF File with Stellar Repair for MS SQL Software?

Conclusion

Permission denied was initiated as a security measure to prevent users from attaching files they didn’t create. Thus, use SQL log-in credentials to attach and remove database files only if you are sure which files you want to attach and what’s their source. The SQL log-in credentials use the Database Engine SID NT SERVICEMSSQLSERVER account, and this is a good way to eliminate the SQL database access denied error message as discussed in this blog. If you’re still unable to attach the database, likely, the MDF file is not in a healthy state (and is corrupted). Try using Stellar Repair for MS SQL to scan and repair MDF files and preserve their data integrity.