Easysoft ODBC-SQL Server Driver User's Guide - Technical Reference

Technical Reference for the Easysoft ODBC-SQL Server Driver

This section contains extra information relating to the deployment of the Easysoft ODBC-SQL Server Driver.

Appendix Guide

ODBC Conformance

The Easysoft ODBC-SQL Server Driver complies with the ODBC 3.52 specification.

The Easysoft ODBC-SQL Server Driver is Level 2 compliant.

ODBC API Support

All ODBC 3.52 calls are supported.

Cursor Support

The Easysoft ODBC-SQL Server Driver supports FORWARD_ONLY, KEYSET_DRIVEN, DYNAMIC and STATIC cursors.

Supported Data Types

The Easysoft ODBC-SQL Server Driver supports the following SQL Server data types:

The SQLGetTypeInfo Function

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 SQLSetConnectAttr Function

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:

Attribute Set before or after connection to server

SQL_COPT_SS_INTEGRATED_SECURITY

Before

SQL_COPT_SS_PRESERVE_CURSORS

Before

SQL_COPT_SS_TXN_ISOLATION

Either

SQL_COPT_SS_INTEGRATED_SECURITY

Whether to use Windows or SQL Server authentication to validate the connection.

Value Description

SQL_IS_OFF

Default. Use SQL Server Authentication to authenticate the connection.

SQL_IS_ON

Use Windows Authentication to authenticate the connection.

Windows authentication examples:

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

.

.

.

/* Use Windows Authentication to validate the connection */

SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,

(void *) SQL_IS_ON, 0);

/* 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 */

/* - the Easysoft 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,

SQL_DRIVER_COMPLETE);

¯ OR ¯

SQLSetConnectAttr(dbc, SQL_COPT_SS_INTEGRATED_SECURITY,

(void *) SQL_IS_ON, 0);

SQLDriverConnect(dbc, NULL, "DSN=MYDSN",

SQL_NTS, outstr, sizeof(outstr), &outstrlen,

SQL_DRIVER_COMPLETE);

 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:

 [MYDSN]

 Driver = Easysoft ODBC-SQL Server Driver

 Server = myserver\SQLEXPRESS

 User = mywindowsuser

 Password = mywindowspassword

SQL_COPT_SS_PRESERVE_CURSORS

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.)

Value Description

SQL_PC_OFF

Default. Cursors are closed when a transaction is committed or rolled back by using SQLEndTran.

SQL_PC_ON

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:

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt_select, stmt_update;

SQLRETURN ret;

SQLCHAR last_name[ 64 ], first_name[ 64 ], cursor_name[ 64 ], update_sql[ 64 ];

SQLSMALLINT reports_to, cursor_len;

SQLLEN indicator[ 3 ];

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,

(void *) SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Enable manual-commit mode */

SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,

SQL_AUTOCOMMIT_OFF, 0);

/* Preserve cursors when transactions are committed/rolled */

/* back. Alternatively, add PreserveCursor = Yes to the DSN */

SQLSetConnectAttr(dbc, SQL_COPT_SS_PRESERVE_CURSORS,

(void *) SQL_PC_ON, 0);

/* Connect to Northwind through the sample DSN */

SQLDriverConnect(dbc, NULL,

"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);

SQLExecDirect(stmt_select,

"SELECT LastName, FirstName, ReportsTo FROM Employees FOR UPDATE",

SQL_NTS);

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,

0, &indicator[ 2 ]);

/* 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 */

do

ret = SQLFetch(stmt_select);

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 */

sprintf(update_sql,

"UPDATE Employees SET ReportsTo = 2 WHERE CURRENT OF %s",

cursor_name);

SQLExecDirect(stmt_update, update_sql, SQL_NTS);

/* Commit the transaction */

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 );

SQLFetch(stmt_select);

/* Display updated record */

printf("%s %s reports to employee ID: %ld\n", first_name,

last_name, reports_to);

SQLCloseCursor(stmt_update); /* Close cursor */

SQLDisconnect(dbc); /* Disconnect from driver */

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

}

SQL_COPT_SS_TXN_ISOLATION

Sets the SQL Server 2005 or later snapshot isolation attribute.

Value Description

SQL_TXN_SS_SNAPSHOT

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 SQLSetStmtAttr Function

The Easysoft ODBC-SQL Server Driver supports the following driver-specific statement attributes:

SQL_SOPT_SS_DEFER_PREPARE

Whether the Easysoft ODBC-SQL Server Driver defers query preparation until execution time.

The attribute is only relevant to SQLServer 2000 and later.

Value Description

SQL_DP_ON

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.

SQL_DP_OFF

The Easysoft ODBC-SQL Server Driver prepares the statement as soon as SQLPrepare is executed.

Any errors in the statement will cause the prepare to fail.

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.

#include <stdio.h>

#include <string.h>

#include <sql.h>

#include <sqlext.h>

#include <sqlncli.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt;

SQLRETURN ret;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,

(void *) SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

SQLDriverConnect(dbc, NULL,

"DSN=SQLSERVER_SAMPLE",

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,

(SQLPOINTER) SQL_DP_OFF, 0);

/* Invalid statement */

ret = SQLPrepare(stmt, "select * from non_existent_table",

SQL_NTS);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {

ret = SQLExecute(stmt);

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);

}

} else {

/* 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,

(SQLPOINTER) SQL_DP_OFF, 0);

/* 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) {

SQLBindParameter();

.

.

.

/* The errors in the statement are not known until this point */

SQLExecute(stmt);

}

Unicode Support

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:

ANSI-Only Version of the Easysoft ODBC-SQL Server Driver

The Easysoft ODBC-SQL Server Driver distribution includes an 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:

[Easysoft ODBC-SQL Server]

Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so

Setup = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so

Threading = 0

FileUsage = 1

DontDLClose = 1

UsageCount = 1

# Install the ANSI driver by adding a new odbcinst.ini section.

# This example odbcinst.ini extract is 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

Threading = 0

FileUsage = 1

DontDLClose = 1

UsageCount = 1

In your data source, specify the new driver name in the Driver entry. For example:

[SQLSERVER_SAMPLE]

Driver =Easysoft ODBC-SQL Server ANSI APIs

.

.

.

The xml Data Type

SQL Server 2005 introduced an xml data type for storing XML documents in table columns or Transact-SQL variables.

The Easysoft ODBC-SQL Server Driver supports the 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]

') as Result

FROM Production.ProductModel

WHERE ProductModelID=7

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

FROM Production.ProductModel

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


Note

When querying or updating xml columns or variables with the xml data type methods, the data source attributes AnsiNPW and QuotedId must be set to 1 (the default value for these settings). Otherwise, queries and modifications will fail for xml data types.


Using Large-Value Data Types

SQL Server 2005 introduced 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:

¯ OR ¯

Snapshot Isolation

SQL Server 2005 introduced 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.

Performing Bulk Copy Operations

bcp Utility

The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of SQL.

Syntax

bcp {[[database_name.][schema].]{table_name |view_name} | "query"}

{in | out | queryout | format}data_file

[-mmax_errors] [-fformat_file] [-x] [-D logfile]

[-Ffirst_row] [-Llast_row] [-bbatch_size]

[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 | 100 | 110 | 120)]

[-q] [-C { RAW } ] [-tfield_terminator]

[-rrow_terminator] [-ooutput_file] [-apacket_size]

[-ddatabase] [-eerror_file] [-Sserver_name[\instance_name][:port]]

[-Ulogin] [-Ppassword] [-useNTLMv2] [-A APP_NAME]

[-T] [-v] [-k] [-K] [-E] [-h"hint [,...n]"] [-Xe filename] [-Xc cypher]

Arguments

database_name

The name of the database where table_name or view_name is located. The database can also be specified with the -d option. If no database is specified, the default database for login is used.

schema

The name of the schema to which table_name or view_name belongs.

table_name

The name of the destination table when importing data into SQL Server (in), and the source table when exporting data from SQL Server (out).

view_name

The name of the destination view when importing data into SQL Server (in), and the source view when exporting data from SQL Server (out).

"query"

An SQL query that returns a result set. If the query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file; subsequent result sets are ignored. If the database containing the target table or view is not the default for login, specify the database with the -d option.

in | out | queryout | format

The direction of the bulk copy:

data_file

The full path to the data file. When data is bulk imported into SQL Server, the data file contains the data to be copied into the specified table or view. When data is bulk exported from SQL Server, the data file contains the data copied from the table or view.

For the format option, you must specify nul as the value of data_file (format nul).

-m max_errors

The maximum number of times that bcp can return SQL_ERROR when bulk copying data from a query before the bcp operation is cancelled.

-f format_file

The full path to a format file:

-x

Used with the format and -f format_file options, the -x option generates an XML-based format file instead of the default non-XML format file. For example, bcp AdventureWorks.Sales.Currency format nul -f bcp.xml -x -c -U mydomain\\myuser -P mypassword -S mymachine\\sqlexpress.

-D logfile

Enables bcp logging and specifies the log file where the logging information is written. This can be a very useful debugging aid but it should be remembered that logging will slow bcp down, so remember to disable logging when you have finished debugging. Ensure that the user who is bcp has write permission to the log file (and to the directory containing it).

-F first_row

The number of the first row to export from a table or import from a data file. first_row should be a value greater than 0 but less than or equal to the total number rows. The default is the first row of the file.

-L last_row

The number of the last row to export from a table or import from a data file. last_row should be a value greater than 0 but less than or equal to the number of the last row. The default is the last row of the file.

-b batch_size

The number of rows per batch when importing data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails:

Batches already imported by committed transactions are unaffected by a subsequent failure.

-n

Use native (database) data types when importing and exporting data. This option does not prompt for each field; it uses the native values.

-c

Use character format when importing and exporting data. Character format uses the character data format for all columns. This option does not prompt for each field; it uses CHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.

-N

Use Unicode character format when importing and exporting character data. Use native format when importing and exporting non-character data.

-w

Use Unicode character format when importing and exporting data. Use this option when importing and exporting non-ASCII data stored in NCHAR, NVARCHAR and NTEXT columns. This option does not prompt for each field; it uses NCHAR as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator.

-V (70 | 80 | 90 | 100 | 110 | 120)

The version of SQL Server that bcp is connecting to, where:

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

110 = SQL Server 2012

120 = SQL Server 2014

-q

Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp and an instance of SQL Server.

-C { RAW }

Specifies the code page of the data in the data file.



Value Description

RAW

No conversion from one code page to another occurs.

-t field_terminator

The field terminator. The default is \t (tab character).

-r row_terminator

The row terminator. The default is \n (newline character).

-o output_file

The name of a file that receives bcp output redirected from the command prompt. For example, if you specified -o /tmp/bcp.txt and exported some data, /tmp/bcp.txt would contain something similar to:

Starting copy...

105 rows successfully bulk-copied to host file. Total received: 105

Network packet size (bytes): 4096

The contents of output_file are overwritten each time you successfully import or export data.

-a packet_size

The packet size in bytes that bcp will request when sending data to and receiving data from SQL Server. The specified packet size must be lower than 65536 bytes.

The default packet size is 4096 bytes. After a successful import or export, the bcp output shows the packet size used.

-d database

The name of the database where table_name or view_name is located. If no database is specified, the default database for login is used.

-e error_file

The name of the file to write errors to.

-S server_name[ \instance_name]

The SQL Server instance that you want to connect to. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name. To include the port that the SQL Server instance is listening on, specify server_name:port.

-U login

The SQL Server login name to use when connecting to SQL Server. If the SQL Server instance uses Windows Authentication, specify the Windows user name to use to authenticate the connection. (Include the -T argument if you specify a Windows user name.) If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.

-P password

The password for login. You do not have to specify a password on the command line. If you omit the -P argument from the command line, bcp will prompt you for one when you run the command.

-useNTLMv2

If you want to use NTLMv2 to authenticate a Windows user specified with -U, include -useNTLMv2 in your bcp command. If NTLMv2 is enabled at your site and you omit the -useNTLMv2, argument, the bcp connection will fail with the error "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

-A APP_NAME

The application name that bcp registers with SQL Server. This is returned by the SQL Server function APP_NAME(). The default application name is BCPTOOL. Use the -a argument to override the default name.

-T

Use Windows authentication to validate the connection. If this argument is specified, login must be a Windows user name.

-v

Reports the bcp version number.

-k

Empty columns retain a null value during an import, rather than have any default values for the columns inserted.

-K

Whether to access a SQL Server instance as a Kerberos service.

When you include this argument, the Easysoft ODBC-SQL Server Driver will attempt to obtain a service ticket for the following Service Principal Name (SPN):

MSSQLSvc/server

where:

server is the name or IP address of the SQL Server machine specified with the -S argument.

Do not supply a user name or password if you include the -K argument. The Kerberos application kinit must have already been used for authentication on the Easysoft ODBC-SQL Server Driver machine. For more information about kinit and accessing SQL Server as a Kerberos service, see the following Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-server-driver/kerberos.html

-M

The Service Principal Name (SPN) for a SQL Server instance that has been registered as a Kerberos service. If the SPN contains an instance name, you need to include the -M argument along with the -K argument. For example:

bcp -S "mysqlservermachine\myinstance" -K -M MSSQLSvc/mysqlservermachine:myinstance

-E

Use identity values in the imported data file for the identity column. If -E is not specified, identity values in the data file being imported are ignored, and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation.

-h"hint [,...n]"

The hint or hints to be used during a bulk import of data into a table or view.

ORDER(column [ASC | DESC] [,...n])

The sort order of the data in the data file.

ROWS_PER_BATCH = num

Number of rows of data per batch. Used when -b is not specified, resulting in the entire data file being sent to the server as a single transaction. The server optimises the bulk load according to the value num. By default, ROWS_PER_BATCH is unknown.

KILOBYTES_PER_BATCH = num

Approximate number of kilobytes of data per batch. By default, KILOBYTES_PER_BATCH is unknown.

TABLOCK

A bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired.

CHECK_CONSTRAINTS

All constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted.

FIRE_TRIGGERS

Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. If FIRE_TRIGGERS is not specified, no insert triggers will run.

Remarks

-Xe filename

Use this option to specify a source for random data for an SSL connection. Use filename to specify a randomness device. You only need to use this option if bcp cannot find a source for random data on your system.

-Xc cypher

For an SSL connection, request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. Separate multiple cypher suites with a colon. For example:

-Xc AES:3DES

Table-Valued Parameters

Table-valued parameters, introduced in SQL Server 2008, allow multiple rows or values to be passed to a query or stored procedure in one call. Table-valued parameters decrease network latency by reducing network round trips. For example, given the task of updating multiple order line items an application traditionally would call one procedure to update the order and another procedure to update the line items. The second procedure would be called multiple times, once for each line item, therefore requiring multiple database round trips to fulfill the objective.

The Easysoft ODBC-SQL Server Driver enables data to be sent as a table-valued parameter with all values in memory.

The following example inserts an order and multiple order detail rows by passing a table-valued parameter to one stored procedure.

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <stdlib.h>

#include <string.h>

#include "sqlncli.h"

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT hstmt;

/* ODBC API return status */

SQLRETURN ret;

/* SQL parameters */

#define ITEM_ARRAY_SIZE 20

SQLCHAR CustCode[6];

SQLCHAR *TVP = (SQLCHAR *) "TVParam";

SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];

SQLINTEGER OrdNo;

char OrdDate[23];

/* Indicator/length variables associated with SQL parameters */

SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Connect to the DSN mydsn.

* You will need to change mydsn to one you have created and tested

* The SQL Server database your DSN connects to needs:

*

* These tables:

*

* CREATE TABLE dbo.TVPOrder (

* CustCode varchar(5),

* OrdNo int identity,

* OrdDate datetime

* )

*

* CREATE TABLE dbo.TVPItem (

* OrdNo int,

* ProdCode int,

* Qty int

* )

*

*

* This user-defined table type:

*

* IF (SELECT COUNT(*) FROM sys.table_types

* WHERE name = 'TVPParam' AND schema_id = 1) = 0

* CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)

*

* This procedure:

*

* CREATE PROCEDURE

* dbo.TVPOrderEntry

* (

* @CustCode varchar(5),

* @Items TVPParam READONLY,

* @OrdNo integer output,

* @OrdDate datetime output)

* AS

* SET @OrdDate = GETDATE();

*

* INSERT INTO TVPOrder (OrdDate, CustCode)

* VALUES (@OrdDate, @CustCode);

*

* SELECT @OrdNo = SCOPE_IDENTITY();

*

* INSERT INTO TVPItem (OrdNo, ProdCode, Qty)

* SELECT @OrdNo, ProdCode, Qty FROM @Items

*/

SQLDriverConnect(dbc, NULL, "DSN=SQLSERVER_2012_EXPRESS_64_BIT;", SQL_NTS,

NULL, 0, NULL, SQL_DRIVER_COMPLETE);

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt);

/* Bind parameters for call to TVPOrderEntry */

/* 1 - CustCode input */

ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);

/* 2 - Items TVP */

ret = SQLBindParameter(hstmt,

2,

SQL_PARAM_INPUT,

SQL_C_DEFAULT,

SQL_SS_TABLE,

ITEM_ARRAY_SIZE, /* ColumnSize: For a table-valued parameter this is the row array size */

0, /* DecimalDigits: For a table-valued parameter this is always 0 */

TVP, /* ParameterValuePtr: For a table-valued parameter this is the type name of the */

/* table-valued parameter, and also a token returned by SQLParamData */

strlen(TVP), /* BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS */

&cbTVP); /* StrLen_or_IndPtr: For a table-valued parameter this is the number of rows actually used */

cbOrdNo = 0;

cbOrdDate = 0;

/* 3 - OrdNo output */

ret = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo,

sizeof(SQLINTEGER), &cbOrdNo);

/* 4 - OrdDate output */

ret = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR,SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate,

sizeof(OrdDate), &cbOrdDate);

/* Bind columns for the table-valued parameter (parameter 2) */

/* First set focus on parameter 2 */

ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);

/* Col 1 - ProdCode */

ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);

/* Col 2 - Qty */

ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);

/* Reset parameter focus */

ret = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);

/* Populate parameters */

cbTVP = 0; /* Number of rows available for input */

strcpy((char *) CustCode, "BEAUC"); cbCustCode = SQL_NTS;

ProdCode[cbTVP] = 555;cbProdCode[cbTVP] = sizeof(SQLINTEGER);

Qty[cbTVP] = 5;cbQty[cbTVP] = sizeof(SQLINTEGER);

cbTVP++; /* Number of rows available for input */

ProdCode[cbTVP] = 666;cbProdCode[cbTVP] = sizeof(SQLINTEGER);

Qty[cbTVP] = 6;cbQty[cbTVP] = sizeof(SQLINTEGER);

cbTVP++; /* Number of rows available for input */

/* Call the procedure */

ret = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);

}

Binding Procedure Parameters by Name

The Easysoft ODBC-SQL Server Driver supports named parameters, which allows an application to specify stored procedure parameters by name instead of by position in the procedure call.

This C code sample calls the AdventureWorks2008 stored procedure uspSearchCandidateResumes. The sample specifies the stored procedure's parameters by name.

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

main() {

SQLHENV env;

SQLHDBC dbc;

SQLHSTMT stmt;

SQLHANDLE ipd;

SQLRETURN ret;

/* Procedure input */

SQLCHAR param_name_1 [ 64 ], param_name_2 [ 64 ],

param_name_3 [ 64 ], param_name_4 [ 64 ];

/* Search candidate resumes for this text: */

SQLCHAR search_string[ 64 ] = "ISO9000";

/* Enable the default values for the other procedure */

/* parameters to be overridden. Initialise variables */

/* to the corresponding parameter's default value. */

BOOL use_inflectional = 0;

BOOL use_thesaurus = 0;

SQLSMALLINT language = 0;

/* Procedure output */

SQLCHAR col_1_name [ 64 ], col_2_name [ 64 ];

SQLINTEGER col_1;

SQLSMALLINT col_2, col_1_name_length, col_2_name_length;

SQLLEN len_1, len_2;

/* Allocate an environment handle */

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

/* We want ODBC 3 support */

SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)

SQL_OV_ODBC3, 0);

/* Allocate a connection handle */

SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

/* Connect to the DSN mydsn */

/* Change mydsn to one you have created and tested */

SQLDriverConnect(dbc, NULL, "DSN=mydsn;", SQL_NTS,

NULL, 0, NULL, SQL_DRIVER_COMPLETE);

/* Allocate a statement handle */

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

/* Prepare the statement that will call the procedure */

SQLPrepare(stmt, "{call uspSearchCandidateResumes (?, ?, ?, ?)}",

SQL_NTS);

/* Bind local variables to the parameters in the preceding */

/* statement. */

SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,

SQL_CHAR, 1000, 0, search_string, 0, 0);

SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,

1, 0, &use_inflectional, 0, 0);

SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_BIT, SQL_BIT,

1, 0, &use_thesaurus, 0, 0);

SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_INTEGER,

SQL_INTEGER, 1, 0, &language, 0, 0);

/* Bind columns in the procedure result set to local */

/* variables */

SQLBindCol(stmt, 1, SQL_INTEGER, &col_1, 0, &len_1);

SQLBindCol(stmt, 2, SQL_INTEGER, &col_2, 0, &len_2);

/* Get IPD handle. The IPD contains information about the */

/* statement parameters, such as their SQL data types, */

/* lengths, and nullability. */

SQLGetStmtAttr(stmt, SQL_ATTR_IMP_PARAM_DESC, &ipd, 0, 0);

/* Set the SQL_DESC_NAME field of the IPD to the parameter */

/* name */

SQLSetDescField(ipd, 1, SQL_DESC_NAME, "@searchString",

SQL_NTS);

SQLSetDescField(ipd, 2, SQL_DESC_NAME, "@useInflectional",

SQL_NTS);

SQLSetDescField(ipd, 3, SQL_DESC_NAME, "@useThesaurus",

SQL_NTS);

SQLSetDescField(ipd, 4, SQL_DESC_NAME, "@language", SQL_NTS);

/* Execute the prepared statement */

SQLExecute(stmt);

/* Retrieve the parameter names */

SQLGetDescField(ipd, 1, SQL_DESC_NAME, param_name_1,

sizeof(param_name_1), NULL);

SQLGetDescField(ipd, 2, SQL_DESC_NAME, param_name_2,

sizeof(param_name_2), NULL);

SQLGetDescField(ipd, 3, SQL_DESC_NAME, param_name_3,

sizeof(param_name_3), NULL);

SQLGetDescField(ipd, 4, SQL_DESC_NAME, param_name_4,

sizeof(param_name_4), NULL);

printf( "Procedure Input\n");

printf( "===============\n");

printf( "%s value:\t%s\n", param_name_1, search_string);

printf( "%s value:\t%d\n", param_name_2, use_inflectional);

printf( "%s value:\t%d\n", param_name_3, use_thesaurus);

printf( "%s value:\t%d\n", param_name_4, language);

printf( "\nProcedure Output\n");

printf( "===============\n");

/* Retrieve the column names for the procedure result set */

SQLColAttribute (stmt, 1, SQL_DESC_NAME, col_1_name,

sizeof(col_1_name), &col_1_name_length, 0);

SQLColAttribute (stmt, 2, SQL_DESC_NAME, col_2_name,

sizeof(col_2_name), &col_2_name_length, 0);

printf( "%s\t\t%s\n", col_1_name,col_2_name);

/* Fetch the procedure result set. */

while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {

printf( "%d\t\t\t%d\n", col_1, col_2);

}

/* Free up allocated handles and disconnect from the driver */

SQLFreeHandle(SQL_HANDLE_STMT, stmt);

SQLDisconnect(dbc);

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

}

When run, the sample produces output similar to that shown in the following shell session:

$ cc -I/usr/local/easysoft/unixODBC/include/ uspSearchCandidateResumes.c -o uspSearchCandidateResumes -L/usr/local/easysoft/unixODBC/lib/ -lodbc

$ chmod +x ./uspSearchCandidateResumes

$ ./uspSearchCandidateResumes

Procedure Input

===============

@searchString value: ISO9000

@useInflectional value: 0

@useThesaurus value: 0

@language value: 0

Procedure Output

===============

JobCandidateID RANK

1 9

7 9

10 12

SQL Server Authentication Modes

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.

Windows Authentication

The Easysoft ODBC-SQL Server Driver asks SQL Server to use Windows Authentication to validate the connection if:

¯ OR ¯

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.

SQL Server Authentication

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.

Encrypting Connections to SQL Server

SQL Server 2000 and latercan 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 later 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.

In this section

Accessing SQL Server over an Encrypted Connection

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:

[SQLSERVER_SAMPLE_SSL]

Driver = Easysoft ODBC-SQL Server SSL

Description = Easysoft SQL Server ODBC driver

Server = MYSQLSERVERMACHINE\MYINSTANCE

Port =

Database =

User = MYDOMAIN\myuser

Password = mypassword

.

.

.

 For more information about configuring ODBC data sources, see Setting Up Data Sources on Unix.

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.

4.  Do one of the following:

5.  Use isql to test the data source. For example:

 cd /usr/local/easysoft/unixODBC/bin.

 ./isql -v SQLSERVER_SAMPLE_SSL

Configuring and Testing SSL Encryption

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.

Installing an SSL Certificate

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 or later can use an SSL certificate from a trusted CA if available or generate a self-signed certificate.


Note

Even though SQL Server 2005 or later 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

Testing That SSL is Available on the SQL Server Machine

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

1.  Do one of the following:

2.  Restart the instance.

3.  Check the SQL Server error log (drive:\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.


Note

You can verify that SQL Server has successfully generated a self-signed SSL certificate by checking the SQL Server error log for a line containing:

A self-generated certificate was successfully loaded for encryption.

If SQL Server is unable to generate a self-signed certificate, you will be unable to connect to the instance over an encrypted connection. Note that when testing SSL with SQL Server Express, we had to change the account used by the SQL Server instance from Network Service to Local System before the instance could generate a certificate.


Installing the Root CA Certificate

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 or later 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:

 mmc

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.

Configuring the Client to Request an Encrypted Connection

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

1.  Do one of the following:

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:

 [SQLSERVER_SSL_CONNECTION]

 Driver = Easysoft ODBC-SQL Server SSL

 Server = MYSQLSERVERMACHINE\MYINSTANCE

 User = MYDOMAIN\myuser

 Password = mypassword

 Encrypt = No

 TrustServerCertificate = No

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:

 8.E.m.p.l.o.y.e

 e.I.D.......=.R

 a.t.e.C.h.a.n.g

 e.D.a.t.e......

 <.R.a.t.e......

 0.P.a.y.F.r.e.q

 u.e.n.c.y......

 =.M.o.d.i.f.i.e

 d.D.a.t.e......

6.  Press RETURN to exit isql.

7.  In your data source, set the Encrypt attribute to Yes.

8.  Do one of the following:

 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.

 .E..{i.2.8.G.q..

 .n..{X.... 4..O.

 &....Lt..Z.wrH.8

 .W..{..........,

 ....1s_..).\k.6.

 ..4U..4..D...5.U

 &...I......+..w.

 l.W...&}x.......

 ....%......7...J

 ..C$...,j..52.~.

 .w. Q.qE.Q....]4

 .\.Y?...|R.VOr.S

 .....K.W.. 2.#.T

 .G..+..F.....T..

 @"..+-.......

Encrypting the Login Packet

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 or later 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.


Note

For Windows logins, SQL Server transmits the domain and user name in plain text in the response to the login packet. If you do not want this information sent in plain text, you need to enable encryption either on the SQL Server or Easysoft ODBC-SQL Server Driver with SSL Support machine.


Easysoft ODBC-SQL Server Driver with SSL Support Attribute Fields

The following attributes may be set in the odbc.ini file:

Attribute Description

Encrypt = Yes | No

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.

TrustServerCertificate = Yes | No

Enables the client to request encryption even when an SSL certificate has not been installed on the SQL Server 2005 or later machine.

If SQL Server 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 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.

By default, TrustServerCertificate is ON (set to Yes).

CertificateFile = filename

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.

Examples

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:

CertificateFile = /usr/share/ssl/CA/MyCA.cer

Cypher = value

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

LOGGING = Yes

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:

Encryption: RC4

Encryption strength: 128-bit

Cryptographic checksum: MD5

Authentication: RSA

 

(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

# integrity protection.

Cypher = 3DES+SHA

¯ OR ¯

# Request Advanced Encryption Standard (AES) for data

# encryption. If AES is not available on the server,

# request 3DES.

Cypher = AES:3DES

¯ OR ¯

# Use Secure Hash Algorithm (SHA) to protect data

# integrity. Let the SSL layers negotiate which

# encryption algorithm to use.

Cypher = SHA

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.

Entropy = filename

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 :

Entropy = /etc/entropy

Figure 13: Easysoft ODBC-SQL Server Driver with SSL Support data source settings.

Database Mirroring

Database mirroring is a feature introduced in SQL Server 2005 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.

In this section

Making the Initial Connection to a Database Mirroring Session

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.

 Server = 123.34.45.56:4724

 # 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.

 Database = AdventureWorks

 # This login must have permission to access the database on both

 # the principal and mirror database server.

 User = my_domain\my_username

 Password = my_password

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.


Note

A database mirroring session does not protect against server-access problems that are specific to client machines, such as when a client machine is having a problems communicating with the network. A connection attempt to a mirrored database can also fail for a variety of reasons that are unrelated to the Easysoft ODBC-SQL Server Driver; for example, a connection attempt can fail because of a network error.


Data Source Attributes for a Mirrored Database

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.



Attribute Notes

Server

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

¯ OR ¯

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:

# IPv4 address

Server=123.34.45.56:4724

¯ OR ¯

# IPv6 address (SQL Server 2005 or later)

Server=2001:4898:23:1002:20f:1fff:feff:b3a3*7022

IPv6 = 1

Database

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.

Failover_Partner

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.

User

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.

Figure 14: Data source attributes for a mirrored database

Connection Retry Algorithm

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:

8%, 8%, 16%, 16%, 24%, 24%

The retry time is calculated by using the following formula:

RetryTime =PreviousRetryTime + ( 0.08 *LoginTimeout )

Where PreviousRetryTime is initially 0. For example:

Round Retry Per Attempt

1

0 + (0.08 * 15000) =1200 msec

2

1200 + (0.08 * 15000) =2400 msec

3

2400 + (0.08 * 15000) =3600 msec

The Impact of a Stale Failover Partner Name

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

where:

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

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.

 User = my_domain\my_user

 Password = my_password

 ClientLB = 0

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.

Connecting to SQL Server 2005 or Later by Using IPv6

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.

The features of IPv6 include:

IPv6 support was introduced in SQL Server 2005. Currently, SQL Server supports IPv6 on Windows XP (Service Pack 2), Windows Server 2003, Windows Vista and Windows Server 2008.

On Windows XP and 2003 Server, IPv6 is a separate component. Windows Vista and Windows Server 2008 provide 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:

Configuring Your Client Machine for IPv6

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/.

Configuring Your ODBC Data Source for IPv6

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

IPv6 = 1

¯ OR ¯

Server=ABCD:EF12:0:0:0:0:0:3456\myinstance

IPv6 = 1

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.

Threading

The Easysoft ODBC-SQL Server Driver is thread safe in accordance with the ODBC specification and can safely be used behind threaded applications.

Tracing

The ODBC calls an application makes can be traced:

Within the driver manager by an application

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.

From within the driver manager

For the unixODBC Driver Manager, add two attributes to the [ODBC] section (create one if none exists) in odbcinst.ini.

Trace = Yes

TraceFile =logfile

For example:

[ODBC]

Trace = Yes

TraceFile = /tmp/unixodbc.log

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.

From within the Easysoft ODBC-SQL Server Driver

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.

For example:

[SQLSERVER_SAMPLE]

.

.

.

LOGFILE = /tmp/sql-server-driver.log

LOGGING = Yes

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:

LOGGING = Process

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.

To include additional information in ODBC error messages returned by the Easysoft ODBC-SQL Server Driver, add the following line to your data source:

OVERRIDE = 32

This Driver Manager log file extract shows the extra information output by setting the OVERRIDE attribute. The extra information is shown in bold text.

DIAG [08004] [Easysoft][SQL Server Driver 10.0][server='MYMACHINE\SQLEXPRESS_ADV',proc='',class=16,line=1,conn='mymachine\sqlexpress_adv']Database 'AdventureWorks' does not exist. Make sure that the name is entered correctly.