How to connect to a database with "ReadOnly" option

[Mr_Pearl]

I have ReadOnly access to MsSql database. I am not able to connect using Qlik MsSql connector because by default Qlik has "ReadWrite" as connect options.

Is it possible to change "ReadWrite" to "Read" option in data load editor or in QMC --> data connection?

I know I can ditch the Qlik MsSql connector and use ODBC connector and in Windows configure it by including "Application Intent" as "READONLY". But this would eventually result in our organisation using only windows ODBC drivers instead of any of the Qlik connectors.

I think "QvxConnectOptions" type is responsible for this Qlik default behavior. Is it advisable to change that file? do you know where I can find this file related to mssql connector?

If this is not possible, please let me know your opinion.

[Vegar]

What's the error message you get when trying g to connect?

Which database version are you trying to connect to? Is it a database version supported by the connector? Only MSSQL 2012 - 2017 are supported. If you are using SQL server 2019 you can not use the connector.

[Mr_Pearl]

@VegarI get error ERROR [08S01] TDSConnectionFailed. I believe the reason for you to ask about the error while my enquiry is about how to apply a fix for an error is because you think I got the diagnosis wrong and thereby fix is also wrong. Here is little story on how I narrowed down to the cause and reason of the error.

When using Qlik MsSql connector it connects to the source database without issue, it even fetches data without issue but after few seconds the source database disconnects the connection. When digged into it I found that administrators running the MsSql database has setup a schedule task to disconnect any data connection which are connected as "ReadWrite". I wanted to test this out, so I setup a ODBC DSN in my server and configured it to "ReadOnly" under "Application Intent". Then I used Qlik ODBC connector to connect through my windows (system) DSN. This fetched all the data I need. Then I reconfigured System ODBC DSN to "ReadWrite", this resulted in Qlik fetching data for a while and then connection is broken. This proves that information obtained from MsSQL administrator is correct. So then I tried to understand how and where Qlik is establishing data connection through MsSql connector and from log files it is apparent that QvX request are going out as "ReadOnly" as false, which means read/write is true. I also learned it from several other Qlik source that Qlik ODBC Data connections are connecting as "Read/Write" by default. This makes sense in normal scenario but it is hard for me to believe that you cannot change this option to "ReadOnly" as this will be case in many enterprise level Qlik systems where the data sources are highly protected and often you get only read permission.

[Vegar]

It seems like you have circled in the cause of your issue very well. 

If you don't get helpfull response from the community I think you could consider creating a case to the Qlik Support.  They should be able to help you to configure the connector correctly or tell you if this is a bug or an intended limitation to the connector.

[stevedark]

Hi @Mr_Pearl 

Did you get a a response to this, or work out a way around it?

I'm getting the same error message at one of my clients.

Steve

[Mr_Pearl]

@stevedarkUnfortunately Qlik doesnt seem to have configure change option. So I have to install Microsoft ODBC Drivers and configure it there.

[stevedark]

Hi @Mr_Pearl 

Thanks for getting back to me on this. In the end I found that the OLE DB driver worked, so didn't need to configure ODBC on the server.

It was a bit of a strange one, but there was a firewall upgrade between it being in a working state and a non-working state, so that was obviously a factor. Why it should cause that issue I don't know.

At least it is working now. 🙂

Many thanks,

Steve