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-
