Easysoft ODBC-SQL Server Driver User's Guide - Configuration

Configuring the Easysoft ODBC-SQL Server Driver

The Easysoft ODBC-SQL Server Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load, which SQL Server instance to connect to and how to connect to it. This chapter describes how to create data sources, use DSN-less connections and configure the Easysoft ODBC-SQL Server Driver.

Before setting up a data source, you must have successfully installed the Easysoft ODBC-SQL Server Driver.

For Easysoft ODBC-SQL Server Driver installation instructions, see Installation.

Chapter Guide

Configuring the Easysoft ODBC-SQL Server Driver

This section describes how to configure the Easysoft ODBC-SQL Server Driver to connect to a SQL Server database by using a data source or a DSN-less connection string. The section assumes you are, or are able to consult with, a database administrator.

Setting Up Data Sources on Unix

There are two ways to set up a data source to your SQL Server data:

¯ OR ¯

By default, the Easysoft ODBC-SQL Server Driver installation creates a SYSTEM data source named [SQLSERVER_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-SQL Server Driver distribution, the SYSTEM odbc.ini file is in /etc.

If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.

If you accepted the default choices when installing the Easysoft ODBC-SQL Server Driver, USER data sources must be created and edited in $HOME/.odbc.ini.


Note

To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j.

By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini.


You can either edit the sample data source or create new data sources.

Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.


Note

Attribute names in odbc.ini are not case sensitive.


The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source. The Easysoft ODBC-SQL Server Driver distribution includes two drivers:

When the Easysoft ODBC-SQL Server Driver is installed into unixODBC, entries for the standard driver (Easysoft ODBC-SQL Server) and the driver with SSL support (Easysoft ODBC-SQL Server SSL) are placed in odbcinst.ini.

For Easysoft ODBC-SQL Server Driver data sources, you need to include a Driver = Easysoft ODBC-SQL Server entry.

For Easysoft ODBC-SQL Server Driver with SSL Support data sources, you need to include a Driver = Easysoft ODBC-SQL Server SSL entry. For more information about configuring Easysoft ODBC-SQL Server Driver with SSL Support data sources, see Encrypting Connections to SQL Server.

To configure a SQL Server data source, in your odbc.ini file, you need to specify:

For example:

 [SQL Server]

 Driver = Easysoft ODBC-SQL Server

 # To connect to the default instance, omit \my_instance_name.

 Server = my_sqlserver_hostname\my_instance_name

 User = my_domain\my_domain_user

 Password = my_password

 If the SQL Server Browser or listener service is not in use at your site and you want to connect to an instance that is not listening on the default TCP port (1433), you also need to specify the port: For example, to connect to a SQL Server instance that is listening on port 1500, add this entry:

 Port = 1500

Setting Up Data Sources on Mac OS X

To create an Easysoft ODBC-SQL Server Driver data source on Mac OS X, use ODBC Administrator. ODBC Administrator is a component of iODBC that provides a GUI with which you can add, modify, delete and examine data sources. If ODBC Administrator is not included with your version of Mac OS X, you can download it from the Apple Support web site (http://support.apple.com/kb/DL895).

Data sources are stored in the odbc.ini file. User data sources are stored in ~/Library/ODBC. To find out the directory where System data sources are stored, in a Terminal window, type the following command:

iodbc-config --odbcini

iodbc-config is a script that outputs iODBC configuration information. The --odbcini option prints the system wide odbc.ini file path. The following line shows some example output from the previous command:

 /Library/ODBC/odbc.ini

 


Note

To add, remove or edit System data sources, you need to be logged in as an administrator user.

Depending on the permissions on the system wide odbc.ini file, you may have to log in as the root user to add, remove or edit System data sources. Otherwise, the changes that you make will not be saved in the odbc.ini file.

By default, the root user account is not active. For information about enabling the root user and the implications of using the root account, in the Mac OS X Help, search for "root user."


To add an Easysoft ODBC-SQL Server Driver data source

1.  Open ODBC Administrator in the /Applications/Utilities folder.

 ODBC Administrator is displayed.

Figure 4: The Mac OS X ODBC Administrator

2.  Do one of the following:

 If the System DSN tab is locked, the Add button will be unavailable. To unlock the System DSN tab, click the lock icon, and then type an administrator user name and password when prompted.

The Choose A Driver dialog prompts you to choose the driver for which you want to set up the data source.

Figure 5: The Mac OS X ODBC Administrator Choose A Driver dialog box

3.  From the list of ODBC drivers, choose Easysoft ODBC-SQL Server Driver, then click OK.

The Easysoft ODBC-SQL Server Driver for Mac OS X DSN dialog box is displayed.

 

Figure 6: The Create SQL Server Data Source dialog box

4.  In the Data Source tab, in the Name box, type the data source name.

5.  In the Description box, type some text to describe the data source.

 Some applications display the description text to help users differentiate between different data sources.

6.  In the Server box, type the host name or IP address of the machine where the SQL Server instance is running. To connect to a named instance you also need to specify the instance name. For example:

 my_sqlserver_hostname\my_instance_name

 Alternatively, choose a SQL Server instance from the drop-down list.

7.  Choose the Authentication tab.

 

Figure 7: The Create SQL Server Data Source dialog box--Authentication tab

8.  Do one of the following:

9.  In the User box and Password boxes, type a valid SQL Server login name and password.

 If the SQL Server login is a Windows user name, use the format:

 domain\username

 where:

10.  If the SQL Server Browser or listener service is not in use at your site and you want to connect to an instance that is not listening on the default TCP port (1433), you also need to specify the port. To do this choose Advanced options, and then type the Port in the Port box. Choose Finished.

 For example, to connect to a SQL Server instance that is listening on port 1500, type 1500 in the Port box.

 

Figure 8: The Create SQL Server Data Source dialog box-- Advanced Authentication Options

11.  Choose the Connection tab.

 

Figure 9: The Create SQL Server Data Source dialog box--Connection tab

12.  If you want to change the default database to use for the connection, choose the database you want from the Default database list. Note that if the login does not have permission to access the database, the connection will fail.

13.  Choose the Test tab.

14.  Choose the Test Connection button to verify that you can successfully connect to SQL Server with your new data source.

 

Figure 10: The Create SQL Server Data Source dialog box--Test tab

If the connection fails, refer to Troubleshooting Database Connection Problems.

15.  In ODBC Administrator, click Apply to save your new data source.

Testing Data Sources

iodbctest is a command line ODBC application that is supplied with iODBC. Use it to test your Easysoft ODBC-SQL Server Driver data sources. If a connection can successfully be made, you can query the remote database by executing SQL statements in iodbctest.

To test an Easysoft ODBC-SQL Server Driver data source

1.  Open Terminal in the /Applications/Utilities folder.

2.  At the shell prompt, type:

 iodbctest

3.  Type DSN=name

 where name is the name of an Easysoft ODBC-SQL Server Driver data source that you want to test.

 If the connection succeeds, iodbctest prompts you to type some SQL.

 If the connection fails, iodbctest displays an error to help you identify what the problem is.

4.   To exit iodbctest, type quit.

Attribute Fields

The following Easysoft ODBC-SQL Server Driver attributes may be set in the odbc.ini file:

Attribute Description

Driver = value

The name of the ODBC driver to use with this data source. To connect to a SQL Server 2000 or later instance over an encrypted connection, set this attribute value to Easysoft ODBC-SQL Server SSL. Otherwise, set this attribute value to Easysoft ODBC-SQL Server.

Description = value

A single line of descriptive text that may be retrieved by some applications to describe the data source.

Server = value

The SQL Server instance that you want to connect to. To connect to the default SQL Server instance, type:

machinename

where machinename is the name or IP address of the host where SQL Server is running.

Note that if you are connecting to a SQL Server 2005 or later instance that is listening on an IPv6 address, set the IPv6 attribute to 1.

To connect to a named instance, type:

machinename\instancename

where instancename is the SQL Server instance.

To connect to the default SQL Server Express named instance, type:

machinename\sqlexpress

 

Connection Failover

If your SQL Server database is available on more than one SQL Server machine, you can define a primary server for the database and additional fallback database servers. By default, the Easysoft ODBC-SQL Server Driver will try to connect to the first server that you specify. If that server is unavailable the Easysoft ODBC-SQL Server Driver will try to connect to the next server in the list and so on. Use the format:

Server = primaryserver[:port] [, fallbackserver[:port]...]

where:

  • primaryserver is the name or IP address of the primary SQL Server machine on which your database is available.
  • port is the TCP port on which the instance is listening. If omitted, the driver will try to connect to the instance that is listening on port 1433.
  • fallbackserver is the name or IP address of an alternative SQL Server machine on which your database is available.

For example:

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

Connection attempts continue until either a connection is successfully made or all the servers in the list have been tried once.

Note that your SQL Server login (as specified by User and Password) needs to be valid on each SQL Server machine in the list. The SQL Server login must have permission to access the database on each SQL Server machine.

If you want to balance the load between database servers, configure the driver to randomly choose the database server it connects to. To do this, set the ClientLB attribute to 1.

Port = num

The TCP port that SQL Server is listening on.

If you are connecting to a default instance that is listening on port 1433, the Port setting can be omitted.

If the SQL Server Browser or the SQL Server 2000 listener service is running, the Easysoft ODBC-SQL Server Driver will automatically detect the port number and the Port setting can be omitted.

By default, named instances of SQL Server use dynamic ports, which means that an available port is assigned when the instance starts. If a SQL Server instance is listening on a dynamically allocated port number, you must omit the Port setting and let the Easysoft ODBC-SQL Server Driver use the browser or listener to detect the port number.

If the SQL Server Browser or listener is not running at your site, your database administrator will have configured each SQL Server instance to listen on a specific TCP port. You need to specify this port with the Port setting.

If your database administrator has hidden the SQL Server instance from the SQL Server Browser or listener, you need to specify the port number of the hidden instance.

If your database administrator has configured the SQL Server instance to listen on multiple ports, use the Port setting to specify the appropriate port number from the available alternatives.

User = value

The SQL Server login name to use when connecting to SQL Server.

If the SQL Server instance uses Windows Authentication (also known as trusted connections), the Windows user name to use to authenticate the connection. Use this format:

domain\username

where:

  • domain is the name of the Windows domain that the SQL Server machine is in or one that the SQL Server machine trusts.
  • username is the user name of a user who belongs to this domain.

If the SQL Server instance permits SQL Server Authentication, you can also specify a SQL Server user name.

To specify the login name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-SQL Server Driver attributes in the connection string, see DSN-less Connections.

Password = value

The password for the login name specified by User.

To specify the login password in the connection string, use PWD rather than Password.

ServerName = value

This attribute is only relevant if you are using SQL Azure and lets you specify the fully qualified domain name (FQDN) of the SQL Azure server that you want to connect to. For example:

xyz12345yzx.database.windows.net

There are two ways to specify the SQL Azure server, as shown by the following data source extracts:

ServerName = xyz12345yzx.database.windows.net

User = myuser

¯ OR ¯

Server = xyz12345yzx.database.windows.net

User = myuser@xyz12345yzx

For more information about SQL Azure, see the following Easysoft tutorial:

http://www.easysoft.com/products/data_access/odbc-sql-azure-driver/linux-unix.html

Database = value

The default database to use for the connection.

If you omit this attribute, the connection uses the default database defined for the login in SQL Server. The default database for users who do not have their own SQL Server login depends on the local group on the SQL Server machine that they belong to. The default database for members of the local Administrators group is the one defined for the BUILTIN\Administrators login. The default database for members of the local Users group is the one defined for the BUILTIN\Users login (SQL Server Express Edition only).

If the database does not exist or the login does not have permission to access the database, the connection will fail.

Note that using the default database for the login ID is more efficient than specifying a default database in the ODBC data source.

QuotedId = 0 | 1

When ON (set to 1), QUOTED_IDENTIFIERS is set to ON for the connection. SQL Server will then follow the SQL-92 rules regarding the use of quotation marks in SQL Statements. Double quotes can only be used for identifiers, such as column and table names. Character strings must be enclosed in single quotes:

SELECT CompanyName

FROM "Customer and Suppliers by City"

WHERE City = 'New York'

If a single quotation mark is part of the literal string, it can be represented by two single quotation marks.

When OFF, QUOTED_IDENTIFIERS is set to OFF for the connection. SQL Server then follows the legacy Transact-SQL rules regarding the use of quotation marks. Identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

For more information about the QUOTED_IDENTIFIERS option, see the SQL Server Transact-SQL documentation.

By default, QuotedId is ON.

AnsiNPW = 0 | 1

When ON (set to 1), the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options are set to ON for the connection.

When ANSI_NULLS is ON, SQL Server enforces ANSI rules for handling NULL comparisons. The ANSI syntax IS NULL or IS NOT NULL must be used for all NULL comparisons. For example:

SELECT *

FROM MyTable

WHERE MyColumn IS NULL

The Transact-SQL syntax = NULL and <> NULL are not supported.

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators must be used to make comparisons with NULL and nonnull values in a table.

When ANSI_WARNINGS is ON, SQL Server generates warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL. For example, SQL Server will generate error and warning messages for divide-by-zero errors, string too large for database column errors and when NULL values are encountered when using aggregate functions. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised.

When ANSI_PADDING is ON, trailing blanks on varchar values and trailing zeroes on varbinary values are not automatically trimmed.

For more information about the ANSI_NULLS, ANSI_WARNINGS, and ANSI_PADDING options, see the SQL Server Transact-SQL documentation.

By default, AnsiNPW is ON.

Language = value

The national language to use for SQL Server system messages. Use this format:

Language = language

where language is one the language aliases contained in the sys.syslanguages table.

For example:

Language = French

If no language is specified, the connection uses the default language specified for the login on the server.

Appname = value

The name SQL Server uses to identify the application that connects using this data source. For example, the following entry identifies an application as isql:

Appname = isql

The default value is ODBC.

SQL Server stores the application name in the master.dbo.sysprocesses column program_name. The name is returned by the APP_NAME function.

MARS_Connection = 0 | 1

When ON (set to 1), multiple active result sets (MARS) are enabled on the connection if the server is SQL Server 2005 or later. MARS allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while result sets are open. For example, an application might retrieve unprocessed items from an Orders table and then, while looping through the active result set, use an UPDATE statement to mark each order as processed.

For non-MARS connections (MARS_Connection turned OFF) and earlier versions of SQL Server, applications cannot maintain multiple active statements on a connection. Applications that attempt to do this fail with the error "connection is busy with results of another hstmt". The application has to process or cancel all result sets from one batch before it can execute any other batch on that connection. Note that server-side cursors can be used to work around this limitation. There is a performance penalty associated with server-side cursors however.

For more information about MARS, see the Microsoft article Multiple Active Result Sets (MARS) in SQL Server 2005.

By default, MARS_Connection is OFF.

Logging = value

Whether Easysoft ODBC-SQL Server Driver logging is enabled. To enable Easysoft ODBC-SQL Server Driver logging, add a Logging=Yes entry to the relevant DSN section of the odbc.ini file.

LogFile = value

Use the LogFile attribute to specify the Easysoft ODBC-SQL Server Driver log file name and location. 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).

PreserveCursor = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver preserves cursors when SQLEndTran commits or rolls back a transaction.

By default, PreserveCursor is OFF, which means that cursors are closed when a transaction is committed or rolled back by using SQLEndTran.

This behaviour can also be configured by setting SQL_COPT_SS_PRESERVE_CURSORS with SQLSetConnectAttr. For more information and a code sample, see SQL_COPT_SS_PRESERVE_CURSORS.

Wsid = value

The workstation ID. The default value is the host name of the machine where the ODBC application is running. SQL Server stores the workstation ID in the master.dbo.sysprocesses column hostname. The ID is returned by sp_who and the HOST_NAME function.

Version7 = 0 | 1

Set to Version7 to 1 if you are connecting to a SQL Server 7.0 database.

When initiating the connection, the Easysoft ODBC-SQL Server Driver tries to discover the version of the SQL Server instance. Setting Version7 to 1 reduces the number of steps in the discovery process for SQL Server 7.0 databases. This results in a slightly quicker connection time.

By default, Version7 is OFF (set to 0).

ForceShiloh = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver assumes that it is connecting to a SQL Server 2000 instance and only uses the SQL Server 2000 version of TDS to communicate with the instance.

By default, ForceShiloh is OFF (set to 0).

ClientLB = 0 | 1

Whether the Easysoft ODBC-SQL Server Driver tries to balance the load between the servers specified by the Server setting. The ClientLB setting only has an effect if you specify a primary server and additional fallback servers with Server.

When ClientLB is ON (set to 1), the Easysoft ODBC-SQL Server Driver randomly selects a server to connect to. If the server is unavailable, the Easysoft ODBC-SQL Server Driver then moves sequentially through the list of other servers.

When ClientLB OFF (set to 0, the default), the Easysoft ODBC-SQL Server Driver tries to connect to the servers in the order that they are defined in. (Primary server first and then each additional fallback server.)

Example

You specify a primary server (sqlsrvhostA) and two fallback servers (sqlsrvhostB and sqlsrvhostC):

Server = sqlsrvhostA,sqlsrvhostB,sqlsrvhostC:1583

When ClientLB is ON, the Easysoft ODBC-SQL Server Driver will randomly choose a server to connect to. If, for example, the driver tries to connect to sqlsrvhostB first, it will then try to connect to sqlsrvhostC (if sqlsrvhostB is unavailable) and sqlsrvhostA (if sqlsrvhostC is unavailable).

When ClientLB is OFF, the Easysoft driver will try to connect to sqlsrvhostA and then sqlsrvhostB (if sqlsrvhostA is unavailable) and finally sqlsrvhostC (if sqlsrvhostB is unavailable).

Failover_Partner = value

Use Failover_Partner 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 server specified by Failover_Partner.

For more information about database mirroring, see Database Mirroring.

MultiSubnetFailover = 0 | 1

Set MultiSubnetFailover to 1 when connecting to a SQL Server Failover Cluster Instance or the availability group listener of a SQL Server availability group. Setting MultiSubnetFailover to 1 provides faster detection of and connection to the (currently) active server.

By default, MultiSubnetFailover is OFF (set to 0).

ApplicationIntent = ReadOnly | ReadWrite

Specifies the application workload type when connecting to a SQL Server Failover Cluster Instance or the availability group listener of a SQL Server availability group. The default is ReadWrite.

VarMaxAsLong = 0 | 1

When OFF (set to 0), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_VARCHAR with a zero length, which means the maximum size is unlimited. Some applications may interpret this to mean that the column size is zero bytes rather than unlimited and allocate a buffer that is too small for the column data. To work around this, try setting VarMaxAsLong to 1. When ON (set to 1), the Easysoft ODBC-SQL Server Driver returns a varchar(max) column as a SQL_LONGVARCHAR.

By default, VarMaxAsLong is OFF.

VarMaxAsVarchar = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver maps a varchar(max) column to a varchar(4000) column and an nvarchar(max) column to a nvarchar(4000) column.

By default, VarMaxAsVarchar is OFF.

DisguiseGuid = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver describes the UNIQUEIDENTIFIER data types as CHAR rather than GUID. This is a workaround for applications such as Oracle's HSODBC that do not recognise UNIQUEIDENTIFIER types and therefore fail to return data from tables containing these column types.

By default, DisguiseGuid is OFF (set to 0).

DisguiseLong = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver describes IMAGE and TEXT data types as VARBINARY and VARCHAR. This is a workaround for applications such as Oracle's HSODBC that cannot handle IMAGE and TEXT types and therefore fail to return data from tables containing these column types.

By default, DisguiseLong is OFF (set to 0).

LimitLong = num

The maximum size in bytes that the Easysoft ODBC-SQL Server Driver returns for image, ntext, text, nvarchar(max), varbinary(max) and varchar(max) columns. Use LimitLong to restrict the size returned by the driver when describing these data types.

Note LimitLong only has an effect on MAX data types if VarMaxAsLong is ON (set to 1).

By default, LimitLong is OFF (set to 0).

DPrec = num

The precision to use when converting float(25-53) data in a result set to a string

If an application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the FPrec attribute to specify the precision to use when the driver does this conversion for float(25-53) data.

The default precision is 6.

FPrec = num

The precision to use when converting float(1-24) or real data in a result set to a string

If your application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the FPrec attribute to specify the precision to use when the driver does this conversion for float(1-24) or real data.

The default precision is 6.

Strftime = format

The format to use when converting ftimestamp data in a result set to a string.

If your application specifies a string as the target type for timestamp data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-SQL Server Driver converts the data to the target type. Use the Strftime attribute to specify the format to use when the driver does this conversion for date, datetime, datetime2, datetimeoffset, smalldatetime, and time data.

The Easysoft ODBC-SQL Server Driver uses strftime to do the conversion, and so format should be one of the format strings supported by strftime. For the available format strings, see the strftime(3) man page.

For example, the format string specified in the following line:

STRFTIME = %d %h %Y %T

would produce:

11 May 2011 12:35:29

given this SQL statement:

SELECT CAST('2011-05-11 12:35:29.123' AS datetime)

Strfsize = num

The display size of a column is the maximum number of characters needed to display data in character form. It may be necessary to increase the default display size for timestamp data to accommodate some of the formats that strftime supports.

For example, to set the display size to 32, add the following line to your data source:

STRFSIZE = 32

ConvToUtf = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver converts UCS-2 encoded data to UTF-8 and vice versa. This enables applications running on UTF-8 platforms to work with Unicode data stored in nchar, nvarchar, nvarchar(max) and ntext columns.

SQL Server uses UCS-2 to encode data in nchar, nvarchar, nvarchar(max) and ntext columns. If your application expects UTF-8 encoded data, and is unable to convert data to this encoding scheme, it will be unable to process Unicode data stored in nchar, nvarchar, nvarchar(max) and ntext columns. To work around this, add this line to your ODBC data source.

ConvToUtf = 1

ConvToUtf also affects SQL statement text, metadata (table names and so on), and SQL statement parameters that are bound as a wide type (SQL_WCHAR, SQL_WVARCHAR, SQL_WLONGVARCHAR). For example:

SQLPrepare( hstmt, "INSERT INTO MYNCHARTABLE VALUES (?)", SQL_NTS );

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, 100, 0, &cval, sizeof( cval ), &len1 );

By default, ConvToUtf is OFF (set to 0).

Example: Retrieving Data

We ran OpenOffice.org 2.0 on Ubuntu from a shell in which the LANG environment was set to en_GB.UTF-8. With ConvToUtf set to 0, we connected to a SQL Server data source in OOo Base and ran this SQL:

 

use Northwind

select CompanyName from Suppliers where SupplierID = 29

SQL Server stores data in the CompanyName column as a UCS-2 encoded nvarchar type.

The results for this query should be:

Forêts d'érables

Instead, we got these results:

For?ts d'?rables

The ? symbols indicate that application was unable to convert the character from the server encoding scheme to the client encoding scheme.

In OOo Writer, we used the Insert > Special Character command to insert ê and é into a new document. We did this to show that OpenOffice.org running on this system and environment was capable of rendering these two characters. We then saved the document as a Text file and ran the following command at the shell prompt:

$ file ooo_chars.txt

ooo_chars.txt: Unicode text, UTF-8

The file command's output indicates that the encoding scheme OpenOffice was using is UTF-8.

We set ConfToUTF to 1 and reconnected to the data source in Base. Running the same query returned the expected results. This is because the Easysoft ODBC-SQL Server Driver converts the UCS-2 encoded data to UTF-8, the encoding OpenOffice expects.

 

Example: Inserting Data

We created a SQL file named insert-northwind-shipper.sql on a Ubuntu machine:

-- Insert new record into the Northwind shippers table

USE Northwind;

INSERT INTO Shippers (CompanyName, Phone) VALUES (N'♦ Diamond Shipping', '(11) 555-2167');

SELECT * FROM Shippers;

To create the file, we used the Vi IMproved (vim) text editor from a shell in which the LANG environment was set to en_GB.UTF-8. To insert the character in vim, we typed CTRL+V u2666. (u+2666 is the Unicode code point for this character.) The N prefix before the INSERT statement value tells SQL Server that the string contains a Unicode character. To confirm that the SQL file was UTF-8 encoded, we ran the file command:

$ file insert-northwind-shipper.sql

insert-northwind-shipper.sql: Unicode text, UTF-8

In the same shell, we used insert-northwind-shipper.sql as an input file to isql:

/usr/local/easysoft/unixODBC/bin/isql -v SQLSERVER_SAMPLE < insert-northwind-shipper.sql

The SQLSERVER_SAMPLE data source connects to a SQL Server instance that serves the Northwind database. In the data source, ConfToUTF was set to 1. The command's output confirmed that the new record had been successfully inserted and that the Ubuntu machine was capable of rendering the character:

SQL>--+---------------------+----------------------+

| ID | CompanyName | Phone |

+-----+------------------------+-------------------------+

| 1 | Speedy Express | (503) 555-9831 |

| 2 | United Package | (503) 555-3199 |

| 3 | ♦ Diamond Shipping | (11) 555-2167 |

+------+-----------------------+-------------------------+

ConvWToUtf = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver converts strings passed to Unicode ODBC calls (with suffix "W") to UTF-8. The Easysoft ODBC-SQL Server Driver also converts metadata and result sets returned by Unicode ODBC calls to UTF-8.

By default, ConvWToUtf is OFF (set to 0).

SQLServerUTF = 0 | 1

When ON (set to 1), the Easysoft ODBC-SQL Server Driver sets the ConvWToUtf attribute to 1 prior to connecting to the data source. This provides a workaround for applications that pass UTF-8 encoded strings to SQLConnectW, SQLDriverConnectW and SQLBrowseConnectW.

The SQLServerUTF attribute must be specified in a section named ODBC in odbc.ini. For example:

[ODBC]

SQLServerUTF = Yes

By default, SQLServerUTF is OFF (set to 0).

Client_CSet = encoding

Specifies the encoding on the Easysoft ODBC-SQL Server Driver machine.

If set, the Easysoft ODBC-SQL Server Driver tries to convert to and from the specified encoding when retrieving and submitting character data. For example, if you have an application that is expecting EUC-JP encoded character data, you would need to set Client_CSet to specify the EUC-JP encoding:

# Convert from EUC-JP when submitting data to SQL Server

# Convert to EUC-JP when retrieving data from SQL Server

Client_CSet = EUC-JP

All character data is affected by Client_CSet, including data stored in char, varchar, text, nchar, nvarchar, nvarchar(max) and ntext columns, metadata (table names and so on) and SQL statement text and parameters.

Use Client_CSet if you experience data loss/corruption when working with character data and your application cannot convert data to the encoding scheme it expects.

The Easysoft ODBC-SQL Server Driver uses a built-in version of iconv to do the conversion. For a list of available encodings for Client_CSet, run this command on the machine where the Easysoft ODBC-SQL Server Driver is installed:

iconv -l

Set Client_CSet to the encoding that corresponds with the LANG environment variable value on the client machine. For example, if LANG was set to en_US.UTF-8 on the client machine, you would set Client_CSet to UTF-8.

If iconv cannot convert a character, the Easysoft ODBC-SQL Server Driver will omit the character and write this entry to the unixODBC or driver log file (assuming logging is enabled):

One or more characters in the input stream could not be converted

 

Note that if your client machine encoding is UTF-8, you can use Client_CSet as an alternative to ConvToUTF. You do not need to set both data source attributes.

If you specify a Server_CSet value without specifying a Client_CSet value, the Easysoft ODBC-SQL Server Driver uses ISO8859-1 as the client machine encoding.

Server_CSet = encoding

Specifies the SQL Server encoding for non-Unicode character data.

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the specified encoding when retrieving SQL Server data stored in char, varchar and text columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the specified encoding when binding parameter markers. For example:

INSERT INTO my_table(my_char_col, my_varchar_col, my_text_col) VALUES (?,?,?)

SELECT * FROM my_table WHERE my_varchar_col = ?

Use Server_CSet if you experience data loss/corruption when working with data stored in char, varchar and text columns.

The Easysoft ODBC-SQL Server Driver links to iconv on your machine at run-time to do the conversion.

Set Server_CSet to the iconv encoding that corresponds with the SQL Server code page. To find out the SQL Server code page, run:

SELECT COLLATIONPROPERTY('collation' , 'CodePage') AS CodePage

where collation is the SQL Server database collation, if set, otherwise the SQL Server instance collation.

 

In the following example, collation would be Cyrillic_General_CI_AS.

SELECT DATABASEPROPERTYEX('MyDatabase', 'Collation') SQLCollation;

SQLCollation

------------

NULL

SELECT SERVERPROPERTY('Collation') SQLCollation;

SQLCollation

------------

Cyrillic_General_CI_AS

For a list of iconv encodings, run this command on the machine where the Easysoft ODBC-SQL Server Driver is installed:

iconv -l

As an example, if the SQL Server collation was Cyrillic_General_CI_AS, the associated code page is 1251, and you would set Server_CSet to WINDOWS-1251.

If you set the Client_CSet attribute without setting the Server_CSet attribute, the Easysoft ODBC-SQL Server Driver uses the ISO8859-1 encoding as the Server_CSet value.

Server_UCSet = encoding

Specifies the SQL Server encoding for character data.

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the specified encoding when retrieving SQL Server data stored in nchar, nvarchar and ntext columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the specified encoding when binding parameter markers.

The default Unicode encoding is UTF-16le.

Use_LCID = 0 | 1

Whether to automatically work out which character set to use based on the SQL Server column or instance Locale ID (LCID).

If set, the Easysoft ODBC-SQL Server Driver tries to convert character data from the character set when retrieving SQL Server data stored in char, varchar and text columns. The Easysoft ODBC-SQL Server Driver also tries to convert strings to the character set when inserting data into char, varchar and text columns.

The ODBC application must bind the character data as a Unicode data type (for example, a SQL_WCHAR).

This feature emulates the Microsoft Native Client ODBC Driver's automatic translation of character data, and, as is the case with the Microsoft driver, not all character sets are supported.

If your character set is not supported, the Client_CSet and Server_CSet attributes provide an alternative conversion mechanism.

By default Use_LCID is OFF (set to 0).

LCID = localeid

Sets the ClientLCID the parameter in the TDS login packet. The ClientLCID parameter is described in the TDS protocol specification:

http://download.microsoft.com/download/a/e/6/ae6e4142-aa58-45c6-8dcf-a657e5900cd3/%5BMS-TDS%5D.pdf

Trusted_Domain = value

The Windows domain that the user specified with User belongs to.

If the user belongs to the same domain as the one that the SQL Server machine is in, you can omit Trusted_Domain. The Easysoft ODBC-SQL Server Driver automatically detects the domain in this case.

If you specify a Domain with Trusted_Domain, set Trusted_Connection to 1 and omit the domain from User. For example:

# Windows authentication

User = mylocalmachineuser

Password = mypassword

Trusted_Connection = 1

Trusted_Domain = mymachinename

Trusted_Connection = 0 | 1

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

NTLMv2 = 0 | 1

If you want to use NTLMv2 to authenticate the Windows user specified with User, set set NTLMv2 to 1. Otherwise, leave NTLMv2 set to its default value 0 (OFF).

IPv6 = 0 | 1

Set IPv6 to 1 when connecting to a SQL Server 2005 or later instance that is listening on an IPv6 address.

By default, IPv6 is OFF (set to 0), which means that the Easysoft ODBC-SQL Server Driver assumes that the target SQL Server instance is listening on an IPv4 address.

For more information about IPv6, see Connecting to SQL Server 2005 or Later by Using IPv6.

ConnectionTimeout = num

The number of milliseconds to wait for any request on the connection to complete before returning to the application. After the initial connection to the SQL Server machine has been established, the Easysoft ODBC-SQL Server Driver will wait num milliseconds each time it needs a response from SQL Server. If no response is received from SQL Server before the timeout expires, the Easysoft ODBC-SQL Server Driver returns the error Timeout expired.

The default value 0 means that no connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_CONNECTION_TIMEOUT connection attribute will override ConnectionTimeout.

LogonTimeout = num

The number of milliseconds to wait for a TCP connection to the SQL Server machine to be established before returning to the application. When you define a timeout, the initial connection phase lasts for num milliseconds. If the Easysoft ODBC-SQL Server Driver is unable to connect to the target SQL Server machine before the timeout expires, it returns the message Connection timeout expired. Note that if you specify a named instance in the Server attribute value, the driver returns a different timeout message: Failed to get datagram from socket.

The default value 0 means that no initial connection timeout is applied by the Easysoft ODBC-SQL Server Driver.

The Easysoft ODBC-SQL Server Driver classes the connection phase as obtaining the IP address of the SQL Server machine and connecting to it. This means that if you specify the Server attribute value as a machine name rather than an IP address, your system resolver library will be used (possibly examining /etc/hosts or doing a DNS query). On some operating systems, gethostbyname(), the call used to resolve a machine name into an IP address, cannot be interrupted and the connection timeout will not work. If this is a problem for you, either specify the SQL Server machine as an IP address or tell your resolver library to consult /etc/hosts before DNS and place an entry in /etc/hosts.

A timeout set by calling SQLSetConnectAttr with the SQL_ATTR_LOGIN_TIMEOUT connection attribute will override LogonTimeout.

RcvBuffer = num

The size of the receive buffer for the socket in bytes. Possible values for num are:

0, do not set the receive buffer size, use the system default value.

n, where n is a number greater than 0, set the receive buffer to the specified size by passing n to the setsockopt() function .

By default, the system default receive buffer size is used.

SoKeepalive = 0 | 1

Whether to use TCP keepalive probes to verify that an idle connection is still intact.

When ON (set to 1), keepalive probes are sent, after a period of inactivity, to verify that the connection to the SQL Server machine is still valid. To do this, the Easysoft ODBC-SQL Server Driver sets the SO_KEEPALIVE socket option by using setsockopt(). If no response to the probes is received, the socket is closed.

The duration of the period of inactivity is a system default, and is typically two hours.

By default, SoKeepalive is OFF (set to 0).

PacketSize = num

The TDS packet size in bytes that the Easysoft ODBC-SQL Server Driver will request. The specified packet size must be lower than 65536 bytes.

The default packet size is 4096 bytes.

ColumnEncryption = Enabled | Disabled

Set this attribute to Enabled if you want to query or update data held in an Always Encrypted column. You will also need to set the Driver attribute to Easysoft ODBC-SQL Server SSL.

Always Encrypted columns were introduced in SQL Server 2016. For more information, see:

http://www.easysoft.com/blog/sql-server-always-encrypted.html

Allow_C_Comment = 0 | 1

SQL comments are nonexecuting text strings used to document or temporarily disable SQL statements. SQL Server supports comments preceded by double hyphens (--) or delimited by forward slash-asterisk character pairs (/* ... */).

By default, the Easysoft ODBC-SQL Server Driver strips single line /* ... */ comments from SQL statements before passing the SQL to SQL Server. For example:

SELECT ContactID, /* FirstName, */ LastName FROM Person.Contact

becomes:

SELECT ContactID, LastName FROM Person.Contact

To preserve single line /* ... */ comments in the SQL that is passed to SQL Server, set the Allow_C_Comment attribute to 1.

Kerberos = 0 | 1

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

When ON (set to 1), the Easysoft ODBC-SQL Server Driver will attempt to obtain a service ticket for the following Service Principal Name (SPN):

MSSQLSvc/server:port

where:

server is the name or IP address of the SQL Server machine specified with the Server attribute.

port is the port on which the SQL Server instance is listening, which is specified with the Port attribute.

The ServerSPN attribute provides an alternative way to supply a service principal name.

Do not specify a User or Password value in the data source if you set Kerberos to 1. 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

By default, Kerberos is OFF (set to 0), and the Easysoft ODBC-SQL Server Driver will use either SQL Server or Windows authentication (see SQL Server Authentication Modes) to validate a user specified in the data source.

ServerSPN = value

The Service Principal Name (SPN) for a SQL Server instance that has been registered as a Kerberos service.

Your database administrator will have registered an SPN for your SQL Server instance. Contact your database administrator for the SPN value and then specify that value with ServerSPN.

As an alternative to the ServerSPN attribute, you can set the Kerberos attribute to 1 and the Easysoft ODBC-SQL Server Driver will build an Service Principal Name from the Server and Port attribute values.

If the SPN contains an instance name (for example, MSSQLSvc/mysqlservermachine:myinstance), you need to use the ServerSPN attribute rather than the Kerberos attribute.

Do not specify a User or Password value in the data source if you specify a ServerSPN value.

FailoverServerSPN = value

The SPN for a mirror server instance that has been registered as a Kerberos service. For more information about Database Mirroring see Database Mirroring. For more information about Kerberos and Database Mirroring, see the Easysoft tutorial:

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

GSSLib = value

The Easysoft ODBC-SQL Server Driver uses libgssapi_krb5.so, the Kerberos GSS-API library, to request service tickets for accessing SQL Server instances. If the Easysoft ODBC-SQL Server Driver is unable to open this library, the connection will fail with the error:

Krb5: failed to open gss lib (libgssapi_krb5.so)

If the Kerberos GSS-API library is not called libgssapi_krb5.so in your GSS-API distribution, use the GSSLIB attribute in your data source to specify the alternative GSS-API library. For example:

GSSLIB = /opt/extension/lib/libgssapi.so

GSSHost = 0 | 1

Whether the Easysoft ODBC-SQL Server Driver allows the use of GSS_C_NT_HOSTBASED_SERVICE or GSS_C_NT_USER_NAME as the target principal name.

When ON (set to 1), the Easysoft ODBC-SQL Server Driver allows the use of GSS_C_NT_HOSTBASED_SERVICE.

By default, GSSHOST is OFF (set to 0).

GSSFlag = req_flags

The Easysoft ODBC-SQL Server Driver allows you to pass req_flags to the gss_init_sec_context() function, which is used to initiate a security context for the driver. The Key Distribution Center (KDC) uses this security context to verify the identity of the client. To pass req_flags to gss_init_sec_context(), use the GSSFLAG attribute:

GSSFLAG = req_flags

where req_flags is a bitmask specifying the requested GSS services. To look up the available bitmask values, refer to the gssapi.h header file for the GSS-API distribution on the Easysoft ODBC-SQL Server Driver machine. The driver default GSSFLAG value is 4, which sets the GSS_C_REPLAY_FLAG flag.

As an example, to request credential delegation, set the GSS_C_DELEG_FLAG flag by including this line in your data source:

GSSFLAG = 1

Figure 11: Easysoft ODBC-SQL Server Driver data source settings.

ODBC Driver Manager Attribute Fields

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

Attribute Description

SQLServerUTF = 0 | 1

When ON (set to 1), the ConvWToUtf attribute is set to 1 prior to connecting to the data source. This provides a workaround for applications that pass UTF-8 encoded strings to SQLConnectW, SQLDriverConnectW and SQLBrowseConnectW.

The SQLServerUTF attribute must be specified in a section named ODBC in odbc.ini. For example:

[ODBC]

SQLServerUTF = Yes

By default, SQLServerUTF is OFF (set to 0).

Figure 12: ODBC Driver Manager attribute fields.

Environment

The Easysoft ODBC-SQL Server Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-SQL Server Driver installation:

By default, this is located in /usr/local/easysoft/unixODBC/lib.

By default, this is located in /usr/local/easysoft/lib.

By default, this is located in /usr/local/easysoft/lib.

By default, this is located in /usr/local/easysoft/lib.

For more information about libestdscrypt.so, see Windows Authentication.

You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.


Note

The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl).


Establishing a Test Connection

The isql query tool lets you test your Easysoft ODBC-SQL Server Driver data sources.

To test the Easysoft ODBC-SQL Server Driver connection

1.  Change directory into /usr/local/easysoft/unixODBC/bin.

2.  Type ./isql -v data_source, where data_source is the name of the target data source.

 If you are unable to connect, see Troubleshooting Database Connection Problems for help on solving some common connection problems.

3.  At the prompt, type an SQL query. For example:

 SQL> select * from mytable;

¯ OR ¯

 Type help to return a list of tables:

 SQL> help

 

Troubleshooting Database Connection Problems

This section lists some common connection problems and their solutions.

Client unable to establish connection: OS Error: 'Failed to find host address 'myhost\myinstance'

Check the Server attribute in your data source specifies a valid machine name or IP address. Check that the machine name can be looked up by using DNS or is present in /etc/hosts. Check that you are on the same network as the target host by pinging the machine:

ping myhost

If ping times out or fails, then either the DNS lookup is not working properly or there is some other networking or routing issue that needs to be resolved. Contact your network administrator.

Client unable to establish connection: OS Error: 'Connection refused'

Check that the SQL Server instance that you are trying to connect to is running.

On the SQL Server machine, "SQL Server <instance>" will be listed in output of the net start command, if the SQL Server instance is running.

If SQL Server is listening on a fixed TCP port, check that you can use telnet to connect to the port that you have specified in the data source:

telnet hostname port

where hostname is the host name or IP address of the machine where SQL Server is running and port is the port number that you have specified with the Port attribute. If the SQL Server instance is listening on this port, you will see output similar to:

Connected to myserver

Escape character is '^]'

To exit from telnet, type CTRL-] and then quit.

If you do not see this output or a "Connection refused" error displays, SQL Server is not listening on the specified port. Contact your database administrator for the correct SQL Server port.

If you are using the correct port but are unable to connect with telnet, the SQL Server instance may not allow remote TCP/IP connections. See Client unable to establish connection: Server not configured for TCP connection.

Client unable to establish connection: Server not configured for TCP connection

The TCP/IP protocol must be enabled in the instance that you are trying to connect to.

In the SQL Server Configuration Manager, in the list of network protocols for the instance, the status for TCP/IP must be set to "Enabled".

By default, SQL Server 2005 and later do not allow remote connections, which means that the default setting for TCP/IP is "Disabled".

Client unable to establish connection: OS Error: 'Failed to get datagram from socket'

The Easysoft ODBC-SQL Server Driver uses the SQL Server Browser or the SQL Server 2000 listener service to find out what TCP port SQL Server is listening on. If the SQL Server Browser or listener service is not running and active, the Easysoft ODBC-SQL Server Driver will be unable to open a connection for this purpose and the "Failed to get datagram from socket" error displays.

On the SQL Server machine, "SQL Server Browser" will be listed in output of the net start command, if the SQL Server Browser is running. If net start shows that the SQL Server Browser service is running, the service may not be active. In the SQL Server Configuration Manager, the Active option must be set to "Yes" in the Advanced SQL Server Browser property tab. (The SQL Server Browser service must be restarted before any change to this setting takes effect.)

If you are connecting to SQL Server through a firewall, the firewall needs to allow connections through:

If UDP port 1434 is not open, the firewall will block the connection when the Easysoft ODBC-SQL Server Driver attempts to discover the SQL Server port and the 'Failed to get datagram from socket' will display.

Because the SQL Server Browser or listener accepts unauthenticated UDP requests, it may have been turned off as a security measure, and your database administrator will have configured each SQL Server instance to listen on a specific TCP port. You need to specify this port number with the Port setting. For example, if SQL Server is listening on port 1500, add this line to the data source in odbc.ini:

Port = 1500

The "Failed to get datagram from socket" error also displays if you try to connect to a hidden SQL Server instance. You need to specify the port that the hidden instance is listening even though the SQL Server Browser or listener may be running.

Login failed for user ''. The user is not associated with a trusted connection

Check that the User and Password attributes for the data source in the odbc.ini specify a valid Windows user name and password.

This error also displays if you try to connect to SQL Server with a SQL Server user name and password but SQL Server's authentication mode is set to Windows Authentication only. To connect by using a SQL Server account, the security mode for the SQL Server instance must be changed to mixed (both SQL Server and Windows authentication are enabled).

To enable mixed mode, your database administrator must set the SQL Server security property Server Authentication to SQL Server and Windows Authentication mode. Note that Microsoft recommend that Windows authentication is used to connect to SQL Server whenever possible.

Login failed for user 'myuser'.

Check that the User and Password attributes for the data source in the odbc.ini specify a valid SQL Server user name and password.

This error also displays if you try to connect to SQL Server with a valid Windows user name and password but no corresponding SQL Server login exists. For example, SQL Server Setup creates a login named BUILTIN\Administrators that allows members of the local Administrators Windows group to access SQL Server. As a security measure, the database administrator may delete this login and members of this group will then need individual SQL Server login accounts to access SQL Server.

Ask your database administrator to create a SQL Server login for you that uses Windows authentication to validate your connection details.

DSN-less Connections

In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:

SQLDriverConnect(..."DRIVER={Easysoft ODBC-SQL Server};

Server=server;UID=user;PWD=password;

Port=port;"...)

where server is the SQL Server instance that you want to connect to, user and password are a valid SQL Server login and password and port is the TCP port that SQL Server is listening on. You need to use the Easysoft ODBC-SQL Server DRIVER keyword to identify the Easysoft ODBC-SQL Server Driver.

Other Easysoft ODBC-SQL Server Driver attribute settings, as described in Setting Up Data Sources on Unix, can be added to the connection string using the same PARAMETER=value; format. For example, the following connection string changes the default database with the Database attribute:

"DRIVER={Easysoft ODBC-SQL Server};Server=myhost\\SQLEXPRESS;UID=mydomain\\myuser;PWD=mypassword;Port=1500;Database=Sales;"