Creating DSNs for SQL Server: In depth on how to make one.

The Open Database Connectivity (ODBC) was established based on the idea that many disparate applications needed a "consistent" way to talk to their databases in various RDBMS back-ends. This also allows different RDBMS systems to interact directly without having to go to "text file" imports and exports. The database can be directly connected to extract the data.

Note that this is oriented towards Windows 2003, Windows 2000 and Windows XP 32 bit (x386) architecture. I have not verfied these steps on the following operating systems: Windows 2008, Vista, or Windows 7.
 

1. Acronyms and Definitions:

DSN: Data Source Name
MDAC: Microsoft Data Access Components -- The set of SQL and other default drivers. With Windows XP SP1 and higher, they are automatically loaded to the newest version via Windows Update Services or WSUS.
HKLM & HKCU:      HKEY_LOCAL_MACHINE & HKEY_CURRENT_USER. Reference to sections of the registry hive
RDBMS: Relational DataBase Management System

2. Manually Configuring:

There are two ways to start the ODBC Administrator:
-->      Go into the Control Panel --> Administrative Tools --> Data Sources (ODBC)
-->      Alternative method: Start --> Run --> ODBCAD32 --> .
On a 64 bit operating system you need to access the ODBC Administrator via the %WINDIR%\SysWOW64\odbcad32 .exe to create ODBC calls usable by 32 bit (Office) applications.

The first screen that you will come to is this on the User DSN.
There is very little call to ever use a User DSN. The effect of this is that it will override a System DSN and could point to the wrong database or server. They should be restricted to developers use only.

The typical adds and changes will be done from the System DSN tab:
To create a new one click on the Add button. This will bring up a list of the various ODBC drivers available on the local machine. SQL Server is typically at the bottom of the list.
  Some PCs will have the SQL Native Client listed as an available option as well. This is the newer version of the SQL Server client, but is not consistently loaded on enough Windows XP workstations to make it the common choice. Your environment may differ. The use of the SQL Native Client isn't significantly different, but would require some adjustments in the registry setup.  This article will concentrate on the SQL Server config.

Select the SQL Server driver and you will arrive at this screen:
  -->      Type the name of the DSN. Typically this is in line with the application name or database you are trying to use.
-->      The Description is totally optional.
-->      The Server can be in several forms:
            1.      MySQLServerName
            2.      MySQLServerName\Insta nceName
            3.      IPAddress (i.e 10.65.1.3)
            4.      IPAddress\InstanceNam e (i.e 10.65.1.3\InstanceName)

The next screen concerns what form of security you are going to use connect to the server.
When it says NT authentication it is referring to Trusted Security or Active Directory logins. This is using your Windows login (MyDomain\MyUserName) to authenticate to the SQL Server. This is dependent on your SQL Server Administrator adding your userid (or group) from the domain into the SQL Server security with the appropriate permissions. The choice is also dependent on the application that will be using the ODBC call. Some, such as Access and other Microsoft Office applications can use either SQL or NT authentication. Some are totally restricted to SQL Authentication such as Great Plains.

Very rarely you will have to edit the Client Configuration. The reasons to make a change to it include:
     1.      You want to use a protocol other than TCP/IP (not recommended).
      2.      You are having issues with DNS that is the client is not
            resolving the DNS alias name to the Servers IP Address.
      3.      If you are using some port other than 1433 for your SQL Server.

The next screen you select your default database (or sometimes referred to as a catalog in a connection string).
  Dependent on the userid that you are using to connect to SQL Server, you may have access to multiple different databases. It is suggested that regardless of having access to only one or many, you always specify the default database by clicking on the "Change default database to:" check box.

Most SQL Servers are also not setup to use the ANSI settings by default. Uncheck them as a regular practice.

On the next screen you will have some additional options.
  Most SQL Servers are not setup that you need to have the "Perform translation for character data" check box turned on.

The Encryption check box is also specific to your environment. Some companies, government, and military settings will require internal encryption on the LAN. The issue with encryption is that it increases overhead and delays as the client has to encrypt the data/request, the SQL Server has to then decrypt it, then turn around and encrypt to send the response back to the client and subsequent decryption.

After you click the Finish you will get a Test Data Source and then a Completed Successfully display.
If it fails you can go back and edit setting by hitting the Cancel button. Otherwise hit OK.

You can edit any of the settings, including the DSN Name, by clicking on the DSN Name and clicking Configure. The only thing that you are not able to change is the original Driver selection.

[step="3"title="Registry Settings:"][]/step]
Normally you won't manually edit the ODBC settings in the registry. This is mostly used for development, troubleshooting, and deployment situations.

The hives for the ODBC Calls reside under the HKLM\SOFTWARE\ODBC\ and HKCU\SOFTWARE\ODBC\

The Drivers are in the HKLM\SOFTWARE\ODBC\ODBCINS T.INI group. This list can also be seen on the Drivers tab of the ODBC Data Source Administrator. Only on very rare occasions do you want to do any editing of the drivers list. It is in the same Header/Body style discussed later on.

The DSNs are in the HKLM & HKCU\SOFTWARE\ODBC\ODBC.IN I hives.

There are two portions to a DSN. The "header" and the "body". The header is an entry in the "\SOFTWARE\ODBC\ODBC.INI\O DBC Data Sources" key. It is the name of the DSN (ExampleDSN) and the corresponding driver (SQL Server).

Each Header in the "ODBC Data Sources" key needs a matching "body" (i.e. HKLM\SOFTWARE\ODBC\ODBC.IN I\ExampleD SN).

You can on occasion have an orphaned body. The ODBC.INI\ExampleDSN will be listed but you won't have a header listed in the "ODBC.INI\ODBC Data Sources". It does not hurt anything. This can happen for various reasons. In my company, we will push the server and database changes, as a registry entry, to every PC in the headquarters via login script whether they use the application or not. This takes only a moment, instead of trying to do a registry read and determine whether the application is installed.

The other side of that is if you have a header with no body you will probably have an issue. If there is ever a problem seeing the list of DSNs in the ODBC Administrator tool, it may be an issue that you have a Data Sources entry without a corresponding body. Just delete the extra header and restart the ODBC Admin and it should be fine.

There are several ways to add or change an ODBC call without using the ODBC Administrator. Directly editing the registry. This process need to be used with caution as serious damage can be done by editing or deleting the wrong info.
 
To register a new, or overwrite, an existing ODBC call you can create a file in notepad like below:
 

Windows Registry Editor Version 5.00 
                      [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
                          "ExampleDSN"="SQL Server"  
                      [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ExampleDSN]
                          "Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
                          "Description"="To give details"
                          "Server"="ServerName"
                          "QuotedId"="No"
                          "AnsiNPW"="No"
                          "Database"="AdventureWorks"
                          "LastUser"="sa"
                          "AutoTranslate"="No"
                      

Select all Open in new window

If you want default to Windows/AD Authentication add "Trusted_Connection"="Yes" to the registry key.

Then if you save it with the .reg extension (MyODBC.reg) during login you can use a command line script like:
      -------------------- ---------- ---------- ---------- ---------- ---------- --------
      %SystemRoot%\regedit .exe -s X:\MyPath\MyODBC.reg
      -------------------- ---------- ---------- ---------- ---------- ---------- --------
to register an ODBC call without the user being aware it is being done.

The way the Microsoft has designed it ODBC drivers they do not ever retain the password of the user. If you were to manually add the password to the registry entry it would ignore key and still ask for a password or use AD to authenticate the user.

[step="4"title="Using VBA to register a System DSN:"][]/step]
The code that is in the following snippet can be used to programmatically add an ODBC call to a user's workstation as well. We commonly use it on the first form event to create a System DSN. Again the user is generally not even aware it is happening.
 

Option Compare Database
                      Option Explicit 
                      Private Const REG_SZ = 1    'Constant for a string variable type. 
                      'If you would want to create a User DSN instead of a System DSN you
                       would uncomment the HKEY_CURRENT_USER below and comment out the 
                       HKEY_LOCAL_MACHINE. Then you would need to do a replace of the 
                       HKEY_LOCAL_MACHINE with HKEY_CURRENT_USER Also replace HKLM with
                       HKCU.  
                      Private Const HKEY_LOCAL_MACHINE = &H80000002
                      'Private Const HKEY_CURRENT_USER = &H80000001  
                      Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
                         "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
                         phkResult As Long) As Long 
                      Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
                         "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
                         ByVal reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
                         cbData As Long) As Long 
                      Private Declare Function RegCloseKey Lib "advapi32.dll" _
                         (ByVal hKey As Long) As Long  
                      'This function is designed for creating system level ODBC calls to MS SQL _
                       Server databases on the fly.
                       
                      'The form events are listed below in the order that they occur.  The call to _
                       this function has to be done in the Open Event, because the Load Event is _
                       where it will attempt to use the ODBC call to start pulling in data.
                       
                      'Open Þ Load Þ Resize Þ Activate Þ Current
                       
                      Dim DataSourceName As String
                      Dim DatabaseName As String
                      Dim Description As String
                      Dim DriverPath As String
                      Dim DriverName As String
                      Dim LastUser As String
                      Dim Regional As String
                      Dim Server As String
                      Dim AddSetup As String
                      Dim YesAnswr As String
                       
                      Dim lResult As Long
                      Dim hKeyHandle As Long
                         
                         'Specify the DSN parameters.
                       
                         DataSourceName = "MySystemDSN"           '<--- The name of the ODBC Call. (1) See below.
                         DatabaseName = "MyDBName"              '<--- This is the database you are targeting.
                         Description = "My DSN Description"          '<--- (2) See below.
                         DriverPath = "C:\WINDOWS\system32"   '<--- Default value. Don't change it.
                         LastUser = "sa"                      '<--- This can be any valid SQL Userid.
                         Server = "MyServerName"          '<--- The server or DNS value you are targeting.
                         DriverName = "SQL Server"            '<--- Default value. Don't change it.
                         AddSetup = "No"                      '<--- This is for Additional Setup Options
                         YesAnswr = "Yes"                     '<--- This is for Trusted Connection
                       
                          '(1) This is what you want the ODBC call to be named, such as "VDW" or _
                               "ISIR". You do need to be aware that if you are putting a front-end _
                               on a delivered application that you are not overwriting some setting _
                               that the application comes with.  You can check this by looking at _
                               at a workstation that the application is installed on.
                          
                          '(2) Description: This is optional, but if you don't use it delete it _
                               in the set values section below, or change it to a single space.
                       
                         'Create the new DSN key.
                       
                         lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
                              DataSourceName, hKeyHandle)
                       
                         'Set the values of the new DSN key.
                       
                         'The first three below (QuotedId, AnsiNPW, AutoTranslate) are optional depending _
                          on the ODBC setup that you are creating.  If you were setting up an ODBC call _
                          manually on the second to last page it has check boxes for _
                              Use ANSI quoted identifiers. = QuotedId _
                              Use ANSI nulls, paddings and warnings. = AnsiNPW _
                          and on the last page it has a check box for _
                              Perform translation for character data. = AutoTranslate _
                          On our home grown databases and many of our delivered ones these are turned off _
                          by default. If they would need to be turned on (checked), you just delete the _
                          line that matches it.
                       
                         'Uncomment the Trusted_Connection line below to default the ODBC call to _
                             use Windows Authentication instead of SQL Authentication.
                         'lResult = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
                            ByVal YesAnswr, Len(YesAnswr))
                         lResult = RegSetValueEx(hKeyHandle, "QuotedId", 0&, REG_SZ, _
                            ByVal AddSetup, Len(AddSetup))
                         lResult = RegSetValueEx(hKeyHandle, "AnsiNPW", 0&, REG_SZ, _
                            ByVal AddSetup, Len(AddSetup))
                         lResult = RegSetValueEx(hKeyHandle, "AutoTranslate", 0&, REG_SZ, _
                            ByVal AddSetup, Len(AddSetup))
                         lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
                            ByVal DatabaseName, Len(DatabaseName))
                         lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
                            ByVal Description, Len(Description))
                         lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
                            ByVal DriverPath, Len(DriverPath))
                         lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
                            ByVal LastUser, Len(LastUser))
                         lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
                            ByVal Server, Len(Server))
                       
                         'Close the new DSN key.
                       
                         lResult = RegCloseKey(hKeyHandle)
                       
                         'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
                         'Specify the new value.
                         'Close the key.
                       
                         lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
                            "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
                         lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
                            ByVal DriverName, Len(DriverName))
                         lResult = RegCloseKey(hKeyHandle)
                       
                      End Function
                      

Select all Open in new window