Microsoft SQL Plugin for Exact Data Match

This document explains how to configure the Microsoft SQL EDM plugin v1.1.0 with the Exact Data Match module of the Netskope Cloud Exchange platform. This plugin is used to pull raw data from configured Microsoft SQL server to generate EDM hashes.

Prerequisites

To complete the configuration, you need:

• A Netskope tenant (or multiple, for example, production and development/test instances).
• A Netskope Cloud Exchange tenant with the Tenant plugin and Exact Data Match plugin already configured.
• Microsoft SQL server setup
• Microsoft SQL Server database user with read-only (Select) access to fetch the data.

Microsoft SQL Server EDM Plugin Support

This plugin is used to pull raw data from configured Microsoft SQL server to generate EDM hashes. The plugin supports advanced data sanitization, normalization, and hash generation capabilities to ensure data quality and security compliance.

Microsoft SQL Database Permissions

Required Permissions

• Database user must have read permissions for the specified database.
• Network connectivity to the Microsoft SQL server on the configured port.
• Sufficient disk space on Cloud Exchange for temporary file processing.

from sqlalchemy import create_engine
eng = create_engine(
       connection_string, connect_args={"connect_timeout": CONNECTION_TIMEOUT,”TrustServerCertificate”:”yes”}
)

with eng.connect() as connection:
    # used to stop user from executing any database modification query.
    query = text(config["query"])
    result = connection.execute(query)

while True:
    rows = result.fetchmany(BATCH_SIZE)
    if not rows:
        break  # No more rows to fetch
    self.store_data_to_csv(rows, csv_path)

Performance Matrix

Here is the performance reading conducted for fetching and sanitizing ~1M Rows (25 columns, per column ~50 characters long string,1.3 GB size, Avg Column Uniqueness: ~96%, Avg Row Uniqueness: ~96%) from Database table on a Large CE instance with these specifications:

Workflow

1. Create a user on Microsoft SQL server with read permissions.
2. Configure Microsoft SQL EDM Plugin.
3. Configure sharing between Microsoft SQL EDM Plugin and EDM Netskope plugin.
4. Validate the Microsoft SQL plugin.

Watch a Video

Click play to watch a video.

Configure the Microsoft SQL Server

Create a user 

Follow the below steps to create the user on Microsoft SQL Database:

1. Log in to the Microsoft SQL Server instance and open the SQL terminal.

2. Execute the following commands, or follow this guide to create users with the necessary read-only (Select) privileges.

   • Create Login:

     • For SQL Server Authentication: Create login <YourLoginName> with password=<YourPassword>;
     • For Windows Authentication: CREATE LOGIN [<DomainName>\<YourLoginName>] FROM WINDOWS;

   • Switch to the Target Database and create a User:

     USE <YourDatabaseName>;
     CREATE USER <YourUserName> FOR LOGIN <YourLoginName>;

   • Grant Read-Only (SELECT) Permissions by Adding User to db_datareader Role: ALTER ROLE db_datareader ADD MEMBER <YourUserName>;

   Note

   • Replace <YourLoginName> and <YourPassword> with your actual login name and password.
   • Replace <YourUserName> with the name you want to assign to the database user.
   • Replace <YourDatabaseName> with the actual name of the database.
   • For Windows authentication, use the correct format: [domain\username].

Configure the Microsoft SQL EDM Plugin

1. In Cloud Exchange, go to Settings > Plugin Store.

2. Search for and select the Microsoft SQL v1.1.0 (EDM) plugin.

   

3. Enter the Basic Information:

   • Configuration Name: Enter a name appropriate for your plugin.

     The configuration name you provide will be used to give the name to the generated hash file to share with the Netskope tenant. Be aware that if you later delete this configuration and create a new one with the same name, the hash file may already exist in the Netskope tenant. In such cases, attempting to push the hash file to the tenant will result in an error, as the file with the same name already exists. Consider using a unique name to avoid this issue.

   • Sync Interval: Interval to pull the data from this plugin source. Default value is 12 hours. (Default: 12 hours)

   

4. Click Next and enter the Configuration Parameters:

   • Server IP/Hostname: IP address or Hostname where the Microsoft SQL server is located.

   • Username: Microsoft SQL database username to access the server. Ensure that it is a valid username with the read-only permission.

   • Password: Password associated with the provided database username.

   • Database Name: Microsoft SQL database name from which the data is to be retrieved. Ensure that the database name is spelled correctly, as database names are case-sensitive.

   • Port: Enter the TCP port number that the Microsoft SQL database is running.

   • Query: Microsoft SQL database query for which the data is to be retrieved to generate the EDM hashes. Ensure that the query is validated and read-only.

   • Remove Quotes: Enable this if your data file encloses fields in double quotes, especially when values contain commas. Quoted fields will be parsed as single columns. Improper quote placement may cause rows to be skipped. Helpful for CSVs exported from databases.

   

   By default, quotes are treated as literal text. Enable Remove Quotes toggle if your CSV uses double quotes to encapsulate fields that contain commas (like "123 ABC Street, Suite 100"). This ensures the field is treated as a single column. Note that this mode requires strict CSV formatting. If a field starts with a quote, any character, including a space, following the closing quote but preceding the comma (like Word",), this will cause the row to be skipped.

5. Click Next and enter the Hash Generation and Sanitization Parameters:

   • Select the appropriate options for sanitization and hash generation operations:

     • Sanitization (Name Column): Sanitize the content by checking the Name Column checkbox. (Default: Unchecked). The Sanitization Process performs the following actions:
       • One character: The cell will be marked as invalid if it contains only one character.
       • Digits: Cells containing digits will be marked as invalid.
       • Stopwords: Cells that match a stopword from the list will be marked as invalid (This works only if the Remove stopwords checkbox is checked).
       • Non-alphanumeric characters: Remove all special characters to validate the cell.
     • Hash Generation is divided into two parts:
       • Normalization: Select the value from dropdown to normalize the data value. (Default: None)
       • Create Dictionary: Select value from dropdown to create dictionary of unique values for selected field that can be used in DLP rule in netskope tenant. (Default: None)

   • Remove Stopwords: Mark as checked if you want to remove certain stopwords as part of the sanitization process.(Default:Unchecked) Ensure that Name Column is checked for the applicable field to reflect the changes.

     Note

     • Use Normalization parameter to normalize the fetched results. For example, a number such as 123-45-6789 or 123 45 6789 will be treated as 123456789. Number normalizations ignore characters such as dots, dashes and spaces. A string normalization ignores the case sensitivity of the letters.
     • Use the Create Dictionary option only when necessary, as this operation is resource-intensive and may impact system performance. Choose this option thoughtfully for optimal efficiency.

   

6. Click Next. Preview the sanitization sample output by clicking Preview Good File or Preview Bad File.

   

   If you are using the plugin with sanitization On, then the performance may be differ due to extra processing.

7. Proceed without sanitization: Uncheck this option to proceed with sanitization. (Default: Checked)

   All the data will be under consideration for hash generation if this option is Unchecked; otherwise, only the Good File content will be part of the hash generation.

8. Click Save. You will be redirected to Exact Data Match > Plugins page where you can see your configured plugin.

   

Configure an EDM Sharing Configuration for Microsoft SQL

A sharing configuration is used to share the generated EDM hashes with the destination platform. To share EDM hashes with your Netskope Tenant, create a sharing configuration using these steps:

1. Go to Exact Data Match > Sharing and click Add Sharing Configuration.

   

2. Configure the sharing parameters:

   • Source Configuration: Select the configured EDM Microsoft SQL plugin.
   • Destination Configuration: Select a destination where EDM Hash will be shared.
   • Target: The value is automatically set according to the selected Destination Configuration.

   

3. Click Save.

   

Validate the Microsoft SQL EDM Plugin

Validate the Pull

To validate the pulling of the configured plugin in Cloud Exchange, go to Settings > Logging and search for the Microsoft SQL EDM plugin logs.

You can verify the plugin operation from the logs available at Logging in Cloud Exchange:

If a sharing configuration has been established for the source plugin, its status can be monitored on the Sharing and Upload Management page.

The status values are as follows:

• Scheduled: Indicates that the sharing has been configured and the pull and push operation are still waiting in the queue for processing.
• Generating Hash: Indicates that the generating hash process has been started.At this stage, in the background fetching > validating > sanitization(if opted for) > generating hash staged will be included.
• Uploading Hash: Indicates that uploading hash to the destination configuration has been started.
• Upload Completed: Indicates that hashes are uploaded to the destination configuration.
• Checking Apply Status: At this stage, checking the apply status of hashes to the destination configuration.
• Apply In Progress: This represents that the hash process is started and in progress state on the destination.
• Completed: Indicates that hash file has been pushed successfully to destination configuration.
• Failed: Indicates that the final result of the action has been failed to execute.The actions are Generating Hash/Uploading Hash/Checking Apply Status.

Shared EDM hashes can be verified from the logs available at Logging in Cloud Exchange:

Validate the Push to your Netskope Tenant

To ensure the push of EDM hashes on the Netskope tenant from Cloud Exchange:

1. Log in to your Netskope tenant, go to Policies > DLP.

   

2. Click Edit Rules and select Data Loss Prevention.

   

3. On the Exact Match tab, a list of files is shown.

   

Troubleshooting the Microsoft SQL Plugin

Unable to configure the MS SQL plugin

If you are unable to configure the Microsoft SQL plugin, it could be due to one of the following reason:

• Incorrect credentials provided.
• The user does not have required permissions.
• Incorrect hostname/server IP.
• Incorrect Database Name.

What to do:

1. Make sure to provide correct credentials.
2. Make sure that the user has the required permissions for database and table.
3. Make sure that the correct hostname/ server IP.
4. Make sure that database name is correct.

Hash generation fails or takes too long

If hash generation fails or performance is poor.

What to do:

1. Check available disk space on Cloud Exchange (ensure at least 2x CSV file size).
2. Reduce file/table size or split into smaller tables.
3. Disable sanitization if not required to improve performance.
4. Monitor system resources during processing.

Known Behaviors

• Sanitization can reduce processing speed by approximately 65% but improves data quality.
• Dictionary creation is resource-intensive and should only be used when necessary.
• The plugin creates temporary files during processing that are automatically cleaned up.
• SSH connection timeouts may occur with very large files; consider increasing timeout values.
• Memory usage scales with CSV file size/table size; monitor system resources during large file processing.