How to fix {"message": "Internal Server Error" } when building flask rest api on top of synapse pool in azure apps

Question

How to fix {"message": "Internal Server Error" } when building flask rest api on top of synapse pool in azure apps

John Bronzo 21

Hello, I am following this blog post: https://towardsdatascience.com/how-to-create-apis-on-top-of-synapse-serverless-sql-pools-f09e087400ab I have created the app locally and everything runs fine. I added one more stored procedure on the azure sql pool. After deploying the working app to the azure app service, I am receiving mentioned error on all 3 endpoints {"message": "Internal Server Error" }. After viewing log streams for the app it seems to be something with the connection string.

file "/tmp/8dc271de8c7010e/app.py", line 62, in __getConnection
2024-02-06T14:24:44.006171191Z     connstr = os.environ['SQLAZURECONNSTR_TAXI'] + ';Authentication=ActiveDirectoryMsi;

However, I have entered the proper connection string on azure app and gave app right on synapse sql pool to execute stored procedures. User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Smaran Thoomu 24,110 Microsoft External Staff Moderator

Hi @John Bronzo ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

Based on the error message you provided, it seems that there is an issue with the connection string. Please ensure that the connection string is correct, and that the app has the necessary permissions to access the Synapse SQL pool.

Here are a few things you can check:

Verify that the connection string is correct and that it includes the correct credentials to access the Synapse SQL pool. You can check the connection string in the Azure portal by going to your App Service and navigating to Configuration > Application settings.
Ensure that the app has the necessary permissions to access the Synapse SQL pool. You can check this by going to the Synapse workspace and navigating to Access control (IAM) > Role assignments. Make sure that the app has the necessary role assignments to access the SQL pool.
Check the logs for any additional error messages that might provide more information about the issue. You can view the logs by going to your App Service and navigating to Log stream.
Ensure that the stored procedure you added to the Synapse SQL pool is working correctly. You can test this by executing the stored procedure directly in the Synapse workspace.

If you are still having issues, you can try updating the connection string in your code to include the username and password instead of using Active Directory authentication. You can also try using a different driver or library to connect to the Synapse SQL pool.

I hope this helps! Let me know if you have any further questions.

John Bronzo 21 Reputation points

2024-02-08T03:21:13.64+00:00

Thank you for the repIy. I was able to get it working by changing the method I used to connect the synapse pool. Followed this guide and used a FastAPI framework instead of flask. I will also try to implement this connection method on the flask app to see if it works there. https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-sql-python-quickstart?view=azuresql&tabs=windows%2Csql-inter For creating the connection string in the application, this microsoft documentation creates an entry in the application settings instead of connection strings. Any idea if there is a difference?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-02-08T04:36:20.29+00:00

John Bronzo Glad to hear that you were able to get it working by changing the method to connect to the Synapse pool!

Regarding your question about creating an entry in the application settings instead of connection strings, both methods achieve the same result of providing configuration settings to your application. The main difference is that connection strings are a specific type of configuration setting that are used to store database connection information, while application settings can be used to store any type of configuration information.

In general, it's a good practice to use connection strings to store database connection information, as it makes it easier to manage and update the connection information separately from other configuration settings. However, if you only have a few configuration settings to manage, using application settings can be a simpler approach.

I hope this helps! Let me know if you have any further questions.
John Bronzo 21 Reputation points

2024-02-08T13:11:27.4933333+00:00

Makes sense. One last question. For connection string, what does changing the type do? In my first attempt I had the connection string entered here and the type as "SQLAzure" since I am connecting to a synapse pool. Could this have been my issue before?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-02-09T10:49:53.8033333+00:00

Hi John Bronzo

The type parameter in the connection string specifies the type of database you are connecting to. For example, if you are connecting to a SQL Server database, you would set the type parameter to SQLServer. If you are connecting to an Azure SQL Database, you would set the type parameter to SQLAzure.

In the case of Synapse SQL pool, you should set the type parameter to SQLDW or Synapse. So, if you were connecting to a Synapse SQL pool, you should set the type parameter to SQLDW or Synapse, depending on the version of Synapse you are using.

Setting the type parameter incorrectly could cause issues with connecting to the database, so it's important to make sure that it is set correctly. However, it's unlikely that setting the type parameter to SQLAzure would cause the issue you were experiencing, as this parameter is used to specify the type of database, not the authentication method or other connection details.

If this answers your query, please do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

How to fix {"message": "Internal Server Error" } when building flask rest api on top of synapse pool in azure apps

0 additional answers

Your answer