DataMasque Portal

Microsoft SQL Server connections

DataMasque masks Microsoft SQL Server tables in place.

The separate Microsoft SQL Server (Linked Server) connection type was removed in DataMasque 3.26.18. Existing connections of that type are archived when you upgrade, and can no longer be used for masking runs.

Required privileges

A DataMasque connection for Microsoft SQL Server requires:

  • CONNECT and CREATE TABLE permissions.

  • SELECT, UPDATE, INSERT, ALTER and REFERENCES permissions to the target schema.

  • Ensure the connection user has the default schema set to the target schema for masking.

USE <target database>;
GRANT CONNECT, CREATE TABLE TO <connection user>;
GRANT SELECT, UPDATE, INSERT, ALTER, REFERENCES ON SCHEMA::<target schema> TO <connection user>;
ALTER USER <connection user> WITH DEFAULT_SCHEMA = <target schema>;

Connection parameters

Microsoft SQL Server Advanced Settings

For Microsoft SQL Server databases, the following fields are optional:
Schema The name of the schema that DataMasque will operate on. If not specified, the database user's default schema will be used.
Instance The instance name of the named database instance that DataMasque will connect to.

Note: If a port number is specified in the connection, the value of the instance will be ignored when connecting. In order to connect to a named instance, the port number must be left blank. By default, named instances are configured on a dynamic port: it is best practise to use a non-default port where possible to minimise possible attack vectors.

Encrypted connections

DataMasque supports encrypted connections to Microsoft SQL Server instances that have the 'Force Protocol Encryption' option enabled. No additional configuration is required in DataMasque - encryption will be initiated by the server.

Note on SQL Server 2008: DataMasque formally supports SQL Server 2012 and above. Connections to SQL Server 2008 may work on a best-effort basis, but only if the instance has cumulative update KB3135244 applied to enable TLS 1.2. Instances without this update will fail to negotiate a connection.

Limitations

Microsoft SQL Server Limitations

  • from_unique_imitate is not supported on IDENTITY columns for Microsoft SQL Server.

  • No form of character set / collation / encoding setting is available for Microsoft SQL Server connections. DataMasque always interoperates with Microsoft SQL Server databases using UTF-8. SQL Server replaces any characters not representable in the database or column's collation with alternate characters. For example, č (lowercase c with caron) might become c (lowercase c) or a question mark ?.
    Ensure your ruleset's masked values, custom discovery match rules, and so on contain only characters from the target database or column's collation setting.

Note: nchar / nvarchar columns with _SC (supplementary character) collations can store any Unicode data, so consider using columns of this type if you need to ensure all Unicode characters can be read and written without data loss.

  • In rare circumstances, using case-sensitive collations (for example, collations containing _CS or _BIN) may cause unexpected errors. See the SQL Server Collation and Unicode support documentation for more information.

  • The transaction log (.ldf file) can grow significantly during masking. DataMasque does not disable or truncate the transaction log as part of a masking run; the database administrator should take any necessary steps to ensure the transaction log stays at a manageable size. Refer to the Troubleshooting page for more information.

Supported data types

The following Microsoft SQL Server data types are supported.

Data type Description
char(n) Fixed-length character data of n size.
varchar(n) Variable-length character data of n size.
text 32 bit string value.
nvarchar(n) Variable-length character data of length n characters or bytes using national character set.
nchar(n) Fixed-length character data of length n using national character set.
nvarchar2(n) Variable-length character data of length n characters or bytes using national character set.
decimal(p,s) Numerical value with p precision and s decimal places.
bigint 64 bit integer value.
int 32 bit integer value.
smallint 16 bit integer value.
tinyint 8 bit integer value.
float, real Floating point number.
money Currency value.
date Date value with no time.
datetime Date including a time.
datetimeoffset Date and time, including time zone.
datetime2 Date that is combined with a time of day based on 24-hour clock.
xml XML data.

Important! When attempting to mask text data type in Microsoft SQL Server, 2GB will be allocated for each text element when determining the maximum size of the element. To ensure that masking can run correctly, please ensure enough memory is available to the DataMasque instance.

Unsupported key columns

Data Type Unsupported Format
datetime All
datetime2 Precision >= 7
datetimeoffset Precision >= 7
time Precision >= 7