Azure Data Factory running Powershell script as Admin

Question

I created a Powershell script that connects to Azure MSSQL MI and run 3 queries then export the results in an excel file with multiple spreadsheets. But in order to do so. I need to install the modules needed for the script to run. These installations need to have admin access.

I managed to do this by manually running the Powershell ISE as Admin then execute the ps1 file. Now I want to run this script using Azure Data Factory in which these are the steps I did.

1. Save the ps1 file in the Storage Account > Containers
2. Created a Batch Account and Pools
3. Created Data Factory Pipeline with Custom Activity that points to the Container

[

](https://i.stack.imgur.com/bMmzG.jpg)

Here are the contents of the Powershell script ( Note: In the first part before Install_modules occured that's what I've found to run this file as an ADMIN):

param([switch]$Elevated)  

function Test-Admin {      
	$currentUser = New-Object Security.Principal.WindowsPrincipal 		$([Security.Principal.WindowsIdentity]::GetCurrent())    

	$currentUser.IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)  

}  

if ((Test-Admin) -eq $false)  {      
	if ($elevated) {          
		# tried to elevate, did not work, aborting      
	} else {          
		Start-Process powershell.exe -Verb RunAs -ArgumentList ('-noprofile -file "{0}" -elevated' -f ($myinvocation.MyCommand.Definition))      
	}      
	exit  
}  
'running with full privileges'    

Install-Module sqlserver 
Install-Module -Name ImportExcel 
Install-Module SqlPs  

$server = 'someserver' 
$database = 'MyDB' 
$user = 'user' 
$pass = 'password'  
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$server,1433;Initial Catalog=$database;` Persist Security Info=False;Authentication=Active Directory Password;User ID=$user;Password=$pass;MultipleActiveResultSets=False;` Encrypt=False;TrustServerCertificate=True;Connection Timeout=10;")   

$SheetNames =  @("Front_Tab","Balance Trending","X01 YTD Data") 
$Queries =  @("SELECT * FROM sys.databases","SELECT * FROM sys.tables", "SELECT * FROM sys.objects")  $conn.Open()  

foreach ($sheet in $SheetNames) 
{     
	$cmd = New-Object System.Data.SqlClient.SqlCommand($Queries[$SheetNames.IndexOf($sheet)], $conn)    
	$dataset = New-Object System.Data.DataSet     
	$dataadapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$dataadapter.fill($dataset)      
	$dataset.Tables | Export-Excel \\somelocation\Queryresult.xlsx -WorksheetName $sheet  
}  

$conn.Close()  
Write-Output "Executed Successfully"

After running the ADF Pipeline the it generates some error text file in the Jobs and there's no file in the specified Export-Excel location

Start-Process : This command cannot be run due to the error: The specified service does not exist as an installed service. At D:\batch\tasks\workitems\adfv2-adfbatchpool\job-1\f6db97e3-f919-448e-a279-c159defb2e57\wd\ADFFile.ps1:12 char:9 +         Start-Process powershell.exe -Verb RunAs -ArgumentList ('-nop ... +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + CategoryInfo          : InvalidOperation: (:) [Start-Process], InvalidOperationException     + FullyQualifiedErrorId : InvalidOperationException,Microsoft.PowerShell.Commands.StartProcess     Command

I'm not sure how to approach this next since my goal about this is to run the ps1 file inside the batch account or batch pool as an Administrator.

Here are the links I've taken a look at:

Run Powershell Script as Admin

Elevated Privileges

Admin ON-PREM Agent

Run Script as Admin Automatically

Answer 1

Hello Colinn Calaguas

If you want to make sure your pool has appropriate rights, please check the below pool setting page (start task page) where you can make sure your pool acts as admin

Please revert, if you have further questions.

Thanks