using sqlcmd command to get the result to a file in a shared path with user name and password protection

Question

I have a sqlcmd command which will give the result of to a file which will be placed in a shared folder.

exec xp_cmdshell 'sqlcmd -s $dataSource -d $dbName -i $inputFilePath -o $outputFilePath'

Now, what if the shared drive is protected and requires username and password. How to give credential in the Sqlcmd to bypass the authentication.

Answer 1

xp_cmdshell will execute under the NT (Windows) credentials of:

• impersonated login if logged in using Windows credentials
• service account if logged in using SQL credentials and no explicit credential object exists
• explicit credential is logged in using SQL credentials associated with a credential (see CREATE CREDENTIAL

if you insist on accessing a remote resource (file share) using the default context, you're uphill shitcreek without a paddle, as impersonated access to remote resources is 'double-hop' and requires constrained delegation for at leats one of the cases (logged in using NT).

A better option is to explicitly map the remote share \\server\share locally as a drive X: and then access drive X: instead. Mapping a drive locally allows for persisted credentials to be stored, but you have to be careful to make sure the mapping is visible in the service account session. Which is... basically impossible, see Map a network drive to be used by a service.

Now that you know why you cannot do this properly and you'll be pulling your own hair, meanwhile turn white from constantly be fighting difficult to troubleshoot failures, stand back and look at the problem from a different angle: Why do you want to use xp_cmdshell to call sqlcmd? Call sqlcmd directly, from a job/process. SQL Agent has all the support for you need for this, just set the job to run under a proxy account with appropiate credentials to connect to both the remote share and the destination $datasource.