Problem attaching a database

    Hello, I have made a database in SQL Server 2005 Developer edition. I tried to attach it to a SQL Server 2005 Express Edition but I coundn't. I can attach and dettach it to the developer edition but I cannot do it to the express edition. When I am running the store procedure:

exec sys.sp_attach_single_file_db @dbname = 'LinGS',   @physname = 'c:\LinGS.mdf'

I receive the following error:

File activation failure. The physical file name "C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS_log.ldf" may be incorrect.
.Net SqlClient Data Provider: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'c:\LinGS_log.LDF'.
.Net SqlClient Data Provider: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'LinGS'. CREATE DATABASE is aborted.

Can someone help me how to attach this database

Thanks in advance

ps. Now I cannot attach to the developer edition by running the following command:

exec sys.sp_attach_single_file_db @dbname = 'LinGS',
   @physname = 'C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS.mdf'

I receive the following error:

.Net SqlClient Data Provider: Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Documents and Settings\Kekakos Elias\My Documents\Visual Studio 2005\Projects\LinGS\LinGS.mdf". Operating system error 5: "5(error not found)".

Answer this question

Problem attaching a database

Shawn Stern
I am getting the same issue! and I believe it is a system security issue...

so... how do you get SQL Server to run under the system administrator account.. I get this error when I try "Attaching" or "Createing" a whole new database from a script... the same error "System Error 5 (Accessed Denied)"

I need to change ALL our SQL Server instances to run under the administrator account... how do we do this

ward0093
TheCurse
Is this problem solved The easy way:
For your data_file.mdf diretory, make sure the user security group has write and create data permission.
Shane Gidley
I am having the same problem when trying to attach a database. I can attach the database just fine from the computer on which SQL Server 2005 is installed. However, if I try to attach the database from a workstation I receive the error "Unable to open the physical file 'c:\Program Files\Microsoft SQL Server\MSSQL1.1\MSSQL\Data\TravCom.mdf'. Operating system error 5 (Access Denied)."

Previously I was using SQL Server 2000 and this worked just fine from both the server and the workstation. However, since upgrading to SQL Server 2005 I no longer can attach from the workstation. I can detach the database from the workstation, but I cannot reattach it.

Just for kicks and giggles I enabled complete sharing to the Data folder on the server. However, that shouldn't matter. It didn't in previous versions.

Also, I can create a brand new database on the server from a workstation as well as any other administrative functions I can think of, just not attaching a database. I tried both the sp_attach_db stored procedure as well as CREATE DATABASE FOR ATTACH command. Both give me the same error.

Does anyone have a solution - or could this be a bug I'm thinking the latter.
othman_11
I had the same problem and changed Account that was used by SQL Server service to log on. See appropriate SQL Server service proterties in "SQL Server Configuration Manager -> SQL Server Services"
CodeAnger
If you detach a database that is your default database, you can encounter an error when trying to re-attach it. Try connecting to the database under another administrator account, set your default database to master, reconnect under your original account, and try to attach the database.
ShinjisukeSono
Hi there,

You've probably resolved this issue but here's my 2cents worth:

I found that when using the Attach Database wizard i've had to remove the LDF entry where there wasn't an LDF file. The wizard create one for you automatically.

So if, in your script, you're specifying an LDF file, maybe try removing it.

Let me know if it works.

Cheers,
Jeff Walsh
If you are getting "Operating system error 5 (Access Denied)", then this is strictly a Windows security issue. Are you sure that the account SQL Server runs in has access to the file Does it work if you start SQL Server in your account by switching to the MSSQL\BINN directory and starting sqlservr.exe from the cmd window
ebc
The error 5 is an "access denied" error.
You may need to use an admin account to actually change the security on the file such that the sql server process can open it.
Hope that helps.
coronaride
I'm not sure I understand how this can be a Windows security issue since 1) it worked just fine before under SQL Server 7 and SQL Server 2000, and 2) it works just fine when trying to attach from the server itself. Let me try to explain further.

I have created a utility which allows a user to attach a database. The utility creates an ODBC connection to the SQL Server using the sa account and password. The utility then calls the CREATE DATABASE xxx FOR ATTACH. This utility works splendidly when run from the same computer that SQL Server is installed. However, when run from a workstation it fails with the Access Denied error.

Since the utility works when run from the server then I would imagine that the account SQL Server is running under has access to the file. It shouldn't matter which computer is issuing the CREATE DATABASE command, as long as that workstation has access to the server and the connection is made with the proper credentials (sa account). Furthermore, I am able to run many other statements from the utility on the workstation, including detaching the database, creating a new database, shrinking the log file, create users, grant/revoke priveledges, and every other administrative task I have tried. Just not the attach. I even tried using the classic sp_attach_db stored procedure to no avail.

So, could this still be a security issue The only thing I can really think of is that the way the attach in SQL 2005 has changed where it must be using the Windows account from the workstation somehow to validate access to the file.

Any other suggestions
aalford
An alternative solution is to not use the root directory as the destination.
tamikn
If you installed it already (with the NetworkService account) is there a way to change the existing install I don't want to have to go un-install all the machines running with NetworkService.

Thanks,

ward0093
LuisValencia
I had the same problem.

Solution: Install SQL server express with SQLACCOUNT paremeter

Example:

setup.exe /qn INSTANCENAME=MYSERVER ADDLOCAL=SQL_Engine,SQL_Data_Files,Client_Components,Connectivity SQLBROWSERAUTOSTART=1 SQLACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" AGTACCOUNT="NT AUTHORITY\SYSTEM" ASACCOUNT="NT AUTHORITY\SYSTEM" RSACCOUNT="NT AUTHORITY\SYSTEM"

Default is Network Service (NT AUTHORITY\NetworkService) Account.
ThomaWe
does your problem solved

in case it doesn't,

the first time u connect to the SQL server, try using Windows Authentication instead of SQL Server Authentication.

i face the same problem and it works that way.
sweetnlowe
Hi!
Well I faced the same problem creating a new database through scripts. Pazout's comment helped quite a bit. There are few other important things that I noticed though:
I was able to create a database in "C:" (i.e.\myname\Database.mdf" ) or any other drive. But the security error occured when I tried to create the database files in "Program Files".

Now to resolve this you need not to uninstall Sql Express. Just go to SQL express configuration manager and click on Services from the left pane. From the right pane select Sql server service and go to its properties. The builtin login should be "Nerwork Service", change it to "Local system" and restart the service. thats it.

cheers :)
Problem attaching a database

Answer this question