17

I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio.

After installing everything, I tried to attach the MDF file but then it tells me:

the directory lookup for the file "D:{folderName}{filename}.LDF" failed with the operating system error 21 (error not found)

An LDF file did not come with the database, so presumably it should be generated automatically.

Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there.

  1. Why is it trying to look for a log file at a specific path? Why not where the database file is?

  2. How can I attach this database?

I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it.

This time, when I hit "OK", I get a different error message:

Database cannot be upgraded because it is read-only or has read-only files.
Make the database or files writeable, and rerun recovery.

File activation Failure. The physical name D:{folder}{file}.LDF may be incorrect.
New log file "..." was created. (Microsoft SQL Server, Error: 3415)

So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues.

Additional information:

  • The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login.

  • I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed.

  • I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed".

  • Closing SSMS and reopening it as an Administrator made no difference.

  • select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.

Improve this question
0

12 Answers 12

Reset to default
12

This isn't an access issue. The problem is that you are attaching the database to a higher version of SQL Server than the instance the .mdf was originally attached to and it's read only. SQL Server is trying to upgrade the database as part of the attach and can not because the db is in read-only mode.

If your database is in READ_ONLY (which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that:

...for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

You need the accompanying .ldf for the database. I would contact whoever provided the database and ask them to provide both the .mdf and .ldf files. Then, you can attach it with the following syntax:

CREATE DATABASE [foo]
ON (FILENAME='<<path to mdf>>')
LOG ON (FILENAME='<<path to ldf>>')
FOR ATTACH;

The READ_ONLY status cannot be removed from the database without attaching it because it is stored in the metadata of the database. To alter it, the database needs to be online and attached.

Another alternative is to ask for a backup of the database. You can work around some of these issues more easily if you're doing a database restore instead of an attach.

Improve this answer
1
  • 4
    Using LOG ON with the matching ldf did not resolve the issue for me
    – Mugen
    Commented Aug 13, 2017 at 7:08
50

I just found a post that answered this question:

This may not work in all cases, but I was trying to install the Semantic Search Database and got the same error.

You have to run SQL Server Management Studio as an administrator, and it works.

Improve this answer
5
  • And still the case for SQL 2019.
    – bvj
    Commented Jun 1, 2020 at 22:10
  • Wow, I had no idea SSMS needed this access. I figured surely it was the MSSQL engine, but nope. This was my issue. Thanks!
    – NTDLS
    Commented Jul 3, 2021 at 3:07
  • This worked for me
    – sav
    Commented May 6, 2022 at 2:40
  • I can't believe this was it! In my case the SQL Server user did have access to read and write all the mdf, ndf, ldf, but I still ran into the error. Running SSMS elevated worked fine. Go figure!
    – Coxy
    Commented Dec 5, 2022 at 5:13
  • This worked for me too. Thank you very much!
    – Jana Sattainathan
    Commented Mar 29 at 13:34
12
  1. Open up my computer.
  2. Locate the main folder where you have kept the ".mdf" and ".ldf" file.
  3. Right click on the folder click on "Properties".
  4. In the properties window click on "Security"
  5. Click on "Edit" button
  6. Enable "Read and execute", "Read", "Write" for the listed users and apply the changes to the following folders till the database files.
  7. Try to attach the ".mdf" file
Improve this answer
2
  • thanks ,my old windows gone and i think these files locked by that OS. but just need to change permission
    – saber tabatabaee yazdi
    Commented Dec 28, 2018 at 18:24
  • Works on Linux in 2024.
    – J. Mini
    Commented Jun 30 at 9:51
1

Right click the .mdf and .ldf files, one at a time and click 'properties', then at the bottom where it says ' this file came from another computer and might be blocked to help protect this computer ' click unblock button.

Then right click, -> properties, -> security tab -> edit button and select 'users' and give full control permission.

Improve this answer
0
1

In my case the resolution was to login to SSMS with windows Auth mode instead of the sql user that has sysadmin role. just make sure the user that you used in windows auth mode have a proper permission(my case was sysadmin) My guess is sql user doesn't have a proper permission.(I encounter this issue when I migrate/copied mdf and ldf from different machine)

Improve this answer
1

In my case, I solved it by running SSMS elevated as Admin.

Improve this answer
1
0

I have also had this issue come up. I assisted my customer by restarting the SQL service, and was able to attach the MDF per normal procedure.

Improve this answer
0

Not permissions in our case, it was the version of SSMS !

(it was 2012 locally where we got the error, but worked when we used SSMS 17+ from a remote box)

Improve this answer
0

In my case, the problem was caused by incorrect permissions on the drives where the SQL Server data is stored. As in, a NAS device.

Improve this answer
-1

In my case, the issue while attaching a database was "Database cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery"

The solution that word on this was as below;

  1. Close the SSMS and run again as an Administrator
  2. Do not use SQL user or even SA
  3. login using your windows authentication user
  4. After login just attached the database, it would work
  5. Things to make sure that all the database files must be available in same location before attach.
Improve this answer
2
-2

1) Open Sql Server Configuration Manager

2) Select Sql Server Services(Left pane)

3) Doubleclick your SqlServer(right Pane)

4) Select Log On tab

5) Select built-in radio button and change local system or network service in combobox.

6) Click Ok.

7) Attach mdf file in sql server manager.

Improve this answer
0
-2

Your SQL Server service is probably running under NT Service\MSSQLServer account. Changing it Local System will give it access.

Probably Best to create a user and give that user full permissions to the folder the files are in.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.