I have two computers, computer A is with SQL Server but has no space left, the other is computer B which has free space.
I'd like to create a new database on computer B. This is what I've done so far:
-
I've created a shared folder called shared on computer B, and mapped it on computer A as a network drive X:/ (or also \ADMIN-PC\shared).
-
set privileges on the folder so that Everyone can do everything
- I've checked that I can access the drive X from cmd
- I've run SQL Server Management Studio as Administrator, and logged in using Windows Authentification
-
then I've tried to create database
CREATE DATABASE [networked] ON PRIMARY
( NAME = N'networked', FILENAME = N'\ADMIN-PC\shared\networked.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networked_log', FILENAME = N'\ADMIN-PC\shared\networked_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Which gives me this
Directory lookup for the file "\\ADMIN-PC\shared\networked.mdf" failed with the operating system error 5(Access is denied.)
I've also tried to attach existing database on the network drive, with same result. How should I set the privileges so that I can create/connect to database on network drive?
Best Answer
Just to state the full limitations, this is SQL 2008 R2 and above, and it's best to use fully qualified names for the shares rather than mapped drives.
Are both computers on a domain? The SQL service account (or if it's NETWORK SERVICE then the computer account instead like DOMAIN\COMPUTER$) needs a few things:
That last one is a little doozy. As you mentioned you were running SSMS as Administrator (which is one issue sometimes). Otherwise you may be having double-hop issues; which you could eliminate by running it from the source machine over RDP (if you aren't already).
I feel like I'm not telling you anything you don't know already. When things get sticky there are two tools:
Hope that helps a little.