CODEWITHSUNDEEP
sql-serverazureazure-file-share
Wenlocke
Wenlocke

Reputation: 155

How to let local SQL see Azure file Share

We're running a number of azure VM's with local SQL Server instances for development purposes (not externally accessible). We also have an Azure File share that we are using as a shared drive for the machines, which we are mapping under a /user: username /pass: password credential.

I am aware that it is possible from other answers to get SSMS to see the mapped drive (and hence be able to properly back up to that drive, or restore from that drive) by running a script within SSMS that uses XP_CMDSHELL to map the drive within the context of the SQL Server service user, but this has to be done every time the server is started.

So the question becomes: is there any way I can feed the /user and /pass credentials to the NTSERVICE\MSSQLSERVER user so that it automatically has access to the mapped drive, or to get it to do a mapping that that account can see on machine startup? I'm not planning to try and have the active server files (.mdf and .ldf) on the share as that's a bad idea, but I would like to be able to backup and restore to the fileshare through SSMS without having to manually intervene when the machine is started.

Upvotes: 0

Views: 355

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89396

cmdkey stores a persistent credential. You don't have to run it on every boot.

xp_cmdshell runs as the service account, so you can persist the credential for Azure files using that command. The script to persist the credential is available in the Azure portal for your storage account.

You don't want a drive mapping: just use UNC paths.

eg

exec sp_configure 'show advanced options', 1

reconfigure

exec sp_configure 'xp_cmdshell', 1

reconfigure

go

exec xp_cmdshell 'cmdkey /add:<YourStorageAccount>.file.core.windows.net\backups /user: AZURE\<yourUser> /pass:lmweksD...36DxuReQ=='

go

exec sp_configure 'xp_cmdshell', 0

reconfigure

go

Upvotes: 0

Related Questions