Impersonation of user during the runtime

[srpower]

Not able to perform the DB Deployment using Domain User and impersonating Azure Agent user from azure DevOps pipeline

What we want to achieve: • The Goal is to deploy the Dacpac file on the SQL Server, by overriding/impersonating the self-hosted agent user during run time with environment-specific domain user using sqlpackage.exe and Windows authentication method.

Different methods we tried:

Please find the different approaches Tried in PowerShell to enable the above requirement.

1. Start-Job: We try to start a background Job using a command prompt in the background where we invoke the sqlpackaage.exe using a different user and credentials and receive the output of the job to print them.

The Start Job approach failed with the following error :

Error : [localhost] An error occurred while starting the background process. Error reported: The directory name is invalid. At E:\workspace\Source\PowerShell\Deployment\cmpy_DatabaseDeployer.ps1:86 char:5

Receive-Job -Job $job -AutoRemoveJob -wait #-ArgumentList ($SQLPa … CategoryInfo : OpenError: (localhost:String) , PSRemotingTransportException FullyQualifiedErrorId : -2147467259,PSSessionStateBroken The error denotes that the provided paths in the sqlpackage.exe is invalid but all the directory name and Paths are provided in Absolute format and the L-key we are using during the runtime has sufficient access to the folders and files. but still the error occurs from Azure DevOps Pipeline. The deployment from the local seems to be working but from Azure DevOps pipeline we are getting the above error.

2. Invoke-Command: With this approach, we tried to create a remote session with the localhost or another Build server and do the deployment. The error we get while trying this method is :

Publishing to database ‘BEABCD501’ on server ‘MSSQL-xxxx.cmpygroupit.com’. Initializing deployment (Start) Initializing deployment (Failed) Time elapsed 0:00:00.90 ##[debug]STDERR: *** Could not deploy package. ##[debug]STDERR: At E:\workspace\Source\PowerShell\Deployment\cmpy_DatabaseDeployer.ps1:102 char:9 ##[debug]STDERR: + Invoke-Command -Session $session -ScriptBlock { ##[debug]STDERR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##[debug]STDERR: + CategoryInfo : NotSpecified: (*** Could not deploy package.:String) , RemoteException ##[debug]STDERR: + FullyQualifiedErrorId : NativeCommandError ##[debug]STDERR: ##[debug]Exit code: 1

3. Simple get child item is also failing

cd “C:\Program Files\Microsoft SQL Server\160\DAC\bin” get-childitem

$username = ‘cmpygroupit\S001986’ $password = ‘[Redacted]’ #$username, $password = Get-CredentialsFromVault -ApplicationName ‘BuildFarm’ -Environment $Environment -secretName ‘Remoting’ $securePassword = ConvertTo-SecureString $password -AsPlainText -Force $credential = New-Object System.Management.Automation.PSCredential ($username, $securePassword)

$SQLPackageExeArgs=[String]::Format(‘’‘C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe’’ /action:publish /sourcefile:{0} /TargetConnectionString:‘‘TrustServerCertificate=True; Persist Security Info=True; Integrated Security=True; initial Catalog={1}; Data Source={2}’’ /p:BlockOnPossibleDataLoss=false /p:TreatVerificationErrorsAsWarnings=True /p:ScriptDatabaseOptions=False’,$DACFilePath,$DBName,$DBServer) #$SQLPackageExeArgs=[String]::Format(‘/action:publish /sourcefile:{0} /TargetConnectionString:’‘TrustServerCertificate=True; Persist Security Info=True; Integrated Security=SSPI; initial Catalog={1}; Data Source={2}’’ /p:BlockOnPossibleDataLoss=false /p:TreatVerificationErrorsAsWarnings=True /p:ScriptDatabaseOptions=False’,$DACFilePath,$DBName,$DBServer)

Start-Job ##### $job = Start-Job -ScriptBlock { #Set-Location ‘C:\Program Files\Microsoft SQL Server\160\DAC\bin\’ #sqlpackage.exe $using:SQLPackageExeArgs write-host ‘Hello’ } -Credential $credential | Wait-Job Receive-Job -Job $job -wait -ErrorAction Inquire image.png

Note:

• The user used for the deployment has admin rights on the self-hosted agent to perform the deployment and on the Database it has required access to deploy the dacpac. • The FW connection is also open from the Self-hosted agent to the target database DB and login to the DB using the environment-specific L-key is also checked.

References: • SqlPackage Publish - SQL Server | Microsoft Learn • Choose an authentication mode - SQL Server | Microsoft Learn • SQL Server authentication vs. Windows authentication (quest.com)

[neemobeer]

Hard to tell with what is provided, but are you trying to access something on a mapped drive in the context of the impersonated user? If the answer is yes, then that’s the reason for the invalid directory error. Mapped drives do not exist in impersonated sessions. If memory serves me you can work-around that by mapping the session from the impersonation session, but you may need to call everything in a script block instead.