Prevent mxODBC Connect Server from listening on sockets which are
unbound due to a configuration error. The OS will typically assign a
random port and listen on all interfaces, which can pose a security
issue.
mxODBC Connect Enhancements
Add allow_clients configuration variable to [Session] config section of
the mxODBC Connect Server. This allows overriding the list accepted
client IP addresses/networks and helps to e.g. allow connections from
clients not on the server's interface networks.
Added "connections" shortcut support for allow_clients to easily access the list of configured networks defined by the connection sections.
Added "all" and "localhost" shortcuts support for allow_clients to simplify allowing any client IP address or only the local machine.
Added support for the BinaryNull work-around added to mxODBC 3.3.5 in order to better support VARBINARY columns in MS SQL Server.
Both mxODBC Connect Client and Server will need to upgraded to version
2.1.4 in order to be able to use the new singleton.
The mxODBC Connect Client can now be compiled to a wheel file to simplify deployment. Simply point the pip at the prebuilt archive.
mxODBC API Enhancements
Upgraded the mxODBC Connect Server to mxODBC 3.3.5.
MS SQL Server
Documented and recommended use of SET NOCOUNT ON for
running multiple statements or stored procedures. This can not only
resolve issues with error reporting, it also results in better performance.
Added a work-around for MS SQL Server Native Client to be able to
support VARCHAR/VARBINARY(MAX) columns when using the Native Client with direct execution mode or Python type binding mode.
Thanks to ZeOmega for reporting this.
Added new helper singleton BinaryNull to allow binding a NULL to a
VARBINARY column with SQL Server in direct execution mode or Python type binding mode (as used for FreeTDS).
Using the usual None doesn't work in those cases,
since SQL Server does not accept a VARCHAR data type as
input for VARBINARY, except by using an explicit "CAST(? AS VARBINARY)".
mxODBC binds None as VARCHAR for best compatibility, when not getting
any type hints from the ODBC driver.
Added a fix for the MS SQL Server Native Client error "[Microsoft][ODBC Driver 11 for SQL
Server][SQL Server]The data types varchar and text are incompatible in
the equal to operator." when trying to bind a string of more than 256
bytes to a VARCHAR column while using cursor.executedirect().
cursor.execute() was unaffected by this. Thanks to Paul Perez for
reporting this.
Added a note to avoid using "execute " when calling stored procedures
with MS SQL Server. This can result in '[Microsoft][SQL Native
Client]Invalid Descriptor Index' errors. Simply dropping the "execute "
will have the error go away.
Added a work-around to address the FreeTDS driver error '[FreeTDS][SQL
Server]The data types varbinary and image are incompatible in the equal
to operator.' when trying to bind binary strings longer than 256 bytes
to a VARBINARY column. This problem does not occur with the MS SQL
Server Native Client.
Reenabled returning cursor.rowcount for FreeTDS >= 0.91. In previous
versions, FreeTDS could return wrong data for .rowcount when using
SELECTs.This should make SQLAlchemy users happy again.
Add work-around to have FreeTDS ODBC driver accept binary data in
strings as input for VARBINARY columns. A side effect of this is that
FreeTDS will now also accept binary data in VARCHAR columns.
SAP Sybase ASE
Added work-arounds and improvements for Sybase ASE ODBC drivers to enable working with BINARY and VARBINARY columns.
Added a work-around for a cursor.rowcount problem with Sybase ASE's ODBC
driver on 64-bit platforms. It sometimes returns 4294967295 instead of
-1.
Added note about random segfault problems with the Sybase ASE 15.7 ODBC driver
on Windows. Unfortunately, there's nothing much we can do about this,
other than recommend using the Sybase ASE 15.5 ODBC driver version which
does not have these stability problems.
Fixes
Added improved documentation on the direct execution model available in mxODBC.
This can help in more complex parameter binding situations and also
provides performance boosts for a few databases, including e.g. MS SQL
Server.
Upgraded default RSA key length for demo certificates to 2048 bits.
mxODBC Connect Enhancements
Upgraded eGenix PyRun used for mxODBC Connect Server on Linux to 2.1.0.
Upgraded the Python version used for mxODBC Connect Server to 2.7.9.
Fixed a bug causing pip uninstall not to remove .pyc/.pyo files
Resolved an intermittent error related to hash seeds which sometimes
caused prebuilt archives to not install correctly. Thanks to Albert-Jan
Roskam for reporting this.
mxODBC API Enhancements
Upgraded the mxODBC Connect Server to mxODBC 3.3.2.
MS SQL Server
Fixed an "ODBC driver sent negative string size" error when using empty
strings or None with output parameters for SQL Server
ODBC drivers.
Clarified that due to the way the SQL Server ODBC driver sends data, mixing output parameters and output result sets is not possible. A work-around for this is to send back output parameters as additional result set.
SAP Sybase ASE
Added a work-around for the Sybase ASE ODBC driver which has problems with BIGINT columns. These are now supported.
Fixed a possible "ODBC driver sent negative string size" error when using empty
strings or None with output parameters for Sybase ASE
ODBC drivers.
Fixes
Fixed the handling of None as default value for output parameters in e.g.
stored procedures to use VARCHAR binding rather than CHAR binding. The
latter caused padding with some database backends.
Changed cursor.colcount to be determined
on-demand rather than right after the prepare step of statement
execution.
In this patch level release we have put great emphasis on enhancing
the TLS/SSL setup of the mxODBC Connect product, addressing recent
attacks on SSLv3 and improving the security defaults.
Security Enhancements
Updated included eGenix pyOpenSSL to 0.13.6, which includes OpenSSL 1.0.1j and enables the TLS_FALLBACK_SCSV protection against protocol downgrade attacks.
OpenSSL cipher string list updated to use the best available ciphers in
OpenSSL 1.0.1j per default and support perfect forward security.
OpenSSL context options setup to disallow weak protocol features.
Disabled SSLv3 for the mxODBC Connect Client in response to the recent POODLE attack on SSLv3.
mxODBC Connect Client
2.1.1 will not be able to communicate with mxODBC Connect Server 2.1.0
and earlier when using SSL mode. The error message looks like this:
[Error] [('SSL routines', 'SSL23_GET_SERVER_HELLO', 'unsupported
protocol')] (using pyOpenSSL) or [SSLError] [Errno 1] _ssl.c:493:
error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number (using
the ssl module).
Enabled TLS v1, v1.1 and v1.2 for the mxODBC Connect Server in SSL
mode and have it use the best possible protocol when talking to a
client.
The server will still support SSLv3 for backwards compatibility
reasons, since older mxODBC Connect Clients only support SSLv3. This
will be changed in the next major/minor mxODBC Connect Server release.
Fixed a linker setting on Linux to have the mxODBC Connect Server use the embedded OpenSSL libraries instead of the system ones.
Improved the protocol handlers for SSL connection setups using mixed plain text/TLS connections to renew the session id after having established the TLS session.
mxODBC Connect Enhancements
Fixed a problem where connection/cursor.messages could not be accessed from the client side.
mxODBC Connect Client is now also available as web installer, greatly simplifying the installation of the client. It is now possible to install the client using a single pip command:
Upgraded eGenix PyRun used for mxODBC Connect Server on Linux to 2.0.1.
Upgraded the Python version used for mxODBC Connect Server on Windows to 2.7.8.
Asynchronous Processing
Fixed a problem which prevented the mxODBC Connect Client to connect to the server when using both gevent integration and the Python ssl module for communication.
mxODBC API Enhancements
Upgraded the mxODBC Connect Server to mxODBC 3.3.1.
SQL Server
Documented a solution for a problem with the SQL Server 2012 parser
complaining about not being able to deduce types of some operations using more than one bound variable, e.g. "col1 >= ? + ?".
Teradata
Improved the Teradata ODBC driver setup instructions to address some common gotchas when setting up mxODBC to work with these drivers.
Fixed a problem with Teradata and the test suite
which resulted in an error "[Teradata][ODBC Teradata Driver] Beyond
SQL_ACTIVE_STATEMENTS limit". The driver needs an explicit call to
cursor.flush() to close any open result sets before running commits or
rollbacks.
Misc
Fixed a problem in cursor.getcolattributes() that caused errors to be ignored.
Added better protection against ODBC driver bugs in getenvattr().
Fixed an attribute error when using the NamespaceRowFactory function.
Fixed a deprecation warning when using the NamespaceRowFactory function.
The complete list of changes in mxODBC 3.3.1 is available on the mxODBC changelog page.
mxODBC Connect 2.1 makes most of the new mxODBC 3.3. APIs and
enhancements available in the mxODBC Connect Client. This is a short
summary of the available new features and enhancements.
Stored Procedures
mxODBC Connect now has full support for input, output and input/output parameters in stored procedures and stored functions, allowing easy integration with existing databases systems.
User Customizable Row Objects
Added support for user customizable row objects by adding cursor/connection .rowfactory and .row constructor attributes. When set, these are used to wrap the normal row tuples returned by the .fetch*() methods into dynamically created row objects.
Added new RowFactory classes to support cursor.rowfactory and cursor.row. These allow dynamically creating row classes that provide sequence as well as mapping and attribute access to row fields - similar to what namedtuples implements, but more efficient and specific to result sets.
Fast Cursor Types
Switched to forward-only cursor types for all database backends, since this provides a much better performance for MS SQL Server and IBM DB2 drivers.
Added a new .cursortype attribute to allow adjusting and
inspecting the ODBC cursor type to be used for an mxODBC Connect cursor
object. Default is to use forward-only cursors, but mxODBC also support
several other useful cursor types such as static cursors with full
support for result set scrolling.
More new Features
Enhanced cursor.prepare() to allow querying cursor.description right after the prepare step and not only after calling a cursor.execute*() method.
Added iterator/generator support to .executemany(). The parameters list can now be an iterator/generator, if needed.
Added new connection.dbapi property to easily access module level symbols from the connection object.
Timestamp seconds fraction resolution is now determined
from the scale of a datetime/timestamp SQL column, using the
connection.timestampresolution as lower bound, when using SQL type
binding. In Python type binding mode, the connection.timestampresolution
determines the scale with which a variable is bound. This allows for
greater flexibility when dealing with database backends that don't
provide full nano-second second resolution, such as e.g. MS SQL Server.
mxODBC Connect now accepts Unicode string values for date/time/datetime/timestamp column types in SQL type binding mode. Previous versions already did in Python type binding mode.
mxODBC Connect now uses unicode(obj, encoding) semantics
when binding Python objects to SQLWCHAR database parameters.
Additionally, it ignores the encoding in case obj is a number, to avoid
conversion errors.
Added new cursor.encoding attribute. This gets its default values from the connection the cursor was created on and allows for per-cursor encoding settings.
Added cursor.bindmethod which inherits from
connection.bindmethod when creating the cursor. This allows adjusting
the variable bind method on a per-cursor basis, rather than only on a
per connection basis as in previous mxODBC Connect versions.
mxODBC Connect API Enhancements
The SQL lookup object is now cached on the client side to avoid frequent roundtrips when using symbols which are needed for stored procedures with input/output parameters.
The SQL lookup object now supports ODBC 3.8 symbols and values, including driver specific symbols used by the MS SQL Server Native Client and IBM DB2 ODBC drivers.
Improved the server side object management to simplify
client side garbage collection considerations. Even though we still
encourage using explicit garbage collection of cursors, connections and
server sessions on the client side, mxODBC Connect Server will now
handle most situations even without these explicit calls.
Asynchronous Processing
Tested with the latest gevent and greenlet packages. mxODBC
Connect Client will happily work together with the asynchronous
libraries gevent. All it takes is a single configuration entry in the
client side config file.
Security Enhancements
Changed the way passwords are stored in the server's
authorized-users.txt file in order to make password storage more secure.
The file now stores salted SHA-256 password hashes instead of the MD5
hashes used in version 2.0 and earlier.
User authentication now uses salted SHA-256 password hashes
when transferring the login data from the client to the server. This
provides better protection when using plain text client server setups and additional security over SSL network connections.
ODBC Driver/Manager Compatibility Enhancements
unixODBC
mxODBC Connect Server is now built against unixODBC 2.3.2 on Linux.
DataDirect
Updated the DataDirect binding to version 7.1.2 of the DataDirect ODBC manager on Linux.
Oracle
Added work-around for Oracle Instant Client to be able to use integer output parameters.
Added a work-around for Oracle Instant Client to have it
return output parameters based on the input placeholder Python parameter
types. It would otherwise return all parameters as strings.
Disabled a test for Oracle Instant Client which tries to set a pre-connect connection option for timeouts, since the ODBC driver segfaults with this option.
MS SQL Server
mxODBC Connect Server now defaults to 100ns connection.timestampresolution for MS SQL Server 2008 and later, and 1ms resolution for MS SQL server 2005 and earlier. This simplifies interfacing to SQL Server timestamp columns by preventing occasional precision errors.
Tested mxODBC Connect Server successfully with new MS SQL Server Native Client 11 for Linux. Unicode connection strings still don't work, but everything else does.
Added documentation on how to use Kerberos with mxODBC and SQL Server for authentication on both Windows and Linux to the mxODBC User Manual.
Added note about problems of the FreeTDS ODBC driver dealing with TIME and DATE columns to the to the mxODBC User Manual.
Sybase ASE
Added work-around for the Sybase ASE ODBC driver, which doesn't always pass back NULL correctly to mxODBC Connect Server on 64-bit Unix systems.
Changed the variable type binding mode default for the Sybase ASE ODBC driver from Python type binding to SQL type binding, which resolves issues with e.g. the Unicode support for that driver.
Added note about a segfault problem with the Sybase ASE 15.7 ODBC driver which is caused by the driver corrupting the heap.
IBM DB2
Added work-around for the IBM DB2 ODBC driver, which doesn't always pass back NULL correctly to mxODBC Connect Server on 64-bit Unix systems.
PostgreSQL
Added work-around to force Python type binding for the PostgreSQL ODBC drivers. More recent versions of the driver report supporting SQL type binding, but they don't implement it.
Added work-around to have PostgreSQL ODBC drivers properly work with binary data for BYTEA columns.
MySQL
mxODBC Connect Server now supports native Unicode with the recent MySQL ODBC drivers - provided you use the Unicode variants of the drivers.
Changed the default binding mode for MySQL ODBC drivers to Python type binding. This works around a problem with date/time values when talking to MySQL 5.6 servers.
Upgraded client and server to the most recent eGenix pyOpenSSL Distribution 0.13.3.1.0.1.7
in order to address the recently found Heartbleed bug in OpenSSL 1.0.1 -
1.0.1f. From the eGenix pyOpenSSL Distribution changelog:
CVE-2014-0160 ("Heartbleed Bug"): A missing bounds check in the handling of the TLS heartbeat extension can be
used to reveal up to 64kB of memory to a connected client or server. This
issue did not affect versions of OpenSSL prior to 1.0.1. For information, also have a look at the Heartbeet Bug website.
Added new server connection configuration setting connection_cursortype which allows modifying the default cursor type (connection.cursortype
attribute value; see changelog entry for mxODBC Connect 2.0.3) without
having to modify the client side applications. The setting can be
applied on a per connection basis for enhanced flexibility.
Added back the DataDirect ODBC manager subpackage which was missing in the 2.0.3 builds.
The mxODBC DataDirect subpackage is now built against the current DataDirect ODBC manager version 7.1.2.
Fixed the logging of allowed clients in the server.log to show the defined connection's subnets.
Client Enhancements
Fixed a problem with the .warningformat attribute not working as expected on cursors and connections.
Performance Enhancements
MS SQL Server performance can be much enhanced,
and increased to levels beyond that of mxODBC Connect 2.0.2 and
previous
releases, by adjusting the default cursor type to forward-only cursors
instead of static cursors. mxODBC Connect 2.0.4 makes this possible
without having to change client side applications by modifying the
server-config.ini file as follows:
[Connection_Example] ... # Use the faster forward-only cursors on this connection connection_cursortype = SQL.CURSOR_FORWARD_ONLY
The performance increase compared to mxODBC Connect 2.0.2 is enormous: from 2-3x faster executes/fetches for average queries, up to 300x faster for simple cases.
In mxODBC Connect 2.1, we will switch to using forward-only cursors per default for all database backends.
IBM DB2 can benefit from the same performance enhancements using forward-only cursors.
The effect is a lot smaller, but still noticeable: up to 2x faster executes/fetches with forward-only cursors, compared to mxODBC Connect 2.0.2.
MS SQL Server performance can now be much enhanced,
and increased to levels beyond that of mxODBC Connect 2.0.2 and previous
releases, by adjusting the default cursor type to forward-only cursors instead of static cursors:
# Connect to the remote database from mx.ODBCConnect.Client import ServerSession session = ServerSession(...) ODBC = session.open() connection = ODBC.DriverConnect(...) # Use the faster forward-only cursors connection.cursortype = ODBC.SQL.CURSOR_FORWARD_ONLY # Cursors created on this connection will then default # to forward only cursors cursor = connection.cursor()
The performance increase compared to mxODBC Connect 2.0.2 is enormous: from 2-3x faster executes/fetches for average queries, up to 300x faster for simple cases.
In mxODBC Connect 2.1, we will switch to using forward-only cursors per default for all database backends.
IBM DB2 can benefit from the same performance enhancements using forward-only cursors.
The effect is a lot smaller, but still noticeable: up to 2x faster executes/fetches with forward-only cursors, compared to mxODBC Connect 2.0.2.
Added documentation to explain the different cursor types, compatibility with different database backends and effects on performance to the mxODBC documentation.
The server installer on Windows will now install the Microsoft
Visual C++ 2008 SP1 Redistributable Package (if necessary) instead of
shipping with side-by-side runtime DLLs. This resolves installation issues on fresh Windows server installations.
Improved the active connection logging to show more accurate figures in situations where a lot of new connections are opened at once.
mxODBC Connect Server will now free resources on broken connections
much earlier than before. The setting is configurable using the
max_session_reconnect_time parameter in the server's [Activity]
configuration and defaults to 60 seconds.
Client Enhancements
No fixes were necessary.
Misc
Added a note that even with an unlimited license, the server sill uses an adjustable max_session configuration parameter to limit the effect of denial-of-service attacks.
mxODBC Connect Server is now also available as native 64-bit build for Windows 2008R2, Vista and 7 x64.
All mxODBC Connect Server executables are now signed on Windows to reduce the number of UAC dialogs during installation and use.
The mxODBC Connect tray app was rewritten in C to reduce the memory footprint.
The mxODBC Connect Server tray application was updated to work on Windows 7 as well.
mxODBC Connect now supports Python 2.7 both on the client and server side.
mxODBC Connect Server now supports unixODBC 2.3 or later on Unix platforms. unixODBC 2.2 is no longer supported on 64-bit systems.
API Enhancements:
mxODBC Connect Server now uses mxODBC 3.2 internally and makes its API available in the mxODBC Connect Client. This is a major step forward from the mxODBC 3.0 version used in mxODBC Connect Server 1.0.
mxODBC Connect Server now features all the ODBC driver compatibility enhancements
provided by mxODBC 3.2, including better support for MS SQL Server
Native Client, Oracle Instant Client, Sybase ASE, IBM DB2, Teradata and Netezza.
mxODBC Connect Client comes with all mxODBC 3.2 enhancements, including:
connection and cursor objects can be used as context managers
adjustable parameter styles (qmark or named)
connection .autocommit attribute to easily switch on autocommit
adjustable timestamp resolution
new possibilities to set connection and cursor options to adjust the ODBC objects to your application needs, e.g. set a connection read-only or set a query timeout
adjustable decimal, datetime and string formats
adjustable warning format to be able to handle server warnings without client interaction
greatly improved result set scrolling support
Unicode support for all catalog methods
Access to additional result set meta data via cursor.getcolattribute()
See the included mxODBC 3.2 documentation for more details..
Asynchronous Execution:
mxODBC Connect Client now integrates directly with gevent, allowing client applications to run asynchronous tasks while performing remote database queries.
mxODBC Connect Client also works with a monkey-patched gevent environment.
Security:
mxODBC Connect now uses the official IANA registered port 6632 (mxodbc-connect) for both plain text and SSL-encrypted communication.
Added STARTTLS support to be able to use a single port for both unencrypted and SSL-encrypted communication.
mxODBC Connect Client no longer requires a client certificate and key for SSL connections.
mxODBC Connect Client now allows selecting the used SSL module from two available options: Python standard lib ssl module and pyOpenSSL.
Upgraded to pyOpenSSL 0.13.0-1.0.0j on the server side.
mxODBC Connect Server will now use SHA1 digests for client certificate checks instead of MD5 to improve security.
mxODBC Connect Client can now additionally read client certificate
and private key from the config_data dictionary instead of from files
only - provided that pyOpenSSL is used (Python's ssl module doesn't
support this).
Client certificate checks are now also supported when using the standard Python ssl module on the client side.
Fixes:
mxODBC Connect Server will now return ProtocolErrors to the client
side and close the connection in case it finds that it cannot decode
the client side pickle.
Fixed a problem with database connections being kept alive in
sessions that were not explicitly closed by the client application.
mxODBC Connect pure Python prebuilt archives did not always install on non-Linux platforms.
ServerSession.close() will no longer cause error messages at
Python exit time, if the close action cannot be communicated to the
server.
mxODBC Connect Client will now raise a
mx.ODBCConnect.Error.ConnectionFailureError in case of server connection
failures due to timeouts.
Fixed a bug in session.open() which caused the module_name parameter not to get used.
Misc:
Python 2.3 and 2.4 support was removed from mxODBC Connect Client.
The start menu entry on Windows now includes a link to the correct
ODBC manager to be used with mxODBC Connect Server. This helps finding
the right one on Windows x64 platforms which provide two versions.
mxODBC Connect Client will now directly install into the correct
directories on Linux distributions that use different directories for
platform dependent and non-dependent directories (e.g. 64-bit RedHat and
64-bit OpenSUSE), without needing additional options on the install
command.
Added backwards compatibility support for the old-style using_ssl way of configuring server connections.
Changes from 1.0.1 to 1.0.2:
Upgraded the eGenix pyOpenSSL version included in mxODBC Connect Server to 0.9.0-0.9.8k
Fixed
a problem with connections sometimes timing out after 10 seconds of inactivity.
Connection
errors now cause an implicit immediate close of the connection (without
having to wait for a timeout). This allows the client to shutdown much
faster when exiting the Python client application in the situation of a broken server connection.
Clarified the INI file format used by mxODBC Connect Server and Client and added an extra section for this to the documentation.
Changes from 1.0.0 to 1.0.1:
Added cursor iterator support to mxODBC Connect Client.
Upgraded mxODBC version included in mxODBC Connect Server to 3.0.3
Fixed
a problem with database connections being kept alive in sessions that
were not explicitly closed by the client application.
Fixed a bug in print_resultset() due to a missing import in one of the modules.
mxODBC Connect Client prebuilt archives failed to install on non-Linux platforms.
mxODBC
Connect Client's ServerSession.close() will no longer cause error
messages at Python exit time, if the close event cannot be communicated
to the server.
Changes from 0.9.3 to 1.0.0:
Further improved the mxODBC Connect network layer,
resulting in much better fetch and round-trip performance, esp. for SSL
connections
Improved the documentation, added screenshots and more
configuration notes as well as tips on how to tune the network
performance
Fixed a problem with a full Windows event log causing the mxODBC Connect Server not to start
The main_timeout configuration setting in both client and server configurations was split into send_timeout and
receive_timeout for better customization on asymmetric network setups
The default for the client's server_connections configuration option now is the sorted list of connection section names
Changes from 0.9.2 to 0.9.3:
Enhanced the mxODBC Connect client-server performance
substantially
Added optional compression of all network communication (enabled per default)
Fixed a bug related to a missing DLL in the Windows installer of 0.9.2
Fixed a problem with fail-over on SSL-enabled connections
Changes from 0.9.1 to 0.9.2:
Enhanced the SQL Server in the Linux mxODBC Connect Server version
Improved the documentation and clarified a few things