Skip to content

Files

Latest commit

 

History

History
391 lines (296 loc) · 34.4 KB

active-directory-app-provisioning-sql.md

File metadata and controls

391 lines (296 loc) · 34.4 KB

This document describes the steps you need to perform to automatically provision and deprovision users from Azure Active Directory (Azure AD) into a SQL database.

For important details on what this service does, how it works, and frequently asked questions, see Automate user provisioning and deprovisioning to SaaS applications with Azure Active Directory and on-premises application provisioning architecture.

Important

The default verbosity of the logs is set to Verbose. If you are using the SQL connector without Windows Integrated Auth, please set the verbosity to Error as described here.

Prerequisites for provisioning to a SQL Database

On-premises prerequisites

  • The application relies upon a SQL database, in which records for users can be created, updated, and deleted.
  • A computer with at least 3 GB of RAM, to host a provisioning agent. The computer should have Windows Server 2016 or a later version of Windows Server, with connectivity to the target database system, and with outbound connectivity to login.microsoftonline.com, other Microsoft Online Services and Azure domains. An example is a Windows Server 2016 virtual machine hosted in Azure IaaS or behind a proxy.
  • The computer should have .NET Framework 4.7.2 and an ODBC driver for the SQL database.

Configuration of the connection to the application's database is done via a wizard. Depending on the options you select, some of the wizard screens might not be available and the information might be slightly different. Use the following information to guide you in your configuration.

Supported systems

  • Microsoft SQL Server and Azure SQL
  • IBM DB2 10.x
  • IBM DB2 9.x
  • Oracle 10 and 11g
  • Oracle 12c and 18c
  • MySQL 5.x

Note: The table-based method of the generic SQL connector requires that column names are case-insensitive. MySQL is case-sensitive on Linux and Postgres is case-sensitive across platforms. As a result, they are not currently supported with table-based method and configuring provisioning users into those databases is outside the scope of this article.

Cloud requirements

  • An Azure AD tenant with Azure AD Premium P1 or Premium P2 (or EMS E3 or E5).

    [!INCLUDE active-directory-p1-license.md]

  • The Hybrid Identity Administrator role for configuring the provisioning agent and the Application Administrator or Cloud Application Administrator roles for configuring provisioning in the Azure portal.

Prepare the sample database

In this article, you will configure the Azure AD SQL connector to interact with your application's relational database. Typically, applications manage access with a table in their SQL database, with one row in the table per user. If you already have an application with a database, then continue at the next section.

If you do not already have a database with a suitable table, then for demonstration purposes, you should create one which Azure AD can be permitted to use. If you're using SQL Server, then run the SQL script found in Appendix A. This script creates a sample database with the name CONTOSO, containing a single table Employees. This is the database table that you'll be provisioning users into.

Table Column Source
ContosoLogin Azure AD user principal name
FirstName Azure AD given name
LastName Azure AD surname
Email Exchange Online email address
InternalGUID Generated by the database itself
AzureID Azure AD object ID
textID Azure AD mail nickname

Determine how the Azure AD SQL Connector will interact with your database

You will need to have a user account in the SQL instance with the rights to make updates to data in the database's tables. If your SQL database is managed by someone else, contact them to obtain the account name and password for Azure AD to use to authenticate to the database. If the SQL instance is installed on a different computer, you will also need to ensure that the SQL database allows incoming connections from the ODBC driver on the agent computer.

If you have an already existing database for your application, then you will need to determine how Azure AD should interact with that database: direct interaction with tables and views, via stored procedures already present in the database, or via SQL statements you provide for query and updates. This is because a more complex application might have in its database additional auxiliary tables, require paging for tables with thousands of users, or could require Azure AD to call a stored procedure that performs additional data processing, such as encryption, hashing or validity checks.

When you create the configuration for the connector to interact with an application's database, you will configure first an approach for how the connector host reads the schema of your database, and then configure the approach the connector should use on an ongoing basis, via run profiles. Each run profile specifies how the connector should generate SQL statements. The choice of run profiles, and the method within a run profile, depends on what your database engine supports and the application requires.

  • After configuration, when the provisioning service starts, it will automatically perform the interactions configured in the Full Import run profile. In this run profile, the connector will read in all the records for users from the application's database, typically using a SELECT statement. This run profile is necessary so that later, if Azure AD needs to make a change for a user, Azure AD will know to update an existing record for that user in the database, rather than create a new record for that user.

  • Each time changes are made in Azure AD, such as to assign a new user to the application or update an existing user, the provisioning service will perform the SQL database interactions configured Export run profile. In the Export run profile, Azure AD will issue SQL statements to insert, update and delete records in the database, in order to bring the contents of the database in sync with Azure AD.

  • If your database supports it, you can also optionally configure a Delta Import run profile. In this run profile, Azure AD will read in changes that were made in the database, other than by Azure AD, since the last full or delta import. This run profile is optional since it requires the database to be structured to allow changes to be read.

In the configuration of each run profile of the connector, you will specify whether the Azure AD connector should generate its own SQL statements for a table or view, call your stored procedures, or use custom SQL queries you provide. Typically you will use the same method for all run profiles in a connector.

  • If you select the Table or View method for a run profile, then the Azure AD connector will generate the necessary SQL statements, SELECT, INSERT, UPDATE and DELETE, to interact with the table or view in the database. This is the simplest approach, if your database has a single table or an updatable view with few existing rows.
  • If you select the Stored Procedure method, then your database will need to have four stored procedures: read a page of users, add a user, update a user and delete a user, you will configure the Azure AD connector with the names and parameters of those stored procedures to call. This approach requires more configuration in your SQL database and would typically only be needed if your application requires additional processing for each change to a user, of for paging through large result sets.
  • If you select the SQL Query method, then you will type in the specific SQL statements you want the connector to issue during a run profile. You'll configure the connector with the parameters that the connector should populate in your SQL statements, such as to page through result sets during an import, or to set the attributes of a new user being created during an export.

This article illustrates how to use the table method to interact with the sample database table Employees, in the Export and Full Import run profiles. To learn more about configuring the Stored Procedure or SQL Query methods, see the Generic SQL configuration guide that provides more details and specific requirements.

Choose the unique identifiers in your application's database schema

Most applications will have a unique identifier for each user of the application. If you are provisioning into an existing database table, you should identify a column of that table which has a value for each user, where that value is unique and doesn't change. This will be the Anchor, which Azure AD uses to identify existing rows to be able to update or delete them. For more information on anchors, see About anchor attributes and distinguished names.

If your application's database already exists, has users in it, and you want to have Azure AD keep those users up to date, then you will need to have an identifier for each user that is the same between the application's database and the Azure AD schema. For example, if you assign a user to the application in Azure AD, and that user is already in that database, then changes to that user in Azure AD should update an existing row for that user, rather than add a new row. Since Azure AD likely does not store an application's internal identifier for that user, you will want to select another column for querying the database. The value of this column could be a user principal name, or an email address, employee ID, or other identifier that is present in Azure AD on each user that is in scope of the application. If the user identifier that the application uses is not an attribute stored in the Azure AD representation of the user, then you do not need to extend the Azure AD user schema with an extension attribute, and populate that attribute from your database. You can extend the Azure AD schema and set extension values using PowerShell.

Map attributes in Azure AD to the database schema

When Azure AD has established a link between a user in Azure AD and a record in the database, either for a user already in the database or a new user, then Azure AD can provision attribute changes from the Azure AD user into the database. In addition to the unique identifiers, inspect your database to identify if there are any other required properties. If there are, then ensure that the users who will be provisioned into the database have attributes that can be mapped onto the required properties.

You can also configure deprovisioning behavior. If a user that is assigned to the application is deleted in Azure AD, then Azure AD will send a delete operation to the database. You may also wish to have Azure AD update the database when a user goes out of scope of being able to use the application. If a user is unassigned from an app, soft-deleted in Azure AD, or blocked from sign-in, then you can configure Azure AD to send an attribute change. If you are provisioning into an existing database table, then you'll want to have a column of that table to map to isSoftDeleted. When the user goes out of scope, Azure AD will set the value for that user to True.

Install the ODBC driver

The Windows Server where you'll be installing the provisioning agent requires an ODBC driver for your target database. If you're planning to connect to SQL Server or Azure SQL database, then you should download the ODBC driver for SQL Server (x64) and install that on the Windows Server.

Create the DSN connection file

The generic SQL connector requires a Data Source Name (DSN) file to connect to the SQL endpoint. First, you need to create a file with the ODBC connection information.

  1. Start the ODBC management utility on your server. Use the 64-bit version. Screenshot that shows ODBC management.
  2. Select the File DSN tab, and select Add. Screenshot that shows the File DSN tab.
  3. If you're using SQL Server or Azure SQL, select SQL Server Native Client 11.0 and select Next. If you're using another database, select its ODBC driver. Screenshot that shows choosing a native client.
  4. Give the file a name, such as GenericSQL, and select Next. Screenshot that shows naming the connector.
  5. Select Finish. Screenshot that shows Finish.
  6. Now configure the connection. If the SQL Server is located on a different server computer, then enter the name of the server. Then, select Next. Note that the following steps will differ depending upon which ODBC driver you're using. These assume you're using the driver to connect to SQL Server. Screenshot that shows entering a server name.
  7. If the user you are running this step as has permissions to connect to the database, then keep Windows authentication selected. If the SQL Server administrator requires a SQL local account, then provide those credentials instead. Then select Next. Screenshot that shows Windows authentication.
  8. Enter the name of the database, which in this sample is CONTOSO. Screenshot that shows entering a database name.
  9. Keep everything default on this screen, and select Finish. Screenshot that shows selecting Finish.
  10. To check everything is working as expected, select Test Data Source. Screenshot that shows Test Data Source.
  11. Make sure the test is successful. Screenshot that shows success.
  12. Select OK twice. Close the ODBC Data Source Administrator.

Download, install and configure the Azure AD Connect Provisioning Agent Package

  1. Download the provisioning agent and copy it onto the Windows Server that has connectivity to your SQL server.

    [!NOTE] Please use different provisioning agents for on-premises application provisioning and Azure AD Connect Cloud Sync / HR-driven provisioning. All three scenarios should not be managed on the same agent.

  2. Open the provisioning agent installer, agree to the terms of service, and select next.
  3. Open the provisioning agent wizard, and select On-premises provisioning when prompted for the extension you want to enable.
  4. Provide credentials for an Azure AD administrator when you're prompted to authorize. The Hybrid Identity Administrator or Global Administrator role is required.
  5. Select Confirm to confirm the installation was successful.
  6. Sign in to the Azure portal.
  7. Go to Enterprise applications > Add a new application.
  8. Search for the On-premises ECMA app application, and add it to your tenant.
  9. Navigate to the provisioning page of your application.
  10. Select Get started.
  11. On the Provisioning page, change the mode to Automatic. Screenshot that shows changing the mode to Automatic.
  12. On the On-Premises Connectivity section, select the agent that you just deployed and select Assign Agent(s).
  13. Keep this browser window open, as you complete the next step of configuration using the configuration wizard.

Configure the Azure AD ECMA Connector Host certificate

  1. On the Windows Server where the provisioning agent is installed, launch the Microsoft ECMA2Host Configuration Wizard from the start menu.
  2. After the ECMA Connector Host Configuration starts, if this is the first time you have run the wizard, it will ask you to create a certificate. Leave the default port 8585 and select Generate to generate a certificate. The autogenerated certificate will be self-signed as part of the trusted root. The SAN matches the host name. Screenshot that shows configuring your settings.
  3. Select Save.

Create a generic SQL connector

In this section, you'll create the connector configuration for your database.

  1. If you have not already done so, launch the Microsoft ECMA2Host Configuration Wizard from the start menu.

  2. Select New Connector. Screenshot that shows choosing New Connector.

  3. On the Properties page, fill in the boxes with the values specified in the table that follows the image and select Next. Screenshot that shows entering properties.

    Property Value
    Name SQL
    Autosync timer (minutes) 120
    Secret Token Enter your own key here. It should be 12 characters minimum.
    Extension DLL For the generic SQL connector, select Microsoft.IAM.Connector.GenericSql.dll.

  4. On the Connectivity page, fill in the boxes with the values specified in the table that follows the image and select Next. Screenshot that shows the Connectivity page.

    Property Description
    DSN File The Data Source Name file you created in the previous step, which is used to connect to the SQL instance.
    User Name The username of an account with rights to make updates to the table in the SQL instance. If the target database is SQL Server, the user name must be in the form of hostname\sqladminaccount for standalone servers or domain\sqladminaccount for domain member servers.
    Password The password of the username just provided.
    DN is Anchor Unless your environment is known to require these settings, don't select the DN is Anchor and Export Type:Object Replace checkboxes.

Retrieve the schema from the database

After having provided credentials, the ECMA Connector Host will be ready to retrieve the schema of your database.

  1. On the Schema 1 page, you'll specify the list of object types. In this sample, there is a single object type, User. Fill in the boxes with the values specified in the table that follows the image and select Next. Screenshot that shows the Schema 1 page.

    Property Value
    Object type detection method Fixed Value
    Fixed value list/Table/View/SP User

  2. Once you clicked Next, an additional page will automatically appear, for the configuration of the User object type. On the Schema 2 page, you'll indicate how users are represented in your database. In this sample, it's a single SQL table, named Employees. Fill in the boxes with the values specified in the table that follows the image and select Next. Screenshot that shows the Schema 2 page.

    Property Value
    User:Attribute Detection Table
    User:Table/View/SP Employees

  3. Once you clicked Next, an additional page will automatically appear, for you to select the columns of the Employees table that are to be used as the Anchor and DN of users. On the Schema 3 page, fill in the boxes with the values specified in the table that follows the image and select Next. Screenshot that shows the Schema 3 page.

    Property Description
    Select Anchor for :User User:ContosoLogin
    Select DN attribute for User AzureID

  4. Once you clicked Next, an additional page will automatically appear, for you to confirm the data type of each of the columns of the Employee table, and whether the connector should import or export them. On the Schema 4 page, leave the defaults and select Next. Screenshot that shows the Schema 4 page.

  5. On the Global page, fill in the boxes and select Next. Use the table that follows the image for guidance on the individual boxes. Screenshot that shows the Global page.

    Property Description
    Data Source Date Time Format yyyy-MM-dd HH:mm:ss

  6. On the Partitions page, select Next. Screenshot that shows the Partitions page.

Configure the run profiles

Next, you'll configure the Export and Full import run profiles. The Export run profile will be used when the ECMA Connector host needs to send changes from Azure AD to the database, to insert, update and delete records. The Full Import run profile will be used when the ECMA Connector host service starts, to read in the current content of the database. In this sample, you'll use the Table method in both run profiles, so that the ECMA Connector Host will generate the necessary SQL statements.

  1. On the Run Profiles page, keep the Export checkbox selected. Select the Full import checkbox and select Next. Screenshot that shows the Run Profiles page.

    Property Description
    Export Run profile that will export data to SQL. This run profile is required.
    Full import Run profile that will import all data from SQL sources specified earlier.
    Delta import Run profile that will import only changes from SQL since the last full or delta import.

  2. Once you clicked Next, an additional page will automatically appear, for you to configure the method for the Export run profile. On the Export page, fill in the boxes and select Next. Use the table that follows the image for guidance on the individual boxes. Screenshot that shows the Export page.

    Property Description
    Operation Method Table
    Table/View/SP Employees

  3. On the Full Import page, fill in the boxes and select Next. Use the table that follows the image for guidance on the individual boxes. Screenshot that shows the Full Import page.

    Property Description
    Operation Method Table
    Table/View/SP Employees

Configure how attributes are surfaced in Azure AD

  1. On the Object Types page, fill in the boxes and select Next. Use the table that follows the image for guidance on the individual boxes.

    • Anchor: This values of this attribute should be unique for each object in the target database. The Azure AD provisioning service will query the ECMA connector host by using this attribute after the initial cycle. This anchor value should be the same as the anchor value you configured earlier on the Schema 3 page.
    • Query Attribute: This attribute should be the same as the Anchor.
    • DN: The Autogenerated option should be selected in most cases. If it isn't selected, ensure that the DN attribute is mapped to an attribute in Azure AD that stores the DN in this format: CN = anchorValue, Object = objectType. For more information on anchors and the DN, see About anchor attributes and distinguished names.
    Property Description
    Target object User
    Anchor ContosoLogin
    Query Attribute ContosoLogin
    DN ContosoLogin
    Autogenerated Checked

  2. The ECMA connector host discovers the attributes supported by the target database. You can choose which of those attributes you want to expose to Azure AD. These attributes can then be configured in the Azure portal for provisioning. On the Select Attributes page, add all the attributes in the dropdown list one at a time. Screenshot that shows the Select Attributes page.
    The Attribute dropdown list shows any attribute that was discovered in the target database and wasn't chosen on the previous Select Attributes page. Once all the relevant attributes have been added, select Next.

  3. On the Deprovisioning page, under Disable flow, select Delete. Please note that attributes selected on the previous page won't be available to select on the Deprovisioning page. Select Finish. Screenshot that shows the Deprovisioning page.

Ensure the ECMA2Host service is running

  1. On the server the running the Azure AD ECMA Connector Host, select Start.
  2. Enter run and enter services.msc in the box.
  3. In the Services list, ensure that Microsoft ECMA2Host is present and running. If not, select Start. Screenshot that shows the service is running.

Configure the application connection in the Azure portal

  1. Return to the web browser window where you were configuring the application provisioning.

    [!NOTE] If the window had timed out, then you will need to re-select the agent.

    1. Sign in to the Azure portal.
    2. Go to Enterprise applications and the On-premises ECMA app application.
    3. Click on Provisioning.
    4. If Get started appears, then change the mode to Automatic, on the On-Premises Connectivity section, select the agent that you just deployed and select Assign Agent(s). Otherwise go to Edit Provisioning.

  2. Under the Admin credentials section, enter the following URL. Replace the {connectorName} portion with the name of the connector on the ECMA connector host, with the same case as was configured in the wizard. You can also replace localhost with the host name.

    Property Value
    Tenant URL https://localhost:8585/ecma2host_{connectorName}/scim

  3. Enter the Secret Token value that you defined when you created the connector.

    [!NOTE] If you just assigned the agent to the application, please wait 10 minutes for the registration to complete. The connectivity test won't work until the registration completes. Forcing the agent registration to complete by restarting the provisioning agent on your server can speed up the registration process. Go to your server, search for services in the Windows search bar, identify the Azure AD Connect Provisioning Agent Service, right-click the service, and restart.

  4. Select Test Connection, and wait one minute. Screenshot that shows assigning an agent.

  5. After the connection test is successful and indicates that the supplied credentials are authorized to enable provisioning, select Save.
    Screenshot that shows testing an agent.

Configure attribute mappings

Now you need to map attributes between the representation of the user in Azure AD and the representation of a user in the on-premises application's SQL database.

Configure attribute mapping

  1. In the Azure AD portal, under Enterprise applications, select the the On-premises ECMA app application, and then the Provisioning page.

  2. Select Edit provisioning, and wait 10 seconds.

  3. Expand Mappings and select Provision Azure Active Directory Users. Screenshot that shows provisioning a user.

  4. Select Add New Mapping. Screenshot that shows Add New Mapping.

  5. Specify the source and target attributes, and add all the mappings in the following table. Screenshot that shows saving the mapping.

    Mapping type Source attribute Target attribute
    Direct userPrincipalName urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:ContosoLogin
    Direct objectID urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:AzureID
    Direct mail urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:Email
    Direct givenName urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:FirstName
    Direct surName urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:LastName
    Direct mailNickname urn:ietf:params:scim:schemas:extension:ECMA2Host:2.0:User:textID

  6. Once all of the mappings have been added, select Save.

Assign users to an application

Now that you have the Azure AD ECMA Connector Host talking with Azure AD, and the attribute mapping configured, you can move on to configuring who's in scope for provisioning.

Important

If you were signed in using a Hybrid Identity Administrator role, you need to sign-out and sign-in with an account that has the Application Administrator, Cloud Application Administrator or Global Administrator role, for this section. The Hybrid Identity Administrator role does not have permissions to assign users to applications.

  1. In the Azure portal, select Enterprise applications.
  2. Select the On-premises ECMA app application.
  3. On the left, under Manage, select Users and groups.
  4. Select Add user/group. Screenshot that shows adding a user.
  5. Under Users, select None Selected. Screenshot that shows None Selected.
  6. Select users from the right and select the Select button.
    Screenshot that shows Select users.
  7. Now select Assign. Screenshot that shows Assign users.

Test provisioning

Now that your attributes are mapped and users are assigned, you can test on-demand provisioning with one of your users.

  1. In the Azure portal, select Enterprise applications.
  2. Select the On-premises ECMA app application.
  3. On the left, select Provisioning.
  4. Select Provision on demand.
  5. Search for one of your test users, and select Provision. Screenshot that shows testing provisioning.
  6. After several seconds, then the message Successfully created user in target system will appear, with a list of the user attributes.

Start provisioning users

  1. After on-demand provisioning is successful, change back to the provisioning configuration page. Ensure that the scope is set to only assigned users and groups, turn provisioning On, and select Save.

    Screenshot that shows Start provisioning.

  2. Wait several minutes for provisioning to start. It might take up to 40 minutes. After the provisioning job has been completed, as described in the next section, if you are done testing, you can change the provisioning status to Off, and select Save. This action stops the provisioning service from running in the future.

Troubleshooting provisioning errors

If an error is shown, then select View provisioning logs. Look in the log for a row in which the Status is Failure, and click on that row.

If the error message is Failed to create User, then check the attributes that are shown against the requirements of the database schema.

For more information, change to the Troubleshooting & Recommendations tab. If the ODBC driver returned a message, it could be displayed here. For example, the message ERROR [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'FirstName', table 'CONTOSO.dbo.Employees'; column does not allow nulls. is an error from the ODBC driver. In this case, the column does not allow nulls might indicate that the FirstName column in the database is mandatory but the user being provisioned did not have a givenName attribute, so the user could not be provisioned.

Check that users were successfully provisioned

After waiting, check the SQL database to ensure users are being provisioned.

Screenshot checking that users are provisioned.

Appendix A

If you're using SQL Server, you can use the following SQL script to create the sample database.

---Creating the Database---------
Create Database CONTOSO
Go
-------Using the Database-----------
Use [CONTOSO]
Go
-------------------------------------

/****** Object:  Table [dbo].[Employees]    Script Date: 1/6/2020 7:18:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employees](
	[ContosoLogin] [nvarchar](128) NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Email] [nvarchar](128) NULL,
	[InternalGUID] [uniqueidentifier] NULL,
	[AzureID] [uniqueidentifier] NULL,
	[textID] [nvarchar](128) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [DF_Employees_InternalGUID]  DEFAULT (newid()) FOR [InternalGUID]
GO