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

Solution 1

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)

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; 

Solution 2

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)

Solution 3

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.

Solution 4

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.

Solution 5

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

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

View more solutions