SQL Server connector

The SQL Server connector supports SQL Server 2012 and later versions, including SQL Server in the cloud (AWS, Azure SQL, etc...).

SSL/TLS connections are fully supported, both to the servers and the clients.


Limitations

The following are known limitations of this connector:

SQL Server is a vast and complex beast, so it's always possible we missed a feature. If so, please let us know and we'll fix it promptly.

Parameters

This connector takes the following parameters:

Connection name

Can be any name you want.


Active

If checked, then this connection will be active any time Gallium Data is running, meaning that the local port will be open and listening for requests. If you uncheck this (and then click Publish), then the connection is "commented out" -- it's as if it didn't exist, but you can still keep it around and re-activate it later.


Type

The type of database for this connection. This is set when you create the connection and cannot be changed afterwards.


Server host

The name or address of the database server. This can be a partial name (like dbserver), a fully qualified name (like dbserver.it.acme.com), an IP4 address (like 181.77.230.42) or an IP6 address if your network supports IP6 (e.g. 266a:1790:2920:69e0:35c:d8ac:da8e:15e0). 

If you are running Gallium Data as a Docker container, and the database is on the host machine, you can use host.docker.internal (on Windows or Mac, but see here for Linux).


Local address

The address on which to listen for requests. This is typically only relevant if your server has more than one address. By default, Gallium Data will listen on all addresses (which is equivalent to 0.0.0.0).


Server port

The port on the database server. For SQL Server, it's usually 1433 but it can also be something else. 


Local port

The port number in the Docker container on which Gallium Data will be listening to requests from database clients. If you use a port other than 1433, your SQL Server clients will need to specify that port, typically in the form galliumproxy,1435 (i.e. the name of the machine running Gallium Data, followed by a comma and the port you chose).

Remember that this is the port in the Docker container -- if you map it to your host machine, you can map it to any other port number.


Trust server certificate

Whether to accept SQL Server's SSL certificate at face value, or not. 

If this is false, and SSL is required, then SQL Server's certificate will be verified, either by being signed by a well-known certificate authority, or by verifying it with the chain of certificates provided in the project's Trust entry. 

If this is true, then the SQL Server SSL certificate will be accepted without verification.


Timeout to server

This specifies the number of milliseconds to wait when establishing a connection to the database server. The default value (10 seconds) is usually adequate for most cases.


Result set batch size (rows)

If set, result sets will be processed in batches of rows, and regular result set filters will not be invoked -- only result set batch filters (if any). This number indicates the largest number of rows in batches. The actual batches may contain fewer rows if the end of the result set has been reached, or if the Result set batch size (bytes) parameter is also specified.

Typical values are between 10 and 1000. Very large values may require more heap allocated to the JVM, and will adversely affect latency.


Result set batch size (bytes)

If set, result sets will be processed in batches, and regular result set filters will not be invoked -- only result set batch filters (if any). This number indicates approximately the largest amount of memory that a batch should grow to before it is submitted to the response filters. The actual batches may contain fewer rows if the end of the result set has been reached, or if the Result set batch size (rows) parameter is also specified.

Typical values are between 1000 and 1000000. Very large values may require more heap allocated to the JVM, and will adversely affect latency.

Related pages

SQL Server packets: describes all the packet types used by SQL Server

SQL Server filters: shows all the filter types for SQL Server

Examples: a few examples of using filters with SQL Server

Contexts: lists the JavaScript contexts available in filters

MSSQLUtils object: useful methods

Special objects

TypeInfo: describes a data type

ColumnMetadata: describes a column

DataClassification: contains the data classification for a result set

Articles

Tutorial: shows the basics of Gallium Data with SQL Server

SQL Server data classification comes alive: explains what data classification is, and how Gallium Data can make it active

SQL Server data classification tutorial: shows how Gallium Data can use SQL Server's data classification feature to enforce fine-grained access control

Row-level security:  SQL Server vs. Gallium Data: compares SQL Server's row-level security to Gallium Data's fine-grained security

Data masking: SQL Server vs. Gallium Data: compares SQL Server's data masking feature to using Gallium Data