3

Is there an alternative to xp_cmdshell and net use for backing up MSSQL to a UNC with a different user?

I'm now using 

EXEC xp_cmdshell 'net use \\SERVER\SHARE  Password /USER:DOMAIN\USER /PERSISTENT:yes'

as a preexec script.

But xp_cmdshell is disabled by default (owing to security issues, I'd guess).

squillman
  • 37,618
  • 10
  • 90
  • 145
Mössler
  • 31
  • 1
  • 2

3 Answers3

2

For BACKUP statements SQL Server will use the service account under which the database engine service is running as the security context for interaction with the operating system. Therefore you would need to grant the SQL Server service account access to the share. There's no way around this other than your xp_cmdshell hack.

For example:

If the SQL Server service is running as the domain user MYDOMAIN\SQLUser then you would need to modify the share permissions to allow access to this user.

If SQL Server is running as NT AUTHORITY\NETWORKSERVICE then, assuming your SQL Server machine name is SQLHOST, you would need to modify the share permissions to allow access to SQLHOST$.

squillman
  • 37,618
  • 10
  • 90
  • 145
0

I think there aren't other ways for that.

normal TSQL Backup Skripts only allow to backup to local disc.

If you have enough space you could make a SQL-Task which makes a backup on local disk and after this move it via powershell to a UNC path.

if you don't have enought space for this: with the following you can activate xp_cmdshell again...

    -- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

see: http://msdn.microsoft.com/en-US/en-en/library/ms190693.aspx

frupfrup
  • 853
  • 3
  • 13
  • 27
  • TSQL `BACKUP` allows backup to UNC paths – squillman Oct 29 '14 at 16:17
  • Oh ok. I'm very sorry. Did't know that. Saw you post above and did a voteup. So i have learned something today. Thank you! – frupfrup Oct 30 '14 at 07:18
  • one last thought: Maybe you have a chance to use SQL-Server Proxy? see following links: (couldn't test it at the moment) http://www.mssqltips.com/sqlservertip/1199/sql-server-agent-proxies/ AND http://technet.microsoft.com/de-de/library/ms189064(v=sql.105).aspx – frupfrup Oct 30 '14 at 07:28
  • It's a good thought :) But backups happen through the database engine which doesn't need a proxy. In fact, you can't create a proxy for the database engine. – squillman Oct 30 '14 at 12:29
0

Use a symbolic link that's been created after using net use to put in the appropriate credentials.

net use \\SERVER\SHARE  Password /USER:DOMAIN\USER /PERSISTENT:yes
mklink /d \\SERVER\SHARE C:\Mounts\SHARE

Then in the script, use C:\Mounts\SHARE as the backup path.

austinian
  • 1,699
  • 2
  • 15
  • 29