June 11, 2007 at 4:49 am
Just installed SQL Server 2005 Express Advanced Edition. Also downloaded AdventureWorks database.
But when I try to attach to this database (which the MSI intall placed in the default directory of c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL), I get the error shown below.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. (Microsoft SQL Server, Error: 5123)
I can attach to the database when it is located somewhere else, say c:\tmp.
The same happens when I try to create a new database. I cannot create it in the default location, but c:\tmp works.
I'm connected and running the service as a local administrator on a Windows XP/SP2 machine.
I'm pretty miffed and any suggestions would be welcome.
June 11, 2007 at 11:13 am
I guess that being a local administrator doesn't give you everything under the sun. And in the process of digging into this, I discovered the complexity of the Windows file/folder permissions - which is a nightmare. In any case, somehow, somewhere I enabled the right permission for the right user and things started going in my direction.
Aside from specifically having to enable the tcp/ip protocol for both client and server, the only other hiccup was default vs. named instance. The default is the latter (and this is not a play on words). Thus you need to specify server\instance-name (which defauts to SQLEXPRESS) for the server in you connection strings.
Now it's time to dig into all these SQL Server 2005 Express variants and trying to understand which is a first a release and which is a service pack.
Also need to figure out how to keep BOL local without using up my Internet bandwidth - which is downloading - well, you know what...
July 3, 2007 at 2:07 am
OS error 5 corresponds to permissions problem in the file and folder. Check for that also check if inhereitence of permission is propagated to the folders below the root folder.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
August 15, 2007 at 9:33 am
Hi,
This may be a stupid question, but....
I am having the same OS Error 5 permissions problem when trying to connect to a newly copied .MDF file with a newly installed (.Net 2.0) application. That is, this is a .Net 2.0 application I have developed for which I have created an install, and installed SQLExpress as a prerequisite. I have used "XCopy deployment" (per Sql server books online) with the AttachDBFilename parameter in the connect string for the application.
Miraculously, the SQL Express install seemed to work, and I do believe the problem really is a Windows permissions problem. I am pretty lame about Windows permissions, but I have given the user the application was installed under Full Control of everything in the directory - however, the directory keeps reverting to read-only, and I keep getting this message. Yet, this user can create a text file in the same directory with no problem! And, in other parts of the tree installed with the same permissions, the application can write to the error log, etc.
This is running under XP Professional sp2, on a computer in a domain.
However, the same connection is working fine on a standalone XP Professional computer, on which there is no Security tab when you right-click on a directory, just a link saying remote access has been disabled, etc. Apparently, this standalone mode (no network connections) avoids all permissions checking.
How do I give the right permissions on the machine in the domain so that I can do what I need to do in the directory?
Thanks for any help you can shed on this!!!
Elizabeth
August 15, 2007 at 11:32 am
Well, I am answering my own question!
As soon as I posted this, I found the answer elsewhere on this forum (not in this thread). I was able to go back to the test machine and test it, and it worked, so I am copying the answer which that brilliant person gave. Thank you, whoever you are!!
This is the answer - how this person solved the permissions issue. It was permissions for the SERVICE that had to change. Here is that post. This person's problem was creating on another drive - mine was simply having the ability to create the connection anywhere!
***********************************
Solved CREATE DATABASE on other drive. Permissions issue.
Windows Start -> Administrative Tools -> Component Services -> Services (local) -> MSSQLServer, right clicked Properties.
LogOn was `Local System account`, changed to `This account` with proper Domain\User having sysadmin privileges.
Exited from Managment Studio, went back in with Windows authorization as that user.
CREATE DATABASE then worked for the E: drive.
Note: during install, under Components to Install there is an Advanced option which allows browse to set dbs default to a different drive.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply