An ODBC application has two methods of giving an ODBC driver the information the driver needs to connect to the proper server and database. Either the application can connect using an existing ODBC data source containing this information, or it can call either SQLDriverConnect or SQLBrowseConnect, which provides the information in the connection string parameter.
ODBC data sources contain information that tells a driver how to connect to a database. ODBC data sources can be created by using the ODBC Administrator application in Control Panel or by an application calling the ODBC SQLConfigDataSource function.
Data source definitions are stored in C:\Windows\System\Odbc.ini for the Microsoft Windows® version 3.x and Windows for Workgroups version 3.x operating systems.
Win32 data sources fall into one of two categories (for details, see Microsoft Knowledge Base article Q136481):
On the Microsoft Windows NT® operating system, user data sources are specific to the Windows NTaccount under which they were defined. User-specific data sources are not always visible to applications running as Windows NT services. Windows 95 data sources are stored in the following registry key:
HKEY_CURRENT_USER\Software\ODBC\Odbc.ini.
On Windows NT, system data sources are visible to all Windows NTaccounts on the computer. System data sources are always visible to applications running as Windows NTservices. The ODBC driver manager that ships with Microsoft Office 97 also supports system data sources on Windows 95 clients. Windows NTsystem data sources are stored in the following registry key:
HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini.
Information about the drivers installed on a client is stored in C:\Windows\System\Odbcinst.ini in Windows 3.x or Windows for Workgroups 3.x and in HKEY_LOCAL_MACHINE\Software\ODBC\Odbcinst.ini in Windows NTand Windows 95.
Each driver needs to store driver-specific information in its data sources. When a user adds a data source using ODBC Administrator, the driver displays a dialog box, where the user specifies data source information. When a data source is defined with SQLConfigDataSource, the function accepts an attribute string parameter that can contain driver-specific keywords. All of the SQLConfigDataSource driver-specific keywords for the SQL Server ODBC driver have counterparts in the dialog box that displays when using ODBC Administrator.
Here's an example SQLConfigDataSource call that sets up a SQL Server data source referencing a server using DHCP on TCP/IP:
RETCODE retcode;
UCHAR *szDriver = "SQL Server";
UCHAR *szAttributes =
"DSN=my65dsn\0DESCRIPTION=SQLConfigDSN Sample\0"
"SERVER=my65server\0ADDRESS=HRServer\0NETWORK=dbmssocn\0"
"DATABASE=pubs\0";
retcode = SQLConfigDataSource(NULL,
ODBC_ADD_DSN,
szDriver,
szAttributes);
The following sections describe the driver-specific keywords supported by the Microsoft SQL Server ODBC driver.
The SERVER, NETWORK, and ADDRESS parameters associate a data source with a specific instance of SQL Server on the network. These parameters are directly related to the advanced entries created with the SQL Server Client Configuration Utility:
If ADDRESS is present, it is always used as the network address for the connection. If ADDRESS is not present, then SERVER is used as the network address for the connection.
Here's an example entry to make a named pipes connection to a server:
SERVER=xyz,NETWORK=dbnmpntw,ADDRESS=HRServer
The following entry evaluates to the same network address:
SERVER=HRServer,NETWORK=dbnmpntw
Here's an example entry to make a sockets connection to the same computer:
SERVER=tcpxyz,NETWORK=dbmssocn,ADDRESS=123.123.123.123,1433
There are two special cases to consider:
The ODBC data source for this case is specified as:
SERVER=(local),NETWORK=(default),ADDRESS=(default)
When using this data source, the driver attempts to connect to a SQL Server on the same computer using Windows NTlocal-named pipes instead of a network implementation of named pipes.
An example of an entry for this case is:
SERVER=HRServer,NETWORK=(default),ADDRESS=(default)
The default Net-Library is set using the SQL Server Client Configuration Utility.
The SERVER, NETWORK, and ADDRESS parameters specified on SQL Server ODBC driver data sources operate the same way as the Server, DLL, and Connection String parameters specified for advanced entries made with the SQL Server Client Configuration Utility. For more information about the advanced-entry parameters, see the Microsoft SQL Server Administrator's Companion. The same parameters can be specified in the data source creation dialog box displayed in ODBC Administrator.
The relationship between the parameters is illustrated in the following table.
| SQLConfigDataSource | ODBC Administrator | SQL Client Configuration Utility |
| SERVER | Server | Server |
| NETWORK | Network Library | DLL |
| ADDRESS | Network Address | Connection String |
If a data source is defined with the SERVER, NETWORK, and ADDRESS parameters, a SQL Server advanced connection entry is made in the registry, and can be viewed using the SQL Client Configuration Utility.
This parameter specifies the default database for the ODBC data source.
This parameter specifies the default national language to use.
This parameter specifies whether to convert extended characters to OEM values.
SQL Server is usually run with one of three code pages:
The default code page for U.S. MS-DOS computers.
The code page typically used by UNIX systems.
The code page defined as a standard by the ANSI and ISO standards organizations. The default code page for U.S. Windows computers. Sometimes called the 1252 code page.
The 437 and 850 code pages are sometimes collectively referred to as the OEM code pages.
All three code pages define 256 different values to use in representing characters. The values from 0 to128 represent the same characters in all three code pages. The values from 129 to 255, which are known as the extended characters, represent different characters in all three code pages.
Because ODBC applications are Windows applications, they generally use ANSI code page 1252. If they are communicating with a SQL Server also running ANSI code page 1252, there is no need for character-set conversion. If they connect to a server running a 437 or 850 code page however, the driver must be informed that it should convert extended characters from their 1252 values to 437 or 850 values before sending them to the server. In this case, the data source should have OEMTOANSI=YES. For a more in-depth discussion of SQL Server code pages, see Microsoft Knowledge Base article Q153449.
This parameter specifies the name of the ODBC translation DLL to use with the data source.
This parameter specifies the name of the translator to use with the data source.
This parameter specifies whether translation should be done on the data going to SQL Server. YES specifies translation; NO specifies no translation. For more information about ODBC translation, see the ODBC 2.0 Programmer's Reference.
This parameter specifies whether the driver generates stored procedures to support the ODBC SQLPrepare function. For more information, see "SQLExecDirect vs. SQLPrepare/SQLExecute."
The following driver-specific SQLConfigDataSource keywords are new in SQL Server 6.5 SP2.
This parameter specifies whether the driver should issue a SET QUOTED IDENTIFIERS ON option when connecting to a SQL Server version 6.0 or later database. YES specifies QUOTED_IDENTIFIERS is ON; NO specifies the option is OFF. For more information, see "SET Options Used by the Driver."
This parameter specifies whether the driver should SET ON the ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS options when connecting to a SQL Server version 6.5 or later database. YES specifies the options are ON; NO specifies they are OFF. For more information, see "SET Options Used by the Driver."
The following driver-specific SQLConfigDataSource keywords are new in SQL Server 6.5.
This parameter specifies the file name the driver should use to log long-running queries. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."
This parameter specifies whether the data source should do query profiling. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."
This parameter specifies the interval for long-running queries. The interval is specified in milliseconds. If a query is outstanding for a period exceeding the QueryLogTime, it is written to the QueryLogFile. For more information, see "ODBC Driver Profiling Features."
This parameter specifies the file name the driver should use to log long performance statistics. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."
This parameter specifies whether the data source should log performance statistics. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."
This parameter specifies whether the data source should use trusted connections when connecting to SQL Server. 1 specifies trusted connections; omitting the parameter specifies no trusted connections. For more information, see "Integrated and Standard Security."
When you add, modify, or double-click a SQL Server data source in ODBC Administrator, the SQL Server ODBC driver displays the ODBC SQL Server Setup dialog box. The parameters in this dialog box control the same features that are controlled by the SQLConfigDataSource keywords earlier in this paper, although they have slightly different names. Many of the options are in the dialog box that displays when you click Options. To specify the query and performance profiling options, click Options, and then click Profiling.
An ODBC application can connect to a SQL Server without referencing a data source:
RETCODE retcode;
UCHAR szDSN[MAXBUFLEN+1] =
"DRIVER={SQL Server};SERVER=MyServer;"
"UID=sa;PWD=astring;APP=Generic32;DATABASE=pubs";
UCHAR szUID[MAXUID+1] = "sa",
szAuthStr[MAXAUTHSTR+1] = "password",
szConnStrOut[MAXBUFLEN+1];
SWORD swStrLen;
retcode = SQLDriverConnect(hdbc1,
NULL,
szDSN,
(SWORD)strlen(szDSN),
szConnStrOut,
MAXBUFLEN,
&swStrLen,
SQL_DRIVER_NOPROMPT);
The SQL Server ODBC driver supports three classes of keywords on SQLDriverConnect:
The SQL Server ODBC driver supports the four standard ODBC SQLDriverConnect keywords: DSN, UID, PWD, and DRIVER.
On SQLDriverConnect the SQL Server ODBC driver supports all of the driver-specific keywords it supports for SQLConfigDataSource. See the list earlier in this paper for a description of these driver-specific keywords.
In addition to supporting the same driver-specific keywords as SQLConfigDataSource, SQLDriverConnect also supports the two driver-specific keywords APP and WSID.
This keyword specifies the application name to be recorded in the program_name column in master.dbo.sysprocesses. APP is equivalent to a DB-Library application calling the DBSETLAPP function in C or the SQLSetLApp function in the Visual Basic® programming system.
This keyword specifies the workstation name to be recorded in the hostname column in master.dbo.sysprocesses. WSID is equivalent to a DB-Library application calling the DBSETLHOST function in C or the SQLSetLHost function in Visual Basic.
The SQL Server ODBC driver returns SQL_SUCCESS_WITH_INFO on a successful SQLConnect, SQLDriverConnect, or SQLBrowseConnect. When an ODBC application calls SQLError after getting SQL_SUCCESS_WITH_INFO, it can receive the following messages:
The following example shows these messages being returned on a successful connect by the System Administrator (SA) login. The SA login has its default database at the server defined as the master database, the server is running US English, and the connect used an ODBC data source that specified pubs as the default database.
Full Connect:
szSqlState = "01000", *pfNativeError = 5701,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'master'."
szSqlState = "01000", *pfNativeError = 5703,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed language setting to 'us_english'."
szSqlState = "01000", *pfNativeError = 5701,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
Changed database context to 'pubs'."
Successfully connected to DSN 'my60server'.
Applications can ignore these 5701 and 5703 messages; they are informational only. Applications cannot, however, ignore a return of SQL_SUCCESS_WITH_INFO return code on the SQLConnect, SQLDriverConnect, or SQLBrowseConnect. This is because messages other than 5701 and 5703 that do require action may be returned. For example, if a driver connects to a SQL Server with outdated system stored procedures, one of the messages returned through SQLError is:
SqlState: 01000
pfNative: 0
szErrorMsg: "[Microsoft][ODBC SQL Server Driver]The ODBC
catalog stored procedures installed on server
my421server are version 02.00.4127; version 06.00.0115
or later is required to ensure proper operation.
Please contact your system administrator."
An application's error handling routines for SQL Server connections should call SQLError until it returns SQL_NO_DATA_FOUND and act on any messages other than the ones that return a pfNative code of 5701 or 5703.
SQL Server offers three security models for authenticating connection attempts:
The SA defines SQL Server logins with passwords in SQL Server and then associates the logins with users in individual databases. With older versions of SQL Server, all connection attempts must specify a valid login and password. SQL Server version 6.0 or 6.5 also allows trusted connections to a server running standard security. SQL Server logins are separate from Windows NTuser IDs.
The SA defines logins for those Windows NTuser accounts that are allowed to connect to SQL Server. Users do not have to specify a separate login and password when they connect to SQL Server after logging on to the Windows NTnetwork. When they attempt to connect, the Net-Library attempts a trusted connection to SQL Server. If the user's Windows NTaccount is one that the SA specified to SQL Server, the connection succeeds.
The SA defines both SQL Server logins and Windows NTaccounts as SQL Server logins. Users with validated Windows NTaccounts can connect using trusted connections; other users can connect using standard security with the SQL Server logins.
The SQL Server ODBC driver always uses a trusted connection when connecting to a server running integrated security. The driver can also be instructed to open trusted connections when connecting to a server that is running with standard or mixed security. Only the named pipes or multiprotocol Net-Libraries support integrated security and trusted connections.
There are two ways to tell the driver to use trusted connections:
When defining a data source using the ODBC Administrator, you can select Use Trusted Connection. When defining a data source using SQLConfigDataSource, an application can specify Trusted_Connection=1.
Before making a connect request, the application can set a driver-specific option:
SQLSetConnectOption(hdbc, SQL_INTEGRATED_SECURITY, SQL_IS_ON);
Integrated security offers several benefits:
Integrated security is only available when using either the named pipes or multiprotocol Net-Libraries. When using the multiprotocol Net-Library, the SA can also configure the server to encrypt packets sent across the network, so that even users of network sniffers cannot see the data. The named pipes and multiprotocol Net-Libraries can also work with either a TCP/IP, SPX/IPX, or NetBEUI protocol stack. This means a client running only a TCP/IP protocol stack can use either the Windows sockets, named pipes, or multiprotocol Net-Libraries. The Windows sockets (TCP/IP), SPX/IPX, Appletalk, DECNet, and Banyan Vines Net-Libraries only work with their single, associated, protocol stack.
Due to their added functionality, such as the encryption feature, the multiprotocol Net-Libraries are somewhat slower than the others. Testing at Microsoft has found that the TCP/IP Net-Libraries are somewhat faster than the other Net-Libraries. Other considerations, however, such as database design, indexing, and the design of queries and applications, usually have a greater impact on performance than the choice of a Net-Library.
Applications running against SQL Server 6.0 or 6.5 can sometimes improve their performance by resetting the TDS network packet size. The default packet size is set at the server, and is 4K. 4K generally gives the best performance. Applications can set the packet size themselves if testing shows that they perform better with a different packet size. ODBC applications can do this by calling SQLSetConnectionOption with the SQL_PACKET_SIZE option before connecting. Some applications may perform better with a larger packet size, but performance improvements are generally minimal for packet sizes larger than 8K.
The Odbcping.exe utility can be used to check whether an ODBC connection can be made between a client and a SQL Server. The command syntax to use the utility is:
odbcping {/Sservername | /Ddatasource} /Ulogin_id /Ppassword
where
servername
Is the network name of the server running SQL Server.
datasource
Is the name of an ODBC data source.
login_id
Is the SQL Server login ID.
password
Is the login password.
You must specify either /S or /D, but not both. (The version of odbcping that ships with SQL Server 6.0 will not accept the /D parameter, only /S, /U, and /P.)
When odbcping makes a successful connection, it displays a message indicating the connection was successful and the versions of the driver and server. For example:
CONNECTED TO SQL SERVER
ODBC SQL Server Driver Version: 02.65.0201
SQL Server Version: SQL Server for Windows NT6.50 - 6.50.201 (Intel X86)
Apr 3 1996 02:55:53
Copyright (c) 1988-1997 Microsoft Corporation
If the connect attempt is not successful, odbcping displays the errors it receives. (The 6.0 version of odbcping does not display the Native Error code.) For example:
COULD NOT CONNECT TO SQL SERVER
SQLState: 01000 Native Error: 2
Error Message: [Microsoft][ODBC SQL Server Driver][dbnmpntw]
ConnectionOpen (CreateFile()).
SQLState: 08001 Native Error: 6
Error Message: [Microsoft][ODBC SQL Server Driver][dbnmpntw]
Specified SQL Server not found.
The pfNative (or Native Error) code is important in diagnosing connection problems. For more information, see "pfNative Error Codes."