Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I appreciate that the above should not be done ('Creating a New Database Across a Network with SQL Server 2008 R2') for all of the reasons outlined here but a client wants to know how to do this and cannot be told otherwise.

To setup and test this I have two laptops connected across a local area network (machine one is 'VAIOE', machine two is 'VAION'). I am attempting to write a new database using VIAOE running SQL Server 2008 R2 Developer Edition across the network to a shared drive on VIAON called 'CShare' (VAION's C:Drive). I have enabled all of the appropriate permissions such that I am able to navigate (using VAIOE) on to VAION '\VAION\CShare\Databases\' and create a new Access database (for example).

Now to attempt to create a database (from VAIOE onto the C:Drive of VAION) using SQL Server 2008 R2 in management studio, I open a new scipt and write (USING Master):

CREATE DATABASE [networkedR2] ON  PRIMARY
( NAME = N'networkedR2', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf' , 
  SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
LOG ON 
( NAME = N'networkedR2_log', FILENAME = N'\\VAION\CShare\aaData\aaSSDBs\networkedR2_log.ldf' , 
  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) 
GO

and I get the following error

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "\\VAION\CShare\aaData\aaSSDBs\networkedR2.mdf" failed with the operating system error 5(failed to retrieve text for this error. Reason: 15105).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

indicating I do not have the correct permissions. Can someone tell me ewhat I have done wrong here as I have hit a wall?

Edit: I am aware that for earlier versions of SQL Server the DBCC TRACEON(1807, -1) flag was required. This should not be the case here.

share|improve this question
    
The .mdf and .ldf file already in the network drive , i no need to create just need to attach/ –  user20721 Mar 1 '13 at 4:08
add comment

2 Answers 2

What account is SQL Server running as on VAIOE? That will be the one that needs permission to access the share on VAION. And if SQL Server isn't running as a domain account, that might be tricky to get it to authenticate correctly.

share|improve this answer
    
You'll need a domain account when crossing servers. –  jl01 Jan 19 '12 at 16:48
    
+1 Did you successfully navigate to VAION using the same account the SQL Server service is using? If non-domain a local user with same username/password will need to be created on both laptops. –  Shawn Melton Jan 19 '12 at 16:51
    
@ShawnMelton I did. I navigated to CShare on VAION from VIAOE where on VAIOE I was logged in as 'Administrator'. This account is the one SQL Server was installed with and has full privleges as far as SQL Server goes. The account that was currently logged in on VAION when attempting the above was also the 'Administrator' account which has the same password as the one I was using on VAIOE. What do you mean by domain account in the above? –  Killercam Jan 19 '12 at 17:59
    
@jl01 What is meant by a domain account in this context? –  Killercam Jan 19 '12 at 18:01
    
@Killercam An Active Directory domain account. I know you can save passwords when browsing a share (and manually edit them by running "control userpasswords2"), but I have no idea if SQL Server will use those when attempting to authenticate a remote resource. I kind of doubt it. –  db2 Jan 20 '12 at 14:47
add comment

I never tried this across network, but on the data folder on which you want to create the databases, did you create a share in addition to the security? This might be helpful as if you recall , the attach/detach mechanism also requires a share between data folders.

share|improve this answer
    
Share on the top level directory will pass (and is set to pass in this case) all privileges down the hierarchy to subfolders and files. In light of this, your advice will not help me here. Thanks very much for your time. –  Killercam Jan 19 '12 at 16:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.