Add a "test connection" button in the Database Connection screen in Azure Portal

[sander1095]

Is your feature request related to a problem? Please describe. Debugging Static Web App's Database Connection feature (also known as Azure Data API Builder) is very very difficult because no information gets exposed about what is going wrong. I'm currently having this problem and it's described in detail at https://github.com/Azure/data-api-builder/issues/1814.

Locally everything works fine, but when deploying to Azure Static Web App it fails.

Describe the solution you'd like Currently the Azure Portal already mentions that the database needs to accept traffic and needs to allow traffic from other Azure Services. Why not add a "Test connection" button which performs a basic request to the Data Api Builder service (from the SWA) so the connection can be verified?

Maybe there could even be a 2nd button/popup that allows you to do a more advanced request that retrieves a specific entity, ensuring the entire chain should work.

If things do not work, more detailed debug information should be shown.

Describe alternatives you've considered Better debug information in the error messages from Azure Data Api Builder, though that probably doesn't belong here. Anyway, the button would be a lot more UX friendly.

Another alternative is not even requiring a button and testing the connection more thoroughly when a database connection is added so it should always work.

Additional context None

[sander1095]

We might also want to add this as a CLI option

[thomasgauvin]

Noting the feedback & we're investigating how we could provide this option

[sander1095]

Hi @thomasgauvin ! I'm thankful for this.

I'd like to say that I think it would be better to incorporate these "test connection" features into the database link process itself. Having it as an extra button means people might not press it and still be left confused about connections that aren't working. Integrating it into the linking process means that they won't be able to even link a non-working database.

I also just found out that linking a database with an incorrect SQL username/password still results in the database being linked, which (embarrassingly) is the root cause of https://github.com/Azure/data-api-builder/issues/1814.

I find this behaviour to be extremely odd. How can a database be linked if the credentials are incorrect?

TL;DR

When linking a database, do the following:

• Verify that the selected database can be reached (Meaning that Azure service connections are enabled AND other networking things aren't in the way)
  • We could potentially provide a "fix" button in the Portal which will take care of all of this.
• Validate the credentials that are given to connect to the database
• If possible, test the connection by retrieving an entity that is configured in the DAB configuration file.
  • I understand this can be tough if all entities need an authenticated request, so this might be a good thing to pick up in a seperate issue
  • Also, I don't know if you can do a call to the SWA -> DAB before the configuration file is deployed, so that might be another challenge?

[tdutch1]

Diagnostics and logging like what's been suggested is essential. I've seen a lot of posts and comments from those using DAB in SWA where the config works locally but not when deployed. If users can't troubleshoot then they'll contact support, who will then need to find a way to examine the config initialization within the SWA. Maybe require App Insights if using DAB and send all logs there?

[Ticed]

When linking the database and inputting the credentials, the password entered is wrapped by single quotes (i.e a password of ABC123 becomes 'ABC123'). Currently, there is no readily available documentation or troubleshooting to address this. It was only by running the command in the accepted solution on https://learn.microsoft.com/en-us/answers/questions/1198719/response-status-code-does-not-indicate-success-400 that I found out about this.

To solve it, ensure that your connection string within the staticwebapp.database.config.json file contains the quote-wrapped password, as any discrepancy will lead to HTTP 400.