0

I have Synology NAS with local credentials authentication. How can I create a backup from MSSQL to such share? My only option is to authenticate with username/password combo since the Synology UI does not allow adding anything else.

Kamsiinov
  • 103
  • 3

2 Answers2

2

With mssql running under Local System I don't think there's a direct way.

You can either

  • run mssql in a user account (by service configuration) where you have provided and permanently stored the logon credentials or
  • you can stage the backup to a local directory that you then copy or move to the NAS with an additional batch job (task scheduler or similar) and with explicit user logon in the batch (net use \\nasname\sharename /user:username with a previous net use \\nasname\sharename /user:username /savecred with the same user).

I'd prefer the latter, with the benefit that the backup still runs when NAS authentication fails.

Zac67
  • 8,639
  • 2
  • 10
  • 28
  • Earnest question - if you go with the `net use` solution, wouldn't you be able to write the backup directly to the NAS? That is, at that point, I don't think anything in the chain cares what is writing bytes. I do agree with the robustness of "write locally and copy" though. – Ben Thul Jul 13 '22 at 14:58
  • If you use MSSQL's native backup, the file is written by the service. If that's running as Local System you can't login to the NAS unless you find some way to run a batch script from the service. – Zac67 Jul 13 '22 at 16:57
  • 1
    I've had good luck doing `exec xp_cmdshell 'net use ...'` at which point the SQL service account (whatever it happens to be) should be able to access the remote share. I've done this for restore command, but not backups (though I'd expect them to work the same). – Ben Thul Jul 13 '22 at 18:47
  • Yes, that should actually work as well. :-) – Zac67 Jul 13 '22 at 18:58
1

If scheduling a task with powershell is an option you could write a script using dbatools

Mount the share as a drive with Net use and use dbatools command Backup-DbaDatabase to backup to this drive.

You could even use the SQL Servers Agent by creating a Powershell type step in a job.

I guess it'd even be possible to add the credentials to the Credential Manager and directly backup to UNC path without mounting first.

Backup to network drive with default tools (would not recommend)

You'd need to execute this prior to your backup operation. Also: I've never used this to perform regular backups. Works fine though for occasional usage.

Source: Make Network Path Visible For SQL Server Backup and Restore in SSMS

activate xp-cmdshell

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

connect drive

MSSQL 2008

EXEC XP_CMDSHELL 'net use /user:[Domain]\[Username] H: \\RemoteServerName\ShareName [Password]'

MSSQL 2012 +

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName'

delete drive

EXEC XP_CMDSHELL 'net use H: /delete'
Manu
  • 704
  • 2
  • 19
  • Powershell is not an option for me. I would like to use the buillt-in backup mechanisms so I can configure data retention etc. – Kamsiinov Jul 13 '22 at 09:30
  • Ok, I understand. Although this could also be handled in the script. For example by searching for the files with Get-ChildItem query by LastWriteTime property and pipe it into Remove-Item. There is a way to make network drives accessible for mssql, but I would not recommend it to perform regular backups. I'll add it to my Answer. – Manu Jul 13 '22 at 10:21
  • Yea I understand that I could do all of that with scripts and will do that if the less scripted way is too cumbersome. – Kamsiinov Jul 13 '22 at 10:34