Microsoft SQL

Connects to Microsoft SQL Server so you can query data and upsert rows from a Workflow.

Revision History

0.0.0.31 - Added connection test.
0.0.0.36 - Adjust templates.
0.0.0.39 - Fix attributes.
0.0.0.47 - Added Service Principal and MFA Interactive Auth flows. Added parameter handling to queries.
0.0.0.48 - Connection label changes.

Connection

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

Parameter	Type	Description
`Authentication Mode`	Dropdown	Options: UserCredential, ServicePrincipalSecret, MFA_Interactive
`Server`	String	The SQL server address.
`Database`	String	The SQL database to connect to.
`User ID`	String	The User identifier used to connect to the SQL server.
`Password`	Masked	The Password for the User Id.
`Use Win Auth`	Boolean	When true, uses Windows IntegratedSecurity.
`Trust Server Certificate`	Boolean	Whether to trust the server certificate without validating it.
`Tenant ID`	String	The Azure/Entra tenant ID for service principal authentication or certificate authentication.
`Client ID`	String	The Azure/Entra client/application ID for service principal authentication or certificate authentication.
`Client Secret`	Masked	The Azure/Entra client secret for service principal authentication.
`Access Token`	Masked	The Access token issued by Azure/Entra using your certificate or Service Principle credentials.
`Refresh Token`	Masked	The delegated refresh token returned by Entra interactive authentication.
`Scopes`	String	Scopes requested during interactive Entra authentication. Default: offline_access https://database.windows.net/.default
`Connection String Fragments`	Multiline String	Additional connection string fragments to be added to the Connection. Each fragment should be on a separate line in 'keyword=value' format.

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 obtain and refresh a SQL access token by using an Entra application and client secret.

Follow Microsoft Entra Registered Application, and make sure you add the required API permission:
Select the Tab APIs my organization uses, search for Azure SQL Database and select Delegated permissions. Then select user_impersonation and Add permissions.

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

Microsoft notes that Entra authorization for SQL Server on Azure VMs is supported from SQL Server 2022, and the VM must be registered with the SQL IaaS Agent extension.

MFA_Interactive

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

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

To grant the signed-in 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. For example:

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 database 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 primary key fields used to identify which rows to update.
`IdentityInsert`	Boolean	When `true`, allows identity insert for the target table. The default value is `false`.
`BatchSize`	Integer	The number of rows to upsert per batch. The default value 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 authentication not implemented yet.
Listen method not implemented yet.