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?