[Bug/Incompatibility/Issue] UiPath.Database.Activities v1.9.0

[Jugger]

Hello,

I’ve discovered a couple of issues caused by the version 1.9.0 of DB activities in Windows-Legacy projects, and I’d like to bring it to your attention.

Issue #1:

Upon upgrading from v1.7.1 to v1.9.0, the activity “Connect to database” throws the following exception (on execution) when using the Microsoft.Data.SqlClient provider:

Connect to database: The type initializer for ‘Microsoft.Data.SqlClient.SqlConnection’ threw an exception.

Example of configuration:

Connection string

Data Source=localhost;Initial Catalog=dbDEV;Integrated Security=True;Persist Security Info=False;Encrypt=True;Trust Server Certificate=True;Connect Retry Count=3;Connect Retry Interval=15

Provider name

Microsoft.Data.SqlClient

A couple of workarounds, that I’ve tried, could be to:

1. use the System.Data.SqlClient as provider, but the connection string needs to be modified because this won’t support certain parameters from it, such as: “Trust Server Certificate” (which forces you to also to renounce Encrypt parameter), “Connect Retry Count”, “Connect Retry Interval” etc.
2. use OLEBD (System.Data.OleDb), but again, this alters the connection string, and it won’t allow sanitization because named parameters aren’t supported (e.g., the use of Parameters collection in Run query/command activities). Example:

Run query: Must declare the scalar variable “@ID”.

3. use ODBC which, again, doesn’t support named parameters and it requires the setup of a DSN via OBDC Data Source Administrator - in my case, this won’t be possible on a production machine.

The target database is a SQL Server one, I haven’t tried with others.

As per checking the change log of the said package, I haven’t seen anything about this in v1.9.0 notes. Thus, I assume is not intended.

Issue #2:

The v1.9.0 comes with Microsoft.Identity.Client v4.62.0 as a dependency. Apparently, this causes an issue in the project, when paired with Microsoft 365 UiPath package - specifically when using the “Microsoft 365 Scope” activity.

What happens is that the Microsoft.Identity.Client dependency seems to force the usage of “http://localhost” as redirect URI to handle user consent, over the embedded handling via “urn:ietf:wg:oauth:2.0:oob” redirect URI.

As per MSAL’s Github issues reports, the issue occurs on versions above v4.59.1.

How to replicate exactly:

1. Create a Windows-Legacy project and install UiPath.Database.Activities v1.9.0 & Microsoft 365 (version is irrelevant, but go with the latest).
2. Drag & drop the “Microsoft 365 Scope” activity and configure the Authentication to InteractiveToken.

3. Inside the scope drag something to do with a O365 service (e.g., use “Find Files And Folders” to look for a file in SharePoint), and wrap it in a Try/Catch to catch the inner exception (it’s what interests us).
4. Run the workflow and the following exception will occur:

Only loopback redirect uri is supported, but urn:ietf:wg:oauth:2.0:oob was found. Configure http://localhost or http://localhost:port both during app registration and when you create the PublicClientApplication object. See https://aka.ms/msal-net-os-browser for details.

Adding “http://localhost” (like we do on Windows projects) to the Entra ID app, won’t do anything. The Microsoft 365 Scope activity itself would have to use this redirect URI instead when making the call.

Of course, what works is to downgrade the UiPath.Database.Activities to a version below 1.9.0, but the idea is that any package which contains Microsoft.Identity.Client above v4.59.1 would cause the issue.

[Jon_Smith]

The Windows - Legacy stuff has been on the deprecation timeline for a long time now and they are removing support for activity packages for it. You might want to get it upgraded.

[Jugger]

I understand that.

However, Microsoft 365 package uses “http://localhost” as redirect URI in Windows projects, when Interactive Token authentication method is selected. Now the problem with this redirect is that it’s typically used in development only and not in production apps, where a FQDN is recommended. The organization I work for (and not only them) have localhost blocked in Entra ID apps for the whole production tenant, as part of their security policy.

We can still work with the other redirect (specific to legacy), hence why we’ve got a few processes that use O365 services via Interactive Token and are still on Windows-Legacy for the time being.

Would be nice if UiPath could provide an implementation of the Microsoft 365 package facilitates the usage of a custom redirect URI.