microsoft / microsoft/DacFx

SqlPackage doesn't honor impersonation from PowerShell

Open
#297 3 comments 0 reactions 1 assignee Claimed by @dzsquared View on GitHub
bug documentation
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- SqlPackage or DacFx Version: SqlPackage 162.0.52
- .NET Framework (Windows-only) or .NET Core: Core 6
- Environment (local platform and source/target platforms): Local Windows Server 2016 to Remote Windows Server 2019 running SQL Server 2019, both domain-joined

I have a scenario where a CD pipeline needs to launch PowerShell as a service user, impersonate another service user which has privileges on a remote SQL Server, and use sqlpackage to publish a DACPAC to that server. sqlpackage doesn't appear to actually use the impersonated user when establishing the connection.

**Steps to Reproduce:**

1. Via PInvoke, use the `advapi32` functions `LogonUser` and `DuplicateToken` to create a login token with the Delegation impersonation level
2. Create a new `System.Security.Principal.WindowsIdentity` object with the impersonation token
3. Call Impersonate on the new identity
4. Observe that calling `System.Security.Principal.WindowsIdentity::GetCurrent()` from PowerShell shows the user being impersonated
5. Run **sqlpackage** and use Windows authentication by including `Integrated Security=true;TrustServerCertificate=true;Trusted_Connection=yes` in the connection string

Expected: sqlpackage connects using the impersonated user, because it uses the identity from `GetCurrent()`
Actual: sqlpackage connects as the original user running PowerShell:

```powershell
# $identityName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
# Write-Host "SqlPackage as: $identityName"
# dotnet tool run sqlpackage
SqlPackage as: DOMAIN\SQLUSER
# ...
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\PIPELINEUSER'.
```

**Did this occur in prior versions? If not - which version(s) did it work in?** I don't believe this is a regression

(SqlPackage)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.