search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.38k stars 2.98k forks source link

Connection MSSQL (not localhost) #24100

Closed qgib closed 5 years ago

qgib commented 7 years ago

Author Name: Vincent Neuilly (Vincent Neuilly) Original Redmine Issue: 16190 Affected QGIS version: 2.18.3 Redmine category:data_provider/mssql

Hello,

I think there is a problem with the MSSQL connector because I have different result if I connect with "Add a MSSQL spatial layer" or with "Add vector layer"/Database/MSSQL !

With a localhost Database, I agree everything works fine. But in this example, I don't use a local database, it is located on a virtual network through VPN (database is on a server, and I connect to this server through a VPN).

The thing is I need to have access not only to spatial layers, but to the whole database. And without the data connection, I can't.

Thanks for the answer.

qgib commented 7 years ago

Author Name: Giovanni Manghi (@gioman)

qgib commented 5 years ago

Author Name: Nyall Dawson (@nyalldawson)

Is this still an issue with 3.2? I suspect it's just down to the difficult "magic" connection settings required for sql server connections.

I think there is a problem with the MSSQL connector because I have different result if I connect with "Add a MSSQL spatial layer" or with "Add vector layer"/Database/MSSQL !

With a localhost Database, I agree everything works fine. But in this example, I don't use a local database, it is located on a virtual network through VPN (database is on a server, and I connect to this server through a VPN).

The thing is I need to have access not only to spatial layers, but to the whole database. And without the data connection, I can't.

Thanks for the answer. to Hello,

I think there is a problem with the MSSQL connector because I have different result if I connect with "Add a MSSQL spatial layer" or with "Add vector layer"/Database/MSSQL !

With a localhost Database, I agree everything works fine. But in this example, I don't use a local database, it is located on a virtual network through VPN (database is on a server, and I connect to this server through a VPN).

The thing is I need to have access not only to spatial layers, but to the whole database. And without the data connection, I can't.

Thanks for the answer.

qgib commented 5 years ago

Author Name: Jürgen Fischer (@jef-n)

Bulk closing 82 tickets in feedback state for more than 90 days affecting an old version. Feel free to reopen if it still applies to a current version and you have more information that clarify the issue.

qgib commented 5 years ago

Author Name: Filip Mahieu (@filipmahieu)

In 3.4.1 this exists when connecting to sql server 2016. But only when not in trusted connection; then I cannot access any table/view, even spatial in the database.

mihai1git commented 4 years ago

In 3.8.1 this issue still exists when simply connecting to SQL Server 2008 R2, on localhost with Windows10, from QGIS Navigator tab -> MSSQL item -> New Connection. I tried to list the database with Trusted connection and with not Trusted connection. I disabled the pooling option for ODBC driver versions 13 and 17 from ODBC Data Source Administrator Tool (64-bit).

1.When field: Provider/DSN is empty, the error is: Error opening connection: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed QODBC3: Unable to connect

When I try to Test Connection the error is: Driver not loaded Driver not loaded.

  1. When field: Provider/DSN is filled with Data source name from ODBC Data Source Administrator Tool (64-bit)->System DSN tab, the error is: Error opening connection:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect
gioman commented 4 years ago

In 3.8.1 this issue still exists when simply connecting to SQL Server 2008 R2

@mihai1git not sure about this specific configuration, but on QGIS 3.10 on Windows 10 and the latest SQL Server trial (also on Windows 10) it works all ok.