1

I want to add an AD-Group to SQL Azure using Azure Devops. The AD group contains MSI of a webapp.

We've added a group to the AAD called DemoSqlAdmin, containing the SPN used in the Azure DevOps pipeline. This group has been set as the SQL Server - Active Directory admin.

When running the Azure Devops Task: Azure SQL Database Deployment with Authentication Type 'Active Directory - Integrated' we get the following error:

2019-05-08T17:44:56.3763560Z ##[debug]Run-InlineSql -authenticationType aadAuthenticationIntegrated -serverName demoserver.database.windows.net -databaseName demodatabase -sqlUserName  -sqlPassword  -sqlInline "select getdate()" -connectionString  -ErrorVariable errors -ConnectionTimeout 120 | Out-String
2019-05-08T17:44:56.3903602Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp6824.tmp
2019-05-08T17:44:56.4094244Z Invoke-Sqlcmd -connectionString "Data Source=demoserver.database.windows.net; Initial Catalog=demodatabase; Authentication=Active Directory Integrated;"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp6824.tmp" 
2019-05-08T17:45:01.3106772Z ##[debug]Removing File C:\Users\VssAdministrator\AppData\Local\Temp\tmp6824.tmp
2019-05-08T17:45:01.3177919Z ##[debug]Failed to reach SQL server demoserver.database.windows.net. One or more errors occurred.
2019-05-08T17:45:01.3256036Z ##[debug]Error Message : System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2019-05-08T17:45:01.3268924Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2019-05-08T17:45:01.3282131Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3294469Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3306333Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3323803Z ##[debug]Message To Parse: System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2019-05-08T17:45:01.3336914Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2019-05-08T17:45:01.3348860Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3360481Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3373282Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2019-05-08T17:45:01.3461566Z ##[debug]
2019-05-08T17:45:01.3658976Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp7B8E.tmp
2019-05-08T17:45:01.3678332Z Invoke-Sqlcmd -connectionString "Data Source=demoserver.database.windows.net; Initial Catalog=demodatabase; Authentication=Active Directory Integrated;"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp7B8E.tmp" 
2019-05-08T17:45:01.5110410Z ##[debug]Removing File C:\Users\VssAdministrator\AppData\Local\Temp\tmp7B8E.tmp
2019-05-08T17:45:01.5186309Z ##[debug]No Firewall Rule was added
2019-05-08T17:45:01.5410661Z ##[debug]Caught exception from task script.
2019-05-08T17:45:01.5440006Z ##[debug]Error record:
2019-05-08T17:45:01.6073859Z ##[debug]One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2019-05-08T17:45:01.6087196Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.3.16\DeploySqlAzure.ps1:202 char:5
2019-05-08T17:45:01.6100074Z ##[debug]+     throw $errorMessage
2019-05-08T17:45:01.6113872Z ##[debug]+     ~~~~~~~~~~~~~~~~~~~
2019-05-08T17:45:01.6126477Z ##[debug]    + CategoryInfo          : OperationStopped: (One or more err...roubleshooting-:String) [], RuntimeException
2019-05-08T17:45:01.6137940Z ##[debug]    + FullyQualifiedErrorId : One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sql    azuredeployreadme#troubleshooting-
2019-05-08T17:45:01.6149271Z ##[debug] 
2019-05-08T17:45:01.6166593Z ##[debug]Script stack trace:
2019-05-08T17:45:01.6202488Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.3.16\DeploySqlAzure.ps1: line 202
2019-05-08T17:45:01.6213523Z ##[debug]at <ScriptBlock>, <No file>: line 1
2019-05-08T17:45:01.6225421Z ##[debug]at <ScriptBlock>, <No file>: line 22
2019-05-08T17:45:01.6237258Z ##[debug]at <ScriptBlock>, <No file>: line 18
2019-05-08T17:45:01.6250995Z ##[debug]at <ScriptBlock>, <No file>: line 1
2019-05-08T17:45:01.6269245Z ##[debug]Exception:
2019-05-08T17:45:01.6310341Z ##[debug]System.Management.Automation.RuntimeException: One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2019-05-08T17:45:01.6521044Z ##[error]One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
Luuk
  • 1,959
  • 1
  • 21
  • 43

1 Answers1

0

Check this https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment?view=azure-devops.

Authentication Type (Required) Type of database authentication, can be SQL Server Authentication, Active Directory - Integrated, Active Directory - Password, or Connection String. Integrated authentication means that the agent will access the database using its current Active Directory account context.

MeToo
  • 11
  • I think you're right, I thought selecting the SPN was also selecting the Run AS account, but I guess we just connect with the accounts of the build agent. Let me further investigate. – Luuk May 29 '19 at 07:01
  • @luuk Did you ever figure out if that was the actual case? ie: Is it using the Build Agent account rather than the SPN of the Service connection? – CDerrig Jul 26 '19 at 12:01
  • @CDerrig another team in our company also tried to fix this, but they weren't able to fix it. Maybe because we use hosted agents from Azure DevOps. – Luuk Jul 30 '19 at 09:59
  • 2
    @Luuk Ah, thanks! I ended up getting around this by using an Azure CLI Task to generate an accessToken for the service principal associated with our Azure Service Connection, then passing that token to SqlPackage arguments (/at:($token)) with the "Connection String" authentication type (without specifying username/password or authentication=). Built off these: [MSI](https://blog.bredvid.no/handling-azure-managed-identity-access-to-azure-sql-in-an-azure-devops-pipeline-1e74e1beb10b) and [az cli windows](https://stackoverflow.com/questions/49282998/set-output-variable-in-azure-cli-task-on-vsts) – CDerrig Jul 31 '19 at 15:25
  • @Luuk if you could share more detail about your solution as an answer here that would be really useful. I'm tearing my hair out trying to do the same thing here. In the meantime I will try and copy what you've done. – Josh Gallagher Jun 19 '20 at 12:21
  • @JoshGallagher we didn't solve it yet. When creating new databases we setup the access by running a script manually. – Luuk Jun 23 '20 at 08:30
  • @CDerrig I should have addressed my point to you. I did try and follow the links, but rather than using SqlPackage I used the somre more PS script in the same Azure CLI Task to construct a `System.Data.SqlClient.SqlConnection` and use the `AccessToken` property with the token retrieved from the AZ CLI command. However, I got a failure to login with or similar. Did the token need any formatting? – Josh Gallagher Jun 24 '20 at 17:50
  • 1
    @JoshGallagher So sorry, I didn't see this until now (which is probably way too late), but if I recall correctly, the AzureCLI returned the token surrounded by double-quotes. I actually wrote up an article [on Medium](https://medium.com/microsoftazure/deploying-a-dacpac-to-azure-with-azure-pipelines-and-managed-identity-89703d405e00) describing the full process. – CDerrig Sep 10 '20 at 18:58
  • @CDerrig I discovered the double quotes eventually! Thanks. – Josh Gallagher Sep 10 '20 at 21:43