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

Question

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?

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
– csadam, Commented Jun 16, 2020 at 12:30

juunas · Accepted Answer · 2020-06-16 12:40:57Z

1

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.

answered Jun 16, 2020 at 12:40

juunas

59.6k13 gold badges130 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

csadam Over a year ago

Thanks, this is a good workaround. Meanwhile I managed to find out how to use AD login, it only works with Connectionstring parameter

csadam · Accepted Answer · 2020-06-16 13:47:19Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related