<

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

Published on
21,769 Points
8,869 Views
4 Endorsements
Last Modified:
Awarded
Community Pick
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.
ODBC Admin User DSN TabThere 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:
ODBC Admin System DSN TabTo 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.
 Driver ListSome 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:
 ODBC Setup Page 1-->      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\InstanceName
            3.      IPAddress (i.e 10.65.1.3)
            4.      IPAddress\InstanceName (i.e 10.65.1.3\InstanceName)

The next screen concerns what form of security you are going to use connect to the server.
ODBC Setup Page 2When 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:
Client config      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).
 ODBC Setup Page 3Dependent 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.
 ODBC Setup Page 4Most 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.
Test DSNTest DSN SuccessIf 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\ODBCINST.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.INI hives.

There are two portions to a DSN. The "header" and the "body". The header is an entry in the "\SOFTWARE\ODBC\ODBC.INI\ODBC 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.INI\ExampleDSN).

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"

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

Open in new window

4
Comment
Author:Jim P.
6 Comments
 

Expert Comment

by:Kirkeous
First, thank you for your meticulous support regarding the ODBC. I am actually quite experienced creating ODBC connections. I work in a "closed shell" environment in the Banking industry. I can successfuly configure the ODBC settings manually. I have 50 users I am configuring and I was hoping the code would check the windows authentication box or make it the default. Now I have to travel to each desktop to configure them. And I am on crutches!
0
 
LVL 47

Expert Comment

by:aikimark
@Kirkeous

Use WMI to do remote desktop changes.
0
 
 

Administrative Comment

by:aikimark
@Kirkeous:

Did you mean to post your question in this article or in your open question?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LVL 1

Expert Comment

by:Mitch Swetsky
This is a very detailed and helpful. Thanks.
I have installed Oracle drivers 11.2 R2 and can use Netmanager to test configurations but I can't get the ODBC Data Source administrator to show the Oracle driver.  
0
 
LVL 73

Expert Comment

by:Qlemo
If that is on a 64bit system, remember to use the appropriate ODBC Administrator tool, depending on whether you have 32bit or 64bit drivers installed, and which one you want to use for ODBC. The 32bit ODBC Admin is %SystemRoot%\SysWow64\odbcad32.exe .
0
 
LVL 1

Expert Comment

by:Mitch Swetsky
I am using a windows 7 32bit client with the runtime package installed.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month