The databases page would not load for me after changing the SECRET_KEY. Here is how I fixed it:

psql -d superset -U superset -W
# Password for user superset:
select database_name, password from dbs;
update dbs set password = null;

Now the databases page will load but clicking edit on certain databases still didn’t work and caused this stack trace:

Unable to load dialect <class 'sqlalchemy.dialects.mssql.adodbapi.MSDialect_adodbapi'>: type object 'MSDialect_adodbapi' has no attribute 'dbapi'
2021-09-17 18:29:43,812:WARNING:superset.db_engine_specs:Unable to load dialect <class 'sqlalchemy.dialects.mssql.adodbapi.MSDialect_adodbapi'>: type object 'MSDialect_adodbapi' has no attribute 'dbapi'
2021-09-17 18:29:43,819:ERROR:root:Invalid decryption key
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 126, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xae in position 2: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 85, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 155, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 1448, in get
    return self.get_headless(pk, **kwargs)
  File "/app/superset/utils/log.py", line 242, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 390, in get_headless
    duration, response = time_function(super().get_headless, pk, **kwargs)
  File "/app/superset/utils/core.py", line 1461, in time_function
    response = func(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 1344, in get_headless
    item = self.datamodel.get(pk, self._base_filters, self.show_select_columns)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/models/sqla/interface.py", line 935, in get
    query, _filters, select_columns=select_columns
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 100, in instances
    cursor.close()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 80, in instances
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 80, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 588, in _instance
    populators,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 725, in _populate_full
    dict_[key] = getter(row)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py", line 1278, in process
    return process_value(impl_processor(value), dialect)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 469, in process_result_value
    value = super().process_result_value(value=value, dialect=dialect)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 414, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 128, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

Create a new database (e.g. name = “New DB”) from the UI. You can get the SQLAlchemy URI using this SQL:

select sqlalchemy_uri from dbs where database_name = 'Original DB';

Once the new database is created, back in psql:

select database_name, id from dbs where database_name = 'New DB' or database_name = 'Original DB';
# 5 = New DB
# 2 = Original DB
update tables set database_id = 5 where database_id = 2;
update tab_state set database_id = 5 where database_id = 2;
update query set database_id = 5 where database_id = 2;
update saved_query set db_id = 5 where db_id = 2;
update table_schema set database_id = 5 where database_id = 2;
delete from dbs where database_name = 'Original DB';
update dbs set database_name = 'Original DB' where database_name = 'New DB';

I had the same issue when I use docker-compose to install superset on MacOS.

Due to it is my local test, it is easy for me just to remove old containers and old data and start fresh.

• docker-compose down -v (or docker-compose down and docker volume prune), this will remove the old containers and old data
• docker-compose up, to start fresh

The key point is you need to remove old volumes.

Hi @newtonjose,

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

I’ve encountered the same issue when upgraded superset to 1.4.1 from 1.4.0. I resolved the issue @andtlopez comment with one more step.

1. Change SECRET_KEY on superset_config.py
2. Access psql client and connect to the superset DB (\connect db_name;)
3. update dbs set password = null, encrypted_extra where id=?; <- encrypted_extra should be set to null
4. superset db upgrade
5. superset init
6. superset run
7. Update password on Superset: Data > Databases (I’m using 1.4.1 btw)

I ran into this too when updating the SECRET_KEY, the most reliable/surgical fix seemed to be:

1. run update dbs set password = null, encrypted_extra = null; in the superset database (sets the secrets to blank, DB will not connect)
2. Then edit the BI database connection in the UI, and update the password. Those two fields will be updated and your datasets and charts should all be working normally, as before.

💡 UPDATE: in a classic RTFD moment, looks like this is far simpler if you just rotate secrets the right way: https://superset.apache.org/docs/installation/configuring-superset/#secret_key-rotation

I’ve unfortunately encountered this and here’s what I’ve done to fix this:

1. Change SECRET_KEY on superset_config.py
2. Access psql client and connect to the superset DB (\connect db_name;)
3. update dbs set password = null; (thanks @jhult), exit out
4. superset db upgrade
5. superset init
6. superset run
7. Update password on Superset: Data > Databases (I’m using 1.2.0 btw)

Do note that superset has a cli for this:

https://superset.apache.org/docs/installation/configuring-superset/#secret_key-rotation

Just to help anyone else coming across this.

Trying to migrate / upgrade superset using backup of postgres metadata, shifting to new version. I went from superset 2.0.x to 2.10. Then seeing problems with invalid decryption key, so you see the datasets in superset ui but the database connections barf.

First, fix secret keys in superset_config.py

PREVIOUS_SECRET_KEY = ‘CHANGE_ME_TO_A_COMPLEX_RANDOM_SECRET’ SECRET_KEY = ‘some-really-random-key’

<= determined original key from other (original) installation by getting session on superset container: $ docker exec -it superset_worker bash then:

superset shell

then:

from flask import current_app; print(current_app.config[“SECRET_KEY”])

<= generate from: bash$ openssl rand -base64 42

Second, ensure to specify a version in your docker-compose config: $ vi docker-compose-non-dev.yml x-superset-image: &superset-image apachesuperset.docker.scarf.sh/apache/superset:2.1.0

Third, as per comments above, update the password and encrypted_extra values for your db connections in the restored metadata db: psql: update dbs set password = null, encrypted_extra = null

Fourth, you should now be able to login to superset ui, go to database connections, and just re-enter the password(s) for your database(s) - then the dataset(s) for those databases come back to life.

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

how to recreate or update ,this page get error

This worked for me https://superset.apache.org/docs/installation/configuring-superset/#rotating-to-a-newer-secret_key

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

how to recreate or update ,this page get error

i got an solution, add a config DB_SECRET_KEY in config.py it’s value is origin SECRET_KEY, then use config[“DB_SECRET_KEY”] replace config[“SECRET_KEY”] in superset/models/core.py Database model

I unfortunately cannot remember my previous secret key (My config file got deleted, somehow), please help!!!

You can try to reset your secret key by usingopenssl rand -base64 42 andexport SUPERSET_SECRET_KEY='(just generated secret key)'.

I had the same issue when I use docker-compose to install superset on MacOS.

Due to it is my local test, it is easy for me just to remove old containers and old data and start fresh.

• docker-compose down -v (or docker-compose down and docker volume prune), this will remove the old containers and old data
• docker-compose up, to start fresh

The key point is you need to remove old volumes.

this worked for me

This workaround https://github.com/apache/superset/issues/8538#issuecomment-1046238602, assumes that the meta-data db is using postgres; in my case it was sqlite. So the steps in my case where:

1. Change SECRET_KEY on superset_config.py
2. Access sqlite3 client and connect to the sqlite db file ( sqlite3 superset.db )
3. update dbs set password = null, encrypted_extra = null where id=?; <- encrypted_extra should be set to null
4. superset db upgrade
5. superset init
6. superset run
7. Update password on Superset: Data > Databases (I’m using 1.4.1 btw)

I only changed step 2.

I’ve encountered the same issue when upgraded superset to 1.4.1 from 1.4.0. I resolved the issue @andtlopez comment with one more step.

1. Change SECRET_KEY on superset_config.py
2. Access psql client and connect to the superset DB (\connect db_name;)
3. update dbs set password = null, encrypted_extra where id=?; <- encrypted_extra should be set to null
4. superset db upgrade
5. superset init
6. superset run
7. Update password on Superset: Data > Databases (I’m using 1.4.1 btw)

In step 3 I’m getting a syntax error. Would update dbs set password = null; update dbs set encrypted_extra = null; work?

just sharing my observation that having same secret_key and connecting the from the different host fails with invalid decryption key. would like to know views from community.( i think this by design)

I used a different approach: I could only find 1 place that the DB_SECRET_KEY was being used - which was in the dbs table under password. After rotating the SECRET_KEY, i set that field to null in the database. After that the database screen will work again (it crashes because it can’t decrypt the passwords) - and then reset the passwords.