Script to create ODBC connection

Question

I need to deploy some software through SMS/SCCM and the software requires that an ODBC connection be created in Windows. The connection information I was given requires a user name and password. I have a batch script to import the connection information into the registry, however I don't know how to go about putting the user name and password in. I'd like to script this or put it in some kind of distributable package.

Thanks, -Mathew

You mean an ODBC datasource? Does it show up under administrative tools -> data sources under one of the tabs? — jvilalta, Dec 10 '09 at 23:53

score 11 · Answer 1 · edited Jun 01 '19 at 00:00

11

You may use the follow script:

%WINDIR%\System32\odbcconf.exe CONFIGDSN "SQL Server" "DSN=ControltubProducao|Description=ControltubProducao|SERVER=10.23.22.18|Trusted_Connection=No|Database=Controltub"
%WINDIR%\SysWOW64\odbcconf.exe CONFIGDSN "SQL Server" "DSN=ControltubProducao|Description=ControltubProducao|SERVER=10.23.22.18|Trusted_Connection=No|Database=Controltub"

edited Jun 01 '19 at 00:00

BOB

273
3
9

answered Oct 31 '12 at 14:35

Izaac Silva

111
1
2

3

Wow, this question is so old it's like, a couple of jobs ago :-) Not even sure if I can test this to say if you're correct or not. – Mathew Nov 01 '12 at 16:15

score 5 · Answer 2 · edited Jan 29 '13 at 19:25

5

Here are examples of how to directly edit the ODBC Registry settings with VBScript, PowerShell, or Perl.

edited Jan 29 '13 at 19:25

matt wilkie

17,268
24
80
115

answered Dec 24 '09 at 21:33

ewall

27,179
15
70
84

score 5 · Answer 3 · answered Jan 09 '15 at 15:56

Here's the script I use (jan. 2015). Replace all <> with your names:

@echo off
cls

echo Configure user Data Sources for <ApplicationName>
echo.

set dsn_name=<OdbcLinkName>
set config_dsn=configdsn "SQL Server" "DSN=%dsn_name%|Server=<SqlServerName>|Database=<DatabaseName>|Trusted_Connection=yes"

%windir%\system32\odbcconf %config_dsn%
%windir%\syswow64\odbcconf %config_dsn%

echo Data Source "%dsn_name%" has been configured.
echo.
echo Done.
echo.
pause

How do you supply credentials (user/pass)? – GreenAsJade Oct 27 '15 at 23:08 — GreenAsJade, Oct 27 '15 at 23:08

bibidubabidubuu · Answer 4 · 2021-06-01T05:39:23.370

Option Explicit

Dim strDSNName, strDriver, strServer, strLastUser, strRegional, strDatabase
strDSNName = "DSN NAME"
strDriver = "C:\WINDOWS\system32\SQLSRV32.dll"
strServer = "SERVER ADDRESS"
strLastUser = "USERNAME (PLACEHOLDER)"
strRegional = "Yes"
strDatabase = "DB NAME"

If MsgBox("ODBC configuration listed below is going to be added to current user." & vbcrlf & _
          "Do you want to continue?" & vbcrlf & _
          "DSN Name: " & strDSNName & vbcrlf & _
          "Server: " & strServer & vbcrlf & _
          "Database: " & strDatabase, vbInformation + vbYesNo, "Confirmation Required") = vbYes Then
Else
  MsgBox "Operation cancelled by user.", vbExclamation, "Aborted"
  Wscript.Quit
End If

const HKEY_CURRENT_USER = &H80000001
Dim strComputer, objReg, strKeyPath, strValueName, strValue
strComputer = "."
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
    strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = strDSNName 
strValue = "SQL Server"
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\" & strDSNName
objReg.CreateKey HKEY_CURRENT_USER,strKeyPath

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\" & strDSNName

strValueName = "Database"
strValue = strDatabase
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strValueName = "Driver"
strValue = strDriver
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strValueName = "Server"
strValue = strServer
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strValueName = "Regional"
strValue = strRegional
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strValueName = "LastUser"
strValue = strLastUser
objReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

Set objReg = Nothing

If (Err.Number = 0) Then 
  MsgBox "ODBC added successfully!" & vbcrlf & _
         "Details are listed below:" & vbcrlf & _
         "DSN Name: " & strDSNName & vbcrlf & _
         "Server: " & strServer & vbcrlf & _
         "Database: " & strDatabase, vbInformation, "Succeed"
Else
  Wscript.Echo "Operation failed. Error = " & Err.Number
End If

Script to create ODBC connection

4 Answers4