I have an application that has a complex MSBuild system that executes .SQL scripts against SQL databases stored in a SQL Managed Instance (SMI) in Azure Gov. This is process is being executed from a GitLab CI/CD pipeline. I can execute it without issue using SQL Auth, but security rules from the client forbid using SQL Auth. The SMI has a Managed Identity, located under Enterprise Applications in AAD, that has all the roles needed to execute the scripts, but I get errors when I use ActiveDirectoryInteractive authentication to connect.
I've tried a few ways to make this work. I'm open to any of the following working. Any ideas? #1 is the preferred option if I'm wrong about it being a limitation of SQLCMD. #3 would the next preferred.
- SQLCMD doesn't support ActiveDirectoryInteractive auth without a password.
- A PowerShell script that uses .Net objects. This failed with the following error, sample connection string, sample call, and PS function:
Error:
Exception calling "ExecuteNonQuery" with "1" argument(s): "Failed to connect to server tcp:tcp:myservername.database.usgovcloudapi.net,1433." At C:\MyCodePath\Build\MsBuild\Core\Execute-MsBuildSqlCommand.ps1:48 char:5 $server.ConnectionContext.ExecuteNonQuery($script)
**Sample Connection String**:
Server=tcp:myservername.database.usgovcloudapi.net,1433;UID=My-SMI-Managed-Identity-Name;Authentication=Active Directory Interactive;
**Sample call**
powershell.exe -command "& { C:\MyCodePath\Build\MsBuild\Core\Execute-MsBuildSqlCommand.ps1 -DataBaseServerConnectionString 'Server=tcp:tcp:myservername.database.usgovcloudapi.net,1433;UID=;Authentication=Active Directory Interactive;' -ScriptFilePath 'C:\MyCodePath\Database\Common\DbSql\Drop\DROP_CHECK_CONSTRAINTS.sql' -LibFolderPath 'C:\MyCodePath\Build\Lib'}"
**PowerShell Function**
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string] $DataBaseServerConnectionString,
[Parameter(Mandatory=$true)]
[string] $LibFolderPath,
[Parameter(Mandatory=$true)]
[string] $ScriptFilePath
)
#Write-Output "Execute PS script with Conn String var: "
#Write-Output $DataBaseServerConnectionString
Write-Output $DataBaseServerConnectionString
Write-Output $LibFolderPath
Write-Output $ScriptFilePath
<# These were added to the Build/Lib folder from these packages:
https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects
https://www.nuget.org/packages/Microsoft.Data.SqlClient/
https://www.nuget.org/packages/Microsoft.Azure.Services.AppAuthentication/1.2.0-preview2
#>
Get-ChildItem -Path "$LibFolderPath\PowerShellDBDeploy" | Foreach-Object {
[System.Reflection.Assembly]::LoadFrom($_.FullName)
}
$scriptBaseName = (Get-ChildItem -Path $ScriptFilePath).BaseName
$script = Get-Content $ScriptFilePath
Test-Path "$LibFolderPath\PowerShellDBDeploy"
$connection = New-Object Microsoft.Data.SqlClient.SQLConnection($DataBaseServerConnectionString)
Write-Output "Connection"
$connection
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($connection)
Write-Output "Server Connection"
$serverConnection
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)
Write-Output "Server"
$server
$server.ConnectionContext.ExecuteNonQuery($script)
<# $sqlQueries = $script.Split
$connection.Open();
$transaction = $connection.BeginTransaction($scriptBaseName);
$cmd = new SqlCommand("query", $connection);
$cmd.Transaction = $transaction;
try
{
foreach (var query in sqlqueries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
}
finally
{
$connection.Close();
} #>
- Created a .Net 4.7.2 custom MSBuild Task that's similar to the PowerShell script. The connection string is the same as above. Here's the rest of the information:
Error
error MSB4018: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server tcp:myservername.database.usgovcloudapi.net,1433. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.InvalidOperationException: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.
**Usage**
<ExecuteAzureSqlQuery ServicePrincipalName="My-SMI-Managed-Identity-Name" ServerName="$(ManagedInstanceFQDN)" ScriptFilePath="$(DatabaseFolder)\Common\DbSql\Drop\DROP_CHECK_CONSTRAINTS.sql" />
**Custom Task code** (tried with and without the AccessToken)
public class ExecuteAzureSqlQuery : Microsoft.Build.Utilities.Task
{
#region Properties
/// <summary>
/// Gets or sets the files to update.
/// </summary>
/// <value>The files.</value>
public ITaskItem ServicePrincipalName { get; set; }
public ITaskItem ServerName { get; set; }
/// <summary>
/// Gets or sets the location to save the updated files to
/// <remarks>If this is empty, then the files are save to the same directory as they originated from</remarks>
/// </summary>
/// <value>The output folder to place updated files into.</value>
public ITaskItem ScriptFilePath { get; set; }
#endregion
public override bool Execute()
{
Log.LogMessage(MessageImportance.Normal, $"Server: {ServerName.ItemSpec}, UID: {ServicePrincipalName.ItemSpec}");
string connectionString = $"Server=tcp:{ServerName.ItemSpec},1433;UID={ServicePrincipalName.ItemSpec};Authentication=Active Directory Interactive;";
var connection = new SqlConnection(connectionString);
//connection.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.usgovcloudapi.net").Result;
var server = new Server(new ServerConnection(connection));
FileInfo fileInfo = new FileInfo(ScriptFilePath.ItemSpec);
string script = fileInfo.OpenText().ReadToEnd();
server.ConnectionContext.ExecuteNonQuery(script);
return true;
}
}
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 21%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
