Skip to main content

Open ideas have been reviewed by our Customer Success team and are open for commenting and voting.

4444 Ideas

cyclomedia
Contributor
cyclomediaContributor

Support for Azure Managed Identity (System & User Assigned) in JDBC Connections for FME FlowNew

Currently, FME Flow supports database connections via JDBC, but authentication typically relies on static credentials such as username and password. In modern cloud environments like Azure, using Managed Identity is a best practice for secure, secretless authentication.This idea proposes adding support for Azure Managed Identity including System Assigned and User Assigned identities for JDBC connections to databases such as Azure SQL Database, Azure Synapse, and other services that support token-based authentication.Why is this important?Eliminate hardcoded credentials: Reduces security risks and simplifies credential management. Cloud-native security: Aligns with Azure best practices and enterprise compliance requirements. Automatic token refresh: Improves reliability and reduces operational overhead. Flexibility: Support for both System Assigned and User Assigned identities allows organizations to choose the right approach for their architecture.How it could work:FME Flow detects it is running in Azure and uses the assigned Managed Identity. When establishing a JDBC connection, FME Flow retrieves an OAuth 2.0 access token from the Azure Instance Metadata Service (IMDS). The token is injected into the JDBC connection string or passed as a driver property (depending on the database/JDBC driver). Configuration options in FME Flow to select: Authentication mode: Managed Identity Identity type: System Assigned or specify a User Assigned identity (via client ID) Use Case Example: An organization runs FME Flow on an Azure VM with a User Assigned Managed Identity that has access to multiple Azure SQL Databases. Instead of storing credentials in FME Flow, the system automatically retrieves tokens for secure, passwordless connections. Security NecessityStoring static credentials in FME Flow or any application introduces significant risks:Credentials can be leaked through logs, backups, or misconfigurations. Manual rotation of passwords is error-prone and often neglected. Compliance frameworks (ISO, SOC, GDPR) increasingly require secretless authentication methods.By leveraging Azure Managed Identity, FME Flow can achieve zero-secret architecture, reducing attack surfaces and aligning with modern security standards.

vlroyrenn
Enthusiast
vlroyrennEnthusiast

FeatureWriter: Allow insert ignoring conflicts/"Optimistic upsert" in databasesOpen

I work with time series data a lot, and to keep some slack with regards to “race conditions” across my own flows and other upstream jobs, I tend to use rather generous time windows and “full rerun at the end of the day” configurations to ensure I don’t leave any time gaps in the data destination. That means a lot of the features being inserted at the end will have already been inserted by a previous run and will raise a conflict error if there’s some sort of primary key constraint (or create duplicate entries if these constraints are not enforced).This sort of scenario is what UPSERT is for, but UPSERT causes existing rows to be updated by design. The problem is that several databases, such as Postgres and Snowflake don’t handle large amounts of rows being updated frequently very well, since the “updated row” is actually just a duplicate of the old row with its fields updated, while the “replaced row” is just hidden and actually deleted later by a separate process.Given I know any rows already present in the DB would be identical to the ones I’m inserting, I would much rather skip inserting any conflicting rows instead of causing unneeded churn by “updating” them with the same data. Standard SQL handles this with MERGE INTO … WHEN NOT MATCHED THEN INSERT, but there are also non-standard ways like Postgres’ INSERT INTO … ON CONFLICT DO NOTHING. The only workaround I have at this time is inserting all my features into a temporary table and using an SQLExecutor to write that data to its final destination (something not helped by not being able to use actual temporary tables in FME). Being able to set the FeatureWriter to write new features and ignore conflicts from the get go would be a lot more conveinient for me.Related question/community post: