Skip to content

Latest commit

 

History

History
117 lines (77 loc) · 3.4 KB

mssql.rst

File metadata and controls

117 lines (77 loc) · 3.4 KB
Raw

Microsoft SQL server (MSSQL)

Microsoft SQL server (MSSQL) is part of turbodbc's integration databases. That means that each commit in turbodbc's repository is automatically tested against MSSQL to ensure compatibility. Here are the recommended settings for connecting to a Microsoft SQL database via ODBC using the turbodbc module for Python.

Recommended odbcinst.ini (Linux)

On Linux, you have the choice between two popular drivers.

Official Microsoft ODBC driver

Microsoft offers an official ODBC driver for selected modern Linux distributions.

[MSSQL Driver]
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0

FreeTDS

FreeTDS is an open source ODBC driver that supports MSSQL. It is stable, has been around for well over decade and is actively maintained. However, it is not officially supported by Microsoft.

[FreeTDS Driver]
Driver = /usr/local/lib/libtdsodbc.so

Recommended odbcinst.ini (OSX)

FreeTDS seems to be the only available driver for OSX that can connect to MSSQL databases.

[FreeTDS Driver]
Driver = /usr/local/lib/libtdsodbc.so

Recommended data source configuration

Official Microsoft ODBC driver (Windows)

Put these values in your registry under the given key. Be sure to prefer the latest ODBC driver over any driver that may come bundled with your Windows version.

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MSSQL]
"Driver"="C:\\Windows\\system32\\msodbcsql13.dll"
"Server"="<host>"
"Database"="<database>"

Official Microsoft ODBC driver (Linux)

[MSSQL]
Driver         = MSSQL Driver
Server         = <host>,<port>
Database       = <database>

Note

You cannot specify credentials for MSSQL databases in odbc.ini.

FreeTDS data sources

[MSSQL]
Driver   = FreeTDS Driver
Server   = <host>
Port     = <post>
Database = <database>

Note

You cannot specify credentials for MSSQL databases in odbc.ini.

Recommended turbodbc configuration

The default turbodbc connection options have issues with Unicode strings on MSSQL. Please make sure to set the prefer_unicode option <advanced_usage_options_prefer_unicode>.

>>> from turbodbc import connect, make_options
>>> options = make_options(prefer_unicode=True)
>>> connect(dsn="MSSQL", turbodbc_options=options)

Warning

If you forget to set prefer_unicode, you may get anything from garbled up characters (e.g., u'\xe2\x99\xa5' instead of the unicode character u'\u2665') or even ODBC error messages such as [FreeTDS][SQL Server]Invalid cursor state.