Simple Way To Get SQL Server To Access A Remote NAS

Question

I have no domains in my environment and I want to get my 2019 SQL server to connect to a QNAP NAS using regular credentials on the NAS.

I want to be able to do things like backup the database to it as well as read folders via the script.

I'm currently doing this by mapping drives in my sql script (which means that the passwords are in clear text.) I want to avoid this.

I'd rather create a credential but every time i try, it wants me to map it do an existing user.

Can someone give me the high level picture of what I should do?

For example, do i need a credential and a proxy? Do i need to use "Execute As" in my script?

Please be super simple and concise. I can look up the details of a command here-or-there but I don't know what the high level way to set this up is.

Answer 1

For this to work, the service account for SQL Server needs to have access to the share. Many times, you have a local machine account as the service account, for instance NT Service\MSSQLSERVER. Access to external resources will then be through the machine account. So things may work better with a real account. But even then the NAS must understand Windows accounts.

Given that you don't have a domain, it would be difficult of the NAS would be a Windows fileserver. You would need to run SQL Server under a Windows user, and the fileserver would need to have a user with the same name and password.

Answer 2

Instead of mapping the NAS share as drive, you/SQL Server can it directly using UNC path like

\\NASname\folder

See https://en.wikipedia.org/wiki/Path_(computing)#UNC

Answer 3

There are some situations were SQL Server uses stored credentials, for instance for access to Azure. But they are specific scenarios, and there is no general mechanism.

I can understand that changing the service account is not appealing. It can certainly cause new surprises.

There is one partial solution, however. You mention backing up databases. This is typically done in an Agent job. And with Agent jobs, you can use proxies. Not for T-SQL jobs steps, but CmdExec steps. So you would set up the backup job to use a proxy, and then you would configure this account in Windows with a credential for access to the NAS.

I have some more details on how to set up proxies for Agent jobs here: https://www.sommarskog.se/perm-hijack.html#agentjobs. (But that section does not discuss the NAS part.)

For xp_cmdshell, it is possible to set up a proxy, but this proxy is not used when you are sysadmin. And, again, this is a not a proxy to talk to NAS:s, this is so that non-sysadmin users will not access the computer outside SQL Server with the permissions of the SQL Server account.