Documentation

Microsoft SQL Server

Suggest Edits

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.

    ℹ️

    Note

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

  • Have MS SQL Server installed.

    ℹ️

    Notes:

    • Entitle currently supports versions 2017 and 2019 only.

    • Entitle recommends using an on-premise 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.

💡

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

ℹ️

Note

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.

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

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

  3. Click New Login.

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

Add ‘sysadmin’ role to your Entitle user

ℹ️

Note

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

  1. On the left-side navigation menu, navigate 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 be a member of the sysadmin fixed Server role. Click OK when done.

Verify your Firewall configuration

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

    ℹ️

    Note

    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

💡

Important

This applies to creating an integration either through Entitle on Pathfinder or through the Entitle standalone product.

  1. Sign in to Entitle.
  2. Navigate to the Integrations page.
  3. Click Add Integration.
  4. In the Application field, enter Microsoft SQL Server.
  5. In the Save on dropdown, select Entitle 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:

{
  "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>" 
}
  1. Click Save.
    The Entitle integration with MS SQL saves

MS SQL account mapping

  1. If MS SQL Server accounts are not mapped to the integration, the user may receive the following message when attempting to request access:

  2. 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:

    • Navigate to the Org Settings screen in Entitle.

    • Click + Add Integration.

    • Select Microsoft SQL and choose the accounts you wish to associate with the integration.

    • Click Add Integration.

      Once this process is complete, the integration accounts will be able to request access to the associated MS SQL resources and roles.

Updated 2 days ago

What’s Next