Redash and Oracle via Wallet

[hoebling]

Hi,

I need to configure redash to connect to oracle DB via wallet in the Oracle Cloud. When executing sqlplus in the container running reddish server I am able to connect but not when configuring the data source in redash.

Did anyone have success to configure oracle connections via Wallets?

I created container like this:
`

FROM redash/redash:latest

USER root

Oracle instantclient

ADD oracle/instantclient-basic-linux.x64-19.26.0.0.0dbru.zip /tmp/instantclient-basic-linux.x64-19.26.0.0.0dbru.zip
ADD oracle/instantclient-sdk-linux.x64-19.26.0.0.0dbru.zip /tmp/instantclient-sdk-linux.x64-19.26.0.0.0dbru.zip
ADD oracle/instantclient-sqlplus-linux.x64-19.26.0.0.0dbru.zip /tmp/instantclient-sqlplus-linux.x64-19.26.0.0.0dbru.zip
ADD oracle/Wallet.zip /tmp/Wallet.zip

RUN apt-get update -y
RUN apt-get install -y unzip

RUN unzip /tmp/instantclient-basic-linux.x64-19.26.0.0.0dbru.zip -d /usr/local/
RUN unzip -o /tmp/instantclient-sdk-linux.x64-19.26.0.0.0dbru.zip -d /usr/local/
RUN unzip -o /tmp/instantclient-sqlplus-linux.x64-19.26.0.0.0dbru.zip -d /usr/local/
RUN ln -s /usr/local/instantclient_19_26 /usr/local/instantclient

RUN mkdir -p /usr/local/instantclient/network/admin
RUN unzip -o /tmp/Wallet.zip -d /usr/local/instantclient/network/admin
ADD oracle/sqlnet.ora /usr/local/instantclient/network/admin/sqlnet.ora
#RUN ln -s /usr/local/instantclient/libclntsh.so.19.1 /usr/local/instantclient/libclntsh.so
RUN ln -s /usr/local/instantclient/sqlplus /usr/bin/sqlplus

RUN apt-get install libaio-dev -y
RUN apt-get clean -y

ENV ORACLE_HOME=/usr/local/instantclient
ENV LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/instantclient
ENV TNS_ADMIN=/usr/local/instantclient/network/admin

RUN pip install cx_Oracle==7.0.0

USER redash
#Add REDASH ENV to add Oracle Query Runner
ENV REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle

`

Thanks
Michael

[snickerjp]

@hoebling

Did anyone have success configuring Oracle connections via Wallets?

If you are using Docker, you can use the following tags to get a version that includes the necessary changes:

FROM redash/redash:preview

or

FROM redash/redash:25.1.0

For more details, check the available tags here:
🔗 Redash Docker Hub Tags

With these versions, Oracle Client and Wallet are not required when setting up a new Redash server.

This is because the following pull requests have already been merged:

• Do not makedsn for query_runner/oracle.py #6332
  • Enabled support for Oracle Cloud by allowing DSN Service Name to accept Oracle Cloud connection strings
• Replace cx_Oracle with python-oracledb #6386
  • Replaced Oracle Instant Client with python-oracledb, making Oracle Instant Client unnecessary
• Add redash.query_runner.oracle to default_query_runners #6389
  • Added it to default_query_runners, eliminating the need to specify it in REDASH_ADDITIONAL_QUERY_RUNNERS

[snickerjp]

@hoebling

It’s great to hear that you can configure the host via DNS! Regarding the timeout issue, could you provide more details about your Oracle Database environment?

• Which Oracle Database environment are you using?
  • Oracle Cloud or something else?
  • If Oracle Cloud, is it Autonomous Database or Base Database?
• How are you connecting?
  • Over the public internet or a private connection?

These details will help narrow down the cause of the timeout issue. 🚀

[hoebling]

Hi,

it is oracle cloud Autonomous DB using private connection.
Maybe there is a way to increase logging to see what is done while waiting.
Thanks
Michael

[snickerjp]

@hoebling

OK, got it.

You're using an Autonomous Database, so I'll explain the setup for Autonomous Transaction Processing (ATP).

How to Connect to an Autonomous Database

If Mutual TLS (mTLS) Authentication is Not Required

• No Wallet is needed.
• No need to change the Redash Docker image.

1. Log in to the Autonomous Database console.
2. Select your Autonomous Database instance.
3. Click the "DB Connection" tab.
   • Select TLS authentication: "TLS", then copy the connection string.
4. In Redash, go to Settings → Data Source.
5. Click "+ New Data Source" → Select "Oracle".
6. Enter a name (e.g., ATP).
7. Enter the User and Password for the database.
8. Set "Host" to _useservicename (this is a reserved keyword).
9. Set "Port" to 1521.
10. Paste the Connection String into "DSN Service Name".
    • Example:

      (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=[your private endpoint IP address]))(connect_data=(service_name=******_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
      

    • Replace [your private endpoint IP address] with your Autonomous Database Private Endpoint IP.
    • Ensure ssl_server_dn_match=no.
11. Click "Create".
12. Click "Test Connection" to verify the setup.
    • If successful, click OK.

---

If Mutual TLS (mTLS) Authentication is Required

• A Wallet is needed.
• You need to mount the Wallet in the Docker container.

1. Go to the "DB Connection" tab.
   • Download the Wallet.
   • Select TLS authentication: "mTLS", then copy the Connection String.
2. Click "Download Wallet".
   • Extract the downloaded ZIP file.
   • Inside, you will find tnsnames.ora and sqlnet.ora.
   • tnsnames.ora contains connection details.
   • sqlnet.ora contains SSL settings.
3. Edit tnsnames.ora:
   • Replace the host value with your Autonomous Database Private Endpoint IP.
   • Set ssl_server_dn_match=no.
4. Verify sqlnet.ora:
   • Ensure ssl_server_dn_match=no.
   • Update WALLET_LOCATION to the Wallet path (e.g., /opt/oracle/instantclient/wallet).
5. Mount the Wallet in Redash:

   • If using Docker, mount the Wallet with:

     docker run -d -v /path/to/wallet:/opt/oracle/instantclient/wallet redash/redash

   • If using Docker Compose, add the following settings to docker-compose.yml under the relevant service:

     services:
       server:
         image: redash/redash:25.1.0
         environment:
           TNS_ADMIN: /opt/oracle/instantclient/wallet
         volumes:
           - ./wallet:/opt/oracle/instantclient/wallet

   • Start the container with:

     docker-compose up -d

6. In Redash, go to Settings → Data Source.
7. Click "+ New Data Source" → Select "Oracle".
8. Enter a name (e.g., ATP).
9. Enter the User and Password.
10. Set "Host" to _useservicename (this is a reserved keyword).
11. Set "Port" to 1522.
12. Paste the service name from tnsnames.ora into "DSN Service Name".
    • Example (tnsnames.ora entry):

      redash_high = (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=[your private endpoint IP]))(connect_data=(service_name=******_redash_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
      

    • In this case, enter redash_high.
13. Click "Create".
14. Click "Test Connection" to verify the setup.
    • If successful, click OK.

---

For Autonomous Data Warehouse (ADW)

• The setup is mostly the same as ATP.
• Just ensure you use the correct connection string from ADW.

---

This should help you get Redash connected! 🚀 Let me know if you run into any issues.

[hoebling]

Hi,
thank you for the detailed explanation. Except for the ip address to be configured in tnsnames.ora I have had the setup already before. Error is still {"message": "Task exceeded maximum timeout value (30 seconds)}.
I created small python script inside server container with cx_Oracle and python-oracledb and python-oracledb only works with oracledb.init_oracle_client(lib_dir="/usr/local/instantclient/") set.

Any further suggestions?
Thanks
Michael

[snickerjp]

@hoebling

Network Connectivity Check Inside the Container

1. Using Telnet (if installed)

   telnet [your private endpoint ip address] 1521

   • If you see Connected to ..., the connection is successful.
   • If you get Connection refused or Connection timed out, there might be a network or NSG issue.

2. Using netcat (nc) (if nc is not available)

   • Install nc if missing:

     apt update && apt install -y netcat

   • Then, check the connection:

     nc -zv [your private endpoint ip address] 1521

   • If succeeded appears, the connection is successful.
   • If Connection refused or Connection timed out, check network or NSG settings.

🚨 Please confirm the result of either nc or telnet. This is essential to diagnose the issue. 🚨

---

Confirming Whether TLS or mTLS is Used

You can check this in the OCI console:

1. Log in to OCI
2. Open your Autonomous Database page
3. Go to the "DB Connection" tab
4. Check the TLS authentication setting
   • If TLS → No Wallet is required (Redash settings are sufficient)
   • If mTLS → Wallet is required (must be mounted inside the container)

🚨 Please confirm whether you are using TLS or mTLS. This is necessary to proceed with troubleshooting. 🚨

💡 Note:

• mTLS provides stronger security but can be more complex to configure.
• For private endpoints, security is often maintained effectively with TLS alone, which simplifies the setup.

---

For Private Endpoints, DNS Cannot Be Used

• Typically, Autonomous Database connection strings include a hostname like adb.oraclecloud.com.
• With a Private Endpoint, DNS resolution is not available, so you must specify the IP address instead of the hostname.
  • Modify tnsnames.ora manually and replace the hostname with the Private Endpoint's IP address
  • When setting DSN Service Name, use an IP address instead of a hostname

---

Example of Required Configuration Changes

Incorrect (using a hostname):

(description= (address=(protocol=tcps)(port=1521)(host=mydb.adb.oraclecloud.com))...)

Correct (using an IP address):

(description= (address=(protocol=tcps)(port=1521)(host=10.0.0.15))...)

---

To proceed with troubleshooting, please confirm:
✅ 1. Whether you can connect using nc or telnet from inside the container. (Mandatory)
✅ 2. If your setup requires TLS or mTLS. (Mandatory)
🔍 3. If your tnsnames.ora and DSN Service Name are using an IP address instead of a hostname for the Private Endpoint. (If applicable)

These checks will help narrow down the issue. Looking forward to your response!

[rizwan-ishtiaq]

Using redash:preview or redash:25.1.0
I am getting following error
[2025-03-13 17:11:00,881][PID:10][INFO][xmlschema] Resource 'XMLSchema.xsd' is already loaded [2025-03-13 17:11:13,190][PID:7][INFO][metrics] method=GET path=/ endpoint=redash_index status=302 content_type=text/html; charset=utf-8 content_length=329 duration=0.72 query_count=2 query_duration=9.47 [2025-03-13 17:11:13,625][PID:10][INFO][metrics] method=GET path=/login endpoint=redash_login status=302 content_type=text/html; charset=utf-8 content_length=199 duration=3.17 query_count=2 query_duration=8.38 [2025-03-13 17:11:13,761][PID:10][INFO][metrics] method=GET path=/setup endpoint=redash_setup status=200 content_type=text/html; charset=utf-8 content_length=2829 duration=15.59 query_count=2 query_duration=2.71 [2025-03-13 17:11:13,906][PID:10][INFO][metrics] method=GET path=/static/server.465d8e47c6bfef60fae6.css endpoint=static status=200 content_type=text/css; charset=utf-8 content_length=286209 duration=0.59 query_count=2 query_duration=2.38 [2025-03-13 17:11:14,056][PID:10][INFO][metrics] method=GET path=/static/images/redash_icon_small.png endpoint=static status=200 content_type=image/png content_length=6111 duration=0.62 query_count=2 query_duration=2.68 [2025-03-13 17:11:14,620][PID:8][INFO][metrics] method=GET path=/static/images/favicon-32x32.png endpoint=static status=200 content_type=image/png content_length=2005 duration=0.68 query_count=2 query_duration=7.31 [2025-03-13 17:12:08,810][PID:10][INFO][metrics] method=POST path=/setup endpoint=redash_setup status=302 content_type=text/html; charset=utf-8 content_length=189 duration=349.99 query_count=7 query_duration=16.06 [2025-03-13 17:12:08,965][PID:10][ERROR][redash.app] Exception on / [GET] Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2190, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1486, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.10/site-packages/flask_restful/__init__.py", line 298, in error_router return original_handler(e) File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1481, in full_dispatch_request request_started.send(self, _async_wrapper=self.ensure_sync) File "/usr/local/lib/python3.10/site-packages/blinker/base.py", line 300, in send result = receiver(sender, **kwargs) # type: ignore[call-arg] File "/app/redash/models/users.py", line 51, in update_user_active_at if current_user.is_authenticated and not current_user.is_api_user(): File "/usr/local/lib/python3.10/site-packages/werkzeug/local.py", line 311, in __get__ obj = instance._get_current_object() File "/usr/local/lib/python3.10/site-packages/werkzeug/local.py", line 515, in _get_current_object return get_name(local()) File "/usr/local/lib/python3.10/site-packages/flask_login/utils.py", line 25, in <lambda> current_user = LocalProxy(lambda: _get_user()) File "/usr/local/lib/python3.10/site-packages/flask_login/utils.py", line 359, in _get_user user = self._user_callback(user_id) File "/app/redash/authentication/__init__.py", line 50, in load_user org = current_org._get_current_object() File "/usr/local/lib/python3.10/site-packages/werkzeug/local.py", line 515, in _get_current_object return get_name(local()) File "/app/redash/authentication/org_resolving.py", line 18, in _get_current_org g.org = Organization.get_by_slug(slug) File "/app/redash/models/organizations.py", line 32, in get_by_slug return cls.query.filter(cls.slug == slug).first() File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3429, in first ret = list(self[0:1]) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3203, in __getitem__ return list(res) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 99, in instances with util.safe_reraise(): File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__ compat.raise_( File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_ raise exception File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 80, in instances rows = [proc(row) for row in fetch] File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 80, in <listcomp> rows = [proc(row) for row in fetch] File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/loading.py", line 601, in _instance state.manager.dispatch.load(state, context) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 322, in __call__ fn(*args, **kw) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/mutable.py", line 462, in load val = cls.coerce(key, val) File "/app/redash/models/types.py", line 58, in coerce return Mutable.coerce(key, value) File "/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/mutable.py", line 414, in coerce raise ValueError(msg % (key, type(value))) ValueError: Attribute 'settings' does not accept objects of type <class 'str'>

[snickerjp]

@rizwan-ishtiaq

The error ValueError: Attribute 'settings' does not accept objects of type <class 'str'> suggests that an invalid value (a string) has been set for settings.

Since this setting is entered via the web configuration page, please check the following:

• Ensure that the value entered for settings is of the correct type.
• If possible, try re-entering the value to see if the issue persists.

Additionally, if you could share the value you entered in the settings page, it may help identify the issue more specifically.