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