Microsoft SQL

Microsoft SQL Server is a relational database platform used to store, query, and manage structured business data.

The Flowgear Microsoft SQL Node lets you query data, upsert rows, and listen for query-result changes from a Workflow.

Revision History

0.0.0.31 - Added connection testing.
0.0.0.36 - Updated templates.
0.0.0.39 - Improved method metadata.
0.0.0.47 - Added service principal secret and interactive MFA authentication, and SQL parameters for queries.
0.0.0.48 - Updated connection labels.
0.0.0.50 - Added the Listen method.
0.0.0.51 - Optimized template discovery.

Connection

Use the Connection to choose an authentication mode and supply the SQL Server settings required for that mode.

Property	Type	Description
`Authentication Mode`	AuthMode	Selects which authentication mode the `Connection` should use.
`Server`	String	The SQL Server address.
`Database`	String	The SQL database to connect to.
`User ID`	String	The SQL user identifier used when `Authentication Mode` is `UserCredential`.
`Password`	Masked	The SQL password used when `Authentication Mode` is `UserCredential`.
`Use Win Auth`	Boolean	When `true`, Windows Integrated Security is used for `UserCredential` mode.
`Trust Server Certificate`	Boolean	When `true`, the SQL client trusts the server certificate without validation.
`Tenant ID`	String	The Microsoft Entra tenant identifier used for `ServicePrincipalSecret` or `MfaInteractive` authentication.
`Client ID`	String	The Microsoft Entra application ID used for `ServicePrincipalSecret` or `MfaInteractive` authentication.
`Client Secret`	Masked	The Microsoft Entra client secret used for `ServicePrincipalSecret` or `MfaInteractive` authentication.
`Access Token`	Masked	The token acquired for `ServicePrincipalSecret` or `MfaInteractive` authentication.
`Refresh Token`	Masked	The refresh token returned by `MfaInteractive` authentication.
`Scopes`	String	The scopes requested during `MfaInteractive` authentication. The default is `offline_access https://database.windows.net/.default`.
`Connection String Fragments`	String	Additional connection string fragments, one `keyword=value` pair per line.

Setup Notes

Choose the authentication mode that matches your SQL Server and identity setup.

UserCredential

Use this mode for standard SQL authentication, or set Use Win Auth to true to use Windows Integrated Security.

Enter Server and Database.
If Use Win Auth is false, enter User ID and Password.
If your SQL Server uses a self-signed or private certificate, set Trust Server Certificate as needed.

ServicePrincipalSecret

Use this mode when you want Flowgear to acquire and refresh a SQL access token by using a Microsoft Entra application and client secret.

Follow Microsoft Entra Registered Application, and add the required Azure SQL Database delegated permission user_impersonation.

Enter Server, Database, Tenant ID, Client ID, and Client Secret.
Test the Connection after saving it so Flowgear can validate the settings and acquire an access token.

To grant the application access to the database, run SQL similar to the following as an administrator:

USE master;
GO

CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;
GO

USE [DatabaseName];
GO

CREATE USER [app-name] FOR LOGIN [app-name];
GO

ALTER ROLE db_datareader ADD MEMBER [app-name];
ALTER ROLE db_datawriter ADD MEMBER [app-name];
GRANT EXECUTE TO [app-name];
GO

MfaInteractive

Use this mode when you want to sign in interactively with Microsoft Entra and store delegated access and refresh tokens in the Connection.

Follow Microsoft Entra Registered Application, and add the required Azure SQL Database delegated permission user_impersonation.

Enter Server, Database, Tenant ID, Client ID, and Client Secret.
Leave Scopes as the default unless you have a specific reason to change it.
Click Connect your account to complete the Microsoft Entra sign-in flow.
Test the Connection after the sign-in completes.

To grant the signed-in Microsoft Entra user access to the database, run SQL similar to the following as an administrator:

USE [YourDatabaseName];
GO

CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;
GO

ALTER ROLE db_datareader ADD MEMBER [user@domain.com];
ALTER ROLE db_datawriter ADD MEMBER [user@domain.com];
GRANT EXECUTE TO [user@domain.com];
GO

Query Notifications

The Listen method uses polling by default. To use QueryNotification, select UserCredential authentication and enable Service Broker for the database.

ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

QueryNotification requires a reusable SQL connection string, so it is not available with ServicePrincipalSecret or MfaInteractive authentication. The watch query must also meet SQL Server Query Notification requirements and return exactly one result set.

Methods

The Microsoft SQL Node exposes methods for running queries, upserting rows, and listening for query result changes.

Query

Executes a query against a Microsoft SQL Server database and returns the results.

Use SQL parameters for external values, e.g.:

SELECT TOP 10 * FROM [dbo].[contacts] WHERE [id] = @id

The Parameters object can use either @id or id as the key.

Parameter	Type	Description
`Connection`	Connection	The Microsoft SQL `Connection`.
`Query`	String	The SQL statement to evaluate. Use `Parameters` for external values instead of concatenating SQL.
`Parameters`	Object	Optional SQL parameter values keyed by parameter name. Keys may include or omit the leading `@` prefix.

Return	Type	Description
`Items`	Array	The rows returned by the query. Each row is emitted as an object.

Upsert

Upserts data to a Microsoft SQL Server table.

Parameter	Type	Description
`Connection`	Connection	The Microsoft SQL `Connection`.
`Items`	Array	The items to upsert.
`Schema`	String	The SQL schema for the table.
`TableName`	String	The name of the table to upsert.
`KeyFields`	String	The key fields used to identify which rows to update.
`IdentityInsert`	Boolean	When `true`, identity insert is enabled for the target table.
`BatchSize`	Integer	The number of rows to upsert per batch. The default is `100`.

Return	Type	Description
`RowsAffected`	Integer	The number of rows affected by the upsert operation.

Listen

Listens for changes to a SQL query and emits the current result rows when the configured condition is met.

Parameter	Type	Description
`Connection`	Connection	The Microsoft SQL `Connection`.
`WatchQuery`	String	The SQL query to monitor. The query must return exactly one result set.
`Parameters`	Object	Optional SQL parameter values keyed by parameter name. Keys may include or omit the leading `@` prefix.
`PollInterval`	Integer	The interval in seconds between polling cycles when polling is active. The default is `300`.
`FireOn`	Enum	Select `RowsChanged` to emit only when the result set changes, or `RowsReturned` to emit whenever the query returns rows.
`DetectionMode`	Enum	Select `Polling` to check on each polling cycle, or `QueryNotification` to use SQL Server Query Notifications. The default is `Polling`.

Return	Type	Description
`Change`	Array	The emitted change events. Each event includes `DetectedAtUtc`, `RowCount`, `ResultHash`, and the current result set in `Rows`.

Usage Notes

Query returns one output item per row in the result set.
Upsert works best when each input object uses property names that match the destination column names.
Listen re-runs WatchQuery after a detected change and returns the full current result set. Query Notifications act only as a change signal and do not identify the changed row.
RowsChanged emits when the result hash differs from the previous accepted result. RowsReturned emits whenever the current query returns rows.
Connection String Fragments are applied after the standard Connection settings, so use them only for additional SQL client options.

Known Issues

Certificate-based authentication is not currently available.
QueryNotification depends on SQL Server Service Broker and query-shape limitations. Use Polling when query notifications are not available for the target database or query.