Dears ,
i am trying to make the mapped drive visible to SQL server and as a security requirement, I need to use user name and encrypted password because this will be saved on the logs if it is plain text.
EXEC xp_cmdshell 'NET USE F: \\DefinitelyNotMyLaptop\SQLBackups/ user:SqlUser P@$$w0rd'
i tried to declare a variable and save the .txt password on it but it did not work
any ideas?
use cmdkey to install the credential ahead of time in the Windows Crediential store for the SQL Server service account, eg:
exec xp_cmdshell 'cmdkey /add:myserver /user:myserver\sqluser /pass:P@$$w0rd'
Then omit the password and user from the NET USE command.
exec xp_cmdshell 'NET USE f: \\myserver\share'
Or skip the drive mapping and backup to a UNC path, eg \\myserver\share\mydatabase.bak.
And in a domain environment you should just grant access to the share for the SQL Server Service account (or the machine account if using the default per-service SID).
NET USEcommand allows for any form of encryption, so the network drive in question should be persistently mounted (from Windows explorer, not from cmd shell) using credentials stored and secured by the Windows OS rather than mounted at runtime.xp_cmdshellto do this, as opposed to a standard way in Windows?