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
- Connection parameters
- Encrypted connections
- Limitations
- Supported data types
- Unsupported key columns
Required privileges
A DataMasque connection for Microsoft SQL Server requires:
CONNECTandCREATE TABLEpermissions.SELECT,UPDATE,INSERT,ALTERandREFERENCESpermissions 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.
The Encryption field on the connection chooses how DataMasque connects:
Encryption |
Behaviour |
|---|---|
Optional (default) |
Encrypts when the server asks for it. Against a server that supports encryption without forcing it, only the login is encrypted and data is sent in clear. This is the usual ODBC default, and no warning is written. Against a server that does not support encryption, the connection is unencrypted and the run log gets a TLS fallback warning. |
Required |
Encrypts the login and the data. The connection fails if the server does not support encryption. No warning is written. |
Off |
Does not request encryption: the login, password included, and the data are sent in clear. A server that forces encryption (for example Azure SQL) refuses the connection. No warning is written. A run_sql script is the exception (below). |
To protect the data as well as the login, choose Required.
A run_sql script runs in sqlcmd, which has no level without encryption:
with Off it still encrypts the login when the server supports encryption, so a server that forces encryption accepts it.
Connections saved before this field existed behave as Optional, or as Required if they were saved with encrypt: true.
In the API, the legacy field encrypt: true maps to Required;
it is deprecated in favour of tls_mode, and a request that sets both is refused.
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_imitateis not supported onIDENTITYcolumns 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,
č(lowercasecwith caron) might becomec(lowercasec) 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/nvarcharcolumns 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
_CSor_BIN) may cause unexpected errors. See the SQL Server Collation and Unicode support documentation for more information.Before a
mask_tabletask masks any rows, DataMasque asks SQL Server whether two of the columns the task's rules mask are one column. SQL Server compares column names under its catalog collation: the database collation, except on Azure SQL Database and contained databases, which have a catalog collation of their own. Many collations treat different spellings as one name, for examplestraßeandstrasse, a full-widthAandA, orIandıunder a Turkish collation. If the rules mask two such spellings, the task fails with an error such as:
The rules for table `dbo.addresses` name columns that SQL Server treats as one column: `straße` and `strasse`. Refer to each column by one name only.
To fix it, spell the column the same way in every rule of the task.
Only the columns that rules mask are compared: a column that a rule only reads, such as a hash_columns entry,
is not.
The reverse is not detected. Under a Turkish collation, id and ID are two different columns,
but DataMasque treats them as one column. Do not mask both of them in the same task.
- The transaction log (
.ldffile) 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
textdata 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 |