INFO: Registry Entries and Keywords for SQL Server Connection Strings

Article translations Article translations
Article ID: 229929 - 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.

MORE INFORMATION

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 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:
<B>HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini</B>
If you create a user DSN, the settings are saved in a registry key under the following:
<B>HKEY_CURRENT_USER\Software\ODBC\Odbc.ini</B> <BR/><BR/>
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 NameDefault ValueOther Possible ValuesRemarks
ServerDSN nameAny server name.Change this value to point to your server.
DatabaseNoneAny database name inside the preceding server.Change this value to point to your database.
Languageus_englishAny 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.
UseProcForPrepare10 or 2Starting 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_ConnectionNoYesThis property determines whether you want to use SQL Server Authentication or NT Authentication. Trusted_Connection=NO means that you want to use SQL Authentication.
QuotedIdYesNoThis determines whether to use quoted identifier (for example, a double quote around the table name) or not.
AnsiNPWYesNoThis 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.
AutoTranslateYesNoDetermines whether binary data is treated like character data.
QueryLog_OnNoYesDetermines whether a long running query will be logged.
QueryLogTime30000 in millisecondsAny 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.
QueryLogFileC:\Query.logAny 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.
RegionalNoYesRespect 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_OnNoYesEnables driver performance logging. The default value is "Disables Driver Performance Logging".
StatsLogFileC:\Stats.logAny 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:
<B>HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo</B>
You can use the following in the connection string of your application:

Collapse this tableExpand this table
KeywordFor TCP/IPFor Named Pipes
AddressYourServerName,ThePortNumber (default 1433)\\.\pipe\sql\query
NetworkDBMSSOCNDBNMPNTW


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.

Properties

Article ID: 229929 - Last Review: April 20, 2012 - Revision: 3.0
APPLIES TO
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
Keywords: 
kbinfo KB229929

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com