Integrating SailPoint with Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet).

This document describes features, requirements, and procedures needed to create a direct connect source configuration for a Microsoft SQL source. It also describes how to configure the features that are supported.

Supported Features

The Microsoft SQL Server source supports the following features:

• Account Management

  • Aggregate Microsoft SQL Server Windows Service account as the following:

    • Managed service accounts
    • Group-managed service accounts
    • Virtual accounts

  For more information, refer to Microsoft SQL Server Windows Service account.

• *Password Management

• *Provisioning
• *Access Certifications (certification of entitlements connected to accounts)
• Delete Account
• Aggregate Windows Active Directory groups created on the Microsoft SQL server

For more information on features, refer to Source Features.

The Microsoft SQL Server source supports the Always-On feature of the Microsoft SQL Server target systems.

Direct Permissions

The Microsoft SQL Server source supports the direct permissions:

• Permissions directly assigned to accounts and groups as direct permissions during accounts and entitlement aggregation respectively
• Revocation of the aggregated permissions for accounts through certification

Perform the following steps to enable direct permissions and their aggregation, for the account or the group schema of your Microsoft SQL Server source.

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

3. Use updateSchema API and enter the following content in body.

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

Following targets are supported:

• DATABASE
• SERVER
• PROCEDURE
• ASYMMETRIC_KEY
• SYMMETRIC_KEYS
• USER
• CERTIFICATE_MAPPED_USER
• DATABASE_ROLE

For example,

• GRANT CONNECT on 'databaselogin@databasename:DATABASE'
• GRANT CONNECT SQL on 'serverloginname:SERVER'

Privilege Classification

SailPoint Privilege Classification helps organizations systematically categorize and manage the privilege levels of entitlements within their sources. By classifying entitlements as high, medium, or low, administrators can better understand the potential risk and impact associated with specific access rights. This allows automatic assignment of privilege levels based on system recommendations, custom-defined criteria, or a same level across all entitlements.

For more information, refer to Managing Privilege Classification.

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.

Amazon Web Services Relational Database Service (AWS RDS)

AWS RDS is a managed service provided by Amazon and there are certain restrictions on what an administrative account can perform on the MSSQL instance on AWS RDS. As a result of these restriction, SailPoint Microsoft SQL Server Connector supports Microsoft SQL Server on AWS RDS with the following mentioned limitations:

• The endpoint URL of the AWS RDS instance must be obtained from the AWS Management Console, and it must be accessible from the Microsoft SQL Server source. The AWS Security groups must be updated.

  Note

  The source configuration attributes are the same.

• Operations which are prohibited for master user of the AWS RDS Microsoft SQL instance would also not be allowed for the service account user for SailPoint Microsoft SQL Server Connector.

• The model system database cannot be managed using SailPoint's Microsoft SQL Server Connector and has to be added to the excluded databases list configuration. It is recommended to exclude the other system databases (namely master, tempdb and msdb) as all operations (that is, provisioning) are not supported for these databases.

• The service account has restricted permissions to the following server roles:

  • bulkadmin
  • dbcreator
  • diskadmin
  • securityadmin
  • serveradmin
  • sysadmin

  Note

  It is recommended that these server roles must be made non requestable in SailPoint.

  Apart from the above listed server roles, for provisioning any custom server roles with service account, the following permission must be assigned to the service account:

  grant alter any server role to [account]
  

• If SailPoint Microsoft SQL Server Connector is to be used as a read only connector, there is a limitation from AWS RDS for assigning the following permission (in reference to the permissions mentioned under Aggregation):

  grant connect any database to [account]
  

  As a workaround a database account must be associated with the service account for each database that needs to be managed.

  Note

  Support for Windows authentication is yet to be validated for AWS RDS.

• Few users such as the master and rdsa users can be aggregated, but provisioning operations cannot be performed on these users by the Microsoft SQL Server source.

SSL Connection

The Microsoft SQL Server Source supports the SSL Connection.

Configuration Steps:

1. On the source VA, log in with the SailPoint user.
2. Go to /home/sailpoint/certificates.
3. The final certificate file obtained from the server must be placed in this directory.

4. In the hosts.yaml file make an entry of the SQL Server host in the XXX.XX.XX.XXX format.

   Note

   For information on creating a hosts.yaml file, refer to Configuring a Hosts.yaml File.

5. Restart the CCG with the sudo systemctl restart ccg command.

6. Create a source on SailPoint with the following URL:

   jdbc:sqlserver://<SQL Server hostname>:<port on which TLS is configured>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=<SQL Server hostname>

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.
• The Microsoft SQL Server jar file has been obtained. Refer to Download Microsoft JDBC Driver for SQL Server for detailed instructions.

Prerequisites for Using the AlwaysOn Availability Groups Feature

• All the Server Login accounts must be replicated across all the primary and secondary nodes of the Microsoft SQL Servers which are configured for availability group. Although this is not provided out of the box by Microsoft, there are MSDN articles on how to achieve it.
• Only the user databases that are in the availability databases of the availability group must be part of the databases managed by the Microsoft SQL Server source.

Supported Managed Systems

The SailPoint Microsoft SQL Server connector supports the following versions:

• Microsoft SQL Server 2022

• Microsoft SQL Server 2019 on supported Linux platforms:

  • Red Hat Enterprise Linux
  • SUSE Enterprise Linux Server
  • Ubuntu

  For more information on the Linux versions, refer to Release notes for SQL Server 2019 on Linux.

• Microsoft SQL Server 2019

• Microsoft SQL Server 2017
• Microsoft SQL Server 2016
• Azure Managed Instance. When using an Azure Managed Instance, Windows authentication is not supported. For more information, refer to the Microsoft documentation.
• Microsoft SQL Server on Azure SQL Virtual Machines.
• For Azure SQL database managed systems, use the Microsoft Azure SQL Database connector. For more information, refer to Microsoft Azure SQL Database.
• Microsoft SQL Server hosted on Amazon Web Services Relational Database Service (AWS RDS). For more information on AWS RDS, refer to Amazon Web Services Relational Database Service (AWS RDS).

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 SQL Server service accounts (user) based on the operations:

Test Connection

grant Connect SQL to [user]

Aggregation

use [master]
GO
grant Connect SQL to [user]
grant view any database to [user]
grant view any definition to [user]
grant connect any database to [user]

Enable/Disable User

grant alter any login to [user]

Change Password

grant alter any login to [user]

Delete Account

grant alter any login to [user]

Delete Role

use [database name]
create user [username] for login [server login name]
grant alter any role to [username]

Create Account

grant alter any login to [user]

Create Role

use [database name]
create user [username] for login [server login name]
grant alter any role to [username]

To perform any operation on a database, the service account must have database user on the specific database.

use [database name]
create user [username] for login [server login name]
exec sp_addrolemember db_owner, [username]

User must have proper server role assignments to assign the same role to another user.

For example, if administrator has granted Role1, Role2, Role3 roles to user A then, user A can grant only Role1, Role2, Role3 to any other user. User A cannot assign other roles apart from the roles assigned to it.

Above mentioned permissions are required for adding and removing entitlements.

Revoke Permissions

To revoke permissions, the service account must have Grant access to those permissions. For example, if the service account (user) wants to revoke 'X' permission for any other user, it must have the Grant access to the 'X' permission.

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.