-
Notifications
You must be signed in to change notification settings - Fork 562
Connecting to SQL Server from Windows
Microsoft have written and distributed multiple ODBC drivers, compatible with different versions of SQL Server:
- {SQL Server} - released with SQL Server 2000
- {SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
- {SQL Server Native Client 10.0} - released with SQL Server 2008
- {SQL Server Native Client 11.0} - released with SQL Server 2012
- {ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014
- {ODBC Driver 13 for SQL Server} - supports SQL Server 2008 through 2016
- {ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2017
- {ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2022 (depending on minor version)
- {ODBC Driver 18 for SQL Server} - supports SQL Server 2012 through 2022 (depending on minor version)
Note, the "SQL Server Native Client..." and earlier drivers are deprecated and should not be used for new development.
The drivers can be downloaded from here. It's generally best to use the latest drivers on your PC, regardless of the version of SQL Server you are connecting to, because the drivers are largely backwards-compatible. However you may prefer to use the specific driver for your SQL Server instance.
The connection strings for all these drivers are essentially the same, for example:
DRIVER={ODBC Driver 18 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password
or, in Python:
conn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password')The full list of connection keywords can be seen here. The connection keywords are typically case-insensitive.
You can find out what drivers are installed on your PC by navigating to 'Control Panel -> Administrative Tools -> ODBC Data Sources' and clicking on the 'Drivers' tab. You can also get to this ODBC Administrator window by running 'odbcad32.exe'. Be aware there are separate 32-bit and 64-bit versions of the ODBC Administrator. Lastly, you can get the driver names programmatically by calling the Python function pyodbc.drivers().
A DSN (or Data Source Name) allows you to define the ODBC driver, server, database, login credentials, and other connection attributes all in one place, so you don't have to provide them in your connection string. You can set up DSNs on your PC by using your ODBC Data Source Administrator window.
To get to your ODBC Data Source Administrator window, navigate to 'Control Panel -> Administrative Tools -> ODBC Data Sources'. Under the tabs 'User DSN' or 'System DSN' click on the 'Add...' button and follow the wizard instructions. 'User DSN' is for just you, 'System DSN' is for all users. Choose a driver that is suitable for the version of SQL Server you are connecting to, and add any other connection information that is relevant. Once you have created your new DSN, use it in the pyodbc.connect() function as follows:
conn = pyodbc.connect('DSN=mynewdsn;UID=user;PWD=password')Attributes in the connection string will override any attributes in the DSN.
There are many SQL Server connection attributes but here are some that could prove most useful.
Adding the APP keyword allows you to provide a descriptive label for your database connection (of up to 128 characters). This is useful for database administrators, e.g.:
conn = pyodbc.connect('DSN=mynewdsn;APP=Daily Incremental Backup;')Version 18 of the ODBC Driver defaults encryption to true so you may need to explicitly set this to false ("no") or optional to get your connection to work, e.g.:
conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=test;UID=user;PWD=password;Encrypt=no;')Version 18 of the ODBC Driver added the LongAsMax keyword which causes the driver to send SQL types SQL_LONGVARCHAR, SQL_LONGVARBINARY, and SQL_LONGWVARCHAR as varchar(max), varbinary(max), and nvarchar(max) instead of text, image, and ntext respectively. The SQL data types text, image, and ntext have been deprecated for a long time now and can cause issues. If you are using "max" data type columns in your database, it's probably best to set this, e.g.:
conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=test;UID=user;PWD=password;LongAsMax=yes;')You can connect to your SQL Server instance using a trusted connection, i.e. using your Windows account rather than a login name and password, by using the Trusted_Connection attribute, e.g.:
conn = pyodbc.connect('DSN=mynewdsn;Trusted_Connection=yes;')