Cannot install adventureworks 2012 database - Operating system error 5: Access is denied

Question

I am trying to install AdventureWorks 2012 database in sql server 2012. I got the mdf file from this link - http://msftdbprodsamples.codeplex.com/releases/view/93587

Here is the name of the file I downloaded - AdventureWorks2012_Database.zip

I am doing all this on a windows 7 64 bit.

I get the error below:

Attach database failed for Server 'SuperPC\SQL2012'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Unable to open the physical file "C:\Databases\AdventureWorks2012_Data.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

Did you check if the file the zip file is Blocked. Right Click on the file there should be an option to unblock the file if its blocked. — jcwrequests, Sep 27 '13 at 21:53
@jcwrequests - I unzipped the files and then tried to install them onto sql server 2012. — Steam, Sep 27 '13 at 21:54
Also check if the user that you're using to access the database has access to those files. — Szymon, Sep 27 '13 at 21:56
Go to where they are located and check the security in file properties. — Szymon, Sep 27 '13 at 21:57
Did you run an msi or did you unzipp the mdf file and try to attach too it? — jcwrequests, Sep 27 '13 at 21:57
Blocking will not stop you from unzipping the file but it will have the effect you described. — jcwrequests, Sep 27 '13 at 21:59
@jcwrequests - unzip the mdf and then Object explorer > MyDB > Databases > right-click > attach > add - I add my mdf file here. — Steam, Sep 27 '13 at 21:59
@jcwrequests - okay. i am re-downloading the zips. How do I check if a zip file is "blocked" ? I don't understand what this block means. — Steam, Sep 27 '13 at 22:02
http://dosysadminsdream.wordpress.com/2012/01/18/windows-2008-unblock-executable-button/ check this link to see what I mean about unblock. — jcwrequests, Sep 27 '13 at 22:04
@jcwrequests - no luck. I unblocked and unzipped it. Then, I tried to load it again. Fails with the same error. — Steam, Sep 27 '13 at 22:08
Did you check if the mdf is blocked? Also chech the ownership on the mdf make sure that the server has appropriate ownership. — jcwrequests, Sep 27 '13 at 22:13
@jcwrequests - mdf is not blocked. How do I - make sure that the server has appropriate ownership ? — Steam, Sep 27 '13 at 22:17
http://technet.microsoft.com/en-us/library/cc753659.aspx this will give you info on ownership. — jcwrequests, Sep 27 '13 at 23:28

score 70 · Accepted Answer · edited Sep 20 '16 at 18:21

70

The reason for the problem - Putting the database mdf and ldf files in a directory outside the "official" SQL server installation folder.

Solution -

http://tryingmicrosoft.com/error-while-attaching-a-database-to-sql-server-2008-r2/

Paste your .mdf file and ldf file in this directory - C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA

Solutions that did NOT solve the problem -

1 - Unblocking the zipped files. Also checked that mdf and ldf files are not blocked. (Steps - right click zip file > properties > unblock)

Unblock

2 - Run SSMS 2012 as administrator.

3 - Run SQL query of the form -

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'), 
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf') 
    FOR ATTACH;

edited Sep 20 '16 at 18:21

Dean Kuga

11,878
8
54
108

answered Sep 27 '13 at 22:34

Steam

9,368
27
83
122

After I attach file in the default SQL server directory, I get the same access denied error when trying to access it with an ASP.NET application. – PeterX Mar 04 '14 at 04:00
@PeterX - Did you try any of the solutions which did not work (DNW) for me or the answers given by other people ? See my 1st DNW point. – Steam Apr 11 '14 at 18:03
@RăzvanPanda - different strokes for different folks. Enjoy. If you like my answer, please upvote me. Thanks. – Steam Aug 08 '14 at 18:28
1

@Steam: was just mentioning it so that readers know that running as administrator **might** be a solution, even if it did not work for you – Răzvan Flavius Panda Aug 08 '14 at 19:51

score 19 · Answer 2 · edited May 23 '17 at 12:26

Options

1. Move .mdf and .ldf to SQLServer install directory \Data\ folder (eg C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA)

2. Run SQL Server Management Studio as Administrator

3. Add rights to the SQL Server user to the directory you need access to. I wasn't sure which user was correct but managed to get it working by giving the "[PCNAME]\Users" account full access to the folder.

Steps

Right-Click folder. Select Properties. Select "Security" tab. Click "Edit". Select "Users ([PCNAME]\Users)" eg if the name of the pc was mycomp then this would be "Users (mycomp\Users)"

Click the "Allow" tickbox next to "Full control" under "Permissions for Users"

See here for more details for options 1 & 2 ..

CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105)

Perhaps not a good idea to add just local users full control permissions to that directory. Rather, add `NT Service\MSSQLSERVER` account to the permissions of the folder and grant full control. — scrawny, Mar 03 '16 at 05:45

score 14 · Answer 3 · answered May 16 '14 at 21:32

14

I've changed permissions and that worked for me: security -> Advanced. Add 'NT Service\MSSQLSERVER' and give full access, disable inheritance, add yourself for ease of troubleshooting. Do that on both .mdf and .ldf files.

answered May 16 '14 at 21:32

Mikl X

1,199
11
17

I gave "User" full access to solve the problem. I guess limiting to just mssqlserver is better. – LosManos Sep 23 '14 at 19:33
1

I had to do both current user and NT Server\MSSQLSERVER. I initially tried with just MSSQLSERVER but that didn't work. I then added my user with full permissions – Karlo Medallo Apr 14 '15 at 02:42

score 7 · Answer 4 · edited Dec 20 '14 at 16:36

7

You don't have to move the .mdf file. Just right click the folder the database is in and change the security permissions. I gave authenticated users full control. Then I ran the TSQL script below to attach the database:

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\Adventureworks\AdventureWorks2012_Data.mdf'), 
    (FILENAME = 'C:\Adventureworks\AdventureWorks2012_Log.ldf') 
    FOR ATTACH;

As you can see my database files are located in a folder called Adventureworks.

edited Dec 20 '14 at 16:36

honk

9,137
11
75
83

answered Dec 20 '14 at 16:16

Roger

71
1
1

You are a genius! This was solution the solution to my problem! – villamejia Oct 23 '17 at 00:27

score 6 · Answer 5 · answered Sep 28 '13 at 00:05

6

Error 5 - Access is denied - Comes from the old days of DOS. Basically, it is an ACL - access control list error.

You can put databases anywhere you like as long as the SQL SERVER SERVICE account has access!

It doesn't matter who you are logged in as, it is the service account that needs access to the directory and files.

You do not need to run SSMS as an administrator. I attached databases all the time without this step.

answered Sep 28 '13 at 00:05

CRAFTY DBA

14,351
4
26
30

How did you do it ? Did you do what I mentioned in my answer ? – Steam Sep 28 '13 at 00:57
With Azure IaaS VM SQL Server 2012, I saw the same issue. Adding the NT Service\MSSQLServer service account to security (FullControl) for the target drives solved my issue and I was able to set the ReadOnly option to False. – jl. May 16 '17 at 16:11

score 6 · Answer 6 · answered Dec 13 '13 at 18:16

Just remove the log file, if you are creating the database for the first time by attaching, that might help you.

I was trying to create database by attaching and I kept getting a similar screenshot

enter image description here

Thanks to the article below I found and just had to remove the log, it was obvious but MS did not offer to not show by themselves for new databases. Very bad experience, not sure if its similar for SQL server licensed products.

http://exacthelp.blogspot.in/2012/12/unable-to-open-physical-file-operating.html

score 6 · Answer 7 · answered Mar 09 '15 at 09:12

Steps to attach AdventureWorks2012 database into your local MSSQL database:

Solution 1: If you have enough space in C:\ Drive in Windows and want to create the database in C:\ Drive

Place the AdventureWorks2012_Data.mdf and AdventureWorks2012_log.ldf in the below location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

2.Right click on 'Databases' in the object explorer and select 'Attach'

3.In the Attach Databases dialog box, Click on 'Add' button in the databases to attach: field.

Navigate to the MDF file location C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.

5.Now the database 'AdventureWorks2012' is created in SQL Sever.

Solution 2: If you do not have enough space in C:\ Drive in Windows

1.Connect to your local server by selecting server name XXX\SQLEXPRESS and Authentication - 'Windows Authentication'

2.Right click on 'Databases' in the object explorer and select 'Attach'

3.In the Attach Databases dialog box, Click on 'Add' button in the databases to attach: field.

Navigate to the MDF file location for e.g. F:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.
This will generate the below error message: 'An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.'
SOLUTION is :

i) Right click on the AdventureWorks2012_Data folder, select Properties.

ii) click on 'Security' tab

iii) Select Users(xxx\Users) in the 'Group or user names:' section

iv) Click on 'Edit' button

v) Select Users(xxx\Users) in the 'Group or user names:' in the popup,

vi) Check the 'Full control' check box in the 'Permissions for Users' section.

vii) Click ok & ok.
Now back to SQLsevers's Attach Databases screen, Click on 'Add' button in the databases to attach: field.
Navigate to the MDF file location e.g. F:\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf and click 'OK' in the bottom of the 'Attach Databases' dialog box.
Now the database 'AdventureWorks2012' is created in SQL Sever.

score 3 · Answer 8 · answered May 12 '15 at 22:18

I realise this is an old question, but for me there were actually two requirements for attaching a database outside the default folder:

I had to explicitly grant the current user (me) Full Access to the folder (despite already having implicit permissions)
I had to grant NT SERVICE\MSSQL$INSTANCENAME Full Access to the folder

I assume #1 isn't required if the process (SSMS) is elevated.

score 2 · Answer 9 · edited Oct 24 '13 at 16:08

2

Attach the database .
In the dialogue box, select the file location of the AdventureWorks.
Add the file.
Delete the log file, as the SQL server will create one later once the database is attached .

edited Oct 24 '13 at 16:08

Bob Gilmore

12,608
13
46
53

answered Oct 24 '13 at 15:37

Rupinder Sood

21
1

score 1 · Answer 10 · answered Sep 08 '18 at 21:21

I had the same problem while trying to create new database with custom settings from ASP.NET MVS and I have lost few hours to solve it. Finally my solution was to get into folder options / seurity of my App_Data folder. Then I have add an account 'authenticated users' and gave him full control. And that was all. My application runs well ..

score 0 · Answer 11 · edited Jun 20 '20 at 09:12

Giving the sql service account 'NT SERVICE\MSSQLSERVER' "Full Control" of the database files

If you have access to the server files/folders you can try this solution that worked for me:

SQL Server 2012 on Windows Server 2008 R2

Right click the database (mdf/ldf) file or folder and select "Properties".
Select "Security" tab and click the "Edit" button.
Click the "Add" button.
Enter the object name to select as 'NT SERVICE\MSSQLSERVER' and click "Check Names" button.
Select the MSSQLSERVER (RDN) and click the "OK" button twice.
Give this service account "Full control" to the file or folder.
Back in SSMS, right click the database and select "Properties".
Under "Options", scroll down to the "State" section and change "Database Read-Only" from "True" to "False".

score 0 · Answer 12 · answered Aug 17 '15 at 22:38

0

just add the current user to the security tab will full control and re-run atttach query

answered Aug 17 '15 at 22:38

TrayS

135
2
7

score 0 · Answer 13 · answered Dec 09 '15 at 23:02

I solved this problem by downloading the Adventure works from this website

http://msftdbprodsamples.codeplex.com/downloads/get/723031

Note when downloading an adventure works zip folder, ensure the file is appended with _data e.g AdventureWorks2012_Data.zip as this file contains both the .mdf and .ldf files.

After it is downloaded, extract the folder contents to this url on your computer C:\Program Files\Microsoft SQL Server\MSSQL11.***\MSSQL\DATA. In SQl server, go to the Object Explorer and Right click on the Databases to attach the database. In the Attach dialog box, you should select Adve********.mdf. Everything you should be fine from here.

I hope this helps.

score 0 · Answer 14 · edited Jan 29 '16 at 20:46

NOTE: Windows 7 Pro, SQL 2012

After using DiskManager.msc, I had to add machine_name\SERVICE with full control to both the .mdf and .ldf files.

My Laptop is Borris so:

Right click on file `name -> properties click SECURITY tab, then permissions -> add and then in check for names type in services.
To get back out hit ok, apply, ok, ok, ok, and ok.

Not sure why switching drive letters reset my permissions but it did on Windows enter image description here

score 0 · Answer 15 · answered Oct 15 '17 at 15:51

Thanks for previous suggestions. You don't have to move the database files to the SQL server directory or delete the database log file. Instead you simply give the Full Control permission to both .MDF and .LDF files for Authenticated Users. By doing so I successfully attach both AdventureWorks2012 and AdventureWorksLT2012 sample databases in SQL Server Management Studio (SSMS).

score 0 · Answer 16 · answered Feb 03 '18 at 16:03

0

actually it's pretty easy. Just rename the files. No need to move, add permissions run as whatever...

answered Feb 03 '18 at 16:03

user763539

3,509
6
44
103

score -1 · Answer 17 · edited May 06 '15 at 21:19

-1

I solved this by:

Attaching the file AdventureWorks2012_Data.

1.1. Then the file AdventureWorks2012_log appears and does not exist.
Use the Remove button for eliminating the .log file.
Press OK.

Then the new database appears.

edited May 06 '15 at 21:19

OhBeWise

5,350
3
32
60

answered May 06 '15 at 20:15

gafajardog

1
2

score -1 · Answer 18 · answered Oct 31 '15 at 20:20

iTS EASY, Just Stop the SQL Service, copy what you want or do the restore, and then start the service again... voiláaa You cannot copy anything unless you stop the service. if the things you want to restore are on an active tree you cannot overwrite it, its locked, simple...

I know... thank me :)

Cannot install adventureworks 2012 database - Operating system error 5: Access is denied

18 Answers18

Giving the sql service account 'NT SERVICE\MSSQLSERVER' "Full Control" of the database files

Linked