QGIS to SQL – Can’t Save/Edit a SQL Connection

by David Crowther

Question:

I have an existing Database Connection in QGIS to my SQL Database, but when I edit the details e.g. point to a new Database, the OK button is greyed out – how can I resolve this?

Answer:

In a recent release of QGIS (3.14) there is an issue where for SQL Database Connections you are now required to enter a Provider/DSN value or you cannot press the OK button to Save/Edit the Database Connection.

See below where I have an existing SQL Database Connection to my TrainingDB – called Training_Start and I want to edit this Connection to point to another Database e.g. MapThatV4.

Let’s make the change to this SQL Database Connection:

Renaming the Connection Name to be – MapThatV4
Listing the Databases again and choosing the MapThatV4 Database

After making the required changes I now cannot press the OK button.

The reason for this is that the SQL Connection cannot be edited and Saved without including a Provider/DSN value.

However, SQL Database Connections don’t need the Provider/DSN value and adding one will break the Connection. If you add a Provider/DSN value e.g. TEMP and now try to press list your databases it will throw an error message and you still won’t be able to press OK and Save/Edit the Connection details.

The resolution is to access your SQL Database connection, leave the Provider/DSN blank and then choose the required Database e.g. MapThatV4.

But to then save the changes, you must then add a temporary Provider/DSN value e.g. TEMP…. but don’t press List Database again, simply press OK.

This now means that you have edited/created a SQL Database connection, as you can now press OK. However, if you try to CONNECT and add data from this connection it will fail as the Provider/DSN value breaks the connection.

To resolve this issue, we need to remove the Provider/DSN value, but we can’t do this via the SQL Connection Details as we can’t press OK without the value!!

The solution is to remove the Provider/DSN value using the Advanced Options in your QGIS Settings.

In QGIS choose Settings > Options and in the Options window choose the ADVANCED pane.

As QGIS states…. Be very careful when changing the Advanced Options…… so choose the I will be careful button.

In the Advanced Settings Editor, find the SQL Database Connection – in this case MapThatV4. Open the settings and find the SERVICE line item. Here we can see that the Service String (the Provider/DSN) has a value of TEMP.