Powershell script executed by SQL Server doesn't take effect
0
votes
1
answer
2533
views
I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (
The cmd.exe command line looks like this:
When I execute this command manually, it can change the execution policy without any issue (I changed all execution policy back to the original values after this).
The PowerShell process command line looks like this:
When I execute the PowerShell process command line manually, I can change the execution policies too. I don't have any idea why when I run the command through
c:\temp\sqltotext.ps1
).
echo "haha" | Out-File "c:\temp\haha.txt"
Then I execute the script through xp_cmdshell
like this.
DECLARE @result INT;
EXEC @result = sys.xp_cmdshell N'powershell.exe -Command "Start-Process powershell.exe -Verb RunAs ''-NoProfile -ExecutionPolicy Bypass -File c:\temp\sqltotext.ps1''"';
IF (@result = 0) PRINT 'Success';
ELSE PRINT 'Failure';
It runs successfully and I can find the file c:\temp\haha.txt
with the content haha
.
However, when I change the content of c:\temp\sqltotext.ps1
to:
echo Process CurrentUser LocalMachine | %{Set-ExecutionPolicy bypass -Force -Scope $_}
and execute the same aforementioned TSQL command, the TSQL reports success but I didn't get the expected result (the execution policy in all scopes were NOT changed).
When I execute the PowerShell script manually (in a PowerShell console and type c:\temp>.\sqltotext.ps1
), it works as expected (the execution policy in all scopes were changed). Why this happens?
I did some investigation though. EXEC xp_cmdshell 'whoami.exe'
reports I'm running with nt service\mssqlserver
. I also manually added nt service\mssqlserver
into the administrators
local user group. By using process explorer, I can confirm the PowerShell session indeed was started and all related processes have admin permission (Integrity = High).




xp_cmdshell
, it doesn't change anything.
BTW, I know there are multiple ways to write the command. I'm just talking about the technical skills here, so please don't suggest me changing command syntax etc.
Asked by Just a learner
(2082 rep)
Apr 12, 2021, 01:54 PM
Last activity: Apr 14, 2021, 08:40 AM
Last activity: Apr 14, 2021, 08:40 AM