QGIS Connection with MSSQL: does not work

Question

With QGIS 3.14 I try to get access to a table in MSSQL with points of the GEOGRAPHY type. The table has a PK with clustered index, and a spatial index. There is a filled geometry_columns metadata table. But I can not establish a connection. See picture where I get stuck. If I click on 'List databases' the database in question appears. But If I click 'Test connection' I very briefly see the message 'Testing connection: .....' above, and nothing happens, no error message either. The 'OK' button remains grey so I can not proceed.

Answer 1

This looks like a problem I had too. Fixed it by adding a Provider/DSN of THISISJUNK and then fixing it in the QGIS3.ini file.

Step 1 - Fill something in the Provider/DSN. I used THISISJUNK. That let's you hit OK and save it, but it still doesn't work.

Step 2 - Edit the QGIS3.ini file. Mine was in %APPDATA%\QGIS\QGIS3\profiles\default\QGIS

I searched the file for the 'THISISJUNK' string, and deleted it.
So I changed the line:
connections\MyDBName\service=thisisjunk
to:
connections\MyDBName\service=

Answer 2

This happened to me as well - manually editing the QGIS3.ini file was the only way I was able to connect to my SQL Server. I couldn't click the OK button at all, at any time, until I edited it by hand.

Here is the full config I wrote, to give you an example of what worked for me:

[MSSQL]
connections\my_mssql\allowGeometrylessTables=false
connections\my_msql\authcfg=
connections\my_msql\database=master
connections\my_msql\disableInvalidGeometryHandling=false
connections\my_msql\dontResolveType=false
connections\my_msql\estimatedMetadata=false
connections\my_msql\geometryColumnsOnly=false
connections\my_msql\host="localhost,1433"
connections\my_msql\password=1234
connections\my_msql\projectsInDatabase=false
connections\my_msql\publicOnly=false
connections\my_msql\savePassword=true
connections\my_msql\saveUsername=true
connections\my_msql\service=
connections\my_msql\sslmode=SslDisable
connections\my_msql\username=sa
connections\selected=my_msql