Microsoft SQL Server | Entitle

What is Microsoft SQL Server?

Microsoft SQL Server (MS SQL) is a relational database management system (RDBMS). It is designed to help organizations store, retrieve, and manage data efficiently in a structured format.

How is this integration useful?

Integrating Microsoft SQL Server with Entitle automates database access control, ensuring users have the right permissions for queries, reporting, and management tasks. With this integration, Entitle can manage the following resource types in MS SQL:

• Server (multiple roles can be assigned) – all the roles in server_principals and custom roles created for the server:
  • sysadmin
  • serveradmin
  • securityadmin
  • processadmin
  • setupadmin
  • bulkadmin
  • diskadmin
  • dbcreator
  • Server custom roles
• Database (multiple roles can be assigned) – all the roles in database_principals and custom roles created for the database:
  • db_owner
  • db_securityadmin
  • db_accessadmin
  • db_backupoperator
  • db_ddladmin
  • db_datawriter
  • db_datareader
  • db_denydatawriter
  • db_denydatareader
  • Database custom roles

Prerequisites

• Have SQL Server Management Studio (SSMS) installed.

  ℹ️

  To do so, select Install SSMS on the SQL Server 2019 Evaluation Edition page.

• Have MS SQL Server installed.

  ℹ️

  • Entitle currently supports versions 2017 and 2019 only.

  • Entitle recommends using an on-premises version of MS SQL 2019 on EC2 Amazon and to download the instance on c3.large for installation.

  • The Security group should allow RDP connections (TCP port 3389) and TCP connections to the port your SQL server is listening on (the default is 1433).

  • The configured server authentication must be SQL Server and Windows Authentication mode. SSMS can be used to modify it for a previously installed server:

    1. In SSMS, navigate to Properties → Server properties.

       

    2. Click Security. Under Server authentication, select SQL Server and Windows Authentication mode > OK.

       

    3. Restart the server.

• Must have an admin account in Entitle.
• Access to your MS SQL user, password, server, database, and version.

🚧

Important information

User provisioning is not supported

• MS SQL does not support new users’ provisioning, it can only change permissions. The only supported actor types are the following:
  • Database principle
    • SQL user
  • Server principle
    • SQL login
    • Windows login
• See the MS SQL account mapping section of this guide to map MS SQL users to Entitle.

Create a new login for Entitle using SSMS

ℹ️

Entitle requires creating an admin account that is not a security administrator (sa), as this type is exposed to risks.

1. Connect to your new server address. Open the SSMS program and log in as an admin user.

2. In SSMS, from the left navigation menu, expand the Security folder.

3. In the expanded Security folder, right-click the Logins sub-folder.

4. Click New Login.

   

5. Disable the Enforce password expiration option, and insert a password.

   

Add sysadmin role to your Entitle user

ℹ️

The Entitle user must have the sysadmin role to manage fixed roles, including granting or revoking them from those users.

If you need to grant access to a user without allowing sysadmin, see Least-privilege operating mode.

1. From the left navigation menu, go to the Server Roles tab.

   

2. To add a member to a fixed server role, you must be a member of that fixed server role or the sysadmin fixed server role. Click OK when done.

Least-privilege operating mode

ℹ️

Entitle supports a least-privilege operating mode for Microsoft SQL Server.

Due to limitations in SQL Server’s permission model, this mode still requires elevated server permissions, but does not require sysadmin.

When using least-privilege operating mode, the Entitle service account must be granted the following permissions:

• ALTER ANY LOGIN
• ALTER ANY SERVER ROLE
• CONNECT ANY DATABASE
• CONNECT SQL
• CONTROL SERVER
• VIEW ANY DATABASE
• VIEW ANY DEFINITION
• VIEW SERVER STATE

Custom roles requirement

Least-privilege operating mode requires the use of custom database roles:

• Custom roles must be created in the managed databases.
• Required permissions must be granted to those custom roles.
• Entitle assigns users to these custom roles as part of access provisioning.

In least-privilege operating mode, fixed server roles cannot be altered.

Verify your Firewall configuration

1. It is recommended that you verify your credentials and Firewall configuration locally before creating the integration in Entitle.

   ℹ️

   See How to configure remote access and connect to a remote SQL Server instance with ApexSQL tools.

   • In the Configure a Windows Firewall for Database Engine Access section of the abovementioned guide > step 5 (Profile window), make sure to tick all three checkboxes and not just Domain:

   

2. Download SSMS to your computer and attempt to log in with your Entitle user credentials on your MS SQL Server. If successful, you have verified that the user has been successfully created and the network access is correctly configured.

Create the MS SQL integration in Entitle

1. Log in to Entitle and go to the Integrations page.
2. Click Add Integration.
3. In the Application field, enter Microsoft SQL Server.
4. If you want to be able to create ephemeral accounts, check Allow creating accounts. This does not allow you to create permanent accounts.
5. In the Save on dropdown, select Cloud or your hosted agent.
6. In the Connection field:
   • version: Insert the version of your server.
   • user: Insert the user you created in MS SQL for Entitle.
   • password: Insert the password of the user you created in MS SQL for Entitle.
   • server: Insert your MS SQL server. It can be either the fully qualified domain name or the IP address.
   • database: Insert the names of your selected database.

✅

Example connection JSON

JSON

{
  "version": "2019" or "2017",
  "user": "<YOUR-ENTITLE-MS-SQL-USER>",
  "password": "<YOUR-MS-SQL-PASSWORD>",
  "server": "<YOUR-MS-SQL-SERVER>",
  "database": "<YOUR-MS-SQL-DATABASE>" 
}

7. Click Save.
   The Entitle integration with MS SQL saves

MS SQL account mapping

1. For end-users to be able to submit new access requests to MS SQL through Entitle, an admin must manually map the MS SQL Server accounts to their specific Entitle users’ email addresses:

   1. Log in to Entitle and go to the Org Settings screen.
   2. Scroll down to the System integrations section.
   3. Click + Add.
   4. Select Microsoft SQL and choose the accounts you wish to associate with the integration.
   5. Click Add Integration.
   

If MS SQL Server accounts are not mapped, an error occurs when attempting to request access.

Create an ephemeral account

1. In Entitle, go to the Requests page and click New request.

2. Using the dropdown at the top right, you can make the request for yourself or on behalf of another user.
3. Click on your Microsoft SQL Server application. Choose the resources and the roles you would like to request access to. Then click Next.

4. Set the duration for the ephemeral account and click Next.

5. Select an account to associate with your request. You are not requesting access to the actual user account; instead, Entitle creates an ephemeral account with the same permissions. This temporary account is prefixed with entitle so that any actions you perform are easy to identify, ensuring security and auditability by isolating your activity from the original account.

6. Provide a reason for your request. If a third-party ticketing system is integrated with your Entitle instance, you may link to a ticket. Click Review Request.

7. Review your request’s details and adjust if needed. Click Submit request.

8. Your request is submitted and is added to the My pending requests section.

9. Once approved, you are granted access to the specific role.

10. Use the ephemeral account to do the needed work. Your actions are logged as entitle_[account_name].
11. When the duration expires, the ephemeral account is deleted and cannot be used again. A new ephemeral account must be created if further temporary access is required.