We have just recently asked by a customer to come up with a simple method of quickly update more than 500 machines with multiple ODBC connections. That might be a hell-of-a headache for the IT guys…

The way we have decided to achieve that, was by running a vbscript (I admit that I forgot it even still exists). So I have found the basic idea in the Hey, Scripting Guy! Blog (thank you!) and have made some changes and additions.

So here it is – in full (can be downloaded from here):

sub UpdateOdbc(Name, Server, Database, User, Password, Trusted)

'Updates an existing System ODBC SQL connection.
'Please note that this script must run with administrative privileges.
'Parameters:
'Name (string) - The ODBC DSN Name
'Server (string) - The full database server name. Might be in Server\Instance format
'Database (string) - The database name
'User (string) - the user name used to login
'Password (string) - the password used to login
'Trusted (boolean) - states that the connection is to be trusted connection. When set to True, both username and password are disregarded.
'
'Usage:
'UpdateOdbc "Because Connection", "Because\sql2008", "BecauseDB", "sa", "jku!oi", False
'or - trusted:
'UpdateOdbc "Because Connection", "Because\sql2008", "BecauseDB ", "", "", True
    if OdbcNameExists(Name) = true then 
        DeleteOdbc Name 
        CreateOdbc Name, Server, Database, User, Password, Trusted 
    end if
end sub
 

sub CreateOdbc(Name, Server, Database, User, Password, Trusted)
'Creates a System ODBC SQL connection.
'Please note that this script must run with administrative privileges.
'Parameters:
'Name (string) - The ODBC DSN Name
'Server (string) - The full database server name. Might be in Server\Instance format
'Database (string) - The database name
'User (string) - the user name used to login
'Password (string) - the password used to login
'Trusted (boolean) - states that the connection is to be trusted connection. When set to True, both username and password are disregarded.
'

'Usage:
'CreateOdbc "Because Connection", "Because\sql2008", "BecauseDB", "sa", "jku!oi", False
'or - trusted:
'CreateOdbc "Because Connection", "Because\sql2008", "BecauseDB", "", "", True 
    Const HKEY_LOCAL_MACHINE = &H80000002 

    computer = "." 
    Set registryObject=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
    computer & "\root\default:StdRegProv") 

    keyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" 
    valueName = Name

    'You may want to replace "SQL Server" with
    '"SQL Server Native Client 10.0" for SQL native client 10
    '"SQL Native Client" for SQL Native client
    value = "SQL Server" 
    registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 

    keyPath = "SOFTWARE\ODBC\ODBC.INI\" & Name 
    registryObject.CreateKey HKEY_LOCAL_MACHINE,keyPath 
    keyPath = "SOFTWARE\ODBC\ODBC.INI\" & Name 
    valueName = "Database" 
    value = Database 
    registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 

    'Driver - Defaults to SQL Driver (SQLSRV32.dll) 
    'Instead of SQLSRV32.dll you may use: 
    'SQL Native Client - SQLNCLI.dll 
    'SQL Server Native Client 10 - SQLNCLI10.dll 
    valueName = "Driver" 
    value = "C:\WINDOWS\System32\SQLSRV32.dll" 
    registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 
    valueName = "Server" 
    value = Server 
    registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value
 
    if Trusted = True then 
        valueName = "Trusted_Connection" 
        value = "Yes" 
        registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 
    else 
        valueName = "Password" 
        value = Password 
        registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 

        valueName = "PWD" 
        value = Password 
        registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 

        valueName = "username" 
        value = User 
        registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 

        valueName = "LastUser" 
        value = User 
        registryObject.SetStringValue HKEY_LOCAL_MACHINE,keyPath,valueName,value 
    end if
end sub
 

sub DeleteOdbc(Name)
'Use to delete an ODBC Connection
'Please note that this script must run with administrative privileges.
'
'Parameters:
'Name (string) - The ODBC DSN Name
'
'Usage:
'DeleteOdbc "Because Connection" 
    Const HKEY_LOCAL_MACHINE = &H80000002 
    computer = "." 
    Set registryObject=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
    computer & "\root\default:StdRegProv") 
    keyPath = "SOFTWARE\ODBC\ODBC.INI\" & Name 
    registryObject.DeleteKey HKEY_LOCAL_MACHINE, keyPath 

    keyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" 
    valueName = Name 
    registryObject.DeleteValue HKEY_LOCAL_MACHINE,keyPath,valueName
end sub

function OdbcNameExists(Name) 
    Const HKEY_LOCAL_MACHINE = &H80000002 
    computer = "." 
    Set registryObject=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
    computer & "\root\default:StdRegProv") 
    keyPath = "SOFTWARE\ODBC\ODBC.INI\" & Name 
    registryObject.GetStringValue HKEY_LOCAL_MACHINE, keyPath, "Server", key 

    if IsNull(key) then 
        OdbcNameExists = false 
    else 
        OdbcNameExists = true 
    end if
end function