Set Up Access To Network Shares

Set up access to network shares from SQL Server | sq... https://sqlsunday.com/2021/09/02/set-up-access-to-ne...
Set up access to network

shares from SQL Server
2021-09-02 / DANIEL HUTMACHER
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 (https://sqlsunday.com/2021/02
/05/connect-using-windows-authentication-across-domains/).
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
1 of 6 8/9/23, 08:48
version of the graphical Credential Manager. You can store a new

credential using the following command:
1 cmdkey /add:someserver.mycompany.local /user:networkuser /pass:strong
To view your stored credentials, use
1 cmdkey /list
… and finally, to delete a mistyped or outdated credential,
1 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
(https://dhmacher.files.wordpress.com/2021/09/image-1.png)
This will open up a command prompt logged in as the SQL Server
service account. If you want to make sure, run
1 whoami
Bonus: If the service runs on Local System, you can use the
SysInternals utility psExec (https://docs.microsoft.com/en-
us/sysinternals/downloads/psexec) to start a new session as
System.
2 of 6 8/9/23, 08:48
1 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:
1 EXECUTE sys.sp_configure 'show advanced options', 1;

2 RECONFIGURE;
3
4 EXECUTE sys.sp_configure 'xp_cmdshell', 1;
5 RECONFIGURE;
With that out of the way, you can now run commands:
1 xp_cmdshell 'whoami';
2
3 xp_cmdshell 'cmdkey /add:someserver...';
To lock the xp_cmdshell when you’re done,
1 EXECUTE sys.sp_configure 'xp_cmdshell', 0;

2 RECONFIGURE;
3 EXECUTE sys.sp_configure 'show advanced options', 0;
4 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.
3 of 6 8/9/23, 08:48
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.
1 cmdkey /add:storageaccount.file.core.windows.net /user:storageaccount
… 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.
4 of 6 8/9/23, 08:48
(https://dhmacher.files.wordpress.com/2021/09/image-2.png)
Network access: Do not allow storage of passwords and

credentials for network authentication
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.
AUTHENTICATION ,
CMDKEY ,
CREDENTIAL ,
WINDOWS AUTHENTICATION ,
XP_CMDSHELL
4 thoughts on “Set up access to

network shares from SQL Server”
1. IE
2021-11-02 AT 18:15
Hello, I’m using a Proxy for SSIS executions in SQL Server
Agent with a stored credential (domain user,
“Mydomain\SSISExecutor”). The proxy works fine in SQL
Server Agent, when authenticating to SQL server, fileshares
etc. However, I’ve added an Azure File Share credential to the
“Mydomain\SSISExecutors” credential manager using cmdkey
– jobsteps configured to use the SSIS Proxy running
packages that tries to access the Azure File Share fails with
“The username or password is incorrect”.
Adding the same credential, using cmdkey, to the domain user
that is running the SQL Server Agent service,
“Mydomain\SQLSrvAgentRunner” – and then executing the
job step with SQL Server Agent – the package succeeds in
5 of 6 8/9/23, 08:48
accessing the Azure File Share.

It seems like the Proxy Account is not reading the credential
manager when executing the job step, but the SQL Server
Agent does.
Any thoughts on this?
REPLY
2. Peter
2023-01-28 AT 19:15
If I understand correctly, the default account for different SQL
Server services is virtual account. Does virtual account have
access to network resources? If yes, are there any differences
between virtual account and other account types (domain
account, MSA, gMSA) in terms of accessing network
resources? If there is no difference, what are the reasons to
use other account types (domain account, MSA, gMSA)?
Thanks.
REPLY
◦ Daniel Hutmacher
2023-01-28 AT 20:43
The answer to that is a long blog post in and of itself. I
would start by googling. :)
REPLY
3. Pingback: Cross-domain backup using Ola Hallengren's
scripts
6 of 6 8/9/23, 08:48

Set Up Access To Network Shares

Uploaded by

Copyright:

Available Formats

Set Up Access To Network Shares

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Set Up Access To Network Shares

Uploaded by

Copyright:

Available Formats

Set up access to network shares from SQL Server | sq... https://sqlsunday.com/2021/09/02/set-up-access-to-ne...

Set up access to network

Using a domain account as your SQL Server service account will

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

Windows can store your authentication credentials for remote

The cmdkey command line utility is the keyboard warrior’s

version of the graphical Credential Manager. You can store a new

1 cmdkey /add:someserver.mycompany.local /user:networkuser /pass:strong

To view your stored credentials, use

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

How to make it work in SQL Server

You have a few different avenues to run the cmdkey command as

Run as different user

◦ Open the start menu and find the Command Prompt

psexec can do so much more, including creating a remote

xp_cmdshell isn’t enabled by default (and you should probably

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

To lock the xp_cmdshell when you’re done,

1 EXECUTE sys.sp_configure 'xp_cmdshell', 0;

SQL Server Agent (Operating System

This is the least desirable option in my opinion, because it’s very

SQL Server Agent (PowerShell)

Azure File Share

To set up a stored credential for an Azure File Share, you can

1 cmdkey /add:storageaccount.file.core.windows.net /user:storageaccount

… where the user name, storageaccount, is the name of your

Note that this is not a very granular way to assign permissions to

Make sure that your firewall configuration allows you to send

Allow storage of passwords in Group

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

Network access: Do not allow storage of passwords and

CMDKEY: Credentials cannot be saved from this logon

4 thoughts on “Set up access to

accessing the Azure File Share.

You might also like