Sample Header Ad - 728x90

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 (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). enter image description here The cmd.exe command line looks like this: enter image description here 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). enter image description here The PowerShell process command line looks like this: enter image description here 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 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