I am trying to establish connectivity between MSSQL Server read replica with Redash but getting connection error and it requires to set the property ApplicationIntent=ReadOnly.
See the snapshot:

I am not sure how to set this property in Redash. Please advice.

Technical details:

  • Redash Version: 8.0.2.b37747
  • How did you install Redash: Redash helm chart(version- 2.0.0) and Azure Kubernetes service

I don’t think this is a setting in the front-end. So you need to manually edit the MSSQL query runner to include it.

1 Like

Thanks for the reply.

Could you please tell me how can I do that?

I’m not super familiar with MSSQL. But here’s the code that controls the connection string that’s used. I presume you can introduce further parameters there.

Thanks again
I made the following changes in both mssql.py and mssql_odbc.py to include the property ApplicationIntent=ReadOnly:

mssql_odbc.py

  1. change_1
  2. change_2
  3. change_3

mssql.py

  1. change_1
  2. change_2
  3. change_3

These changes are not reflected on the UI side which I assumed, It would.
Do, I need to recreate the Kubernetes pod with these changes or rerun the python files?

I don’t use k8s. But normally I would need to restart the containers for these changes to take effect.

FYI: you’ll only need one of those files. Probably mssql_odbc.py. The non-ODBC version is being deprecated since the ODBC version is faster / better / more compatible.

Thank you.
I got a chance to make the change as you suggested.
This is the change I made in mssql_odbc.py file:

connection_string_fmt = "DRIVER={{ODBC Driver 17 for SQL Server}};PORT={};SERVER={};DATABASE={};UID={};PWD={};ApplicationIntent=ReadOnly"

Still it didnt work for me . I am getting the same error:

Could you please tell me what am I missing here?

Also I am using the logger but not able to see them in docker logs. I am not sure if you could tell me where these logs could be.

logger.info("SQLServerODBC connection string: %s", connection_string)
logger.debug("SQLServerODBC running query: %s", query)

Just checking: did you rebuild your instance so that the code change takes effect?

Yes, I restarted the container.

Just FYI, I am using 9.0.0-beta.b42121 redash version to test it and did a docker set set up as well.

Do you know if the connection string is case-sensitive? https://techcommunity.microsoft.com/t5/sql-server-support/connect-to-sql-server-using-application-intent-read-only/ba-p/317758

Looking at this documentation it appears ReadOnly is shown as readonly instead.

Tried this way too, as given in the documentation for ODBC connection :
try 1. connection_string_fmt = "DRIVER={{ODBC Driver 17 for SQL Server}};PORT={};SERVER={};DATABASE={};UID={};PWD={};applicationintent=readonly"

try 2. connection_string_fmt = "DRIVER={{ODBC Driver 17 for SQL Server}};PORT={};SERVER={};DATABASE={};UID={};PWD={};ApplicationIntent=readonly"

But no luck, got the same error as before.