none
Error message: "Unable to open the physical file" when trying to attach AdventureWorks database

    Question

  • I have searched the internet and this forum and have not found an answer...

     

    I am trying to install the AdventureWorks database into my single instance of MS SQL Server Express 2005.  I am logged into my machine as an administrator and logged into SQL Server 2005 express 'sa'.  I attempt to run the following script:

     

    exec sp_attach_db @dbname = N'AdventureWorks',

    @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',

    @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'

     

    The error message I get back is:

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf". Operating system error 5: "5(Access is denied.)".

     

    The folder "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" and all the files in it are read-write.  I am 100.0000% certain the files "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf" and "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf" exist!  They are the result of running the installation program AdventureWorksDB.msi, which I downloaded from: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

     

    What do I have to do to install the AdventureWorkds database????

    Sunday, August 31, 2008 4:41 PM

Answers

  • Check the Service account of SQLExpress. You can use Configuration Manager or Services in control pannel. It might be NT AUTHORITY\NetworkService, change that to a proper user who have permission to this location or make it as Local System account and see.


     Madhu K Nair

    SQL Server Blog

    SQL Server 2008 Blog


    Monday, September 01, 2008 6:00 AM

All replies

  • Assure that the ntfs/file permissions give the sqlserver service the rights to access the file.

     

    Sunday, August 31, 2008 5:38 PM
  • Hello,

     

    To try to help you, please, could you give some more informations ?

    - the operating system (XP/Vista), the edition ( Pro/Home...) and the service pack

    - usually, the installer installs the both files in C:\Program Files\Microsoft Sql Server\Samples. Is there any change in the location or have you moved the both files ?

    - could you check with the files explorer , that the 2 files are read-write and not read only( find one file, right-click on it, properties and in the 1st tabpage, you should see a checkbox read-only ( if checked , unchecke it ) ?

    - have you Sql Server Management Studio Express Edition ( at least SP1 ) ?

    If no, download it and use it to attach

    in the object explorer,

    click on your instance to expand it

    right click on databases

    in the contextual menu, click on attach

    in the new form,click on add

    you arrive on a second form : find your file , click on it, and OK

    it's the simplest way to attach ( the sp_-attach_db is complicated to type )

    the error messages are sometimes more clear in SSMSEE than in Sqlcmd

     

    Try also to attach ( thru SSMSEE or Sqlcmd but in using the windows authentification )

    NB: i hope that you are not trying to attach AdventureWorks on a remote instance and on a remote computer ( it would explain access denied )

     

    We are waiting for your feedback to try to help you more efficiently

     

    Have a nice day

     

    Sunday, August 31, 2008 5:52 PM
  • Check the Service account of SQLExpress. You can use Configuration Manager or Services in control pannel. It might be NT AUTHORITY\NetworkService, change that to a proper user who have permission to this location or make it as Local System account and see.


     Madhu K Nair

    SQL Server Blog

    SQL Server 2008 Blog


    Monday, September 01, 2008 6:00 AM
  • That was it.  Thanks for the tip about the permission for the service.  Still don't fully understand this, but at least now I have the AdventureWorks database.

     

    - John Anderson

     

    Monday, September 01, 2008 5:29 PM
  • Hi All.

    I had the same problem -- unable to open physical file on Vista 64.  So, I tried to give the SQL Express Service user permissions, run as LocalSystem, etc... nothing.. 

    In my case I had to give my user account (which is an admin) permissions to the file.. Not what I expected the solution to be since I'm in the Administrator group and I have full control...  

    I figured out the appropriate permissions to use by creating a new database in SSMS and looking at the premissions it had.  Perhaps this is because I created the database via the UI in management studio which is running under my user, rather than the SQL Server User..  And, perhaps because I'm trying to attach from the SSMS UI as well.

    Sunday, September 13, 2009 2:43 PM
  • My files AdventureWorks_log.ldf and AdventureWorks_Data.mdf both had their "Read-only" attribute unchecked, but the folder they were in:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

    Did have its Read-only set. Unchecking that attribute in the parent folder then allowed the DB to be attached.
    Thursday, September 12, 2013 8:41 PM
  • This worked for me:

    http://blog.sqlauthority.com/2012/03/15/sql-server-install-samples-database-adventure-works-for-sql-server-2012/

    • Proposed as answer by rockstar2831 Monday, February 03, 2014 2:23 AM
    Monday, February 03, 2014 2:23 AM