Microsoft SQL

Provides integration with Microsoft SQL Server so you can query data and upsert rows 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.

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

Methods

The Microsoft SQL Node exposes methods for running ad hoc queries and upserting rows into a table.

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.

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.
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.
Database change monitoring is not currently available on this Node.