Azure Devops SqlDacpacDeploymentOnMachineGroup@0 User Context

Question

We have a .dacpac deployment to a SQL Server that uses a connection string with key/value pair: Integrated Security=True;

It works fine to push to our database, but I am wondering what user it would use to do its updates. We use a publish profile that lists no users and the connection information there is the same as the devops version. There is a deployment group set up for it to use, but again there is no user specified that I can see. I am obviously missing something.

My research on the web brings up this from MS:

When this property value is true or yes, the DRDA Service will connect to SQL Server using Windows Authentication.

• Connection credentials are derived from the running MsDrdaService.exe (service account or logged in user when running from command line).
• Connection credentials are derived from Enterprise Single Sign-On service, when the separate Affiliate Application argument is specified with a value. See topic on Affiliate Application for more information.

So with it set to true then what service account would be tied to the deployment? All of this was set up prior to my taking over any of this and I am pretty new to Azure Devops, so any help would be great.

I know that I can supply the userid and password in the connection string, so I am not really looking for that as a solution. I just need to know under what user context the database would be updated since I cannot point to an actual account.

NOTE: I am not a DBA so I do not have any log access or tracing capabilities on the database.

TIA

Answer 1

You haven't shared your pipeline definition but based on your description and assuming you use the Authentication Type of Active Directory - Integrated,

According to the reference documents of SQL,

Connect to an existing database in SSDT - SQL Server Data Tools (SSDT) | Microsoft Learn

Active Directory Integrated Authentication: This authentication method uses Kerberos to authenticate users to SQL Server. Kerberos is a more secure authentication protocol than Active Directory Password Authentication, but it requires that both the client and the server are joined to an Active Directory domain.

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Server | Microsoft Learn

To use Active Directory Integrated authentication mode, you must have an on-premises Active Directory instance that is joined to Microsoft Entra ID in the cloud. You can federate by using Active Directory Federation Services (AD FS), for example. When you're signed in to a domain-joined machine, you can access Azure SQL data sources without being prompted for credentials with this mode. You can't specify username and password in the connection string for .NET Framework applications. Username is optional in the connection string for .NET Core and .NET Standard applications. You can't set the Credential property of SqlConnection in this mode.