SQL Change Automation 4

Use the Azure DevOps Release extension with Windows Authentication credentials

Using the Azure DevOps Release extension, it is possible to use supplied Windows credentials to authenticate with a SQL Server when deploying. This uses PowerShell remoting to impersonate the user with the provided credentials. This can be used in the scenario where you are using a self-hosted agent, but the user that the agent service is running under does not have access to the database.

There are three requirements that must be satisfied to authenticate with a database in this way.

  1. PowerShell remoting must be enabled on the agent. This can be enabled by running the following command in an elevated PowerShell session: Enable-PSRemoting -Force
  2. The credentials provided must be those of a local administrator account on the agent machine. This is a Windows security requirement for PowerShell remoting.
  3. The machine on which agent is running must allow credentials delegation (both sending and receiving). See "Allow credentials delegation" section below for configuration.

For security reasons, these credentials should be stored in a SQL Change Automation target service connection 

Allow credentials delegation (CredSSP)

Both sending and receiving credentials must be enabled on the machine in order for remoting to work under different user credentials.

1. Enable delegating (sending) credentials

  • Open up Local Group Policy Editor (Win+R → gpedit.msc).
  • Go to Computer Configuration → Administrative Templates → System → Credentials Delegation section.
  • Enable both "Allow delegating fresh credentials" and "Allow delegating fresh credentials with NTLM-only server authentication".
  • Add "wsman/<your computer name>" to servers list for both settings.

2. Enable receiving credentials

  • Open up elevated PowerShell window.
  • Run "Enable-WSManCredSSP -Role Server".

Didn't find what you were looking for?