Automate Adding DSNs (ODBC Connections)

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Want to share a script? Click here to contribute!

Author: Darron Nesbitt – jd_nesbitt [at] hotmail [dot] com
Platform: Windows NT, Windows 2000
Type: WSH, VBScrip

This script utilizes VBScript and the Windows Scripting Host to add a DSN for making ODBC connections to SQL Server. The script can easily be modified to create DSNs for other database systems as well.

For example to change the script to add a DSN for an Oracle connection do the following:

1. Change line 62 to

RegObj.RegWrite REG_KEY_PATH & "UserId",LastUser,"REG_SZ"

2. Change lines 54 & 56 to the Oracle Home folder.

DrvrPath = "C:ORANT"

3. Change line 32 to the Oracle ODBC drivers name. This can be found in the ODBC manager on the Drivers tab in the Name column.

DriverName = "Oracle73 Ver 2.5"

4. Replace line 69 with

if WindowsAuthentication = True then
RegObj.RegWrite REG_KEY_PATH &
"AsyncSupport","Enabled","REG_SZ"
else
RegObj.RegWrite REG_KEY_PATH &
"AsyncSupport","Disabled","REG_SZ"
end if

'Changing WindowsAuthentication to True or False will now Enable or
Disable Asnychronous Support.

The example above is for Version 73 of the Oracle Client. Version 8 has several other values that can be added. If you’re curious about the different values that are added to the registry create a bogus ODBC connection in the ODBC Connection Manager. Open the registry editor and browse to HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI. All the keys below this level are connections created in the ODBC Connection Manager. Look at the values added for the connection you just created. Just mimic the entries via the script.

I’ve also attached a Visual Basic version (VB_AutoDSN.txt) which was written to add a DSN for a SQL Server connection. There’s a function called Chk_for_DSN that takes the DataSourceName paramter. It checks to see if the DSN it exists. If it does, it bypasses the creation code. The VB version makes use of Win 32 API functions for accessing the registry and getting the OS version.

Scroll down to view the script.

Download as text file.

Automate Adding DSNs (ODBC Connections)

'***************************************************************
'*****
'*****	This script creates a DSN for connecting to a
'*****	SQL Server database. To view errors comment out line 16
'*****
'*****	Script Name: AutoDSN.vbs
'*****	Author: Darron Nesbitt
'*****	Depends: VBScript, WScript Host
'*****	Created: 10/2/2001
'*****
'***************************************************************

'Values for variables on lines 25 - 29, 32, and 36
'must be set prior to running this script.

	On Error Resume Next

	Dim RegObj
	Dim SysEnv

	Set RegObj = WScript.CreateObject("WScript.Shell")

	'***** Specify the DSN parameters *****

DataSourceName = "Name_of_Connection"
DatabaseName = "Name_of_DB"
Description = "Description of connection"
LastUser = "Default_Username"
Server = "Put_server_name_here"

	 'if you use SQL Server the driver name would be "SQL Server"
DriverName = "SQL Server"

	 'Set this to True if Windows Authentication is used
	 'else set to False or comment out
WindowsAuthentication = True

	'point to DSN in registry
REG_KEY_PATH = "HKLMSOFTWAREODBCODBC.INI" & DataSourceName

' Open the DSN key and check for Server entry
  	lResult = RegObj.RegRead (REG_KEY_PATH & "Server")

	'if lResult is nothing, DSN does not exist; create it
  	if lResult = "" then

	  'get os version through WSCript Enviroment object
 	  Set SysEnv = RegObj.Environment("SYSTEM")
	  OSVer = UCase(SysEnv("OS"))

 	  'check which os is running so correct driver path can be set
	  Select Case OSVer
	Case "WINDOWS_NT"
	DrvrPath = "C:WinNTSystem32"
	Case Else
	DrvrPath = "C:WindowsSystem"
	  End Select

	  'create entries in registry
	  RegObj.RegWrite REG_KEY_PATH & "DataBase",DatabaseName,"REG_SZ"
	  RegObj.RegWrite REG_KEY_PATH & "Description",Description,"REG_SZ"
	  RegObj.RegWrite REG_KEY_PATH & "LastUser",LastUser,"REG_SZ"
	  RegObj.RegWrite REG_KEY_PATH & "Server",Server,"REG_SZ"
	  RegObj.RegWrite REG_KEY_PATH & "Driver",DrvrPath,"REG_SZ"

	  'if WindowsAuthentication set to True,
	  'a trusted connection entry is added to registry
	  'else, SQL Authentication is used.
	  if WindowsAuthentication = True then
	RegObj.RegWrite REG_KEY_PATH & "Trusted_Connection","Yes","REG_SZ"
	  end if

	  'point to data sources key
	  REG_KEY_PATH = "HKLMSOFTWAREODBCODBC.INIODBC Data Sources" &
	  DataSourceName

	  'and add the name of the new dsn and the driver to use with it
	  RegObj.RegWrite REG_KEY_PATH,DriverName,"REG_SZ"

	  MsgBox DataSourceName & " DSN Created!"

	else
		MsgBox DataSourceName & " DSN already exists!"
	end if

	Set RegObj = Nothing
	Set SysEnv = Nothing



'***************************************************************
'  END AutoDSN.txt
'***************************************************************




'***************************************************************
'*****
'*****	VB_AutoDSN.txt
'*****
'***************************************************************

Private Const REG_SZ = 1'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

'Registry action types.
Private Const ERROR_SUCCESS = 0&
Private Const ERROR_NO_MORE_ITEMS = 259&
Private Const REG_OPTION_NON_VOLATILE = 0
Private Const KEY_QUERY_VALUE = &H1
Private Const KEY_SET_VALUE = &H2
Private Const KEY_CREATE_SUB_KEY = &H4
Private Const KEY_ENUMERATE_SUB_KEYS = &H8
Private Const KEY_NOTIFY = &H10
Private Const KEY_CREATE_LINK = &H20
Private Const SYNCHRONIZE = &H100000
Private Const STANDARD_RIGHTS_ALL = &H1F0000
Private Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or _
KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or _
KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))

Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long) _
As Long

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

Public Declare Function GetVersionExA Lib "kernel32" _
(lpVersionInformation As OSVERSIONINFO) As Integer

Public Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128
End Type

Public Function Chk_for_DSN(DataSourceName As String)

' ***********************************************
' Declare local usage variables.
' ***********************************************
Dim dwResult As Long
Dim dwType As Long, cbData As Long
Dim REG_APP_KEYS_PATH As String

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 DrvrPath As String
Dim OSVer As String
Dim WindowsAuthentication As Boolean

Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

DatabaseName = "APR"
Description = "CTS APR DB"
DriverPath = DrvrPath
LastUser = "ctsapr"
Server = "CIITD010"
DriverName = "SQL Server"
WindowsAuthentication = True

REG_APP_KEYS_PATH = "SOFTWAREODBCODBC.INI" & DataSourceName

' ***********************************************
' Open the key for application's path.
' ***********************************************
lResult = RegOpenKeyEx(HKEY_LOCAL_MACHINE, _
 REG_APP_KEYS_PATH, _
 ByVal 0&, KEY_ALL_ACCESS, dwResult)
If Not (lResult = ERROR_SUCCESS) Then


OSVer = getVersion()

Select Case OSVer
Case "W2K"
DrvrPath = "C:WinNTSystem32"
Case "NT4"
DrvrPath = "C:WinNTSystem32"
Case "W95"
DrvrPath = "C:WindowsSystem"
Case "W98"
DrvrPath = "C:WindowsSystem"
Case "Failed"
MsgBox "Failed to get OS Version"
Exit Function
Case Else
DrvrPath = "C:WinNTSystem32"
End Select


'Create the new DSN key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWAREODBCODBC.INI" & _
DataSourceName, hKeyHandle)

'Set the values of the new DSN key.

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 DrvrPath, Len(DrvrPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
  ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
  ByVal Server, Len(Server))

If WindowsAuthentication = True Then
lResult = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
  ByVal "Yes", Len("Yes"))
End If

'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, _
  "SOFTWAREODBCODBC.INIODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
  ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)

MsgBox DataSourceName & " DSN created!"
End If

End Function

Public Function getVersion() As String
Dim osinfo As OSVERSIONINFO
Dim retvalue As Integer

osinfo.dwOSVersionInfoSize = 148
osinfo.szCSDVersion = Space$(128)
retvalue = GetVersionExA(osinfo)

With osinfo
Select Case .dwPlatformId
Case 1
  If .dwMinorVersion = 0 Then
 getVersion = "W95"
  ElseIf .dwMinorVersion = 10 Then
 getVersion = "W98"
  End If
Case 2
  If .dwMajorVersion = 3 Then
 getVersion = "NT3"
  ElseIf .dwMajorVersion = 4 Then
 getVersion = "NT4"
  ElseIf .dwMajorVersion = 5 Then
 getVersion = "W2K"
  End If
Case Else
  getVersion = "Failed"
End Select
End With
End Function

Disclaimer: We hope that the information in these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by me. I shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.