Home » SQL ServerRSS

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Er

I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL.

I get the following message: Unable to open physical file "C:\ArrowSQL\Arr@Data\Arrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)".

I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file.

The machine runs Windows Vista Business. SQL has SP 2 loaded.

What causes this?

 

22 Answers Found

 

Answer 1

it works...thanks two million...dave

 

Answer 2

Thanks a million!

This really helped. Sure looks like a win7 bug to me.

/Lars

 

Answer 3

Chris,

Provide modify privilege for SQLService account for the folder C:\ArrowSQL\Arr@Data and then attach  the db, it will attach without privilege error!!
 

Answer 4

I do not knwo how to "modify privilages for SQL Service account for the folder". Could you direct me to some instructions on how to do this?

Thanks

 

Answer 5

First, check the service account of your SQL Server instance. You can get it using Configuration Manager.

Second, give this account Modify permission for the data folder. 

 

Answer 6

Use the below script to find the sql  service account

Code Snippet

declare @sqlser varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

@value_name='objectname', @value=@sqlser OUTPUT

PRINT'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

After getting the service account try the below steps to provide privilege

Right click on the folder C:\ArrowSQL\Arr@Data and click on properties
Click on security tab
Click on Add button and add sql service account
Provide modify privilege and click ok
Verify both mdf and ldf have modify privilege
Attach the db!
 

Answer 7

Thanks. This worked.

 

Answer 8

uhmm im getting the same error  and tried to execute the code snippet u gave but when i execute it , it gives me the error...

RegOpenKeyEx() returned error 2, 'The system  cannot find the file  specified.'

Msg 22001, Level 1, State 1

sorry im kinda new to this stuff and i really need to fix this problem asap..
 

Answer 9

The above script is written for SQL 2005. Can you provide your SQL Version.

You can also try the below alternate method to find service account

Goto RUN
Type services.msc & press enter
In service control manager find the below service w.r.t to your edition.

For SQL 2000

MSSQLSERVER -- for default instance

MSSQL$instancename -- for named instance

For SQL 2005 & SQL 2008

SQL Server (MSSQLSERVER) -- for default instance

SQL Server (instancename) -- for named instance

Check the "Log On as" for the SQL service and provide modify privilege to that ID in the folder.
 

Answer 10

hello i'm new to sql  and asp.net. i have a web application where i develop at work. which seem to hv probelms to coonecting to the database  sql 2005. i have tried the above mention solutions, but fail to edit the folders and both mdf n ldf file  for permission, due to the fact that when i right click the folder and click on properties, i do not see a "security tab " (i'm using windows xp sp 3). but when i took the application home and test it out the worked fine. so what did i do wrong on my office machine? Please advise. i need to solve this problem ASAp. any solution would be of great help.
 unable  to open  the physical  file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system  error 5: "5(Access is denied.)".
An attempt to attach  an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Unable to open the physical file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Unable to open the physical file "C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf". Operating system error  5: "5(Access is denied.)".
An attempt to attach an auto-named database for file C:\Inetpub\wwwroot\MXMinhouse\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735171
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
System.Web.UI.WebControls.ListControl.PerformSelect() +31
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 

Answer 11

Simply open  "SQL Server Configuration Manager"
In side "SQL Server Configuration Manager" in the right-side, right-click on the service name which you are using currently
Select Properties
Now you can do one of the followings:
     - Change the log on service account to an account with appropriate privileges.
     OR
     - Give the selected log on service account an appropriate privileges on your file  system (for example: D:\SQLDatabase\)

Good luck :)

 

Answer 12

thanks man..

this work for me..
as i m using win7 - sql  2005  express..


many thanks.
 

Answer 13

OS: Win7 sp1 (fully patched to Jan 25th, 2010)
Office2007 with integrated BCM (fully patched to Jan 25th, 2010)
Auto-installed SQL Server2005 Express (fully patched to Jan 25th, 2010)
Created BCM database  called SmallBusiness

I am trying to move BCM database from the default C:\Users\User_name\AppData\Local\Microsoft\Business Contact Manager folder.

- First I backed up the db and log.
- I copied the files into D:\Databases
- Gave permission on the two files to the Log On for SQL Server Service
   - I also gave permission to SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ  <- Most people forget that, and I haven't seen anyone mention it, but it's on the default db BCM creates)
- I detached the SmallBusiness Database
- I try to attach  the new file:
USE [master];
GO
CREATEDATABASE [SmallBusiness]
ON
( FILENAME = N'D:\Databases\SmallBusiness.mdf'
),
( FILENAME
= N'D:\Databases\SmallBusiness.ldf'
)
FOR
ATTACH
GO
- I get the infamous "SQL Server Database Error: Unable to open  the physical  file "D:\Databases\SmallBusiness.mdf". Operating system  error 5: "5(error not found)"

So after reading many posts on this topic, and trying a few things, I decide in desperation to Share the entire drive to everyone, every logon UID on the entire system with modify all. No change.

Ok, so now this is getting silly.

- I create a brand new dummy database using Toad for Data Analysis' Wizard in D:\Databases called TestDB.mdf
- I create a schema and a few tables and fill them up with junk.
- I switch users and everything works as expected.

Now for the kicker:

- I detach this new DB which created successfully in D:\Databases.
- I try to re-attach it from the same location and guess what?
"SQL Server Database Error: Unable to open the physical file  "D:\Databases\TestDB.mdf". Operating system error  5: "5(error not found)"

Ok, so now this is beyond silly.

So I try to re-attach the original BCM DB files back as they were to restore everything the way it was.
"SQL Server Database Error: Unable to open the physical file "C:\Users\User_name\AppData\Local\Microsoft\Business Contact Manager\SmallBusiness.mdf". Operating system error 5: "5(error not found)"

I am Admin, I installed everything, I own the DB, the file permissions on the original C:\ DB did not change.

This is not good.

Can anyone at MS suggest anything?

Regards,
Tom

 

Answer 14

Ok folks, hopefully this will help someone someday.

Even if YOU create a DB or if BCM created one on your behalf, once you detach it, you actually have to explicitly grant yourself full control over the mdf and ldf, even if you are part of the Administrators group in order to re-attach it.

I think this is a WIN7 bug.

The files were granted full control to Administrators, the SQLServer2005 Service Logon, and the SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
But if you want to re-attach it with WIN7, you have to explicitely grant yourself full control. (why? ...who knows...)

So, to move a WIN7 Office2007 BCM database  from it's default location to anywhere on the planet and to ensure any database attach  activity doesn't give you the "unknown error  #5":

1) backup your files
2) copy your mdf and ldf to your favorite folder
3) ensure at minimum the following UIDs have full control over them
- SQLServer2005MSSQLUser$MACHINE_NAME$MSSMLBIZ
- Your current Administrator UserID (even if Administrators have full control and you are an Administrator, you still have to add yourself)
- SQLServer2005 Service Logon
  (you can get this on WIN7 thusly:

declare

 

 

 

 

 

 

 
@sqlservarchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQL$MSSMLBIZ', @value_name='objectname', @value=@sqlser OUTPUT
PRINT'Account Starting sql  Server Service:'+convert(varchar(30),@sqlser)

3) detach your DB

USE [master];
GO
EXEC sp_detach_db @dbname= N'SmallBusiness', @skipchecks='true', @keepfulltextindexfile='true'
GO

4) attach the DB from the new location (ensure you own it while you're at it)

USE
[master];
GO
CREATEDATABASE [SmallBusiness] ON
( FILENAME = N'D:\Databases\SmallBusiness.mdf' ),
( FILENAME
= N'D:\Databases\SmallBusiness.ldf' )
FOR ATTACH
GO
ifexists (selectnamefrom master.sys.databases sd wherename= N'SmallBusiness'andSUSER_SNAME(sd.owner_sid) =SUSER_SNAME() ) EXEC [AnethSmallBusiness].dbo.sp_changedbowner @loginame=N'MACHINE_NAME\USERNAME', @map=false
GO

5) enjoy

I tried moving to a completely different computer \\COMPUTERNAME2\Databases\SmallBusiness.mdf
works perfectly.

PS> My setup is brand new, but sqlcmd doesn't work. Don't waste time, get Toad for Data Analysis. You can do anything on ANY DB with this thing. Great scripting tool, and it has a GUI for all you lazy folk...

Regards,
Tom
 

Answer 15

I agree with Majiktom, it may seem like permissions are OK but you still get errors.  I feel the process is confusing, to say the least! 

My quick fix was to:
- right-click on the file  in Windows Explorer, select Properties
- select the Security tab
- Click Advanced
- Click Change Permission
- Uncheck "Include inheritable permissions...", a window will open
- Click Remove (removes all permissions), the window will close
- Click Add
- Enter your login name and click OK, the permission window will open
- Check Full Control - Allow
- Click OK, OK, OK, OK

Do this for the MDF and LDF files.

I was then able to attach  the database.

Cheers,

-dave
 

Answer 16

Hi,

I have failed some of backup jobs with below error  message. I don't know how I can fix this problem. Could you give me some advice?

Executed as user: COMP\XXXXX. Microsoft (R) SQL server  Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:07:39 AM  Progress: 2010-03-16 11:07:41.99     Source: {91EBDCB5-4E9D-4945-ABA7-57BB23DA6176}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2010-03-16 11:07:43.46     Code: 0xC002F210     Source: Backup DB Execute SQL Task     Description: Executing the query "BACKUP DATABASE [TO1] TO  DISK = N'\\server5\mitsd..." failed with the following error: "Cannot open  backup device '\\server5\mitsdb_backup$\TRG\XXXXX\TO1\TO1.bak'. Operating system  error 5(Access is denied.).  BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:07:39 AM  Finished: 11:07:43 AM  Elapsed:  4.313 seconds.  The package execution failed.  The step failed.
 

Answer 17

Thanks for posting Dave, solved my problem :)
 

Answer 18

Thanks Tom and Dave, I was pulling my hair out messing with SQL server  network service account permissions, but indeed it did seem to be that after reinstalling Windows I had to give myself full permissions on the .mdf and .ldf files.  Very odd.  I claim this is a SQL server bug, because even when I let SQL server run as administrator it didn't fix it, even though it gave it the permission to do it. 
 

Answer 19

Thanks Mr. Sven

 

Regards

Tarun

 

Answer 20

Yes, it seems that I had to right click on the folder containing the .MDF and .LDF files and add my current logged in user "explicit" permissions to the directory in order for SQL server  to attach  the DB.

This is a fact EVEN THOUGH  my current logged in user is in the "(local)/Administrators"  group and the "(local)/Administrators" group already has full file  control permissions of that directory.   Also, my current logged in user is a DB "admin" for the sql  server.   For some reason I needed to "explicitly" add my currently logged in user accounts name.

NOTE: I need to point out that previous posters on this thread are all incorrect when they suggest that the solution is in giving the  "SQL service account name" the permissions.

 

Answer 21

Ok folks, hopefully this will help someone someday.

Indeed helped, thank you!
 

Answer 22

It worked. Thanks a million...:)

 

Tushar M.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter