Home Nodes Microsoft SQL Query Paged

Microsoft SQL Query Paged

Executes SQL queries against a Microsoft SQL database and returns results over a series of pages. This Node requires Microsoft SQL Server 2012 and later.

Revision History

3.0.0.6 Initial Release (preview)
3.0.0.7 Official Release
3.0.0.8 Fixed a bug on XmlQuery.
3.0.0.14 Added support for custom connection string fragments.
3.1.0.0 Added support for Azure Entra Service Principal authentication using certificates.
3.1.0.1 Added support for Azure Entra Service Principal Access Token refresh.

Properties

Connection

Type: Connection Input

Server
Type: String Input
The name or IP address of your SQL Server. Specify a DropPoint if your SQL instance is not Internet-exposed. For example, if the DropPoint is installed on the SQL server, this property may be simply (local) or 127.0.0.1.

Database
Type: String Input
The name of the database you want to query on.

AuthenticationMode
Type: List Input
The method to use for authentication:

  • UserCredential - Choose this to connect with either Windows Authentication or a standard SQL Server login.
  • CertificateCredential - Choose this to authenticate using an Azure AD Service Principal with a client certificate.

UseWinAuth
Type: Boolean Input
This property is only active when AuthenticationMode is UserCredential. Set it to True to use Integrated Security, which authenticates using the Windows account that our Flowgear service is running under. This authentication method can only be used when the Connection is routed through a DropPoint. This means that the security context is the service account used for the DropPoint Windows Service. In addition, if set, UserId and Password are ignored and can be left blank.

UserId
Type: String Input
The username for your SQL login. Used only if AuthenticationMode is UserCredential, and not required when UseWinAuth is True.

Password
Type: Password Input
The password for your SQL login. Used only if AuthenticationMode is UserCredential, and not required when UseWinAuth is True.

TenantId
Type: String Input
The (directory) tenant ID for your Azure Active Directory Application. Required if AuthenticationMode is CertificateCredential.

ClientId
Type: String Input
The client ID for your Azure Active Directory Application. Required if AuthenticationMode is CertificateCredential.

Certificate
Type: X509Certificate2 Input
The client certificate (.pfx) used to authenticate against your Service Principal in Azure. This certificate must contain a private key. Required if AuthenticationMode is CertificateCredential.

AccessToken
Type: Secret Output
The access token issued by Azure AD using your Certificate. This token is automatically refreshed before it expires. Leave it blank, as its acquisition and refresh are both handled by us.

ConnectionStringFragments
Type: String Input
Use this property to add any extra parameters to the SQL connection string that are not part of the standard connection properties. Each fragement should be in the format key=value and be on a new line, e.g. Connect Timeout=30.

Query

Type: Multiline Text Input
The SQL query to run. Can include FOR XML statements.

XmlQuery

Type: Boolean Input
When True, indicates that the query provided in the Query property is an XML Query (ie. a query containing the FOR XML statement - refer to this link for more information on XML queries).

PageSize

Type:Int32 Input
This sets the number of records returned at a time by the node. The Page output will fire each time there are results returned, and the Finished output will fire when there are no more results to return.

ResultXml

Type: Xml Output
The XML result of your query.

RowsAffected

Type:Int32 Output
This return the number of rows returned by SQL. Note that if XmlQuery is true, this is not accurate because the XML string gets split into rows in the result, which is consolidated in the node.

Custom Properties

This node supports Custom Input Properties which can be added dynamically to your SQL Queries.

Configuring your Node

To set up the node:

  1. Enter Server and Database. Ensure that they exactly match their display names.
  2. Choose your desired AuthenticationMode, and follow the corresponding steps:

User Credential

  1. On the Connection, set AuthenticationMode to UserCredential.
  2. If you would like to use Windows Integrated Security, set UseWinAuth to true. The connector will now authenticate using the credentials of the Windows user account that is running the Flowgear DropPoint. No other action is necessary; your node is setup.
  3. If you did not wish to use Windows Integrated Security (UseWinAuth set to false), simply enter your SQL Server's login information: UserId and Password.
  4. Save your Connection.

Depending on the set up of your SQL Server and Database(s), you may need to grant certain permissons. If you encounter unauthorised issues, ensure that whichever user you are currently logged in as has the required permissions for the query. We suggest executing a SELECT system_user query using this node to verify which user you are logged in as. If you are using Windows Integrated Security (UseWinAuth set to true), you will likely run into this. For example, the output of that query could be FlowgearUser. Under the desired Database in SSMS (or any SQL query software connected to your database) connected to your locally-running SQL Server, execute the following query:

ALTER ROLE db_datareader ADD MEMBER [FlowgearUser];  
ALTER ROLE db_datawriter ADD MEMBER [FlowgearUser];

Certificate Credential

  1. Follow this guide to set up a Service Principal.

    • Make note of the name of your Service Principal, as well as its ClientId and TenantId.
    • At the Assign a role to the application section, ensure that SQL Security Manager is added as a role with your Service Principal as the member.
    • You should have a .cer and .pfx of your Certificate. Upload the .cer file to your Service Principal, under Certificates & secrets → Certificates. The .pfx file will be used to set up your Connection.

  2. Your Service Principal is now set up. Under your Subscription that you created your Service Principal under, add the SQL Server and corresponding Database(s) inside of it. The exact set up for this is up to your specific use case and requirements. However, the following are some notes to keep in mind:

    • [Optional] If you want to be able to also use the User Credential Authentication Mode via your SQL Server via Azure, in your SQL Server, go to Settings → Microsoft Entra ID. Uncheck "Support only Microsoft Entra authentication" and Save. This will allow you to do normal SQL Client Authorisation using the UserId and Password of a valid user on your SQL Server.
    • The security of your SQL Server is your choice. We advise to create a private endpoint within VNet and keep public access disabled for the security of your data. Ensure that Flowgear and/or your IP is whitelisted.
    • If you do opt for public access through selected networks, you will likely need to ensure that Flowgear's and/or your IP is whitelisted. To do so, navigate to your Azure SQL Server, and go to Security → Networking → Public access. Under Firewall rules, add the corresponding IP addresses as a rule. The IP address to add when working with Flowgear will be visible via the unauthorised error message.

  3. Both your Service Principal and SQL Server & Database(s) should be now be set up. Now, create a SQL user inside your desired Database in your SQL Server that will be directly mapped to your Service Principal. This will grant the Service Principal the necessary permissions to connect and interact with the database. Do this by using a software like SSMS, and executing the following:

CREATE USER [<Your-Service-Principal-Display-Name>] FROM EXTERNAL PROVIDER;  
ALTER ROLE db_datareader ADD MEMBER [<Your-Service-Principal-Display-Name>];  
ALTER ROLE db_datawriter ADD MEMBER [<Your-Service-Principal-Display-Name>];  
ALTER ROLE db_ddladmin ADD MEMBER [<Your-Service-Principal-Display-Name>];
  1. In Flowgear, on your Connection, set AuthenticationMode to CertificateCredential.
  2. Enter your Service Principal's TenantId and ClientId.
  3. Upload the .pfx counterpart file of the .cer uploaded on your Service Principal. Enter its password when prompted.
  4. Save your Connection.

Testing your Connection

After you have configured your Connection, press Test validate your connection.
Unfortunately, testing your Certificate Credential authentication is not supported at this time and will fail. This is being investigated.

If the test is successful (✓), your credentials are valid.
If it fails (✗), the test will return an error message, and you may need to recheck how you set up the Connection. If this problem persists, please contact support for assistance.

Remarks

Use this Connector to execute large SQL queries against a Microsoft SQL Server instance.

This node make use of the OFFSET - FETCH functionality of Microsoft SQL Server 2012 and higher.

For additional information on querying Microsoft SQL databases, refer to Microsoft SQL Query

SQL Parameters via Custom Properties
Flowgear Custom Properties can be added to this Node to make SQL parameters available in the Query Property. Use of a Custom Property is shown in the linked example workflow (see Examples below).

XML Mode
In XML Query mode, the Connector will automatically build a single XML document from the multiple result-set rows that may be returned.

Other Nodes for SQL databases
If you are updating values in a table based on keys, consider using SQL Table Update instead of crafting update statements.
If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.

Examples

See Sample Workflow for an example.
See Sample Workflow for an example on how to implement pagination in versions of Microsoft SQL Server 2012.

See Also

Microsoft SQL Query
MySQL Query
ODBC Query
OLEDB Query
MariaDB SQL Query
Snowflake SQL Query

Known Issues

  • If you are using CertificateCredential for authentication, the Test button on the Connection will fail. An error occurs when the platform tries to process the uploaded certificate file during the test. This is currently being investigated. To verify your Connection details are correct, you must execute the Node in a full Workflow run. A successful run confirms that your Connection is configured correctly.