backup to NAS using sqlcmd

Question

I've been searching for possible duplicates before asking but can't find anything. The closest was Backup SQL Server To Nas but not exactly my case.

We have a brand new Synology NAS configured, due to some security needs is configured to use a dedicated user and pwd not included on the active directory or alike. Already tested and is ok, can login, see the files and all rights are ok.

On the other side, we have backups running using Ola Hallengren useful scripts, by running slqcmd.

Question: how I can provide user/pwd credentials to access the NAS? Have tried using -U -P but got following error. Then tried with -S but also error. What I'm doing wrong here? I guess should be something silly whatever I'm missing, but can't possible find it.

Sqlcmd: The -E and the -U/-P options are mutually exclusive. Process Exit Code 1. The step failed.

Answer 1

[soapbox]

I would tell them "for security reasons" you should provide Windows Authentication access to that storage. Ask them if they really want authentication information in plain text saved to access a box that stores all the data for the organization and would give a user full access to their data (unless you are encrypting your backups)?

[/soapbox]

For your specific issue the parameters in sqlcmd for authentication are to access a SQL Server instance, not to access external storage or other resource. You will need to adjust the scripts to more or less map the drive with the required credentials and then use that path within your backup script.

If you are on SQL Server 2014 I would suggest using PowerShell script or SQL Agent step as this contains easier methods of working with the file system and mapping drives. You can quickly check access to the path before running the T-SQL code to ensure it does not fail prematurely.

• Yaroslav: We're actually running a job from SQL Server Agent Service Account and operation type is CmdExec
• user507: Then have you tried just creating a Proxy account?
• Yaroslav: totally forgot this question, sorry, ended up using the suggested approach, POSH script mapping the drive and then working with the files, works ok