What's New in SQL Server ODBC Driver Version 1.x

• SQL Server 2008 Support Access SQL Server 2008 and SQL Server 2008 Express from Linux and Unix.
• iconv Support Preserve character data integrity across multiple locales.
• AES, 3DES, DES Encryption Keep data transmitted between Linux/Unix and SQL Server secure with industry standard encryption.
• Transport Layer Security The latest and most secure version of SSL.
• SSL Cipher Suites Enforce security policy with SSL cipher suites.
• FIPS 140-2 Encryption Standards Connect Linux and Unix to FIPS 140-2 compliant SQL Server instances.
• NTLMv2 Authentication Protect sensitive, high-value data in your SQL Server database from unauthorised access.
• UTF-8 Support Preserve the integrity of Unicode SQL Server data on Linux and Unix systems.
• MARS Over SSL The only data access solution that lets Linux/Unix users take advantage of MARS now makes this important SQL Server feature available over an encrypted connection.
• Domain Discovery Reduce the amount of configuration needed to authorise Linux/Unix to SQL Server connections through Windows authentication.
• Microsoft SQL Server Driver Extensions Control SQL Server functionality from Linux/Unix with Microsoft’s recommended configuration methods.

SQL Server 2008 Support

The SQL Server ODBC driver supports SQL Server 2008 and SQL Server 2008 Express, and can connect 32-bit and 64-bit Unix (AIX, HP-UX and Solaris) and Linux (CentOS, Debian GNU/Linux, Fedora, Kubuntu/Ubuntu, Mandrake/Mandriva, OpenSUSE/SUSE, RedHat, RedHat Enterprise Linux (RHEL), Slackware and so on) machines to SQL Server 2008 (Katmai) databases. In solutions built around the SQL Server ODBC driver therefore, the database platform does not dictate the client application platform.

For example, you want to analyse customer location information visually and are considering using SQL Server 2008 as your database backend because it supports spatial data. You use Ruby on Rails on Linux to develop your client applications for its ease of use and accelerated application development time on this cost-effective platform. The SQL Server ODBC driver transparently integrates SQL Server features with applications running on Linux/Unix such as Ruby on Rails. This means that you can evaluate SQL Server solely on the benefits its features brings to your organisation, the database platform does not have any implications for your choice of client application platform.

Our driver’s SQL Server 2008 feature support includes:

• SQL Server 2008 Data Types Our driver enables Linux/Unix applications to take advantage of the new SQL Server 2008 data types:
  • GEOGRAPHY and GEOMETRY Enables spatial data that describes physical locations to be stored in a SQL Server 2008 database. The GEOGRAPHY data type lets you represent three-dimensional (round-earth) data such as as GPS latitude and longitude coordinates. The GEOMETRY data type lets you represent two-dimensional (flat-earth) data such as as points on a map.

    Applications can use spatial data for a variety of tasks: retrieving the name of the salesperson who lives closest to a particular customer; calculating the optimal route for a pallet pick run; keeping track of the current locations of the participants in a multi-player game; predicting the arrival times of buses and trains; performing CAD modelling of an interior space.

    The SQL Server ODBC driver enables organisations to gain competitive advantage through the analysis of spatial data stored in SQL Server while protecting their investment in Linux/Unix applications. For example, the 64-bit SQL Server ODBC driver can enable a 64-bit CAD application on Linux to increase design precision by overlaying detailed spatial data stored in SQL Server. The performance benefits for the memory-intensive CAD application are retained on this cost-effective 64-bit platform, and legal and customer obligations are met, through improved design accuracy.

  • DATE, TIME, DATETIME2 and DATETIMEOFFSET The DATE and TIME data types allow a date to be stored without a time value and vice versa, enabling you to work with just the point in time portion that you need. For example, you can now store information about a particular time that does not relate to a specific date. The DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as 'plus 5 hours'. This helps organisations whose business is distributed globally develop applications that can handle time zone conversion between different locales. For example, an application might use the DATETIMEOFFSET data type to report whether a service that is open from 9:00 A.M. through 5:00 P.M. in one time zone is currently open for business, whatever the local time in the location the application is being run from.
  • FILESTREAM Enables binary large object (BLOB) data to be stored externally on the Windows file system instead of in the database file. FILESTREAM data is still under the control of SQL Server, and so you can retain database engine functionality such as backing up, restoring and controlling access to the data while taking advantage of low cost hardware to store the data. The SQL Server ODBC driver’s FILESTREAM support enables you to use SELECT, INSERT, UPDATE and DELETE statements in your Linux/Unix applications to manipulate binary data stored on remote Windows file systems.
  • HIERARCHYID Enables tree-like structures, such as organisational hierarchies, to be represented in the database without the need for complex SQL. The HIERARCHYID data type and its associated methods make it easy to find out a database record’s position in the hierarchy. For example, in an employees table, the data type’s methods can be used to find out who a particular employee reports to.
• SQL Server 2008 Security The SQL Server ODBC driver’s Windows authentication support means that using the driver to integrate Linux/Unix with SQL Server 2008 will not compromise security best practices defined and enforced by SQL Server 2008’s Policy-Based Management. Because the SQL Server ODBC driver lets you access SQL Server from Linux/Unix by using this best practice login mode, SQL Server authentication support is not a prerequisite for our driver. Your SQL Server instance does not therefore have to vulnerable to attacks associated with this legacy authentication mode.

  Transparent Data Encryption (TDE) is a SQL Server 2008 feature that enables data to be stored securely by encrypting the database files. If the disks that contain the files become compromised, data in those files is protected because that data can only be de-encrypted by authorised personnel who have the correct certificate. However, TDE does not encrypt data as it is transmitted to and from SQL Server over the network. To do this, and add to your defense-in-depth security strategy, use the SQL Server ODBC driver’s SSL support to secure the network connection. The driver’s SSL Cipher Suite support means that you can specify the same encryption algorithm and strength to protect data in transit as you do to protect your database files with TDE.

  The SQL Server ODBC driver enables you to identify your Linux/Unix applications to SQL Server by setting the Appname data source attribute. You can therefore use the SQL Server auditing mechanisms, including the SQL Server 2008 Audit Object, to check that users are using the applications they are supposed to be using when accessing SQL Server from Linux/Unix.

• SQL Server 2008 Native Client Attributes To ensure the SQL Server ODBC driver keeps up to date with Microsoft’s SQL Server Native Client and can replicate that Windows-only software’s functionality on Linux/Unix, our driver supports the new SQL Server 2008 Native Client attribute SQL_SS_TABLE.

Whatever your organisation’s plans for SQL Server 2008 migration are, you can be confident that the Easysoft ODBC-SQL Server Driver is a future proof solution that integrates SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2008 with Unix and Linux.

iconv Support

To help support the character sets in use throughout our worldwide user base, the SQL Server ODBC driver can now use iconv to convert character data from the encoding used by SQL Server to the various encodings used by Linux/Unix client machines (UTF-8, EUC-CN, EUC-JP, EUC-KR, EUC-TW and so on). This feature can help solve the data integrity issues (data loss/corruption) that face organisations who want to increase revenue by developing solutions for international markets.

To use the SQL Server ODBC driver’s conversion mechanism, specify the encoding used by your client machine with the Client_CSet data source attribute. The SQL Server ODBC driver converts character data to the encoding you specify (assuming the characters are present in the target encoding). Do this if you experience character loss or corruption in your application, for example, when the expected characters are replaced by question marks (?).

The SQL Server ODBC driver’s iconv support means that you can build flexible solutions around the driver that will always have a mechanism for converting data to the client system’s encoding, whatever country the solution is deployed in.

See Also

• Client_CSet attribute in the SQL Server ODBC driver User’s Guide

AES, 3DES and DES Encryption

The SQL Server ODBC driver now lets you protect the confidentiality of SQL Server data with the following encryption algorithms: Advanced Encryption Standard (AES), Data Encryption Standard (DES) and Triple-DES (3DES).

• AES encryption AES is the current U.S. government encryption standard. AES is so secure that it is used to protect information classified by the U.S government as TOP SECRET. Assuming the maximum level of encryption (256-bit) is used, cracking AES-encrypted data using the only known cryptanalysis attack (brute force) is currently impossible.
• DES and 3DES encryption DES is the former U.S. government encryption standard. 3DES is a more secure variant of DES, which encrypts data with three passes of the DES algorithm. 3DES support is a prerequisite for access to SQL Server instances running in FIPS 140-2 compliance mode.

Encryption protects data privacy by rendering it unreadable to all but the intended recipient, who has the ability to decrypt the data.

Using encryption to protect the privacy of sensitive, high value data as it is sent across the network is a SQL Server security best practice.

The SQL Server ODBC driver also supports Rivest Cipher 4 (RC4) encryption.

From its initial release, the SQL Server ODBC driver’s built-in support for encryption has protected our security conscious customer’s data without requiring any change to client applications. The driver’s improved encryption support guarantees conformance with security policies that are obligated to use older encryption standards such as DES while retaining the potential to integrate with the most recent encryption standard. You can therefore be confident that choosing the SQL Server ODBC driver to solve your Linux to SQL Server connectivity issues will not compromise current or future security obligations. The SQL Server ODBC driver’s comprehensive support for encryption (and data integrity) standards means that it will not introduce a weak link into your security solution and can safely be deployed in the context of a defence in depth security strategy.

See Also

• Cypher attribute in the SQL Server ODBC driver User’s Guide

Transport Layer Security

The SQL Server ODBC driver can secure the connection to SQL Server instances by using both Secure Sockets Layer (SSL) and Transport Layer Security (TLS). TLS is the latest version of SSL. TLS is more secure than SSL because it uses stronger hash function techniques (Hashed Message Authentication Code or HMAC) to ensure that data retains its integrity from the time it is sent to the time is is received.

The stronger the data integrity mechanism, the more protection your SQL Server data has against attacks such as tampering, interception and forgery. For example, without data integrity protection, data is vulnerable to:

• Data modification attacks: Data is intercepted in transit, altered and retransmitted, for example, a £100 bank deposit is intercepted, changed to £10,000 and retransmitted.
• Data replay attacks: Data is captured and then repeatedly retransmitted, for example, a bank withdrawal of £100 is intercepted and retransmitted ten times so that the final withdrawal amount equals £1000.

SSL Cipher Suites

By default, the SQL Server ODBC driver and SQL Server machine automatically handle the choice of encryption and data integrity algorithm used to protect data exchanged between the machines. If you have a specific security policy at your site, you can configure the SQL Server ODBC driver to request an SSL cipher suite that corresponds with your security requirements. For example, you can use a cipher suite to ensure the SSL connection to the SQL Server machine meets security policy requirements regarding acceptable encryption algorithms.

An SSL cipher suite is a set of authentication, encryption and data integrity algorithms used to protect data exchanged between machines.

The SQL Server ODBC driver’s support for SSL cipher suites means that you can now use different encryption algorithms to protect different database connections. For example, you need to protect your most sensitive data with Triple-DES encryption; because of the performance overhead associated with Triple-DES, your security policy also permits you protect the privacy of less sensitive data with DES. To meet your security-related obligations while retaining control over the trade-off between security level and performance, the SQL Server ODBC driver allows you to choose Triple-DES encryption for connections to some databases and DES encryption for others.

By specifying SSL settings with a cipher suite, rather than letting the driver and SQL Server machine negotiate the settings, you can apply a consistent security policy across different SQL Server machines. For example, when connecting to SQL Server instances running on different Windows platforms, the negotiated SSL settings may not be the same on all machines or consistently satisfy your security requirements.

See Also

• Cypher attribute in the SQL Server ODBC driver User’s Guide

FIPS 140-2 Encryption Standards

The SQL Server ODBC driver supports the Federal Information Processing Standard (FIPS) approved algorithms (AES, DES, 3DES and SHA-1) and can connect Linux and Unix clients to SQL Server instances running in FIPS 140-2 compliance mode.

FIPS 140-2 is a U.S. government standard, published by the National Institute of Standards and Technology (NIST), that defines security requirements for cryptographic software. Some U.S. government agencies can only purchase FIPS 140-2 certified products. Many private companies are required by U.S. government regulation to use FIPS 140-2 certified products. Organisations and companies who do not have to use FIPS 140-2 certified products to address regulatory compliance issues still value the certification, as it is carried out by NIST, an independent third party.

AES, which the SQL Server ODBC driver also supports, is another FIPS standard (FIPS-197).

Because the SQL Server ODBC driver supports government-approved encryption and data integrity standards, it can be deployed in environments where security requirements are defined by regulatory compliance obligations, such as FIPS 140-2 conformance.

See Also

• Cypher attribute in the SQL Server ODBC driver User’s Guide

NTLMv2 Authentication

The SQL Server ODBC driver supports Windows Authentication, a SQL Server authentication mode that allows users to log into SQL Server with their Windows accounts. Because it centralises authentication (password checking happens in one place; one system to define password strength, expiration and account lockout policy), Windows Authentication is Microsoft’s recommended authentication mode. Because no passwords are sent across the network during the authentication process, Windows Authentication is secure and a SQL Server security best practice.

NT LAN Manager version 2 (NTLMv2) is a challenge/response authentication protocol supported by Windows. When the SQL Server ODBC driver attempts to authenticate a Windows user, Windows "challenges" the driver to do a complex calculation that proves it has access to the user’s password and "respond" with the results. Windows does the same calculation. If the two calculations agree, Windows authenticates the user, who can then access SQL Server. Because of the strength of its challenge/response mechanism, NTLMv2 is more secure than older versions of the protocol and provides a greater defence against attempts to crack passwords by capturing the challenge/response.

By simplifying password management, Windows Authentication reduces the support burden and IT administration costs associated with managing user accounts. NTLMv2 enhances the security of this authentication mode, protecting sensitive, high-value data in your SQL Server database from unauthorised access.

The SQL Server ODBC driver supports both NTLMv2 and its predecessor NTLM.

The SQL Server ODBC driver also supports SQL Server authentication, which enables users to access SQL Server by using a SQL Server login account. To prevent a weakness inherent in this authentication mode from being exploited, the SQL Server ODBC driver can also encrypt the SQL Server login ID and password as they are passed over the network. Doing this is a SQL Server security best practice.

See Also

• NTLMv2 attribute in the SQL Server ODBC driver User’s Guide

UTF-8 Support

SQL Server supports the Unicode standard, enabling the database to store and process multilingual data. SQL Server uses the UCS-2 encoding scheme to store Unicode data. An encoding scheme defines how Unicode data is stored as a sequence of bytes. There are multiple Unicode encoding schemes.

The SQL Server ODBC driver conforms with the ODBC specification’s requirements for a Unicode ODBC driver. This means that the driver supports Unicode data types and Unicode versions of the ODBC API, which accept and return Unicode data. The Unicode encoding scheme that ODBC expects is UCS-2, which is the same as SQL Server.

However, many Linux and Unix systems use a different encoding scheme to SQL Server and ODBC: UTF-8. This means that the ODBC mechanism for working with Unicode data is unsuitable and unless the application is able to convert between encoding schemes, data corruption may occur.

To enable Linux and Unix applications to work with Unicode SQL Server data without loss or corruption, the SQL Server ODBC driver can now convert UCS-2 encoded data to UTF-8.

This screen shot shows how the SQL Server ODBC driver’s UCS-2 to UTF-8 conversion mechanism enables a popular Linux application, OpenOffice.org, to process Unicode data stored in the Northwind database. Before enabling UTF-8 support in the driver, certain characters are replaced with ? symbols, indicating that the data has been lost because of incompatible encoding schemes on the client and server machines.

See Also

• ConvToUtf attribute in the SQL Server ODBC driver User’s Guide

MARS Over SSL

We believe that there should be no differentiation between Windows and Linux/Unix users in terms of SQL Server feature availability. As part of this commitment, the SQL Server ODBC driver has supported Multiple Active Results Sets (MARS) since the driver’s initial release. Our driver is the only Linux/Unix SQL Server ODBC driver to support MARS, and therefore the only solution that lets Linux/Unix users take advantage of this feature.

MARS is a SQL Server 2005 feature that simplifies application design by allowing multiple operations to be performed on a single connection. For example, applications can execute other statements (such as INSERT, UPDATE and DELETE statements) while results sets are open. This removes the need for applications to deal with "connection busy" errors or use previous workarounds such as multiple connections or server-side cursors, both potentially expensive operations that can hurt performance.

As part of our ongoing commitment to meeting data access requirements without compromising our customer’s current or future security plans, the SSL version of the SQL Server ODBC driver now supports MARS. This important SQL Server feature is now available to Linux/Unix users over an encrypted network connection therefore.

Domain Discovery

The SQL Server ODBC driver enables Linux and Unix users to access SQL Server by using a Windows domain user account. To authenticate a user by using a domain account, the SQL Server ODBC driver needs to know which domain the account belongs to. Previously, this information was supplied as a SQL Server ODBC driver configuration setting. The SQL Server ODBC driver can now detect the domain automatically.

Automatic domain discovery reduces the amount of configuration needed to connect your Linux and Unix applications to SQL Server. The facility also ensures that any changes you make to your domain structure will not require SQL Server driver reconfiguration on your Linux and Unix clients.

Microsoft SQL Server Driver Extensions

To align our driver with Microsoft’s SQL Native Client driver and ensure that SQL Server features are available to Linux and Unix applications, the SQL Server ODBC driver now supports these Microsoft driver extensions:

• SQL_COPT_SS_PRESERVE_CURSORS This attribute reports and controls how cursors behave at the end of a transaction in manual-commit mode. Using SQL_COPT_SS_PRESERVE_CURSORS is the only method that Microsoft support for client machines to control this cursor behaviour. The SQL Server ODBC driver’s support for SQL_COPT_SS_PRESERVE_CURSORS is a prerequisite for manipulating this functionality from Linux/Unix machines therefore.
• SQL_COPT_SS_INTEGRATED_SECURITY This connection attribute lets you control which authentication mode should be used to validate a SQL Server login. SQL_COPT_SS_INTEGRATED_SECURITY enables this functionality to be manipulated programmatically through SQLSetConnectAttr, supplementing existing data source/connection string attributes for specifying the authentication mode. The facility to specify the authentication mode gives the SQL Server ODBC driver the flexibility to support both Microsoft’s preferred and legacy SQL Server authentication mechanisms: Windows Authentication and SQL Server Authentication.
• SQL_SOPT_SS_DEFER_PREPARE Prepared execution provides an efficient way to execute a SQL statement multiple times. Prepared execution separates SQL statement processing into two separate stages: compilation (or preparation) and execution. A statement does not have to be compiled each time it is executed therefore, reducing the overhead associated with this operation. SQL Server 2000 and later provides native support for prepared execution. Unlike ODBC’s prepared execution model, the default behaviour for this native implementation is to defer statement preparation until execution. Any errors in the statement being prepared are not known until the statement is executed. Some applications may not be able to handle SQL errors if they are returned at the point when the statement is executed rather than prepared. SQL_SOPT_SS_DEFER_PREPARE provides a workaround for applications that expect the ODBC behaviour for prepared execution. The attribute allows you to control whether a statement is prepared immediately (errors are returned at this stage) or deferred until execution.