Sql-server – Connecting Remote Shares via Powershell

azure-vmjobspermissionspowershellsql server 2014

I had a big problem which I just happened to solve, but it's a permissions issue I don't quite understand. Unfortunately I'm in a tight bind and no amount of googling in the past week has really helped, so perhaps someone could explain what happened here:

As part of an interface out to our customers we developed a Powershell script that maps a network drive on our web server WEB. The job step is run on a regular basis to validate the connection is active:

Connect Job

Owner: [JobsLogin]

Step 1: Connect Share

Type: PowerShell

Run as: Sql Server Agent Service Account

Command: 

$Scripts\Map-Share.ps1 (My configuration values)

Map-Share.ps1

param (
    [string]$DriveLetter,
    [string]$Path,
    [string]$User,
    [string]$Password,
    [string]$Persistent="No"
)
if (!(Test-Path "${DriveLetter}:"))
{
    net use ${DriveLetter}: $Path /u:$User $Password /persistent:$Persistent
}

Then we run BCP to output the file to the mapped site:

declare @cmd varchar(2048)
SET @cmd = 'bcp "exec ' + 
    @SPName + ' " queryout "' + @DataFolder + @FileName + '.tmp" ' +
    @BCPFlags
print @cmd

EXEC master..xp_cmdshell @cmd

At some point in the past week this mapping became unavailable and we started receiving the dreaded "BCP cannot open host-file" error message.

After an number of hours mapping network drives, deleting mapped network drives, running diagnostic command shell, pleading with god and then satan, checking registry values, I started to suspect that the mapped drive was not mapped for the user executing sp_cmdshell.

I ran exec sp_cmdshell "cd W:" and found that it wasn't working. I ran exec sp_cmdshell "net use" and saw that the status of W was "Unavailable". Finally, progress!

And a

exec sp_cmdshell "net use /delete W:"

exec sp_cmdshell "Powershell $Scripts\Map-Share.ps1 (My configuration values)"

later, everything works again!

But now I'm stuck, because I can't figure out how to run these commands in the job as the correct user (I've tried the services account with no success). I could change the Connect share job to run the powershell command but that seems like a hack to me (But I'm not a database guy, so maybe that is right).

Anyway, what is really going on here? What user is my drive getting mapped in the connect share job?

Best Answer

Alright, so this solution has turned out to be pretty ugly. The end result is that I have to run the powershell command via the Powershell job step type and run it using xp_cmdshell in a sql job step type. Running it in both contexts guarentees the drive is mapped.

I then run a secondary step for both environments in which I test to make sure the drive is mapped using the same logic, ie:

Sql (CmdShell) Check

exec xp_cmdshell 'powershell if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}'

Powershell Check

if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}
Related Question