Skip to content

passwordless-tutorial-snippet.md

Latest commit

 

History

History
304 lines (219 loc) · 10.8 KB

passwordless-tutorial-snippet.md

File metadata and controls

304 lines (219 loc) · 10.8 KB
author maud-lv
ms.author malev
ms.service service-connector
ms.topic include
ms.date 05/21/2023
ms.custom
devx-track-azurecli
sfi-ga-nochange

Install the Service Connector passwordless extension

[!INCLUDE CLI-samples-clean-up]

Create a passwordless connection

Next, we use Azure App Service as an example to create a connection using managed identity.

If you use:

Note

If you use the Azure portal, go to the Service Connector blade of Azure App Service, Azure Spring Apps, or Azure Container Apps, and select Create to create a connection. The Azure portal will automatically compose the command for you and trigger the command execution on Cloud Shell.

::: zone pivot="postgresql"

The following Azure CLI command uses a --client-type parameter, it can be java, dotnet, python, etc. Run the az webapp connection create postgres-flexible -h to get the supported client types, and choose the one that matches your application.

User-assigned managed identity

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX \
    --client-type $CLIENT_TYPE

System-assigned managed identity

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --system-identity \
    --client-type $CLIENT_TYPE

Service principal

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX\
    --client-type $CLIENT_TYPE

::: zone-end

::: zone pivot="mysql"

Azure Database for MySQL - Flexible Server requires a user-assigned managed identity to enable Microsoft Entra authentication. For more information, see Set up Microsoft Entra authentication for Azure Database for MySQL - Flexible Server. You can use the following command to create a user-assigned managed identity:

USER_IDENTITY_NAME=<YOUR_USER_ASSIGNED_MANAGED_IDENTITY_NAME>
IDENTITY_RESOURCE_ID=$(az identity create \
    --name $USER_IDENTITY_NAME \
    --resource-group $RESOURCE_GROUP \
    --query id \
    --output tsv)

Important

After creating the user-assigned managed identity, ask your Global Administrator or Privileged Role Administrator to grant the following permissions for this identity:

  • User.Read.All
  • GroupMember.Read.All
  • Application.Read.All

For more information, see the Permissions section of Active Directory authentication.

Then, connect your app to a MySQL database with a system-assigned managed identity using Service Connector.

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create mysql-flexible -h to get the supported client types, and choose the one that matches your application.

User-assigned managed identity

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

System-assigned managed identity

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --system-identity mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

Service principal

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

::: zone-end

::: zone pivot="sql"

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create sql -h to get the supported client types, and choose the one that matches your application.

User-assigned managed identity

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX \
    --client-type dotnet

System-assigned managed identity

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --system-identity \
    --client-type dotnet

Service principal

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX \
    --client-type dotnet

::: zone-end

::: zone pivot="fabricsql"

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create fabricsql -h to get the supported client types, and choose the one that matches your application.

Important

Manual access sharing is currently required for complete onboarding. See Share access to SQL database in Fabric.

User-assigned managed identity

az webapp connection create fabricsql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --fabric-workspace-uuid $FABRIC_WORKSPACE_UUID \
    --fabric-sql-db-uuid $FABRIC_SQL_DB_UUID \
    --user-identity client-id=XX subs-id=XX \
    --client-type dotnet

System-assigned managed identity

az webapp connection create fabricsql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --fabric-workspace-uuid $FABRIC_WORKSPACE_UUID \
    --fabric-sql-db-uuid $FABRIC_SQL_DB_UUID \
    --system-identity \
    --client-type dotnet

Service principal

Note

Service connections using service principals are not supported when targeting SQL database in Microsoft Fabric.

::: zone-end

This Service Connector command completes the following tasks in the background:

  • Enable system-assigned managed identity, or assign a user identity for the app $APPSERVICE_NAME hosted by Azure App Service/Azure Spring Apps/Azure Container Apps.
  • Enable Microsoft Entra Authentication for the database server if it's not enabled before.
  • Set the Microsoft Entra admin to the current signed-in user.
  • Add a database user for the system-assigned managed identity, user-assigned managed identity, or service principal. Grant all privileges of the database $DATABASE_NAME to this user. The username can be found in the connection string in preceding command output.
  • Set configurations named AZURE_MYSQL_CONNECTIONSTRING, AZURE_POSTGRESQL_CONNECTIONSTRING, AZURE_SQL_CONNECTIONSTRING, or FABRIC_SQL_CONNECTIONSTRING to the Azure resource based on the database type.
    • For App Service, the configurations are set in the App Settings blade.
    • For Spring Apps, the configurations are set when the application is launched.
    • For Container Apps, the configurations are set to the environment variables. You can get all configurations and their values in the Service Connector blade in the Azure portal.

Service Connector will assign the following privileges to the user, you can revoke them and adjust the privileges based on your requirements.

::: zone pivot="postgresql"

GRANT ALL PRIVILEGES ON DATABASE "$DATABASE_NAME" TO "username"; 

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "username"; 

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "username";

::: zone-end

::: zone pivot="mysql"


GRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO 'username'@'%';

::: zone-end

::: zone pivot="sql"

GRANT CONTROL ON DATABASE::"$DATABASE_NAME" TO "username";

::: zone-end

::: zone pivot="fabricsql"

ALTER ROLE db_datareader ADD MEMBER "username"
ALTER ROLE db_datawriter ADD MEMBER "username"
ALTER ROLE db_ddladmin ADD MEMBER "username"

::: zone-end

Connect to a database with Microsoft Entra authentication

After creating the connection, you can use the connection string in your application to connect to the database with Microsoft Entra authentication. For example, you can use the following solutions to connect to the database with Microsoft Entra authentication.

:::zone pivot="postgresql"

[!INCLUDE code sample for postgres Microsoft Entra authentication connection]

:::zone-end

:::zone pivot="mysql"

[!INCLUDE code sample for mysql Microsoft Entra authentication connection]

:::zone-end

:::zone pivot="sql"

[!INCLUDE code sample for sql Microsoft Entra authentication connection]

:::zone-end

:::zone pivot="fabricsql"

[!INCLUDE code sample for fabricsql Microsoft Entra authentication connection]

:::zone-end