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?
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.