|
| ||||||||||||||||||
 |  |  |  |
This section contains extra information relating to the deployment of the Easysoft ODBC-SQL Server Driver.
The Easysoft ODBC-SQL Server Driver complies with the ODBC 3.51 specification.
All ODBC 3.51 calls are supported.
The Easysoft ODBC-SQL Server Driver supports FORWARD_ONLY, KEYSET_DRIVEN, DYNAMIC and STATIC cursors.
The Easysoft ODBC-SQL Server Driver supports the following SQL Server data types:
| varchar(max), nvarchar(max), varbinary(max) and xml are SQL Server 2005 data types. |
SQL Server treats identity as an attribute, whereas ODBC treats it as a data type. To resolve this mismatch, SQLGetTypeInfo returns the data types: int identity, smallint identity, tinyint identity, decimal() identity, and numeric() identity. The SQLGetTypeInfo result set column AUTO_UNIQUE_VALUE reports the value TRUE for these data types.
For varchar, nvarchar and varbinary data types, the Easysoft ODBC-SQL Server Driver continues to report 8000, 4000 and 8000 for the COLUMN_SIZE value, even though it is actually unlimited. This is to ensure backward compatibility.
For the xml data type, the Easysoft ODBC-SQL Server Driver reports SQL_SS_LENGTH_UNLIMITED for COLUMN_SIZE to denote unlimited size.
The Easysoft ODBC-SQL Server Driver supports a number of driver-specific ODBC connection attributes. These are defined in /usr/local/easysoft/sqlserver/include/sqlncli.h. The Easysoft ODBC-SQL Server Driver may require that the attribute be set prior to connection, or it may ignore the attribute if it is already set:
Whether to use Windows or SQL Server authentication to validate the connection.
| Default. Use SQL Server Authentication to authenticate the connection. | |
Windows authentication examples:
/* Use Windows Authentication to validate the connection */
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,
/* Specify a Windows user name and password. mywindowsuser belongs to the */
/* same domain as the SQL Server machine, so there is no need to specify it */
/* - theEasysoft ODBC-SQL Server Driver will automatically detect the domain */
SQLDriverConnect(dbc, NULL, "DRIVER={Easysoft ODBC-SQL Server};SERVER=myserver\\SQLEXPRESS;UID=mywindowsuser;PWD=mywindowspassword",
SQL_NTS, outstr, sizeof(outstr), &outstrlen,
SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,
SQLDriverConnect(dbc, NULL, "DSN=MYDSN",
SQL_NTS, outstr, sizeof(outstr), &outstrlen,
The Easysoft ODBC-SQL Server Driver data source specified in the SQLDriverConnect call needs to connect with a Windows user name and password. For example:
Driver = Easysoft ODBC-SQL Server Driver
Whether the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.
(You can also configure this behaviour by using the PreserveCursor data source attribute. For more information, see Attribute Fields.)
| Default. Cursors are closed when a transaction is committed or rolled back by using SQLEndTran. | |
| Cursors are preserved when a transaction is committed or rolled back by using SQLEndTran. |
This C code sample uses SQL_COPT_SS_PRESERVE_CURSORS to preserve a cursor following a positioned update:
SQLHSTMT stmt_select, stmt_update;
SQLCHAR last_name[ 64 ], first_name[ 64 ], cursor_name[ 64 ], update_sql[ 64 ];
SQLSMALLINT reports_to, cursor_len;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Enable manual-commit mode */
SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,
/* Preserve cursors when transactions are committed/rolled */
/* back. Alternatively, add PreserveCursor = Yes to the DSN */
SQLSetConnectAttr(dbc, SQL_COPT_SS_PRESERVE_CURSORS,
/* Connect to Northwind through the sample DSN */
"DSN=SQLSERVER_SAMPLE;Database=Northwind",
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate the statement handles */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt_select);
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt_update);
/* Create dynamic, updateable cursor for the positioned update */
SQLSetStmtAttr(stmt_select, SQL_ATTR_CURSOR_TYPE,
(void *) SQL_CURSOR_DYNAMIC, 0);
SQLSetStmtAttr(stmt_select, SQL_ATTR_CONCURRENCY,
(void *) SQL_CONCUR_ROWVER, 0);
"SELECT LastName, FirstName, ReportsTo FROM Employees FOR UPDATE",
SQLBindCol(stmt_select, 1, SQL_C_CHAR, last_name,
sizeof(last_name), &indicator[ 0 ]);
SQLBindCol(stmt_select, 2, SQL_C_CHAR, first_name,
sizeof(first_name), &indicator[ 1 ]);
SQLBindCol(stmt_select, 3, SQL_INTEGER, &reports_to,
/* Get the cursor name for use in the update statement */
SQLGetCursorName(stmt_select, cursor_name,
sizeof(cursor_name), &cursor_len);
/* Move through the result set until the cursor is positioned */
/* on the row for Robert King */
while ((ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) &&
(strcmp(first_name, "Robert") != 0 && strcmp(last_name, "King") != 0));
/* Positioned update of Robert King's line manager */
"UPDATE Employees SET ReportsTo = 2 WHERE CURRENT OF %s",
SQLExecDirect(stmt_update, update_sql, SQL_NTS);
SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
/* The cursor is still open, because SQL_COPT_SS_PRESERVE_CURSORS is set to */
/* SQL_PC_ON. Reposition the cursor and fetch the updated record. */
SQLFetchScroll(stmt_select, SQL_FETCH_PRIOR, 0 );
printf("%s %s reports to employee ID: %ld\n", first_name,
SQLCloseCursor(stmt_update); /* Close cursor */
SQLDisconnect(dbc); /* Disconnect from driver */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
Sets the SQL Server 2005 snapshot isolation attribute.
| Indicates that from one transaction you cannot see changes made in other transactions and that you cannot see changes even when requerying. |
For more information about snapshot isolation, see Snapshot Isolation.
The Easysoft ODBC-SQL Server Driver supports the following driver-specific statement attributes:
Whether the Easysoft ODBC-SQL Server Driver defers query preparation until execution time.
The attribute is only relevant to SQLServer 2000 and later.
| Default. After calling SQLPrepare, the Easysoft ODBC-SQL Server Driver defers statement preparation until SQLExecute or SQLDescribeCol is executed. Any errors in the statement are not known until these functions are executed. | |
| The Easysoft ODBC-SQL Server Driver prepares the statement as soon as SQLPrepare is executed. |
In this C code sample, deferred statement preparation is disabled. The invalid SQL statement the sample contains therefore fails as soon as SQLPrepare is called.
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate the statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Do not defer query preparation. Prepare the statement */
/* as soon as SQLPrepare is executed */
SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE,
ret = SQLPrepare(stmt, "select * from non_existant_table",
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
if (ret != SQL_SUCCESS || ret != SQL_SUCCESS_WITH_INFO) {
/* Because the invalid statement was prepared immediately, */
/* SQLPrepare (below) rather than SQLExecute returns the error. */
extract_error("SQLExecute", stmt, SQL_HANDLE_STMT);
/* The statement is invalid and so cannot be prepared. */
/* See "ODBC from C Tutorial Part 1", on the Easysoft web */
/* site for a definition of extract_error(). */
extract_error("SQLPrepare", stmt, SQL_HANDLE_STMT);
Note that if the statement contains parameters, SQLPrepare returns SQL_SUCCESS even if the statement is invalid. Any errors in the statement are not known until the statement is executed or SQLDescribeParam is called. This behaviour happens regardless of how SQL_SOPT_SS_DEFER_PREPARE is set.
For example, in the following code extract, SQLPrepare succeeds even though the parameterised statement is invalid:
/* Do not defer query preparation. */
SQLSetStmtAttr(stmt, SQL_SOPT_SS_DEFER_PREPARE,
/* This statement is invalid. The parameter marker for the */
/* status column is missing. However, SQLPrepare still succeeds */
ret = SQLPrepare(stmt, "INSERT INTO Orders (OrderId, CustId, OpenDate, SalesPerson, Status) VALUES (?, ?, ?, ?)", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
/* The errors in the statement are not known until this point */
The Easysoft ODBC-SQL Server Driver is a Unicode driver that supports the Unicode version (with suffix "W") of the ODBC calls it implements. Using a Unicode driver with a Unicode application removes the need for the driver manager to map Unicode functions and data types to ANSI. This results in better performance and removes the restrictions inherent in the Unicode to ANSI mappings.
The Easysoft ODBC-SQL Server Driver supports the following SQL Server Unicode data types:
The Easysoft ODBC-SQL Server Driver distribution includes a ANSI-only version of the driver that does not support the Unicode ODBC APIs. This version of the driver should not normally be needed and is only provided for use with old and non-conformant Driver Managers.
If you do need to use the ANSI-only driver, first install the driver under unixODBC. To do this, open /etc/odbcinst.ini in a text editor. Copy the section for the standard driver and paste it below the existing section. Change the [driver name] in the new section. In the Driver entry, suffix the library name with _a . For example:
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
# Install the ANSI driver by adding a new odbcinst.ini section.
# This example odbcinst.ini extractis from a Linux installation
# of the Easysoft ODBC-SQL Server Driver.
[Easysoft ODBC-SQL Server ANSI APIs]
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so
Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
In your data source, specify the new driver name in the Driver entry. For example:
Driver =Easysoft ODBC-SQL Server ANSI APIs
SQL Server 2005 provides an xml data type for storing XML documents in table columns or Transact-SQL variables.
The Easysoft ODBC-SQL Server Driver supports the SQL Server 2005 xml data type and its associated methods: query(), value(), exist(), modify() and nodes().
The query() method lets you use an XML Query (XQuery) definition to search XML data stored in columns and variables of the xml type. The XQuery language is a World Wide Web Consortium (W3C) standard for retrieving or defining a set of XML nodes that meet a set of criteria.
In the following example, an XQuery is specified against the Instructions column in the ProductModel table. The Instructions column data type is xml and therefore exposes the query() method. The ProductModel table is contained in the SQL Server sample database AdventureWorks.
SELECT Instructions.query('declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/AWMI:root/AWMI:Location[@LocationID=10]
The XQuery includes a namespace declaration, declare namespace AWMI=..., and a query expression, /AWMI:root/AWMI:Location[@LocationID=10]. The namespace declaration identifies the XML namespace associated with elements in the Instructions column. The query expression retrieves only those records for which the LocationID attribute value is 10:
<AWMI:Location xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5"...LocationID="10">
This second example uses the query() method to construct an XML element named <Product>. The <Product> element has a ProductModelID attribute, in which the ProductModelID attribute value is retrieved from the database.
SELECT CatalogDescription.query(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; <Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" /> ') as Result
The exist() method lets you filter XML data. For example, add the following WHERE clause to the previous query to find only records that contain a <Warranty> element.
where CatalogDescription.exist(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; /PD:ProductDescription/PD:Features/wm:Warranty ') = 1
Microsoft SQL Server 2005 provides the max specifier, which expands the storage capabilities of the varchar, nvarchar, and varbinary data types to allow storage of values as large as 2 gigabytes (GB). varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.
The Easysoft ODBC-SQL Server Driver exposes the varchar(max), varbinary(max) and nvarchar(max) types as SQL_VARCHAR, SQL_VARBINARY, and SQL_WVARCHAR in ODBC API functions that accept or return ODBC SQL data types.
When reporting the maximum size of a column, the Easysoft ODBC-SQL Server Driver will report either:
SQL Server 2005 provides a new transaction isolation level: snapshot. A snapshot transaction does not block updates executed by another transaction and can continue to read (but not update) the version of the data that existed when it started. Snapshot isolation is also called row versioning because SQL Server keeps "versions" of rows that are being changed: the original version and the version being changed.
Snapshot isolation is enabled for a database when the ALLOW_SNAPSHOT_ISOLATION database option is set to ON. For example, to enable snapshot isolation for the pubs sample database:
ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON
By default, this database option is set to OFF.
The Easysoft ODBC-SQL Server Driver supports snapshot isolation through the SQLSetConnectAttr and SQLGetInfo ODBC API functions.
For snapshot transactions, ODBC applications need to call SQLSetConnectAttr and set the SQL_COPT_SS_TXN_ISOLATION attribute to SQL_TXN_SS_SNAPSHOT. SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level. For example:
SQLSetConnectAttr(dbc, SQL_COPT_SS_TXN_ISOLATION, (SQLPOINTER *)SQL_TXN_SS_SNAPSHOT, 0);
The SQLGetInfo function supports the SQL_TXN_SS_SNAPSHOT value, which has been added to the SQL_TXN_ISOLATION_OPTION info type.
The SQL_COPT_SS_TXN_ISOLATION and SQL_TXN_SS_SNAPSHOT attributes are Easysoft ODBC-SQL Server Driver Driver-specific ODBC extensions. To use these attributes, ODBC applications need to include the sqlncli.h header file. sqlncli.h is installed in /usr/local/easysoft/sqlserver/include.
Users are granted access to SQL Server instances through a SQL Server login. SQL Server provides two ways to authenticate SQL Server logins: Windows Authentication (also known as trusted connections) and SQL Server Authentication.
Windows Authentication allows users to connect to SQL Server by using their Windows user account. SQL Server uses the Windows security system to validate these trusted connections.
SQL Server authentication uses passwords stored in SQL Server to validate the connection.
Windows Authentication is Microsoft's recommended SQL Server authentication mode because it provides the following advantages:
The Easysoft ODBC-SQL Server Driver supports both Windows Authentication and SQL Server Authentication. You specify the SQL Server login name and password with the User and Password data source attributes or the UID and PWD connection string attributes.
The Easysoft ODBC-SQL Server Driver asks SQL Server to use Windows Authentication to validate the connection if:
The Easysoft ODBC-SQL Server Driver passes the domain name, the user name and password to SQL Server in an encrypted form. This process involves both the Data Encryption Standard (DES) encryption method and the MD4 hashing algorithm. The Easysoft ODBC-SQL Server Driver uses open source code for both these methods. The code is distributed under the terms of the GNU Lesser General Public License (LGPL). To read the license, see /usr/local/easysoft/sqlserver/crypt/COPYING.
To comply with the terms of the LGPL, the encryption functions are not included in the main Easysoft ODBC-SQL Server Driver library. Instead, they are provided in the shared library file /usr/local/easysoft/lib/libestdscrypt.so. The Easysoft ODBC-SQL Server Driver distribution includes the source files for this library. The source files are installed in /usr/local/easysoft/sqlserver/crypt. The supplied Makefile will build the library on your Easysoft ODBC-SQL Server Driver platform.
If the User attribute value does not contain a backslash, the Easysoft ODBC-SQL Server Driver asks SQL Server to use SQL Server Authentication to validate the connection. The Easysoft ODBC-SQL Server Driver sends the password to SQL Server in an encrypted form, although the encryption is less strong then that used for trusted connections. The SQL Server user name is sent in plain text.
SQL Server 2000 and SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application.
The Easysoft ODBC-SQL Server Driver with SSL Support lets Linux and Unix applications access SQL Server 2000 and 2005 over an encrypted connection. The SSL version of driver is included in the Easysoft ODBC-SQL Server Driver distribution and should be used instead of the standard Easysoft SQL Server driver whenever an SSL connection is required.
Read this topic if you need to connect to a SQL Server instance over an encrypted connection. Database administrators should refer first to Configuring and Testing SSL Encryption for information about setting up SSL encryption on the client and SQL Server machine.
Before following the steps in this topic, contact your database administrator for the following information:
To access SQL Server over an encrypted connection
1. In /etc/odbc.ini, find the SQLSERVER_SAMPLE_SSL data source.
2. Edit the data source to connect to your SQL Server instance. For example:
Driver = Easysoft ODBC-SQL Server SSL
Description = Easysoft SQL Server ODBC driver
Server = MYSQLSERVERMACHINE\MYINSTANCE
For more information about configuring ODBC data sources, see Setting Up Data Sources.
3. If SSL encryption is enabled on the SQL Server instance, set the Encrypt attribute to No, and then skip to step 5. Otherwise, skip this step.
5. Use isql to test the data source. For example:
cd /usr/local/easysoft/unixODBC/bin.
./isql -v SQLSERVER_SAMPLE_SSL
Refer to this section if you are a database administrator who needs to configure and test the Easysoft ODBC-SQL Server Driver with SSL Support. The section shows you how to configure the driver to request an encrypted connection and verify that data is encrypted.
The section also contains information about setting up SSL encryption on the SQL Server machine. This information is intended to supplement rather than replace the Microsoft SQL Server documentation.
Before you can access SQL Server 2000 over an encrypted connection, an SSL certificate needs to be installed on the SQL Server machine. SQL Server 2005 can use an SSL certificate from a trusted CA if available or generate a self-signed certificate.
| Even though SQL 2005 can make encryption available without an installed SSL certificate, Microsoft recommend using a certificate signed by a trusted authority whenever possible. SSL connections that are encrypted with a self-signed certificate protect against packet sniffing but do not protect against man-in-the-middle attacks. In a man-in the-middle attack, attackers route packets through their servers, which sniff the contents as they pass through. |
If an SSL certificate has not yet been installed on the SQL Server machine, obtain a certificate from a certificate vendor that meets the following requirements:
http://support.microsoft.com/kb/318605
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx
Refer to the following Microsoft documentation for installation information:
http://support.microsoft.com/kb/316898
http://msdn.microsoft.com/en-us/library/ms189067.aspx
The following steps show how to check that SQL Server can successfully load the SSL certificate or generate its own certificate.
To check that SSL encryption is available on the SQL Server
3. Check the SQL Server error log (Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG, by default) to verify that SQL Server did not report any errors when it started.
If encryption is enabled on the SQL Server machine, the client machine does not have to trust the CA that signed the SSL certificate used by the instance, and you can skip this section.
If you request encryption from the client machine rather than the SQL Server machine, the Easysoft ODBC-SQL Server Driver with SSL Support must be able to verify the ownership of the certificate used by the SQL Server instance. (Unless the SQL Server 2005 instance is using a self-generated certificate, in which case you should use TrustServerCertificate attribute to bypass the verification process.) If the server certificate was signed by a public or private CA for which the client machine does not have the public key certificate, you must install the public key certificate of the CA that signed the server certificate. If the client machine already has the public key certificate of the CA that signed the server certificate, this step is not necessary.
To install the root CA certificate on the client machine
1. On the SQL Server machine, in the Windows Run dialog box, type:
2. In Microsoft Management Console, on the File Menu, click Add/Remove Snap-in, and then click Add.
3. In the Add Standalone Snap-In dialog box, double-click Certificates. Click Computer account when prompted and then click Next. Click Finish.
4. Click Close and then OK to close the Add/Remove Snap-in dialog boxes.
5. In the Certificates Snap-in, locate the certificate for the CA that signed the SQL Server certificate. For example, if the CA certificate is in the Trusted Root Certificate Authorities store, double-click Trusted Root Certificate Authorities and click Certificates. In the right pane of the console window, right-click the CA certificate, point to All Tasks, and then click Export.
6. Complete the Certificate Export wizard.
When prompted to choose the export format, make sure that you choose Base-64 encoded X.509.
7. Use FTP to copy the exported CA certificate to the client machine from which you want to access SQL Server.
SSL encryption can be enabled either on the SQL Server machine or the client machine. If you do not want to enable encryption globally on the SQL Server machine, you can enable encryption on a per-client basis.
To configure the client to request an encrypted connection
2. On the machine where the Easysoft ODBC-SQL Server Driver with SSL Support is installed, create an ODBC data source to connect to the SQL Server instance.
In your data source, the Driver attribute must be set to Easysoft ODBC-SQL Server SSL.
Set the Encrypt and TrustServerCertificate attributes to No. For example:
Driver = Easysoft ODBC-SQL Server SSL
Server = MYSQLSERVERMACHINE\MYINSTANCE
3. Use an application such as Microsoft Network Monitor, Snort or Ethereal to capture network traffic between this machine and the SQL Server machine.
Using Network Monitor or a network sniffer tool lets you verify that you have successfully made an encrypted connection to the SQL Server machine. When testing the Easysoft driver, we used Network Monitor on the SQL Server machine and Snort on the client machine to capture network traffic.
For information about using Network Monitor, see the Microsoft Knowledge Base article How to capture network traffic with Network Monitor ( http://support.microsoft.com/kb/148942/EN-US/).
4. Use isql to connect to the data source and retrieve some data. For example:
$ cd /usr/local/easysoft/unixodbc/bin
$ ./isql -v SQLSERVER_SSL_CONNECTION
SQL> select * from HumanResources.EmployeePayHistory where EmployeeID = 1
5. In your network sniffer, verify that the data returned by the Easysoft ODBC-SQL Server Driver with SSL Support is not encrypted.
This fragment of example output shows unencrypted data captured on the client machine by running snort -vde:
7. In your data source, set the Encrypt attribute to Yes.
If the CA's public key was already installed on this machine, specify the path to the CA store that contains the public key. For example, CertificateFile = /usr/share/ssl/certs/ca-bundle.crt. If you exported the CA certificate on the SQL Server machine and copied it to this machine, specify the path to the certificate file. For example, CertificateFile = /usr/share/ssl/CA/MyCA.cer. For more information, see Installing the Root CA Certificate.
9. Use isql to connect to the data source and retrieve the same data as you did in step 4.
10. In your network sniffer, verify that the data returned by the Easysoft ODBC-SQL Server Driver with SSL Support is now encrypted.
This example output shows encrypted SQL Server data captured in Snort.
When connecting to SQL Server, the Easysoft driver passes a user name and password to the SQL Server instance. For Windows user names, a domain name is also sent. These authentication details are stored inside a login packet that is transmitted between the Easysoft driver and SQL Server.
SQL Server 2005 will use SSL to encrypt the login packet, if you connect with the Easysoft ODBC-SQL Server Driver with SSL Support. Unless either the client or the server instance requests encryption, the connection is not encrypted beyond the login packet.
The following attributes may be set in the odbc.ini file:
| Whether the client requests an encrypted connection to SQL Server. If you do not want to enable SSL encryption globally on the server, you can enable SSL encryption on a per client basis. To do this, set Encrypt to Yes. Do not enable SSL encryption on both the server and client, use one or the other. | |
| Enables the client to request encryption even when an SSL certificate has not been installed on the SQL Server 2005 machine. If SQL Server 2005 cannot load a valid SSL certificate at startup time, it will generate a self-signed certificate to make encryption available. When the client requests encryption by setting Encrypt to Yes, the Easysoft ODBC-SQL Server Driver with SSL Support tries to validate the server certificate to verify the identity of the server machine. This is impossible to do with a self-signed certificate since it has not been signed by a trusted root authority. Setting TrustServerCertificate to Yes overrides the server validation. If the SQL Server 2005 ForceEncryption option is enabled, the TrustServerCertificate value is ignored.When encryption is enabled on the SQL Server machine, the Easysoft ODBC-SQL Server Driver with SSL Support bypasses the validation of the server certificate. Note that SQL Server 2000 cannot generate a self-signed certificate. SSL encryption is only available if the SQL Server 2000 instance is running on a computer that has a certificate assigned from a public certification authority. | |
| The file that contains the public key certificate of the CA that signed the SQL Server certificate. The CA certificate file must be in base-64 PEM format. If the CA certificate is not installed on your client machine, you need to export the certificate on the SQL Server machine and install it on the client. For more information, see Installing the Root CA Certificate. To load a CA certificate from the root CA certificate store supplied with the OpenSSL distribution, use: CertificateFile = /usr/share/ssl/certs/ca-bundle.crt To load a private CA certificate named MyCA.cer that you copied to /usr/share/ssl/CA, use: | |
| The cypher suite that the Easysoft ODBC-SQL Server Driver with SSL Support will request during the SSL handshake with the SQL Server machine. A cypher suite is a set of authentication, encryption, and data integrity algorithms used to protect data exchanged between machines. During the SSL handshake part of the connection process, the SSL layer in the ODBC driver and the Schannel layer on the SQL Server machine negotiate to decide which cipher suite they will use. To see which cypher suite is being used for a particular connection, enable Easysoft ODBC-SQL Server Driver with SSL Support logging. To do this, include these lines in your ODBC data source: LOGFILE = /tmp/sql-server-driver.log Connect and then examine the driver log file. Look for a log file entry similar to: SSL using cypher 'RC4-MD5 SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=MD5' This entry shows that the ODBC driver and the SQL Server machine negotiated the following cryptographic protection for the connection: | |
| (You can also display the cryptographic settings negotiated during the SSL handshake by enabling Schannel logging. Enable the "Log informational and success events" Schannel logging option to write this information to the Windows Event Viewer logs. For information about how to do this, see http://support.microsoft.com/kb/260729.) Use the Cypher setting, if you want to request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. For example: # Request Triple DES (3DES) for data encryption. # Request Secure Hash Algorithm (SHA) for data # Request Advanced Encryption Standard (AES) for data # encryption. If AES is not available on the server, # Use Secure Hash Algorithm (SHA) to protect data # integrity. Let the SSL layers negotiate which # encryption algorithm to use. If you specify a cypher suite that is not available on the server machine, the Easysoft ODBC-SQL Server Driver with SSL Support returns the error "Required SSL (failed to receive packet)". If you specify a cypher suite that the Easysoft ODBC-SQL Server Driver with SSL Support does not recognise, the driver returns the error "SSL3_CLIENT_HELLO:no ciphers available". | |
| (For a complete list of valid Cypher values, see http://www.openssl.org/docs/apps/ciphers.html.) Note that if you are connecting to a SQL Server instance that is running in FIPS 140-2 compliance mode, the remote Schannel layer will insist that the driver uses the appropriate cypher suite. There is no need to use the Cypher setting in this situation. Federal Information Processing Standard (FIPS) is a U.S. government standard that defines security requirements for cryptographic modules. For more information about SQL Server and FIPS, see http://support.microsoft.com/kb/920995. | |
| The Easysoft ODBC-SQL Server Driver with SSL Support needs a source of unpredictable data to work correctly. Many open source operating systems provide a "randomness device" (/dev/urandom or /dev/random) that serves this purpose. The Easysoft ODBC-SQL Server Driver with SSL Support tries to use /dev/urandom by default and will also try to use /dev/random if /dev/urandom is not available. If the driver is unable to find a suitable randomness device, it will return the error "SSL connection failed in syscall (errno=2, there may be no source of entropy on this system, consult OpenSSL documentation)". On systems without /dev/urandom or /dev/random, the EGD entropy gathering daemon can be used as an alternative source of random data. It provides a socket interface through which entropy (randomness) can be gathered. Use the Entropy attribute to specify the path to the EGD socket. For example, if you create the socket in /etc when you start the EGD daemon (egd.pl /etc/entropy), use : |
Database mirroring is a SQL Server 2005 feature that increases data availability by creating a standby copy of a database. In database mirroring, all updates to a database (the principal database) are automatically copied to a standby database (the mirror database). If the principal database server fails, the mirror database server takes over the role of principal server and brings its copy of the database online as the principal database.
For example, Partner_A and Partner_B are two partner servers, with the principal database initially on Partner_A as principal server, and the mirror database residing on Partner_B as the mirror server. If Partner_A goes offline, the database on Partner_B can fail over to become the current principal database. When Partner_A rejoins the mirroring session, it becomes the mirror server and its database becomes the mirror database.
To establish the initial connection to a mirrored database, a data source needs to supply the current principal server instance (known as the initial partner). Optionally, the data source can also supply the current mirror server instance (known as the failover partner). This setting is used to connect to the mirror server if the initial connection to the principal server fails. The data source must also supply the database name. The Easysoft ODBC-SQL Server Driver will not attempt to failover to the partner database if this is not done.
In the following example data source, the principal server instance for the AdventureWorks database is 123.34.45.56:4724. The database is mirrored on 123.34.45.57:4724.
[SQL Server 2005 Database Mirroring]
Driver = Easysoft ODBC-SQL Server
# The current principal server instance.
# The current mirror server instance. If the initial attempt to
# connect the principal server fails, try to connect to this server.
Failover_Partner = 123.34.45.57:4724
# You must specify the database to be mirrored.
# This login must have permission to access the database on both
# the principal and mirror database server.
For more information about setting up a data source for a mirrored database, see Data Source Attributes for a Mirrored Database.
When attempting to connect, the Easysoft ODBC-SQL Server Driver begins by using the initial partner name. If the specified server instance is available and is the current principal server instance, the connection attempt usually succeeds.
If the connection attempt to the initial partner fails, the Easysoft ODBC-SQL Server Driver tries the failover partner name, if specified. If the failover partner name is not specified, the original connection attempt continues until the network connection times out or an error is returned (just as for a non-mirrored database).
If the Failover_Partner attribute correctly identifies the current principal server, the Easysoft ODBC-SQL Server Driver normally succeeds in opening the initial connection.
This section discusses the ODBC data source attributes that are relevant for connecting to a mirrored database. For information about all Easysoft ODBC-SQL Server Driver data source attributes, see Attribute Fields.
| Use the Server attribute to specify the current principal database server. You can use the following format for the attribute value: machinename[\instancename]. For example: Server=partner_A_host\instance_2 Note that when you specify a machine name, a DNS lookup is necessary to obtain the IP address of the server. In addition, if you specify an instance that is not listening on the default TCP port (1433), a SQL Server Browser query is also required. These lookups and queries can be bypassed by specifying the IP address and port number of the initial partner. This is recommended to minimise the possibility of external delays while connecting to that partner. To specify the IP address and port, the Server attribute takes the following form: Server=ip_address:port (IPv4 format) or Server=ip_address*port (IPv6 format). If you specify an IPv6 address, you also need to set the IPv6 attribute to 1. For example: # IPv6 address (SQL Server 2005 only) | |
| The data source must specify the name of the mirrored database. To do this, use the Database attribute. This is necessary to enable failover attempts by the Easysoft ODBC-SQL Server Driver. | |
| Use the Failover_Partner setting to specify the current mirror database server. If the initial connection to the principal database server fails, the Easysoft ODBC-SQL Server Driver will attempt a connection to the failover partner specified by Failover_Partner. If the specified server is not acting as a failover partner, the connection is refused by the server. If you omit the Failover_Partner setting and the initial partner specified by Server is unavailable, the connection attempt will fail. The Failover_Partner attribute value takes the form machinename[\instancename]. For example: Server=partner_B_host\instance_2 Alternatively, the IP address and port number of the failover partner can be supplied. If the connection attempt to the initial partner fails, the attempt to connect to the failover partner will be then be freed from relying on DNS and SQL Server Browser queries. To find out the current failover partner for a mirrored database, use tdshelper. For information about how to do this, see The Impact of a Stale Failover Partner Name. | |
| The login that you specify with the User attribute must have permission to access the database on the principal and mirror database server. Otherwise, you will be unable to access the database if the principal role switches and the former mirror server offers its database as the principal database. |
When you specify a failover partner with the Failover_Partner attribute, connection attempts are regulated by a connection retry algorithm that is specific to database mirroring. The connection retry algorithm determines the maximum time (the retry time) allotted for opening a connection in a given connection attempt.
If a connection attempt fails or the retry time expires before it succeeds, the Easysoft ODBC-SQL Server Driver tries the other partner. If a connection is not opened by this point, the Easysoft driver alternately tries the initial and failover partner names, until a connection is opened or the login period times out. The default SQL Server login timeout period is 15 seconds.
The retry time is a percentage of the login period. The retry time for a connection attempt is larger in each successive round. In the first round, the retry time for each of the two attempts is 8 percent of the total login period. In each successive round, the retry algorithm increases the maximum retry time by the same amount. For example, the retry times for the first six connection attempts is as follows:
The retry time is calculated by using the following formula:
RetryTime =PreviousRetryTime + ( 0.08 *LoginTimeout )
Where PreviousRetryTime is initially 0. For example:
The database administrator can change the failover partner at any time. Therefore, a failover partner name specified in a data source might be out of date, or stale. For example, consider a failover partner named Partner_B that is replaced by another server instance, Partner_C. If the Easysoft ODBC-SQL Server Driver supplies Partner_B as the failover partner name, that name is stale. When the failover partner name is stale, the connection attempt will fail if the initial partner specified in the data source is unavailable.
To find out the current failover partner for a mirrored database, use tdshelper:
tdshelper -sinitial_partner -pport -uusername -apassword -fdatabase -v
If the principal server instance reports the name of the failover partner, tdshelper displays the partner instance name in the last line of its output. For example:
cd /usr/local/easysoft/sqlserver/bin
./tdshelper -s my_initial_partner -u myuser -a mypassword -f my_mirroreddb -v
tdshelper: connecting to my_initial_partner
tdshelper: successfully opened connection
tdshelper: successfully logged into server with diagnostic records
tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.
tdshelper: diag record 01000:[Easysoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'my_mirroreddb'.
Connection: connected to my_initial_partner as myuser with mypassword, database='my_mirroreddb', partner='my_failover_partner'
Connection failover maintains data availability by allowing an application to connect to a backup SQL Server machine if the primary server is unavailable.
To configure connection failover, specify a primary server and additional fallback servers in your ODBC data source. Do this with the Server attribute. For example:
[SQL Server High Availability]
Driver = Easysoft ODBC-SQL Server
Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583
# This user name and password must be valid for all servers in the list.
By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable (for example, because of a hardware or operating system failure), the Easysoft ODBC-SQL Server Driver will try to connect to next server in the list. Connection attempts continue until a connection is successfully made or until all the database servers in the list have been tried once.
To balance the load between database servers, set the ClientLB attribute to 1. When ClientLB is enabled, the server that the driver initially connects to is chosen at random.
Note that your SQL Server login (as specified by User and Password) needs to be valid on each SQL Server machine in the list. If the Easysoft ODBC-SQL Server Driver is unable to connect because SQL Server rejects the login information, the driver displays an error and does not try to connect to the next server in the list.
For more information about the Server and ClientLB attributes, see Attribute Fields.
Internet Protocol version 6 (IPv6) is a revised version of the Internet Protocol (IP) designed primarily to address growth on the Internet. It is sometimes referred to as Internet Protocol Next Generation (IPng). The current version of IP, IP version 4 (IPv4), has proven to be robust but is over 20 years old and was not designed to support such widespread use as it does today.
Currently, SQL Server 2005 supports IPv6 on Windows XP (Service Pack 2), Windows 2003 and Windows Vista.
On Windows XP and 2003, IPv6 is a separate component. Windows Vista provides an integrated IPv4 and IPv6 implementation known as the Next Generation TCP/IP stack, which is installed and enabled by default.
IPv4 and IPv6 coexist in all Windows IP implementations. Windows Vista will also run in IPv6-only mode, which means the Vista machine will only handle IPv6 traffic and is only assigned IPv6 addresses.
The Easysoft ODBC-SQL Server Driver supports both IPv4 and IPv6. It can therefore be used to connect to:
IPv6 needs to be enabled on the client machine. The procedure for this depends on the client platform. For more information, consult the IPv6 documentation for your system. For Linux systems, consult the http://www.tldp.org/HOWTO/Linux+IPv6-HOWTO/.
To connect to a SQL Server instance that is listening on an IPv6 address, set the IPv6 data source attribute to 1. If your DNS server or hosts file is set up to resolve IPv6 addresses, in the Server attribute value, specify the IPv6-enabled machine's host name. Otherwise, specify its IPv6 address. For example:
Server=myipv6machine\myinstance
Server=ABCD:EF12:0:0:0:0:0:3456\myinstance
The Easysoft ODBC-SQL Server Driver supports normal and compressed IPv6 addresses. Compressed format is a short form that replaces consecutive leading zeros with two colons (::). For example, the IPv6 address shown in the previous example could be replaced with ABCD:EF12::3456.
The Easysoft ODBC-SQL Server Driver also supports the IPv4-mapped IPv6 address format, which is an IPv6 address that holds an embedded IPv4 address. For example, ::FFFF:192.168.19.46.
The Easysoft ODBC-SQL Server Driver is thread safe in accordance with the ODBC 3.51 specification and can safely be used behind threaded applications.
The ODBC calls an application makes can be traced:
An application can turn tracing on in the Driver Manager by using the ODBC API SQLSetConnectAttr (...,SQL_ATTR_TRACE,...).
The trace file name may also be specified with the SQLSetConnectAttr attribute SQL_ATTR_TRACEFILE.
For the unixODBC Driver Manager, add two attributes to the [ODBC] section (create one if none exists) in odbcinst.ini.
Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it), or no tracing information will be produced.
Driver manager trace files show all the ODBC calls applications make, their arguments and return values. Easysoft ODBC-SQL Server Driver driver tracing is specific to the Easysoft driver and is of most use when making a support call.
To enable Easysoft ODBC-SQL Server Driver logging, add a LOGFILE and a LOGGING attribute to the relevant DSN section of the odbc.ini file.
LOGFILE = /tmp/sql-server-driver.log
The LOGFILE value is the path and file name of the log file. The value shown in the example specifies a log file named /tmp/sql-server-driver.log. The LOGGING value specifies the actions to log. The value shown in the example specifies that all actions should be logged.
Ensure that the user who is running the application to be traced has write permission to the log file (and to the directory containing it).
By default, the Easysoft ODBC-SQL Server Driver appends log information to the file specified by LOGFILE. If you want the driver to generate a new log file for each ODBC session, enable logging on a per process basis. To do this, change the LOGGING entry to:
When you set LOGGING to Process, the Easysoft ODBC-SQL Server Driver creates separate log files that only contain trace output related to a particular driver process. The log file name contains the ID of the driver process (and TDS process) that the log output is associated with. For example, /tmp/sql-server-driver.log.000076BF.B7D076B0.
| Copyright Notice |
| | | |
