MSSQL Driver Documentation

This documentation explains the MSSQL driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

UI Field	Technical Implementation	Description	Required	Default Value
Host	`server` in mssql config	The hostname or IP address of the MSSQL server	Yes	localhost
Port	`port` in mssql config	The port number MSSQL server is listening on	Yes	1433 (from driverPorts)
Database	`database` in mssql config	The name of the database to connect to	Yes	None (must be specified)
Username	`user` in mssql config	Database user for authentication	No	None (when using Windows authentication)
Password	`password` in mssql config	Password for the database user	No	None (when using Windows authentication)
Schema	`dbSchema` in gigadb config	The schema to use for queries and metadata extraction	No	None (defaults to user's default schema)

Technical Details

The MSSQL driver implementation uses the mssql Node.js library which internally uses the tedious library for connections.

Key technical aspects:

Connection pooling is supported through the pooled flag in configuration
Authentication can be either SQL Server authentication (user/password) or Windows authentication
The driver uses INFORMATION_SCHEMA views and sys tables for metadata extraction
Schema extraction includes table names, column information, and data types
DDL generation uses sys tables to create CREATE TABLE statements
The driver requires VIEW DATABASE STATE permissions for accessing sys.dm_db_partition_stats
Bulk insert operations are supported through the bulkBatchInsert method
Foreign key relationships are detected by querying sys.foreign_keys and related tables
Proper handling of Microsoft SQL Server specific data types (uniqueidentifier, datetime2, etc.)

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the MSSQL server.

Connection String Format

MSSQL connections are built using the tedious connection format:

Server=[host],[port];Database=[database];User ID=[user];Password=[password];Encrypt=true;TrustServerCertificate=true;

For Windows authentication, the connection string omits User ID and Password:

Server=[host],[port];Database=[database];Integrated Security=true;Encrypt=true;TrustServerCertificate=true;

Authentication Options

MSSQL supports several authentication types:

Auth Type	UI Mapping	Description
None	No username/password fields	Uses Windows/Integrated authentication
Password	Username/Password fields	Standard SQL Server authentication
Password Manager	Various password manager fields	Fetches credentials from external password managers

The MSSQL driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

API Endpoints Used

MSSQL connections are primarily used in:

Tap creation (data source discovery)
Sink creation (data destination for anonymized data)
Pipeline execution (data extraction and loading)

Node.js Driver Dependency

The MSSQL driver depends on the mssql Node.js library:

Library: mssql
Purpose: Connects to Microsoft SQL Server databases
Features: Connection pooling, prepared statements, bulk operations, transactions

Custom Params

The MSSQL driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, SSL settings, and other advanced options.

Connection Options

# Security and Encryption
encrypt: true
trustServerCertificate: true
cryptoCredentialsDetails:
# TLS configuration options
rejectUnauthorized: false
multiSubnetFailover: false

# Authentication Options
domain: "MYDOMAIN"
trustedConnection: false  # For Windows Authentication with msnodesqlv8 driver

# Connection Behavior
connectionRetryInterval: 500
maxRetriesOnTransientErrors: 3
fallbackToDefaultDb: false
connectionIsolationLevel: "READ_COMMITTED"  # or "READ_UNCOMMITTED", "REPEATABLE_READ", "SERIALIZABLE", "SNAPSHOT"

# Session Settings
appName: "Gigantic Application"
datefirst: 7  # First day of week (1-7)
dateFormat: "mdy"  # or "dmy", "ymd", "ydm", "myd", "dym"
language: "us_english"
textsize: 2147483647  # Maximum size of text or image data

# ANSI Settings
enableAnsiNull: true
enableAnsiNullDefault: true
enableAnsiPadding: true
enableAnsiWarnings: true
enableArithAbort: true
enableConcatNullYieldsNull: true
enableCursorCloseOnCommit: false
enableImplicitTransactions: false
enableNumericRoundabort: false
enableQuotedIdentifier: true

Pool Configuration

# Pool sizing options
min: 0
max: 10

# Pool timeout options
idleTimeoutMillis: 30000

Request Options

# Query execution options
requestTimeout: 15000
stream: false
parseJSON: false
arrayRowMode: false

# Result set options
multiple: false
rowCollectionOnDone: false
rowCollectionOnRequestCompletion: false

Authentication Configuration

# Different authentication types
authentication:
  type: "default"  # or "ntlm", "azure-active-directory-password", "azure-active-directory-access-token", etc.
  options:
    userName: myuser
    password: mypassword
    domain: "MYDOMAIN"

# Azure AD Token Authentication
authentication:
  type: "azure-active-directory-access-token"
  options:
    token: ${env:AZURE_AD_TOKEN}

# Azure AD Service Principal
authentication:
  type: "azure-active-directory-service-principal-secret"
  options:
    clientId: ${env:AZURE_CLIENT_ID}
    clientSecret: ${env:AZURE_CLIENT_SECRET}
    tenantId: ${env:AZURE_TENANT_ID}

Example Custom Configurations

More examples and details on the configurations options can be found here: https://github.com/tediousjs/node-mssql

Basic Configuration

encrypt: true
trustServerCertificate: false
max: 20
requestTimeout: 30000

Windows Authentication

trustedConnection: true
domain: "CORPORATE"
appName: "Gigantic Data Pipeline"

Azure AD Authentication

authentication:
  type: "azure-active-directory-password"
  options:
    userName: azure_user@mytenant.onmicrosoft.com
    password: ${env:AZURE_PASSWORD}
    tenantId: ${env:AZURE_TENANT_ID}
encrypt: true
trustServerCertificate: false

Advanced Configuration with Custom Settings

connectionIsolationLevel: "SNAPSHOT"
dateFormat: "ymd"
language: "British"
textsize: 2000000
enableAnsiNull: false
enableQuotedIdentifier: false
min: 2
max: 15
idleTimeoutMillis: 60000
requestTimeout: 60000