Skip to content

Integrating SailPoint with Microsoft Azure SQL Database

This document is designed to give specific information about the requirements and field definitions needed to get a working instance of an Azure SQL Database connector.

Part of the Azure SQL family, Azure SQL Database is an always-up-to-date, fully managed relational database service built for the cloud. Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.

Note

To view the latest features, enhancements, and fixes for all SaaS connectors, refer to the SaaS Release Notes page.

Supported Features

The Azure SQL Database connector supports the following features:

Note

The Azure SQL Database will only connect with an SQL connection.

Account Management

  • Manage Microsoft Azure SQL Database Logins as Accounts
  • Manage Microsoft Azure SQL Database Users Associated to Login Accounts as Attributes of Server Login Account
  • Aggregate and Refresh Accounts
  • Create, Update, Delete Accounts
  • Enable, Disable, and Change Password
  • Add and Remove Entitlements

Account - Group Management

  • Aggregate Microsoft Azure SQL Database Roles and Fixed Server Roles as Account-Groups.
  • Aggregate and Refresh Groups

Permissions Management

  • Permissions directly assigned to accounts and groups as direct permissions during account and group aggregations respectfully.
  • Revocation of aggregated permissions for accounts through certification.
  • For more information on direct permissions and how to enable them, refer to Direct Permission.

Password Management

If Password Management is enabled for your organization, you can change the password for a user of the source. After enabling password management on the source, be aware of the following:

  • The password policy defined in SailPoint applies to accounts on this source.
  • If you change the password for an application connected to this source, the change will automatically be made to the source itself.

For more information on configuring password management, refer to Password Management Overview.

Prerequisites

Supported Managed Systems

The Microsoft Azure SQL Database connector supports the following version and modules:

  • Microsoft SQL Azure (RTM) - 12.0.2000.8 onwards

Administrator Permissions

Login using administrator credentials and create a new user on managed system using the following command:

CREATE LOGIN <USER> WITH PASSWORD = '<PASSWORD>'

The following minimum permissions are required for Microsoft Azure SQL Database service accounts (user) based on the operations:

Test Connection

Create respective database user in master database for service login account using the following command:

CREATE USER <USERNAME> FOR LOGIN <LOGIN_NAME>

Aggregation

Note

In order to access databases other than master, the service account must have database user mapping on the databases with public role defined.

Connect to master database and run the following commands:

ALTER ROLE loginmanager ADD MEMBER <USERNAME>
ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER <LOGIN_NAME>

Provisioning

The following permissions are required for adding and removing entitlements:

  • To add or remove fixed server roles to or from login accounts, connect to the master database and run the following command:

    ALTER SERVER ROLE <SERVER_ROLE_NAME> ADD MEMBER <LOGIN>

    Note

    Each member of a server role can add or remove other logins to that same role. Only the server admin account or the Azure Active Directory admin account (which can be an Azure Active Directory Group) can add or remove other Logins to or from any of the server roles.

    The user must have the proper server role assignments to assign the same role to another user. For example, if an administrator has granted ROLE1, ROLE2, and ROLE3 roles to user A, then user A can only grant ROLE1, ROLE2, and ROLE3 to other users. User A cannot assign other roles than those assigned to them.

  • To add or remove database roles other than fixed database roles to or from user on to the master database, connect to the master database and run the following command:

    ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER <LOGIN_NAME>

    Note

    Adding or removing to or from fixed database roles form master is not applicable in accordance with Microsoft Azure SQL Database.

  • To add or remove custom roles to or from user onto a database other than master, connect to the respective database and run the following command:

    GRANT ALTER ANY ROLE TO <USERNAME>

  • To add or remove fixed database roles to or from user onto a database other than master, connect to the respective database and run the following command:

    ALTER ROLE db_owner ADD MEMBER <USERNAME>

Use the following commands to manage other permissions:

Create, Enable, Disable, Change Password, or Delete Login

Connect to the master database and run the following command:

ALTER ROLE loginmanager ADD MEMBER <USERNAME>

Revoke Permissions on Databases Other Than Master

Connect to the respective database and run the following command:

GRANT CONTROL TO <USERNAME>

Note

Granting or revoking permissions to or from accounts and groups is not applicable on the master database in accordance with Microsoft Azure SQL Database.

Direct Permission

Every Microsoft Azure SQL Database securable has associated permissions that can be granted to a principal. Permissions are managed at the server level assigned to logins and server roles, and at the database level assigned to database users and database roles. The model for Azure SQL Database has the database permissions, but the server level permissions aren't available.

The Azure SQL Database connector supports the database level permissions assigned to database users that are mapped to a server login or to the database roles as direct permissions.

The connector supports direct permissions assigned onto following targets (securables):

  • DATABASE
  • DATABASE_PRINCIPAL (for example, Database User or Database Role)
  • SCHEMA
  • ASSEMBLY
  • ASYMMETRIC_KEY
  • SYMMETRIC_KEYS
  • OBJECT_OR_COLUMN (for example, Stored procedure, Table, or Table column)

For example:

  • GRANT CONNECT on 'databaseuser@databasename:DATABASE'
  • GRANT EXECUTE on 'databaseuser@databasename:[dbo].[getCustomer](SQL_STORED_PROCEDURE)'
  • GRANT_WITH_GRANT_OPTION CREATE VIEW on 'databaseuser@databasename:DATABASE'

Note

In accordance with the behavior of Microsoft Azure SQL Database, revocation of permissions on special database roles like loginmanager, dbmanager, and fixed database roles is not applicable.

Enable Direct Permissions for Aggregation

To enable direct permissions and their aggregation for the account and group schema, complete the following:

  1. Use the listSources API to get the source ID.
  2. Use the listSchemas API to get the schema ID for the account or group schema for which you want to enable direct permissions.

  3. Use the updateSchema API and enter the following content in body, with the sourceID and the schemaID in the header:

    [    {"op": "add", "path": "/includePermissions", "value": true}]

Documentation Feedback

Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.