How to connect Microsoft SQL server to QGIS
To connect Microsoft SQL Server to QGIS (Quantum GIS), you need to follow these steps:
- Install Necessary Drivers: First, you need to ensure that you have the necessary ODBC drivers for SQL Server installed on your machine. You can download these from the official Microsoft website. ( This is only necessary for older versions of QGIS – if you have a new version you should be fine )
- Open QGIS: Open your QGIS application.
- Add Layer: Go to the “Layer” menu, then select “Add Layer”, and then “Add MSSQL Layer” or “Add Vector Layer” (depending on your version of QGIS).
- New Connection: In the new window that opens, click on “New”.
- Enter Connection Details: Here, you will need to enter the details of your SQL Server database. These include:
- Name: The connection’s name will be saved in QGIS.
- Provider/DSN: This is the name of the SQL Server instance you are connecting to. ( This is optional and you can leave it blank )
- Host: This is the IP address or hostname of the server where the SQL Server database is hosted.
- Database: This is the database name you want to connect to.
- Username: This is the username for your SQL Server database.
- Password: This is the password for your SQL Server database.
- Test Connection: Click on “Test Connection” to ensure that all the details you have entered are correct and that QGIS can connect to your SQL Server database.
- Select Layer: If the connection is successful, you can then select the layer you want to add from your SQL Server database.
- Add: Click “Add” to add the layer to your QGIS project.
Remember, the SQL Server must be configured to allow remote connections, and the necessary ports must be open on any firewalls that may be in place. Also, the user must have the necessary permissions to access the database and perform the required operations.
Want to stay ahead of the geospatial curve? Listen to our podcast!
What is the correct format for the SQL Server instance name
The SQL Server instance name is typically in the format servername\instancename.servername is the name of the server where SQL Server is installed.instancename is the name of the SQL Server instance on that server.
If you’re using the default instance, you can just use the server name. If you’re using a named instance, you need to include both the server name and the instance name.
What ports need to be open on my firewall for QGIS to connect to SQL Server?
By default, SQL Server listens on TCP port 1433. If you’re using a named instance, it might use a different port, and the SQL Server Browser service (which listens on UDP port 1434) helps direct incoming connections to the correct port.
So, at a minimum, you need to open TCP port 1433 and UDP port 1434 on your firewall. If your SQL Server is using a different port, you need to open that port instead of or in addition to 1433.
What permissions does my SQL Server user need to connect to the database from QGIS?
At a minimum, the SQL Server user needs the CONNECT SQL permission to connect to the SQL Server instance, and the VIEW DEFINITION permission on the database to see its structure.
If you want to perform SELECT, INSERT, UPDATE, or DELETE operations, the user also needs the corresponding SELECT, INSERT, UPDATE, or DELETE permissions on the relevant tables.
If you want to perform administrative tasks like creating tables or altering the database structure, the user needs additional permissions like CREATE TABLE, ALTER, etc.
Apple Podcasts
Google Podcasts
Spotify
