Sql-server – n alternative to xp_cmdshell and net use for backing up MSSQL to UNC as a different user

backupnet-usesql server

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).

Best Answer

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$.

Related Topic