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

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

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.