GraphQL Response -> better handling of changed conn string resulting in db authN error (currently http500)

[tarazou9]

When sending a graphql request with an invalid db connection string, the result returns 500 as suppose to return 401. See below screenshot.

Steps to reproduce:

1. From SWA, link a db, and make sure getting a 200.
2. Go to db, regenerate a PK.
3. Send the request again, getting 500.
4. From SWA, unlink and link again, getting 200.

[seantleonard]

Is this response being translated to a 500 error code by hosting mechanisms? or is this a problem when using engine locally as well?

[Mathos1432]

I'm seeing a similar issue with the engine's auth. Sending a request that should result in a 401/403 ends up with a 500 + a message that says that the user is unauthorized.

[seantleonard]

@tarazou9 , this is a database error returned to the engine. Currently, unless the DB errors are transient, a 500 error is returned because it is not something the end-user can change in their request to make the request go through. This would need to be remediated server side with a correct connection string.

In this case, I would suggest we add configuration validation with respect to connection strings. If a connection can be successfully established, then continue startup. Otherwise, the engine should fail to start because this is equivalent to provide invalid configuration.

@Mathos1432 , your issue looks different. Can you open up a separate issue and include your graphQL schema for troubleshooting?

[Mathos1432]

@seantleonard I created #1152

[tarazou9]

@seantleonard There are two scenarios when the connection string could be changed.

1. No graphql request has been made and no container has been allocated for this user. In this case, what you suggest would work since it will fail on Engine startup with correct 401.
2. Graphql requests has been made and a running healthy container has been allocated for this. If the connection string changes on the fly, then the following requests would still fail when establishing a connection to db with the old connection string since the configuration file didn't get changed, and container didn't get deallocated.
   • For CosmosDB in this case, it will return 500 with inside Unauthorized 401 coming from Cosmos SDK.
   • For MsSql from what I had tested locally, it returns 200 with inside message: "message": "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I think maybe we could wrap the try catch in both Cosmos Engine and Sql Engine to check for the unauthorized exception and then bubble up with a correct 401 to handle both above cases, thoughts?