1

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?

  • 1
    The service account needs read write permission to the share. Also, what version of sql server you are using ? – Kin Shah Jul 26 '16 at 13:36
  • Thank you for replying, version is SQL Server 2012 SP1, Standard why is it not possible when "Everyone" has read write permission? Ok, so I'll try to add permission for account NETWORK SERVICE I guess, on the pc B. – DropDropped Jul 26 '16 at 13:47
  • NETWORK SERVICE is an account local to computer A, it will be present on computer B but is not the same account. Running SSMS in administrator mode won't do anything, all actions are taken through account running the SQL Service. – Jonathan Fite Jun 30 '17 at 12:13
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.

  • thank you for the reply. Is it required that both computers had to be on a domain? – DropDropped Jul 26 '16 at 14:58
  • I mean, they are connected directly via ethernet. Is domain necessary? – DropDropped Jul 26 '16 at 15:31
  • So they're both in a workgroup? I don't know if computer names pass over it like with AD. Try process monitor and see what's happening. You might need to change the SQL service (in sql configuration manager) to run under a local user account, and then create that account on the other server with the same password. But when it comes to attaching files it's more complicated because of that extra user check - try and see I guess. – Cody Konior Jul 27 '16 at 1:03
  • They have the same workgroup set. I've added an user account 'adminuser' on computer B (it is on computer A already) with same password. I've set the logon for the mssql services to this 'adminuser'. I've set the network of computer A with SQL server as Home, now when I run CREATE DATABASE, it creates the files, but it cannot connect to the database because db is not listed there, and I got message "Logon failure: unknown user name or bad password." – DropDropped Jul 27 '16 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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