How to add Managed Identity user to Azure SQL database with Powershell?

0
votes

I'm trying to create a deploy script in powershell to create and configure the environment for a web application. The web app uses user-assigned Managed Identity to reach the SQL server.

I have to run the following command in the SQL server:
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

But this command errors when I use sql adminstrator login: "Only connections established with Active Directory accounts can create other Active Directory users."

I add an AD administrator with Set-AzSqlServerActiveDirectoryAdministrator

But how can use this AD login from powershell to run the SQL command?

2
powershellazure-active-directoryazure-sql-databaseazure-powershellazure-managed-identity
Can you show the PowerShell script you are trying to use? - juunas
For the "simple" login I use this command. But it does not work with my AD credentials. Invoke-Sqlcmd -Database "$($sqlDBName)" -ServerInstance "$($sqlServerName).database.windows.net" -Username "$($dbuser)" -Password "$($dbpw)" -OutputSqlErrors $true -Query "CREATE USER [managed_id] FROM EXTERNAL PROVIDER;" - csadam

2 Answers

1
votes

It seems that Invoke-Sqlcmd (or at least the version of it that I have) doesn't support Azure AD authentication.

I've used a PowerShell script like this to create users (you may need to update sqlcmd):

$query = "CREATE USER [$identityName] FROM EXTERNAL PROVIDER;"
sqlcmd -S $serverHostName -d $databaseName -G -N -U $username -t 120 -b -Q $query

The -G flag there tells it to use Azure AD authentication, in this case it will use interactive authentication and require you to login. If I recall, you can use the -U and -P flags to define the username and password as well for the Azure AD user, but that'll only work if the user doesn't have MFA enabled and isn't a federated user.

1
votes

Apparently the Azure AD login only works with the -ConnectionString parameter. So the solution is:

# Create User Managed Identity
  $mi = New-AzUserAssignedIdentity -ResourceGroupName $resourceGroupName -Name $managedIdentityName
# Enable AD login for SQL server
  Set-AzSqlServerActiveDirectoryAdministrator -ServerName $sqlServerName -ResourceGroupName $resourceGroupName -DisplayName $AdAdminUser -ObjectId $AdAdminObjectId
# Add Managed identity login to SQL server
  $connectString = "Server=tcp:$sqlServerName.database.windows.net,1433;Initial Catalog=$sqlDBName;Authentication=Active Directory Password;user=$AdAdminUser;pwd=$AdAdminPw"
  $query = "CREATE USER [$managedIdentityName] FROM EXTERNAL PROVIDER;"
  Invoke-Sqlcmd -ConnectionString $connectString -Query $query -OutputSqlErrors $true