Skip to content

Prerequisites for Microsoft SQL Server

Before setting up the Microsoft SQL Server connector, ensure that the following prerequisites are met.

Prerequisites

Prerequisite Details
JDBC URL Obtain the JDBC URL required to connect to your Microsoft SQL Server database. The connector supports SQL Server on-premises, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
  • General format: jdbc:sqlserver://<SERVER_NAME>:<PORT>
  • Azure SQL Database format: jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433
Network Connectivity The Microsoft SQL Server must be accessible from the Privacera Platform hosts. The standard inbound port for Microsoft SQL Server access is TCP 1433. Make sure that port 1433 is open outbound from Privacera Platform hosts and inbound to your target Microsoft SQL Server.
JDBC Username and Password Create a dedicated SQL Server service account that the Privacera connector will use to manage access permissions. This account creates and manages users, roles, and grants or revokes permissions based on policies defined in Privacera. The service account must have sufficient server-level and database-level privileges to perform required administrative operations. See the Recommendations to Create SQL Server Service Account section below for detailed permission requirements and step-by-step setup instructions.
Database(s) to Manage Identify the database name(s) that the connector will manage. These are specified using the manage.database.list configuration property. It is recommended to start with a non-production (test) database.
Schema and Table Identification Identify the schemas, tables, and views within the managed database(s) that the connector will manage. You may optionally specify a schema list (supports wildcards) to limit the scope of management. System schemas such as sys, INFORMATION_SCHEMA, privacera_security, and default database roles are automatically excluded from discovery and policy management.
Users and Groups to Manage Determine the users, groups, and roles whose access Privacera will manage in Microsoft SQL Server. The connector supports:
  • SQL Server internal users (SQL authentication)
  • SQL Server external users (Azure AD / Microsoft Entra ID authentication using EXTERNAL PROVIDER)
  • SQL Server database roles
  • Privacera-managed users, groups, and roles
Authentication Method Decide how the connector service account will authenticate:
  • SQL Authentication (default): Uses SQL Server username and password
  • Azure AD (Microsoft Entra ID) Authentication: Supported for Azure SQL Database by configuring the jdbc.authentication property

Recommendations to Create SQL Server Service Account

To perform the following steps, log in to SQL Server using an account with SYSADMIN or SECURITYADMIN server role privileges

The service account created in this step is used to configure the Microsoft SQL Server connector with Privacera. The recommended service account name is privacera_policysync; however, you may use a different name if required.

The service account must have the sufficient privileges to manage permissions across all databases, schemas, tables, and views. If Privacera manages users and groups in SQL Server, the service account must also have privileges to create, update, and delete users and roles.

The service account can be configured using one of the following methods:

  1. Azure AD user access control - For Azure SQL Database using Azure AD authentication
  2. Local database user access control - For SQL Server using SQL authentication
  3. Hybrid access control (Azure AD and local database users) - For environments using both Azure AD and SQL authentication

Access Control on Azure AD Users

This configuration applies to Azure SQL Database or SQL Server instances that are configured to use Azure AD (Microsoft Entra ID) authentication.

Prerequisites:

  • Microsoft SQL Server is configured to authenticate Azure AD (Microsoft Entra ID) users
  • Azure AD authentication is enabled on the SQL Server instance

Step 1: Create Azure AD Service Account

Create a service account in Azure AD (Microsoft Entra ID) for Privacera. The recommended account name is privacera_policysync@example.com; however, you may use a different name appropriate for your domain. Record the username and password, as they are required during connector configuration.

Step 2: Grant Database-Level Privileges (Azure AD)

For each database managed by the connector, connect using an account with SYSADMIN or SECURITYADMIN privileges, and execute the following commands:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Connect to the operational database
USE [YourOperationalDatabase];  -- Replace with your database name

-- Create Azure AD user in the operational database
IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL
BEGIN
    CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER;
END;

-- Grant full control on database to privacera_policysync@example.com user
GRANT CONTROL ON DATABASE::[YourOperationalDatabase] TO [privacera_policysync@example.com];
Why CONTROL ON DATABASE is required

The CONTROL ON DATABASE privilege provides comprehensive database-level privileges, including the ability to grant and revoke permissions on all database objects, create users and roles, create schemas, and manage security policies. This enables Privacera to synchronize privileges (e.g., SELECT, UPDATE, INSERT, DELETE) between Privacera policies and SQL Server users and roles.

Access Control on Local Database Users

This configuration applies to SQL Server instances that use SQL authentication (local database users).

Step 1: Create a Local Service Account

Create a service account in the master database to be used by Privacera for policy access control synchronization. The recommended account name is privacera_policysync; however, you may use a different name appropriate for your environment. Record the username and password, as they are required during connector configuration.

Connect to the master database using an account with SYSADMIN or SECURITYADMIN privileges, and execute the following commands:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- Connect to master database
USE master;

-- Create login for the service account
IF NOT EXISTS (SELECT name FROM sys.sql_logins WHERE name = 'privacera_policysync')
BEGIN
    CREATE LOGIN [privacera_policysync] WITH PASSWORD = '<PLEASE_CHANGE>';
END;

-- Create user in master database from the login
IF DATABASE_PRINCIPAL_ID('privacera_policysync') IS NULL
BEGIN
    CREATE USER [privacera_policysync] FROM LOGIN [privacera_policysync];
END;

-- Add to loginmanager server role (provides CREATE LOGIN and ALTER ANY LOGIN)
EXEC sp_addrolemember 'loginmanager', 'privacera_policysync';

-- Add to dbmanager server role (provides CREATE DATABASE and ALTER DATABASE)
EXEC sp_addrolemember 'dbmanager', 'privacera_policysync';
Why loginmanager and dbmanager server roles are required

The loginmanager server role provides the necessary server-level privileges to create and manage logins in SQL Server. The dbmanager server role provides the necessary privileges to create and manage databases. These roles enable Privacera to synchronize users between Privacera and SQL Server.

Step 2: Grant Database-Level Privileges (Local Users)

For each database managed by the connector, connect using an account with SYSADMIN or SECURITYADMIN privileges, and execute the following commands:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Connect to the operational database
USE [YourOperationalDatabase];  -- Replace with your database name

-- Create user in the operational database from the login
IF DATABASE_PRINCIPAL_ID('privacera_policysync') IS NULL
BEGIN
    CREATE USER [privacera_policysync] FROM LOGIN [privacera_policysync];
END;

-- Grant full control on database to privacera_policysync user
GRANT CONTROL ON DATABASE::[YourOperationalDatabase] TO [privacera_policysync];

Hybrid access control (Azure AD and local database users)

This configuration supports both Azure AD users and local database users within the same SQL Server instance.

Prerequisites:

  • SQL Server is configured to authenticate Azure AD (Microsoft Entra ID) users
  • Azure AD authentication is enabled on the SQL Server instance

Step 1: Create an Azure AD Service Account (Hybrid)

Create a service account in Azure AD (Microsoft Entra ID) for Privacera. The recommended account name is privacera_policysync@example.com; however, you may use a different name appropriate for your domain. Record the username and password, as they are required during connector configuration.

Step 2: Grant Server-Level Privileges (Hybrid)

Connect to the master database using an account with SYSADMIN or SECURITYADMIN privileges, and execute the following commands:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Connect to master database
USE master;

-- Create Azure AD user in master database
IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL
BEGIN
    CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER;
END;

-- Add to loginmanager server role (provides CREATE LOGIN and ALTER ANY LOGIN)
EXEC sp_addrolemember 'loginmanager', 'privacera_policysync@example.com';

Step 3: Grant Database-Level Privileges (Hybrid)

For each database managed by the connector, connect using an account with SYSADMIN or SECURITYADMIN privileges, and execute the following commands:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Connect to the operational database
USE [YourOperationalDatabase];  -- Replace with your database name

-- Create Azure AD user in the operational database
IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL
BEGIN
    CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER;
END;

-- Grant full control on database to privacera_policysync@example.com user
GRANT CONTROL ON DATABASE::[YourOperationalDatabase] TO [privacera_policysync@example.com];