- Platform Release 6.5
- Privacera Platform Installation
- About Privacera Manager (PM)
- Install overview
- Prerequisites
- Installation
- Default services configuration
- Component services configurations
- Access Management
- Data Server
- PolicySync
- Snowflake
- Redshift
- Redshift Spectrum
- PostgreSQL
- Microsoft SQL Server
- Databricks SQL
- RocksDB
- Google BigQuery
- Power BI
- UserSync
- Privacera Plugin
- Databricks
- Spark standalone
- Spark on EKS
- Portal SSO with PingFederate
- Trino Open Source
- Dremio
- AWS EMR
- AWS EMR with Native Apache Ranger
- GCP Dataproc
- Starburst Enterprise
- Privacera services (Data Assets)
- Audit Fluentd
- Grafana
- Ranger Tagsync
- Discovery
- Encryption & Masking
- Privacera Encryption Gateway (PEG) and Cryptography with Ranger KMS
- AWS S3 bucket encryption
- Ranger KMS
- AuthZ / AuthN
- Security
- Access Management
- Reference - Custom Properties
- Validation
- Additional Privacera Manager configurations
- CLI actions
- Debugging and logging
- Advanced service configuration
- Increase Privacera portal timeout for large requests
- Order of precedence in PolicySync filter
- Configure system properties
- PolicySync
- Databricks
- Table properties
- Upgrade Privacera Manager
- Troubleshooting
- How to validate installation
- Possible Errors and Solutions in Privacera Manager
- Unable to Connect to Docker
- Terminate Installation
- 6.5 Platform Installation fails with invalid apiVersion
- Ansible Kubernetes Module does not load
- Unable to connect to Kubernetes Cluster
- Common Errors/Warnings in YAML Config Files
- Delete old unused Privacera Docker images
- Unable to debug error for an Ansible task
- Unable to upgrade from 4.x to 5.x or 6.x due to Zookeeper snapshot issue
- Storage issue in Privacera UserSync & PolicySync
- Permission Denied Errors in PM Docker Installation
- Unable to initialize the Discovery Kubernetes pod
- Portal service
- Grafana service
- Audit server
- Audit Fluentd
- Privacera Plugin
- How-to
- Appendix
- AWS topics
- AWS CLI
- AWS IAM
- Configure S3 for real-time scanning
- Install Docker and Docker compose (AWS-Linux-RHEL)
- AWS S3 MinIO quick setup
- Cross account IAM role for Databricks
- Integrate Privacera services in separate VPC
- Securely access S3 buckets ssing IAM roles
- Multiple AWS account support in Dataserver using Databricks
- Multiple AWS S3 IAM role support in Dataserver
- Azure topics
- GCP topics
- Kubernetes
- Microsoft SQL topics
- Snowflake configuration for PolicySync
- Create Azure resources
- Databricks
- Spark Plug-in
- Azure key vault
- Add custom properties
- Migrate Ranger KMS master key
- IAM policy for AWS controller
- Customize topic and table names
- Configure SSL for Privacera
- Configure Real-time scan across projects in GCP
- Upload custom SSL certificates
- Deployment size
- Service-level system properties
- PrestoSQL standalone installation
- AWS topics
- Privacera Platform User Guide
- Introduction to Privacera Platform
- Settings
- Data inventory
- Token generator
- System configuration
- Diagnostics
- Notifications
- How-to
- Privacera Discovery User Guide
- What is Discovery?
- Discovery Dashboard
- Scan Techniques
- Processing order of scan techniques
- Add and scan resources in a data source
- Start or cancel a scan
- Tags
- Dictionaries
- Patterns
- Scan status
- Data zone movement
- Models
- Disallowed Tags policy
- Rules
- Types of rules
- Example rules and classifications
- Create a structured rule
- Create an unstructured rule
- Create a rule mapping
- Export rules and mappings
- Import rules and mappings
- Post-processing in real-time and offline scans
- Enable post-processing
- Example of post-processing rules on tags
- List of structured rules
- Supported scan file formats
- Data Source Scanning
- Data Inventory
- TagSync using Apache Ranger
- Compliance Workflow
- Data zones and workflow policies
- Workflow Policies
- Alerts Dashboard
- Data Zone Dashboard
- Data zone movement
- Workflow policy use case example
- Discovery Health Check
- Reports
- How-to
- Privacera Encryption Guide
- Overview of Privacera Encryption
- Install Privacera Encryption
- Encryption Key Management
- Schemes
- Encryption with PEG REST API
- Privacera Encryption REST API
- PEG API endpoint
- PEG REST API encryption endpoints
- PEG REST API authentication methods on Privacera Platform
- Common PEG REST API fields
- Construct the datalist for the /protect endpoint
- Deconstruct the response from the /unprotect endpoint
- Example data transformation with the /unprotect endpoint and presentation scheme
- Example PEG API endpoints
- /authenticate
- /protect with encryption scheme
- /protect with masking scheme
- /protect with both encryption and masking schemes
- /unprotect without presentation scheme
- /unprotect with presentation scheme
- /unprotect with masking scheme
- REST API response partial success on bulk operations
- Audit details for PEG REST API accesses
- Make encryption API calls on behalf of another user
- Troubleshoot REST API Issues on Privacera Platform
- Privacera Encryption REST API
- Encryption with Databricks, Hive, Streamsets, Trino
- Databricks UDFs for encryption and masking
- Hive UDFs
- StreamSets Data Collector (SDC) and Privacera Encryption
- Trino UDFs for encryption and masking
- Privacera Access Management User Guide
- Privacera Access Management
- How Polices are evaluated
- Resource policies
- Policies overview
- Creating Resource Based Policies
- Configure Policy with Attribute-Based Access Control
- Configuring Policy with Conditional Masking
- Tag Policies
- Entitlement
- Service Explorer
- Users, groups, and roles
- Permissions
- Reports
- Audit
- Security Zone
- Access Control using APIs
- AWS User Guide
- Overview of Privacera on AWS
- Set policies for AWS services
- Using Athena with data access server
- Using DynamoDB with data access server
- Databricks access manager policy
- Accessing Kinesis with data access server
- Accessing Firehose with Data Access Server
- EMR user guide
- AWS S3 bucket encryption
- Getting started with Minio
- Plugins
- How to Get Support
- Coordinated Vulnerability Disclosure (CVD) Program of Privacera
- Shared Security Model
- Privacera Platform documentation changelog
Microsoft SQL Server
These instructions enable and configure Privacera Microsoft SQL (MS SQL) database connector to an existing MS SQL 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
Prerequisites
The MS SQL Server must already be installed and running.
If you are installing an evaluation, you may need to install and configure an MS SQL Server with one or more databases to test against.
1) Target Database Access
The MS SQL Database server must also be accessible from the Privacera Platform host(s). The standard inbound port for MS SQL Server access is TCP 1433. Make sure that is open 'outbound' from Privacera Platform host(s) and inbound to your target MS 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
Confirm the MS SQL Server is configured to work with Azure AD Users.
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.
For each targeted database:
Log on to the target database with an Admin role account.
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
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];
For each targeted database:
Log on to the target database with an Admin role account.
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
Confirm the MS SQL Server is configured to work with Azure AD Users.
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.
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];
For each targeted database:
Log on to the target database with an Admin role account.
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 MS SQL Server Audits
Consult the following article How to Configure MS SQL Server for Database Synapse Audits.
Using information from that article obtain the Audit storage URL. This will be used in the Privacera MS SQL PolicySync configuration.
4) Create an MSSSQL server in AWS RDS to store MSSQL Server Audits
Consult the following article SQL Server Audit.
Using information from that article obtain the Audit storage URL. This will be used in the Privacera MSSQL PolicySync configuration.
CLI Configuration
SSH to the instance where Privacera is installed.
Run the following command.
cd ~/privacera/privacera-manager/config cp sample-vars/vars.policysync.mssql.yml custom-vars/ vi custom-vars/vars.policysync.mssql.yml
Set the properties for your specific installation. For property details and description, see the Configuration Properties section that follows.
Note
Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see .
There are two properties that establish the type of 'masking' that will be supported for this connector: 'native masking', and 'view-based masking'.
Native Masking - in MS SQL known as 'Dynamic Data Masking' - is supported directly by MS SQL Server. This level of masking has low granularity and only supports the ability to mask by database for each user. See Microsoft documentation Dynamic Data Masking for more background.
Privacera Platform supports 'View-based Masking', which for MS SQL supports Row-level filtering and masking. View-based masking is the default and is recommended.
Property Name
Value/Comments
MS SQL_ENABLE_MASKING
Set to
true
to enable MS SQL 'native' masking and disable View-based masking functionality.MS SQL_ENABLE_VIEW_BASED_MASKING
Set to
true
to enable View-based masking functionality.MS SQL_UNMASKED_DATA_ROLE
A comma-separated MS SQL role list for roles authorized for unmasked data. all other users will see masked data.
Run the following commands.
cd ~/privacera/privacera-manager ./privacera-manager.sh update
Configuration Properties
JDBC configuration properties
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
| Yes | Specifies the JDBC URL for the Microsoft SQL Server connector. Use the following format for the JDBC string: jdbc:sqlserver://<JDBC_SQLSERVER_URL_WITH_PORT_NUMBER> | |
|
| Yes | Specifies the JDBC username to use. | |
|
| Yes | Specifies the JDBC password to use. | |
|
|
| Yes | Specifies the name of the JDBC master database that PolicySync establishes an initial connection to. |
|
|
| Yes | Specifies the authentication type for the database engine. The following types are supported:
|
|
| Yes | Specifies the password to use when PolicySync creates new users. | |
|
| No | Specifies the role that owns the resources managed by PolicySync. You must ensure that this user exists as PolicySync does not create this user.
The following resource types are supported:
|
Load keys and intervals
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
|
| No | Specifies how PolicySync loads resources from Microsoft SQL Server. The following values are allowed:
|
|
|
| No | Specifies the interval in seconds for PolicySync to wait before checking for new resources or changes to existing resources. |
|
|
| No | Specifies the interval in seconds for PolicySync to wait before reconciling principals with those in the data source, such as users, groups, and roles. When differences are detected, PolicySync updates the principals in the data source accordingly. |
|
|
| No | Specifies the interval in seconds for PolicySync to wait before reconciling Apache Ranger access control policies with those in the data source. When differences are detected, PolicySync updates the access control permissions on data source accordingly. |
|
|
| No | Specifies the interval in seconds to elapse before PolicySync retrieves access audits and saves the data in Privacera. |
Resources management
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
| No | Specifies a comma-separated list of database names for which PolicySync manages access control. If unset, access control is managed for all databases. If specified, use the following format. You can use wildcards. Names are case-sensitive. An example list of databases might resemble the following: If specified, | |
|
| No | Specifies a comma-separated list of schema names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive. Use the following format when specifying a schema:
If specified, If you specify a wildcard, such as in the following example, all schemas are managed:
The specified value, if any, is interpreted in the following ways:
| |
|
| No | Specifies a comma-separated list of table names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive. Use the following format when specifying a table: <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME> If specified, If you specify a wildcard, such as in the following example, all matched tables are managed:
The specified value, if any, is interpreted in the following ways:
| |
|
| No | Specifies a comma-separated list of database names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all databases are subject to access control. For example: testdb1,testdb2,sales_db* This setting supersedes any values specified by | |
|
| No | Specifies a comma-separated list of schema names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all schemas are subject to access control. For example: testdb1.schema1,testdb2.schema2,sales_db*.sales* This setting supersedes any values specified by | |
|
| No | Specifies a comma-separated list of table names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all tables are subject to access control. Names are case-sensitive. Specify tables using the following format: <DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME> This setting supersedes any values specified by |
Users/Groups/Roles management
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
|
| No | Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies a string to replace the characters matched by the regex specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies a string to replace the characters matched by the regex specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies a string to replace the characters matched by the regex specified by the If not specified, no find and replace operation is performed. |
|
|
| No | Specifies whether PolicySync converts user names to lowercase when creating local users. If set to |
|
|
| No | Specifies whether PolicySync converts group names to lowercase when creating local groups. If set to |
|
|
| No | Specifies whether PolicySync converts role names to lowercase when creating local roles. If set to |
|
|
| No | Specifies how user name conversions are performed. The following options are valid:
This setting applies only if |
|
|
| No | Specifies how group name conversions are performed. The following options are valid:
This setting applies only if |
|
|
| No | Specifies how role name conversions are performed. The following options are valid:
This setting applies only if |
|
| No | Set this property to true if you only want to manage users who have an email address associated with them in the portal. | |
|
|
| No | Specifies whether PolicySync maintains user membership in roles in the Microsoft SQL Server data source. |
|
|
| No | Specifies whether PolicySync creates groups from Privacera in the Microsoft SQL Server data source. |
|
|
| No | Specifies whether PolicySync creates roles from Privacera in the Microsoft SQL Server data source. |
|
| No | Specifies a comma-separated list of user names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive. If not specified, PolicySync manages access control for all users. If specified, An example user list might resemble the following: | |
|
| No | Specifies a comma-separated list of group names for which PolicySync manages access control. If unset, access control is managed for all groups. If specified, use the following format. You can use wildcards. Names are case-sensitive. An example list of projects might resemble the following: If specified, | |
|
| No | Specifies a comma-separated list of role names for which PolicySync manages access control. If unset, access control is managed for all roles. If specified, use the following format. You can use wildcards. Names are case-sensitive. An example list of projects might resemble the following: If specified, | |
|
| No | Specifies a comma-separated list of user names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all users are subject to access control. This setting supersedes any values specified by | |
|
| No | Specifies a comma-separated list of group names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all groups are subject to access control. This setting supersedes any values specified by | |
|
| No | Specifies a comma-separated list of role names that PolicySync does not provide access control for. You can specify wildcards. Names are case-sensitive. If not specified, all roles are subject to access control. This setting supersedes any values specified by | |
|
|
| No | Specifies the prefix that PolicySync uses when creating local users. For example, if you have a user named |
|
|
| No | Specifies the prefix that PolicySync uses when creating local roles. For example, if you have a group named |
|
|
| No | Specifies the prefix that PolicySync uses when creating roles from Privacera in the Microsoft SQL Server data source. For example, if you have a role in Privacera named |
|
|
| No | Specifies whether PolicySync uses the Microsoft SQL Server native public group for access grants whenever a policy refers to a public group. The default value is false. |
|
|
| No | Specifies whether to manage only the users that are members of groups specified by |
|
|
| No | Specifies whether to manage only users that are members of the roles specified by |
Native Row filter
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
|
| No | Specifies whether to use the data source native row filter functionality. This setting is disabled by default. When enabled, you can create row filters only on tables, but not on views. |
|
|
| No | Specifies whether to use secure view based masking. The default value is |
|
|
| No | Specifies whether to use secure view based row filtering. The default value is While Microsoft SQL Server supports native filtering, PolicySync provides additional functionality that is not available natively. Enabling this setting is recommended. |
|
|
| No | Specifies whether to create secure views for all tables and views that are created by users. If enabled, PolicySync creates secure views for resources regardless of whether masking or filtering policies are enabled. |
|
|
| No | Specifies the default masking value for numeric column types. |
|
|
| No | Specifies the default masking value for text and string column types. |
|
|
| No | Specifies the default masking value for date column types. |
|
| No | Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name. If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is | |
|
|
| No | Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name. If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is |
|
| No | Specifies a prefix string to apply to a secure schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name. If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is | |
|
| No | Specifies a postfix string to apply to a secure view schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name. If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is | |
|
| No | Specifies a suffix to remove from a table or view name. For example, if the table is named You can specify a single suffix or a comma separated list of suffixes. | |
|
| No | Specifies a suffix to remove from a schema name. For example, if a schema is named You can specify a single suffix or a comma separated list of suffixes. | |
|
|
| Yes | Specifies whether PolicySync performs grants and revokes for access control and creates, updates, and deletes queries for users, groups, and roles. The default value is |
|
|
| No | Specifies the maximum number of attempts that PolicySync makes to execute a grant query if it is unable to do so successfully. The default value is |
|
|
| No | This property is used to enable the data admin feature. With this feature enabled you can create all the policies on native tables/views, and respective grants will be made on the secure views of those native tables/views. These secure views will have row filter and masking capability. In case you need to grant permission on the native tables/views then you can select the permission you want plus data admin in the policy. Then those permissions will be granted on both the native table/view as well as its secure view. |
Access audits management
Name | Type | Default | Required | Description |
---|---|---|---|---|
|
|
| Yes | Specifies whether Privacera fetches access audit data from the data source. If specified, you must specify a value for the |
|
| No | Specifies the URL for the audit logs provided by the Azure SQL Auditing service. For example: | |
|
|
| No | Specifies the initial delay, in minutes, before PolicySync retrieves access audits from Microsoft SQL Server. |
|
|
| No | Specifies the method that PolicySync uses to load access audit information. The following values are valid:
|
|
|
| No | Specifies how PolicySync loads users from Microsoft SQL Server. The following values are valid:
|
|
|
| No | Specifies whether PolicySync creates local users for external users. |
|
| No | Specifies a comma separated list of users to exclude when fetching access audits. For example: | |
|
|
| No | Specifies whether access policies apply to only groups. If enabled, any policies that apply to users or roles are ignored. |