Home Nodes Microsoft SQL Table Update

Microsoft SQL Table Update

Provides an efficient way for executing bulk insert/update on a Microsoft SQL database without having to hand-write INSERT or UPDATE statements.

This Node may be affected by Windows Regional Settings.

Revision History

1.0.0.0 Initial Release
3.0.0.6 Added support for assigning null values to simple types, minor updates and bugfixes.
3.0.0.12 Added ability to insert into a table with identity fields. Use IdentityInsert custom property.
3.0.0.13 Added the ability to update tables and columns that contain spaces.
3.0.0.14 Added support for custom connection string fragments.
3.0.0.16 Added support for specifying a schema.
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 update.

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.

TableXml

Type: Xml Input
An XML representation of the data to be updated or inserted (see Remarks).

KeyFields

Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml property. It is not necessary to supply key fields if AllowUpdate is false.

AllowInsert

Type: Boolean Input
Indicates whether inserts are allowed into the target table.

AllowUpdate

Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).

RowsAffected

Type: Int32 Output
Returns the number of rows affected by the insert/update operation.

Custom Properties

This node supports Custom Input Properties which can be added dynamically to your SQL Updates/Inserts.

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 update. 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

The XML supplied should contain the following paths root\tablename\columnname. The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.

Here's an example of a payload that will add or insert two rows into a table called inventory:

<root>
	<inventory>
		<code>123</code>
		<description>first inventory item</description>
	</inventory>
	<inventory>
		<code>456</code>
		<description>second inventory item</description>
	</inventory>
</root>

When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.

For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.

If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.

When a column is an identity column, and specific values needs to be inserted, a custom field 'IdentityInsert' with value 'true' could be added to the node.

For tables and columns with spaces, the xml tag name will have to be escaped to make it valid xml. A space should be escaped as _0x0020_

A full list of characters can be found here: Unicode UCS-2 Code Chart (columbia.edu)

Troubleshooting

The most common error that occurs is "Failed to convert parameter value from a String to a Double" (or other numeric type). This occurs due to the Regional Settings in place on the machine on which the DropPoint is running. For example, if the decimal separator is set to a comma, only numbers containing a comma decimal separator will be parsed correctly.

To resolve these problems, first determine exactly which field or fields are not being converted correctly by progressively reducing your XML payload until it contains only the problem fields. Next, review the regional settings at the DropPoint.

If it is not possible to change Regional Settings on the machine, the numeric fields need to be formatted in a way that conforms to the Regional Settings.

Examples

See Sample Workflow for an example.

See Also

MySQL Table Update
ODBC Table Update
OLEDB Table Update
MariaDB SQL Table Update
Snowflake SQL Table Update

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.