INFO: Registry Entries and Keywords for SQL Server Connection Strings

View products that this article applies to.

This article was previously published under Q229929

Expand all | Collapse all

SUMMARY

This article describes the different registry entries that are created when you create a data source name (DSN) by using the Microsoft SQL Server ODBC Driver.

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 (http://support.microsoft.com/kb/256986/EN-US/ ) Description of the Microsoft Windows Registry

The information presented in this article is for troubleshooting purposes only. There may be other parameters that are not documented here.

When you create a system DSN in the ODBC Data Source Administrator in Control Panel, and you use the Microsoft SQL Server ODBC driver, the DSN settings are saved in a registry key under the following with the same name of your DSN:

HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini

If you create a user DSN, the settings are saved in a registry key under the following:

HKEY_CURRENT_USER\Software\ODBC\Odbc.ini

The Microsoft SQL Server ODBC Driver setup also writes parameters to the Odbc.ini registry entry. There are several parameters in the Microsoft SQL Server ODBC Driver setup that have a default value.

When you first start to configure a DSN for SQL Server you will see that some of the parameters already have values. If you do not change the default value of a parameter you will not see a corresponding registry entry.

For example, there is a parameter in the setup named Use ANSI quoted identifiers, which is selected by default. If you clear that parameter you will see an entry in the corresponding DSN, under Odbc.ini in the registry, named QuotedId and the value is No. By default the value is Yes and you will not see the value in the registry if you keep the default value in your setup.

In case of authentication, the default is SQL Server Authentication. If you select NT Authentication you will see a registry entry named Trusted_Connection set to Yes. Therefore, if you do not have any entry in the registry that corresponds to Trusted_Connection, this means that you are using SQL Server Authentication. You can also set this Trusted_Connection to No from your program to get SQL Server Authentication.

The following table lists several parameters and their default values:

Collapse this tableExpand this table

Parameter Name	Default Value	Other Possible Values	Remarks
Server	(local)	Any server name.	Change this value to point to your server.
Database	None	Any database name inside the preceding server.	Change this value to point to your database.
Language	us_english	Any valid language name for your server.	SQL Server language name. SQL Server can store messages for multiple languages in the sysmessages system table. If you connect to a SQL Server with multiple languages, the Language setting specifies which set of messages are used for the connection.
UseProcForPrepare	1	0 or 2	Starting with Microsoft SQL Server driver version 3.7, or later, this property is disabled. Microsoft SQL Server 7.0, and later, always uses a temporary stored procedure whether or not you prepare a SQL statement before execution.
Trusted_Connection	No	Yes	This property determines whether you want to use SQL Server Authentication or NT Authentication. Trusted_Connection=NO means that you want to use SQL Authentication.
QuotedId	Yes	No	This determines whether to use quoted identifier (for example, a double quote around the table name) or not.
AnsiNPW	Yes	No	This determines whether to use ANSI null padding and warnings or not. If set to Yes and you have character data of size 10 (not varchar) in your SQL Server, and you select that data, it returns all 10 characters. If the data only has 2 characters (like "AA"), you will get AA with 8 blank spaces padded at the end.
AutoTranslate	Yes	No	Determines whether binary data is treated like character data.
QueryLog_On	No	Yes	Determines whether a long running query will be logged.
QueryLogTime	30000 in milliseconds	Any integer number. (This setting is used when QueryLog_On is set to Yes.)	Digit character string that specifies the threshold (in milliseconds) for logging long-running queries. Any query that does not receive a response in the time specified is written to the long-running query log file.
QueryLogFile	C:\Query.log	Any valid filename on your disk. (This setting is used when QueryLog_On is set to Yes.)	Full path and name of the file used to log long-running queries.
Regional	No	Yes	Respect client workstation settings for the region when converting date, time, and currency values to character strings. This setting should only be specified for applications that only display data, not for applications that process data.
StatsLog_On	No	Yes	Enables driver performance logging. The default value is "Disables Driver Performance Logging".
StatsLogFile	C:\Stats.log	Any valid filename in your disk. (This setting is used when StatsLog_On is set to Yes.)	Full path and name of the file used to record SQL Server ODBC driver performance statistics.

In future versions of the SQL Server ODBC driver there may be some additional parameters. All of the parameters have a default value, so it will not affect your current setup. This is why some parameters have no entries in the registry.

If you select the default value for a parameter there will be no corresponding registry entry. You will find a registry entry only when you change the default value. You can also overwrite the default behavior by setting the keywords in your connection string.

There are two other keywords that are also very important, Network and Address. Neither of these two keywords are found under the SQL Server ODBC entries in the registry.

These keywords control the network connectivity. You can set up the network protocol that your client uses to connect to SQL Server by clicking the Client Configuration button in the ODBC Data Source Administrator for SQL Server. If you edit the Client Configuration, you will find a corresponding registry entry under:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo

You can use the following in the connection string of your application:

Collapse this tableExpand this table

Keyword	For TCP/IP	For Named Pipes
Address	YourServerName,ThePortNumber (default 1433)	\\.\pipe\sql\query
Network	DBMSSOCN	DBNMPNTW

NOTE: You can also use the preceding keywords when you are connecting to SQL Server using the OLE DB Provider for SQL Server (SQLOLEDB) directly with your connection string.