Microsoft SQL Server
To create a new SQL Server data source:
Work with your database administrator to:
decide whether you want to create a new database user for NOW Privacy or use an existing one
request the connection parameters that will allow you to get data from the database; you’ll use these in step 6
NOW Privacy uses the JDBC API to communicate with target databases
The user credentials have to be for a database user
We do not support Active Directory Authentication
From the main menu select Data Sources
Click New ⇾ New Data Source
Select a dataset or add a new dataset and click Next
Select the Database data source and click Next
Choose SQL Server
Enter the connection parameters:
Field
Description
Host
The IP address or fully qualified domain name of the target server. Can be server\instance or just server.
Database Name
The name of the database you want to crawl. You can only target one database at a time.
Port
The port that SQL Server is using. For example: 1433. Choose port 0 if SQL Server is using dynamic ports.
Schema Name
The schema name of the target database tables/views.
Username
A user who has read access to the data you want to crawl.
Password
The password for the user.
If you use port 0 you must include the instance in the host
If you specify both a port and instance, the port will take precedence and the instance name will be ignored
For SQL to look up the port when specifying port 0:
The firewall must allow traffic on UDP port 1434
For Microsoft SQL Server 2005 or later the SQL Server Browser Service must be running
NOW Privacy tries to connect to the database host
If it fails, it prompts you to correct the parameters
Select the table or view you want to crawl, or click Crawl Whole Schema
If you choose a table or view, we’ll only crawl that data
If you need to crawl another table or view, you’ll need to create another data source
If you click Crawl Whole Schema, we’ll crawl every table we can in the schema
Sample Size is optional; it allows you to sample the data rather than crawling every row
Choose a unique identifier
When you’ve selected the target table or view, choose the column to use as the primary identifier for a row (typically the primary key)
You can select columns from the target table or view that should not be stored by NOW Privacy
Identify versioning columns
NOW Privacy can look at one or more columns to check whether any row has changed since we last crawled it
For example, the database might have a customer_record_last_updated column
Select the columns you want NOW Privacy to look at
Map columns to NOW Privacy fields
The last step in configuring the database connection is to choose how individual columns should be mapped to the existing NOW Privacy metadata fields
You can create compound mappings and new custom fields
Select the country for this data source and click Next
The country is used in geographical visualisations
Optionally, click Add Custom Description
This allows you to use you own description for this data source, rather than accept the default description that NOW Privacy provides
Set the Crawl priority
Specify whether you want to crawl once or continuously
Click Start Crawl Immediately if you want to crawl the data right now
Or you can start a crawl manually anytime you want
Click Create Data Source
Changing the primary key
When you’re using the option to crawl an entire schema, be careful not to change a primary key.
If you ingest the data for an entire schema, then change a primary key, and then re-crawl the data, NOW Privacy will not recognise the ‘new’ version of the data it sees. It will:
assume you deleted all the rows from that row for that primary key
ingest the data as if it were a new row
This slows down the recrawl and means that any tags you have added are lost.