mssql.adapter Integration Documentation

Purpose

The mssql.adapter is an integration module designed for seamless interaction with Microsoft SQL Server databases within the Vantage analytics and data platform. It facilitates both read and write operations, schema introspection, and table management, ensuring secure data handling while restricting destructive operations. By using this adapter, users can efficiently execute SQL queries, manage database schemas, and integrate data from SQL Server into the Vantage platform.

Settings

The mssql.adapter requires specific settings for successful configuration and operation. Below is a detailed explanation of each setting:

1. host

• Input Type: String
• Description: The hostname or IP address of the SQL Server instance. This setting defines where the integration attempts to connect.
• Default Value: No default; must be explicitly provided.

2. port

• Input Type: Numeric
• Description: The port number on which the SQL Server is listening. Default SQL Server instance usually operates on port 1433. Specifying an incorrect port will hinder the connection.
• Default Value: 1433

3. username

• Input Type: String
• Description: The username to authenticate against the SQL Server. Must have necessary permissions based on the operations to be performed.
• Default Value: No default; must be explicitly provided.

4. password

• Input Type: String
• Description: The password associated with the specified username for authentication. This should be handled securely to prevent exposure.
• Default Value: No default; must be explicitly provided.

5. databaseName

• Input Type: String
• Description: The name of the database to which the integration will connect. This database context is crucial for executing queries and performing operations.
• Default Value: No default; must be explicitly provided.

6. ssl

• Input Type: Boolean
• Description: Indicates whether to use SSL encryption for the connection. Setting this to true enhances security but requires the server to be configured accordingly.
• Default Value: false

7. trustServerCertificate

• Input Type: Boolean
• Description: When set to true, it bypasses certificate validation. This setting is useful in development environments but should be used cautiously in production.
• Default Value: true

How It Works

The mssql.adapter establishes a connection to the SQL Server using the provided settings. Once connected, it allows executing SQL queries while enforcing security measures to prevent harmful operations such as data manipulation or schema alterations unless explicitly permitted by user-initiated actions.

Connection Handling

• The adapter utilizes a connection pool to manage database connections effectively.
• The _getPool method initializes the connection using the specified settings and loads the mssql library dynamically.

Security Features

• Forbidden Patterns: The adapter restricts certain SQL commands (e.g., INSERT, UPDATE, DELETE, etc.) through pattern matching via regex to mitigate data corruption risks.
• Destructive DDL Restrictions: Acts against potentially destructive DDL commands (e.g., DROP, TRUNCATE) to safeguard the database schema.

Expected Data

When using the mssql.adapter, the integration expects:

• All connection settings to be configured correctly, without which the connection will fail.
• SQL queries passed to methods like executeReadOnly and executeWrite must be in valid SQL syntax for successful execution.

Use Cases & Examples

Use Cases

1. Data Analysis for Business Intelligence: A company may require querying real-time data from their SQL Server to generate reports or dashboards for decision-making.

2. Data Migration or Synchronization: Businesses may need to migrate existing data from legacy systems into the Vantage platform, necessitating structured read-write operations.

3. Schema Management: Administrators or developers may streamline schema updates by programmatically adding or modifying tables and columns directly through the integration.

Example Configuration

Use Case Example: Data Analysis for Business Intelligence

Configuration Example:

{
  "host": "database.yourcompany.com",
  "port": 1433,
  "username": "analytics_user",
  "password": "securePassword123",
  "databaseName": "SalesData",
  "ssl": true,
  "trustServerCertificate": false
}

Practical SQL Query Example:

• Read Operation:

  javascriptCopy

  const adapter = new MSSQLAdapter(yourEncryptedRecord);
  const query = "SELECT * FROM Sales WHERE SaleDate >= '2023-01-01'";
  const results = await adapter.executeReadOnly(query);

• Write Operation:

  javascriptCopy

  const insertQuery = "INSERT INTO Sales (Amount, SaleDate) VALUES (@amount, @date)";
  const params = {
      amount: 1500,
      date: new Date()
  };
  await adapter.executeWrite(insertQuery, params);

Billing Impact

Integration with SQL Server via the mssql.adapter may incur costs related to data transfer and storage if large volumes of data are queried or managed. Users should review their hosting and database service agreements for potential costs associated with queries executed through this adapter. It is essential to monitor and optimize queries to prevent unintentional excessive costs.