Microsoft SQL Server

Overview

The Microsoft SQL Server plugin provides the functionality to execute queries on a database.

Functionality

The Security Flow Microsoft SQL Server Plugin provides the ability to execute queries and return results one row at a time (streaming) or in a single message.

Instance Configuration Parameters

Property

Description

Instance Name

Name for the Microsoft SQL Server instance.

Unique ID

A system-wide unique identifier for this plugin instance used to locate the service.

Authentication Mechanism

The authentication mechanism used to connect to the database. The following options are supported.

Username/Password Authentication: Username and password combination.
NTLM Authentication: Windows NT LAN Manager (NTLM) authentication protocol.
Azure AD v1.0 Authorization Token using Secret: The oauth2 authorization token is obtained using the Tenant ID, Client ID, and Client Secret tuple from Azure Active Directory (AD) using the Azure AD (v1.0) endpoint. The request is to grant access to the https://database.windows.net/ resource.
Azure AD v1.0 Authorization Token using CBA: The oauth2 authorization token is obtained using the Certificate Based Authentication (CBA) from Azure Active Directory (AD) using the Azure AD (v1.0) endpoint. The request is to grant access to the https://database.windows.net/ resource.

Server Name

SQL Server host name. Within the Azure portal, this is the Server name found in the Overview section of the SQL database.

Server Port

SQL Server connection port. Valid values range from 1 to 65535, but 1433 is the standard value.

Database

SQL Server database name.

Username

The username used for the Username/Password Authentication or the NTLM Authentication authentication mechanisms.

Password

The password used for the Username/Password Authentication or the NTLM Authentication authentication mechanisms.

Domain

The domain used for the NTLM Authentication authentication mechanism.

Tenant ID

The Azure AD Tenant (Directory) ID hosting the application.

Application ID

The Application ID (service principle) used with the Azure AD v1.0 Authorization Token using Secret or the Azure AD v1.0 Authorization Token using CBA authentication mechanisms. This application ID, specified via Display name, must also be added as a database user from an external provider with the minimal appropriate grants.

Client Secret

The client secret of the application used with the Azure AD v1.0 Authorization Token using Secret authentication mechanism.

Certificate File

The Certificate PEM file used with the Azure AD v1.0 Authorization Token using CBA authentication mechanisms containing both the certificate and private key. PEM is a Base64 ASCII encoding of X.509 certificates. For more information, see the Wikipedia PEM page.

Encrypt

Enables encryption for all communication with the SQL Server. Microsoft Azure requires this be enabled.

Verify Certificate

When encryption is enabled, this determines if the server’s SSL certificate must be verified before allowing the SSL connection.

Connection Timeout

The number of seconds before the attempt to connect to the SQL Server is considered failed. Defaults to 5 seconds.

Request Timeout

The number of seconds before a SQL request is considered failed. Defaults to 15 seconds.

Flow Node

This node provides Microsoft SQL Server capabilities to execute queries.

Property

Description

Name

The display name of the node within the flows.

Unique ID

System-wide unique ID of the plugin instance.

SQL Query

The SQL query to be executed. To prevent SQL injection attacks, the field supports parameter substitutions with the SQL Statement Parameters options. The parameters must be prefixed with an @ character.

The field uses variable substitution from the incoming message using a mustache format. A mustache is a set of double curly braces surrounding a variable, i.e. {{ variable }}. For example, {{payload.count}} would substitute in the value of payload.count found in the incoming message. Visit the Template Engine and Formatters page to learn more.

The following sample demonstrates both mustache template substitution and parameter replacement.

SELECT TOP {{payload.count}} *
  FROM testdb.dbo.[People]
    WHERE FIRST_NAME = @firstname
    AND AGE >= @age ORDER BY Id DESC

Two input parameters need to be defined: firstname and age. Also, the incoming message must contain a value at payload.count to limit the maximum number of returned rows.

We strongly recommend limiting selected rows to cap the memory allocation required to process the results.

SQL Statement Parameters

Defines the set of Input and Output parameters required by the SQL Statement. In all cases, the use of parameters is recommended over template substitution to prevent SQL injection attacks. The following configuration settings are available for each parameter row.

Input/Output

Parameter usage direction. Input is an input parameter. Output is an output parameter.

Param

The name of the parameter defined within the SQL Statement. The parameters must be prefixed with an @ character. The parameters in this field must not be prefixed with an @ character.

Type

The SQL Server data type for the parameter. This field will automatically show matching types. Some of the types may include modifiers. The modifiers are:

length – Column length. For example, NVarChar(30).
prec. – Column precision. For example, the following decimal has a precision of 10: Decimal(10,4).
scale – Column scale. For example, the following decimal has a scale of 4: Decimal(10,4).

Source

For input parameters, this selects the context and value when initializing the parameter in the SQL Statement.

Format Output

If set to true, the original database response row is converted to a simplified results of format, which is easier to process but lacks meta data. The change is from:

{
	[column]: {
		value: [value],
		metadata: { ... detailed metadata information on column ... }
	}
}

to:

{
	[column]: [value]
}

Stream Rows

Each row is sent as an individual message out of the node until all matched SELECT statement rows are returned. For queries which do not return any rows, such as a SELECT which does not match any rows or an INSERT, this setting does not come into play. Instead, a summary message listing the affected row count is passed along.

We strongly recommended limiting selected rows to cap the memory allocation required to process the results.

Log Each Row

When in streaming mode, this will audit each message as it is transmitted. If there is the potential for many matches, this should be set to false to prevent the Incident Timeline from being flooded with messages. If set to false, an informational message is added to the Incident Timeline when the query is complete.

Learn More

Authentication Mechanisms Setup

For all authentication mechanisms, the username or application (service principle) must exist as a user within the database with the minimal set of grants.

Username/Password Authentication

A database user with minimal access to the database tables.

NTLM Authentication

A domain user with minimal access to the database tables.

Azure AD v1.0 Authorization Token using Secret

The oauth2 authorization token is obtained using the Tenant ID, Client ID, and Client Secret tuple from the Azure AD (v1.0) endpoint. The request is to grant access to the https://database.windows.net/ resource. The following steps need to be performed to authorize access to the application.

Azure AD Application Configuration for Using a Secret

The following steps can be used to configure an application within Azure AD and the Microsoft Azure with the appropriate permissions to perform queries on a database. These steps assume Microsoft Azure account is associated with Azure AD. If not, read through the Associate or add an Azure subscription to your Azure Active Directory tenant page.

Navigate to the Azure Active Directory admin center. Login using your existing admin account.
Select Azure Active Directory in the left-hand navigation, then select App registrations under Manage.
Select New registration. On the Register an application page, set the values as follows.
- Set Name to something meaningful, such as Nevelex Labs Security Flow SQL Server. Remember this name for later as this is the service principle name needed when creating a user within the SQL Server database.
- By default, Supported account types is set to Accounts in this organizational directory only. Set Supported account types to Accounts in any organizational directory, if multi-tenant support is desired.
- Redirect URI (optional) may be left blank.
- Click the Register button.
If needed, assign an Azure AD admin to the Microsoft Azure SQL server resource.
- Within the Microsoft Azure portal, navigate to the SQL server
  resource.
- Click within the Active Directory admin box and assign a user or group to be the Active Directory admin.
Create the service principle user within the database. The following steps can be used to use the
- Within the Microsoft Azure portal, navigate to the SQL database
  resource.
- Click Query editor (preview) in the main blade.
- Login to access the query editor.
- Create the service principle user using the name of the application created previously.
```
CREATE USER [<application name>] FROM EXTERNAL PROVIDER;
```
- Grant access to the service principle user using the minimal set of resources. For example, the following will allow the new user to read from the books table.
```
GRANT SELECT ON [books] TO [<application name>];
```

Azure AD v1.0 Authorization Token using CBA

The oauth2 authorization token is obtained using the Certificate Based Authentication (CBA) from the Azure AD (v1.0) endpoint. The request is to grant access to the https://database.windows.net/ resource. The following steps need to be performed to authorize access to the application.

Azure AD Application Configuration for Using CBA

The following steps can be used to configure an application within Azure AD and the Microsoft Azure with the appropriate permissions to perform queries on a database. These steps assume Azure AD is already connected to the Microsoft Azure account.

Perform the same setup steps described in Azure AD Application Configuration for Using a Secret section.
Create a self-signed certificate or obtain a certificate utilizing the standard IT procedures of your organization to associate a public certificate with the application. OpenSSL may be used to create a self-signed x.509 certificate in PEM format.
Associate the public certificate file with the Application by performing the following steps.
- Click the Certificates & Secrets button within the application’s Azure AD page.
- Click the Upload certificate button and Add the certificate file.

For additional information, read through the Microsoft Configure Azure Active Directory authentication page.

Nevelex Labs, Main Office

Metro Office Park
2950 Metro Drive, Suite 104
Bloomington, MN 55425
Phone: +1 952-500-8921

Contact Us

EULA