Set up access to network shares from SQL Server

Using a local service account for your SQL Server service, your server won’t automatically have permissions to access to other network resources like UNC paths. Most commonly, this is needed to be able to perform backups directly to a network share.

Using a domain account as your SQL Server service account will allow the server to access a network share on the same domain, but if the network share is not on your domain, like an Azure File Share, you need a different solution.

There’s a relatively easy way to make all of this work, though.

Windows Credentials

Windows can store your authentication credentials for remote resources like Remote Desktop or network shares. You can view and edit those credentials directly in Windows using the Windows Credential Manager. I wrote a post a while back about how you can use that to set up Windows authentication for SSMS in heterogenous environments.

For most service accounts, however, you won’t be able to just log in locally and open the Credential Manager, so we’re going to need a little command line trickery to move on.

cmdkey

The cmdkey command line utility is the keyboard warrior’s version of the graphical Credential Manager. You can store a new credential using the following command:

cmdkey /add:someserver.mycompany.local /user:networkuser /pass:strongPas$w0rdHere

To view your stored credentials, use

cmdkey /list

… and finally, to delete a mistyped or outdated credential,

cmdkey /delete:someserver.mycompany.local

How to make it work in SQL Server

You have a few different avenues to run the cmdkey command as the SQL Server service account, from easy to relatively messy:

Run as different user

If you know the password for the service account and you’re able to log on locally to the server,

• Open the start menu and find the Command Prompt
• Right-click the menu item, open “More” and click “Run as different user”
• In the login prompt, log on as the service account

This will open up a command prompt logged in as the SQL Server service account. If you want to make sure, run

whoami

Bonus: If the service runs on Local System, you can use the SysInternals utility psExec to start a new session as System.

psexec -s -i cmd.exe

psexec can do so much more, including creating a remote session on another machine, or even running a shell command on every computer in a domain. If you’re a systems administrator and you’re not familiar with SysInternals, you should definitely read up.

xp_cmdshell

If you don’t know the password of the service account, you can run commands in SQL Server’s security context using xp_cmdshell. To do this, you’ll need to be a member of the sysadmin server role in SQL Server.

xp_cmdshell isn’t enabled by default (and you should probably turn it off when you’re done), so you may need to reconfigure SQL Server first:

EXECUTE sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

EXECUTE sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

With that out of the way, you can now run commands:

xp_cmdshell 'whoami';

xp_cmdshell 'cmdkey /add:someserver...';

To lock the xp_cmdshell when you’re done,

EXECUTE sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXECUTE sys.sp_configure 'show advanced options', 0;
RECONFIGURE;

SQL Server Agent (Operating System Command)

If your SQL Server Agent service uses the same service account as the SQL Server, or if you have set up a proxy credential for the SQL Server service account, you could create a job with an Operating System step.

This is the least desirable option in my opinion, because it’s very hard to troubleshoot.

SQL Server Agent (PowerShell)

In the same vein, you could also use SQL Server Agent to run a PowerShell command that does the same thing. I don’t know of any native Microsoft modules to work with Credential Store, however. So you’d have to rely on third-party modules for this.

Azure File Share

To set up a stored credential for an Azure File Share, you can perform the following steps:

• In your storage account, find the “Access keys”. You can use any one of them.
• Use cmdkey to add a credential for the storage account.

cmdkey /add:storageaccount.file.core.windows.net /user:storageaccount /pass:1234567890abcdef==

… where the user name, storageaccount, is the name of your Azure storage account, and the password is one of the two access keys for the account.

Note that this is not a very granular way to assign permissions to a share – the holder of the access key has full permissions to the entire storage account.

Make sure that your firewall configuration allows you to send outgoing Internet traffic on tcp/445. Many home/consumer ISPs may block this port, but a VPN could get you across the finish line.

Allow storage of passwords in Group Policy

Storing credentials will only work if it isn’t prevented in your group policy.

If this setting is enabled, you’ll get an error message when you try to store credentials:

CMDKEY: Credentials cannot be saved from this logon session.