ERROR:Powershell script from SQL management studio

[mstechmach]

Hello,

I was trying to execute a PowerShell script from SQL management Studio as shown blow.

DECLARE @cmd VARCHAR(1000);
SET @cmd = 'powershell.exe -File c:\temp\PowerShell.ps1';
EXEC xp_cmdshell @cmd;

and the getting this response.

Here is the PowerShell script content. - I'm trying to start the F5 client by passing credentials.

## Start VPN connection
cd "C:\Program Files (x86)\F5 VPN"
.\f5fpc -start /u "myUserId" /p "pwdHERE" /q

## Wait 10s to complete connection
Start-Sleep -Seconds 25

## Get VPN connectionID
$e = (.\f5fpc -info | findstr /r ^[1-9] | ForEach-Object {$_.Substring(0,7)})

The problem is- when I run the PowerShell script using PowerShell IDE, it works and could start the F5 client.  However, when I run the same script using the SQL management studio as shown above, its not able to start F5 client. 

Could you please review this and let me know if there's anything I'm missing?

Thank you in advance!

[#2]

However, when I run the same script using the SQL management studio as shown above, its not able to start F5 client. 

---

I wonder what you expect here?

The PowerShell script gets executed on the machine where SQL Server is running and this under the SQL Server service account, which has not profile.

That will never work.

Use PowerShell Remoting instead, but if that client has a GUI, it won't work, too.

Running Remote Commands - PowerShell | Microsoft Learn

[#3]

@olafhelper Thank you for your response.

We want to pull data from different external agencies (about 10+) and each of them have their own VPN credentials. So instead of setting up different VM for each agency we are thinking of have one VPN client on a server and dynamically start (with credentials) and stop the F5 using the PowerShell Script.  - this is what we are trying to do.

In your response you mentioned the "no profile" - it we setup a profile, will it work? also how to set the profile. Please share some knowledge on that.

thank you!

[#4]

we setup a profile, will it work? also how to set the profile. 

---

@mstechmach , I wouldn't bet on. Why do you want to find a solution using database engine? I would use a SSIS package or a remote PowerShell solution.

[#5]

Thank you! I will try it out.