# 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](<#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 - Configure at least one virtual appliance cluster and successfully test the connection. For more information, refer to the [Virtual Appliance Reference Guide](). - Identify or create a service account on the Microsoft SQL system. - Obtain the custom Microsoft SQL Server mssql-jdbc-8.4.1.jre8.jar file. For detailed instructions, refer to [How do I obtain the jar file needed for the Microsoft SQL Database direct connect source](). > **Note** > > The Microsoft Azure SQL Database connector shares the same JDBC driver jar that is required for Microsoft SQL Server. ## 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: ```sql CREATE LOGIN WITH 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: ```sql CREATE USER FOR LOGIN ``` ### 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: ```sql ALTER ROLE loginmanager ADD MEMBER ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER ``` ### 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: ```sql ALTER SERVER ROLE ADD MEMBER ``` > **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: ```sql ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER ``` > **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: ```sql GRANT ALTER ANY ROLE TO ``` - 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: ```sql ALTER ROLE db_owner ADD MEMBER ``` 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: ```sql ALTER ROLE loginmanager ADD MEMBER ``` **Revoke Permissions on Databases Other Than Master** Connect to the respective database and run the following command: ```sql GRANT CONTROL TO ``` > [!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: ```json [    {"op": "add", "path": "/includePermissions", "value": true}] ```