Problem attaching DB from Network Share

SQL Server Database Engine

Hemant Kumar



Hello All,
I 'm trying to attach a database to my sql server 2005 using the following syntax:

Code Snippet

DBCC TRACEON(1807, -1)
GO
EXEC sp_attach_single_file_db 'MyNetworkDB', '\\leaf7\dbs\MyNetworkDB.mdf'
GO


Also, the folder dbs and the mdf file MyNetworkDB.mdf has full permission for Everyone.And when executing the above statement, i get the following error:

Code Snippet

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\leaf7\dbs\MyNetworkDB.mdf" failed with the operating system error 5(Access is denied.).






Top

Re: Problem attaching DB from Network Share

oj


This is by design and really not recommended due to I/O problems. If you must, take a look at the following KB.

http://support.microsoft.com/kb/304261






Top

Re: Problem attaching DB from Network Share

Hemant Kumar

Yes oj,

After referring that kb, i added the following statement

DBCC TRACEON(1807, -1)

But after this i get the access denied exception when trying to attach the database. Is there anything that i must look to overcome this access denied exception.




Top

Re: Problem attaching DB from Network Share

oj

Your sqlserver service account must have permission to the network share. By default LocalSystem does not have access to anything on the network. So check your startup account and grant it appropriate permission. You will need FULL right to the network share because sqlserver must be able to take exclusive lock on the files when loading them.





Top

Re: Problem attaching DB from Network Share

Madhu K Nair

Its not at all recommended and it will trouble you if not today tomorrow... change your achitecture instead of sticking on to this.

Madhu





Top

Re: Problem attaching DB from Network Share

Hemant Kumar

Hi Oj,
Can you please guide me how to do this. (granting permission to sql server and all that)





Top

Re: Problem attaching DB from Network Share

Madhu K Nair

Every service in SQL Server starts under some OS user context. So your SQL Server startup account should have access to the Shared netwrok location where the database file is kept. So if you have Domain , use a domain account which have proper permission to the location as startup account of the SQL Server.

refer

http://msdn2.microsoft.com/en-us/library/ms345578.aspx

Madhu





Top

Re: Problem attaching DB from Network Share

oj

Run:

Code Snippet
exec sp_msgetserverproperties

This will give you the account that your sqlserver starts up under. If it is "LocalSystem" you will not have access to network share. If it's a domain account, you need to logon to the server where the share is and give the sqlserver service account full access to the share.

Again, unless your network storage is part of the Microsoft approved list of hardware, you are running under an unsupported platform.





Top

Re: Problem attaching DB from Network Share

Hemant Kumar

Hi oj,
I executed the above system sp and it gave me the following output:

Auto_Start = 1 Startup_Account = NT AUTHORITY\NetworkService

Then i tried to attach the database using the single file and it gave me the following error message:

Code Snippet

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyNetworkDB_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MyNetworkDB'. CREATE DATABASE is aborted.



Then i tried to attach the database using both the mdf and the log file and it gave me the following error message:

Code Snippet

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "\\leaf7\dbs\MyNetworkDB_Log.LDF". Operating system error 5: "5(Access is denied.)".







Top

Re: Problem attaching DB from Network Share

oj

Logon to [leaf7] server and give Network Service full access to [dbs] share. If security is not an option, you can just give [everyone] full access to the share.





Top