Skip to main content

Privacera Documentation

Table of Contents

Microsoft SQL Server connector for PolicySync on Privacera Platform

These instructions enable and configure Privacera Microsoft SQL Server database connector to an existing Microsoft SQL Server database running on the Azure cloud platform or the AWS Relational Database Service (RDS). This connector uses the PolicySync method in which access policies defined in Privacera are mapped to and synchronized to the 'native' access controls in MS SQL.

The PolicySync approach has several benefits and advantages:

  • Fine-grained access control - at the database, schema, table, view, and column levels.

  • Column level masking

  • Dynamic row-level filters on tables and views

Generalized approach for implementing PolicySync

To help you reach compliance, Privacera PolicySync distributes your defined access management policies to the third-party datasources you connect to Privacera.

Use this generalized approach for implementing PolicySync.

  1. Understand how PolicySync works and how it is configured. See PolicySync design and configuration on Privacera Platform.PolicySync design and configuration on Privacera Platform

  2. Decide which PolicySync topology best suits your needs:

  3. Create the required, basic PolicySync configuration. See PolicySync design and configuration on Privacera PlatformPolicySync design and configuration on Privacera Platform

  4. Examine the BASIC and ADVANCED properties, decide which features you want to implement, and set the necessary values in the YAML property file.

Connector name: mssql

When you create the connector as detailed in PolicySync design and configuration on Privacera Platform, use the following reserved word for the name of the connector:PolicySync design and configuration on Privacera Platform

mssql

In formal syntax shown in PolicySync design and configuration on Privacera Platform replace <ConnectorName> with the above and in the example in PolicySync design and configuration on Privacera Platform, replace postgres with the above.PolicySync design and configuration on Privacera PlatformPolicySync design and configuration on Privacera Platform

Prerequisites

The Microsoft SQL Server must already be installed and running.

If you are installing an evaluation, you may need to install and configure an Microsoft SQL Server with one or more databases to test against.

1) Target Database Access

The Microsoft SQL Server must also be accessible from the Privacera Platform hosts. The standard inbound port for Microsoft SQL Server access is TCP 1433. Make sure that is open 'outbound' from Privacera Platform hosts and inbound to your target Microsoft SQL Server.

2) Access Control by Privacera Service Account

Privacera Platform requires access to the target database and the service account must be established in a 'loginmanager' role. This can be configured in three ways: (1) Access Control on Azure AD Users; (2) Access Control on Local Database Users; or (3) Access Control on both Azure AD user and local users.

Access Control on Azure AD Users

  1. Confirm the Microsoft SQL Server is configured to work with Azure AD Users.

  2. In your Azure AD, create a Privacera 'service' user to be used by Privacera for the Policy access control synchronization. For this example we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s). Keep note of the username and password as we'll use both later.

  3. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:

      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::${YOUR_DATABASE} TO [privacera_policysync@example.com];
      

Access Control on Local Database Users

  1. Create a Privacera 'service' user in the master database to be used by Privacera for the Policy access control synchronization. For this example, we'll assume the name is 'privacera_policysync' but set the value appropriately for your domain(s). Keep note of the username and password as we'll use both later.

    IF NOT EXISTS (SELECT name  FROM sys.sql_logins WHERE name = 'privacera_policysync') BEGIN
      CREATE LOGIN [privacera_policysync] WITH PASSWORD = '${PASSWORD}'
      END;
    
    IF DATABASE_PRINCIPAL_ID('privacera_policysync') IS NULL BEGIN
      CREATE USER [privacera_policysync] FROM LOGIN [privacera_policysync];
      END;
    
    EXEC sp_addrolemember [loginmanager], [privacera_policysync];
    
  2. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:

      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::${YOUR_DATABASE} TO [privacera_policysync];
      

Access Control on Azure AD and Local Database Users

  1. Confirm the Microsoft SQL Serveris configured to work with Azure AD Users.

  2. In your Azure AD, create a Privacera 'service' user to be used by Privacera for the Policy access control synchronization. For this example, we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s). Keep note of the username and password as we'll use both later.

  3. Create a Privacera 'service' user in the master database to be used by Privacera for the Policy access control synchronization. For this example, we'll assume the name is 'privacera_policysync@example.com' but set the value appropriately for your domain(s). Keep note of the username and password as we'll use both later.

    IF DATABASE_PRINCIPAL_ID('privacera_policysync@example.com') IS NULL BEGIN
      CREATE USER [privacera_policysync@example.com] FROM EXTERNAL PROVIDER;
      END;
    
    EXEC sp_addrolemember [loginmanager], [privacera_policysync@example.com];
    
  4. For each targeted database:

    1. Log on to the target database with an Admin role account.

    2. Execute the following:

      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::${YOUR_DATABASE} TO [privacera_policysync@example.com];
      

3) Create or Identify an ADLS Gen2 storage used to store Microsoft SQL Server Audits

  1. See Configure Microsoft SQL server for database synapse audits.

  2. Using information from that article obtain the Audit storage URL. This will be used in the Privacera Microsoft SQL Server PolicySync configuration.

4) Create an MS SQL server in AWS RDS to store MSSQL Server Audits

  1. Consult the following article SQL Server Audit.

  2. Using information from that article obtain the Audit storage URL. This will be used in the Privacera Microsoft SQL Server PolicySync configuration.