Skip to content

Setup Access Management for Microsoft SQL Server

This section outlines the steps to set up the Microsoft SQL Server connector in Privacera. Ensure that all prerequisites are met before proceeding.

Best Practice for enabling the connector

This connector will overwrite existing SQL Server access control policies with Privacera-managed policies.

Recommended approach:

  1. Create a test database in SQL Server.
  2. Configure the connector to manage access policies only for that database.
  3. Verify that policy management works as expected.
  4. After verification, configure the connector for your production databases.

Create an Instance of the Microsoft SQL Server Connector

  1. SSH to the instance where Privacera Manager is installed.

  2. Run the following command to navigate to the /config directory.

    Bash
    1
    cd ~/privacera/privacera-manager/config

  3. Create a new directory for the Microsoft SQL Server connector configuration.

    Note

    Replace instance1 with a unique name for your connector (use only alphanumeric characters and hyphens).

    Bash
    1
    mkdir -p custom-vars/connectors/mssql/instance1

  4. Copy the sample connector configuration file to your custom directory:

    Bash
    1
    cp -n sample-vars/vars.connector.mssql.yml custom-vars/connectors/mssql/instance1/

  5. Open the .yml file for editing:

    Bash
    1
    vi custom-vars/connectors/mssql/instance1/vars.connector.mssql.yml

  1. In PrivaceraCloud, navigate to Settings → Applications.

  2. On the Applications screen, select MSSQL application under Available connections.

  3. Enter the application Name and Description, then click Save. The name can be any name of your choice, e.g. MSSQL Connector for account 123456.

  4. Open the MSSQL application.

  5. Enable the Access Management option with toggle button.

Configure Connection Details

Update the following properties in your vars.connector.mssql.yml file:

  1. Specify the Microsoft SQL Server JDBC URL. For example, jdbc:sqlserver://example.database.windows.net:1433.
  2. Provide the Microsoft SQL Server username and password used to authenticate the JDBC connection.
  3. Specify the master database name (typically master) for the master database connection.
  4. Specify the authentication type for the Microsoft SQL Server login.
  5. Specify the default password for new Microsoft SQL Server users created by Policysync Connector.
Authentication Type

The CONNECTOR_MSSQL_AUTHENTICATION_TYPE property specifies the authentication method.

Supported values:

  • SqlPassword: Use when CONNECTOR_MSSQL_JDBC_USERNAME is a local SQL Server user.
  • ActiveDirectoryPassword: Use when CONNECTOR_MSSQL_JDBC_USERNAME is an Azure Active Directory user.

Replace the below example values with your actual values.

YAML
1
2
3
4
5
6
7
8
CONNECTOR_MSSQL_JDBC_URL: "jdbc:sqlserver://example.database.windows.net:1433"
CONNECTOR_MSSQL_JDBC_DB: "master"
CONNECTOR_MSSQL_JDBC_USERNAME: "<mssql-jdbc-username>"
CONNECTOR_MSSQL_JDBC_PASSWORD: "<mssql-jdbc-password>"
CONNECTOR_MSSQL_AUTHENTICATION_TYPE: "SqlPassword"
CONNECTOR_MSSQL_DEFAULT_USER_PASSWORD: "<default-password>"
CONNECTOR_MSSQL_MANAGE_ENTITIES: "true"
CONNECTOR_MSSQL_GRANT_UPDATES: "true"

Enter these fields under the BASIC tab:

  • MSSQL JDBC url: jdbc:sqlserver://<MSSQL_SERVER_HOST>:1433
  • MSSQL JDBC username: <mssql-jdbc-username>
  • MSSQL JDBC password: <mssql-jdbc-password>
  • MSSQL master database: master
  • MSSQL authentication type for the login: SqlPassword
  • Default password for new mssql user: <default-password>

Managed Database

This property is used to specify the database name for which access control policies will be applied.

Important

The MSSQL connector supports only one database at a time for access control. Providing multiple databases in a comma-separated list is not supported. You must specify a single database name.

Example: testdb1.

Warning

  • Database name is case-sensitive.
  • Replace the example value with your actual database name.
  • This field is mandatory. You must specify exactly one database name.

It is recommended to first test the connector with a test database before configuring it for your target database.

YAML
1
CONNECTOR_MSSQL_MANAGE_DATABASE_LIST: "testdb1"

Under the BASIC tab, enter the values for:

  • Database to set access control policies: testdb1

Apply the Configuration

After completing the configuration, start the connector by running the following instructions:

Step 1 - Setup which generates the helm charts. This step usually takes few minutes.

Bash
1
2
cd ~/privacera/privacera-manager
./privacera-manager.sh setup
Step 2 - Apply the Privacera Manager helm charts.
Bash
1
2
cd ~/privacera/privacera-manager
./pm_with_helm.sh upgrade
Step 3 - (Optional) Post-installation step which generates Plugin tar ball, updates Route 53 DNS and so on. This step is not required if you are updating only connector properties.

Bash
1
2
cd ~/privacera/privacera-manager
./privacera-manager.sh post-install

  1. Once all the required fields are filled, click Save.

  2. The configured Microsoft SQL Server connector appears under Connected Applications.

  3. Once saved and enabled, the Microsoft SQL Server connector will start automatically. Then you can hover on the VIEW LOGS button to check the status, either Running or Stopped.

  4. If the connector status shows Stopped after saving, or if configuration changes are not being applied, perform the following steps to restart the Microsoft SQL Server connector application:

    1. Go to Settings → Applications → select the MSSQL connector application.

    2. Edit the application by disabling the Access Management option with toggle button and then Save it.

    3. Open the same application again and then enable the Access Management option with toggle button and then Save it.