Power Automate + BBCRM Read-Only Database (RODBA) – has anyone successfully connected?

Simon Watson

Hi all,

I’m reaching out to see if anyone has successfully connected Power Automate to the Blackbaud CRM read-only database (RODBA) using the SQL Server connector.

For context, our BBCRM is fully hosted by Blackbaud (Azure SaaS), and we have the optional read-only database access enabled. We can successfully connect to RODBA via Power BI using the on-premises data gateway, and also connect directly using SSMS.

We’re now trying to establish a connection from Power Automate using the SQL connector. While we can select the same on-premises data gateway used for Power BI, we haven’t been able to get the connection to succeed.

What we’re seeing

• The connection test fails when creating the SQL connection in Power Automate
• Error message indicates login failure due to trigger execution:

Microsoft SQL: Logon failed for login due to trigger execution. Changed database context to ‘[db]’. Changed language setting to us_english.

Our current thinking

We suspect this may be related to a combination of IP whitelisting and/or encryption or validation requirements on the server side. A few observations:

• Unlike Power BI, there is no option to skip the test connection in Power Automate
• There doesn’t appear to be a way to explicitly enforce encryption in the connector
• The same gateway and credentials work successfully in Power BI

Question
Has anyone:

• Successfully connected Power Automate to BBCRM RODBA?
• Encountered similar login/trigger issues?
• Found any workarounds or required configurations (e.g. gateway settings, connector behaviour, or BB-side changes)?

Any guidance or experience would be really appreciated. Happy to share more detail if helpful.

Thanks in advance,
Simon

Crystal Bruce

Hi @Simon Watson I didn't see many responses to this in the Blackbaud CRM community, so I moved it over to the SKY Developer community for more responses. Thanks!

Steve Walsh

Hi Simon

We are having the same trouble when trying to use the SQL connector from Power Automate through to the RODB. Sadly I haven't made any further progress but will happily share anything I find.

Steve

Crystal Bruce

Hi @Simon Watson - my team has asked that you open a Blackbaud support ticket to investigate this further. They can get more details from you and hopefully provide a solution. If you wouldn't mind returning to community to share the resolution - that would be great!

Simon Watson

Thanks @Crystal Bruce. Yes, a support case has been opened for this request. Case 020844923. 🤞

Simon Watson

Hi @Crystal Bruce and @Steve Walsh.

Just an update on this. We raised both a Blackbaud Support ticket and a Microsoft Support ticket, but unfortunately haven't been able to get this working.

It appears to be a combination of limitations in the Power Automate SQL connection (specifically the inability to configure advanced connection settings or bypass the test connection) together with Blackbaud's security requirements. Microsoft confirmed that the connection is encrypted as expected, but the two platforms still aren't able to establish a working connection.

So, unfortunately, this doesn't appear to be a viable option at this stage, which is disappointing.

Rebecca Sundquist

Hello, @Simon Watson . A "trigger execution" error points to encryption as the reason for Blackbaud to deny access. I see your comment that Microsoft insists the connection is encrypted. But I wonder if encryption is lost somewhere along the way through the gateway. I have tested in Fabric's copy data action within a pipeline and found some parallel behavior, yet I was able to eventually tick a box for encryption and successfully connect.

Depending on what you need your flow to accomplish, I wonder if a Fabric pipeline would work as well. Fabric requires more expensive licensing than Power Automate, and you might not have access to create a Fabric pipeline. The thing to note about the two tools is that Fabric can handle ETL for bulk data management, while Power Automate has some limits on the quantity of data it can manage. PA is built for workflow automations, not extract/transform/load (ETL) data manipulation.

Here is how my testing in Fabric went:

1. I created a new SQL connection in Fabric from my "standard gateway."
   1. Settings > Manage connections and gateways > + New connection
   2. There's no option for "Encrypt Connection," but even without skipping "test connection", my creation was successful
2. In my Pipeline, I added a Copy data action where the Source is using my new connection.
   1. Initially, the connection is not successful.
   2. I click Edit and I find that "Use encrypted connection" is checked!
   3. I enter my Read-only username and password and click "Update"
   4. Test connection succeeds!

I tried to paste pictures here, but they aren't saving. I hope this is helpful.