DEV Community

Cover image for Data Factory - Azure AD Authentication for SQL Database
swatiBabber
swatiBabber

Posted on • Updated on

Data Factory - Azure AD Authentication for SQL Database

To use Azure identity authentication, follow these steps:

  1. Provision an Azure Active Directory administrator for your
    server on the Azure portal
    , if not already done : This is an
    important step as for adding Azure data factory's managed
    identity as a user in SQL, an Azure AD account having at
    least "ALTER ANY USER"
    permission is required.

    If 2nd step is performed using a SQL Authenticated account,
    the SQL command will return error.

  2. Create contained database users for the Azure Data Factory
    managed identity
    .Connect to the database from or to which
    you want to copy data by using tools like SQL Server
    Management Studio, with an Azure AD identity that has at least
    ALTER ANY USER permission. Run the following T-SQL

    CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER

  3. Grant the Data Factory managed identity needed permissions as
    you normally do for SQL users and others. Run the following
    code
    ALTER ROLE [role name] ADD MEMBER [your Data Factory name]

Refer this link for demo.

Top comments (0)