What's New in SQL Server ODBC Driver Version 1.1AES, 3DES and DES EncryptionThe 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 AlsoTransport Layer SecurityThe 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 SuitesBy 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 AlsoFIPS 140-2 Encryption StandardsThe 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 AlsoNTLMv2 AuthenticationThe 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 AlsoSQL Server 2008 ReadyThe SQL Server ODBC driver supports SQL Server 2008, and can therefore connect Unix (AIX, HP-UX and Solaris) and Linux (CentOS, Debian, Fedora, Mandrake, RedHat, SUSE and Ubuntu—Edgy Eft/Feisty Fawn/Gutsy Gibbon/Hardy Heron) machines to SQL Server 2008 (Katmai) databases. Our driver enables Linux/Unix applications to take advantage of the new SQL Server 2008 data types: spatial (GEOGRAPHY and GEOMETRY), date/time (DATE, TIME, DATETIMEOFFSET and DATETIME2) and FILESTREAM. 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. UTF-8 SupportSQL 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 AlsoMARS Over SSLWe 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 DiscoveryThe 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 ExtensionsTo 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.
| 
