Attaching / creating database on network drive - Access is denied

Question

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:

1. 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).

2. set privileges on the folder so that Everyone can do everything

3. I've checked that I can access the drive X from cmd
4. I've run SQL Server Management Studio as Administrator, and logged in using Windows Authentification

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

Answer 1

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:

• Full control on the share.
• Full control on the NTFS folder on the other side of the share.
• If you're attaching files, your own account needs the same permissions.

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:

• xp_fileexist and xp_cmdshell (to check SQL's access to various parts, like can it ping the network address, can it see the share, can it echo something to a file there, etc).
• When you're out of all options, run the sysinternals utility Process Monitor on the share destination (fine for Windows, not an option when it's NetApp or something else). You can add a filter on access denied, these pop up when you try to access the share, and you can dig into the account details being used for the access and getting denied.

Hope that helps a little.