0

We are doing the following to give SQL Server access to a network drive (using a specific username and password) to allow backups to be written to subdirectory2 on the network share networklocation:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
EXEC XP_CMDSHELL 'net use "\\networklocation\subdirectory1\subdirectory2" password /USER:domain\username /persistent:yes'
GO

The issue is that when SQL Server is restarted (or the server is restarted) this access is lost.

So we can tried to add the /savecred flag like this:

EXEC XP_CMDSHELL 'net use "\\networklocation\subdirectory1\subdirectory2" password /USER:domain\username /savecred /persistent:yes'
GO

but this gives the error:

"A command was used with conflicting switches".

There seems to be no way to save this information and persist it. I could create a startup stored procedure that is run when SQL Server is restarted but this does not seem a clean way to do it.

Does anyone know how this can be done?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ian Hannah
  • 131
  • 1
  • 1
  • 10
  • 2
    Why not grant SQL Server access to this directory via user permissions and save yourself trouble running CMDSHELL scripts. – Alex Jun 25 '20 at 21:30
  • I believe that to do that, we would need to run SQL under the same domain account and we do not want to that. If there is another way, would you be able to give more information? – Ian Hannah Jun 26 '20 at 12:27
  • Have a look at this: https://sqlcommunity.com/best-practices-for-sql-server-service-account/ . – Alex Jun 26 '20 at 21:17

0 Answers0