Mass changing of DSNs

DSN changes are ubiquitous in any most systems that need to connect directly to a database. Although you can prevent them by aliasing your DB to a CNAME and doing the modification globally, or adding an additional IP address, that’s something you learn “for the next time”. Here’s my script on how to do this easily.

The code:

Option Explicit
const HKEY_LOCAL_MACHINE = &H80000002
Dim DBs, strComputer, StdOut, oReg, Servers, NewServer
' Edit the following lines
DBs = Array("DBName", "AnotherDB", "YetAnotherDB")
Servers = Array("servername", "servername.domain.local", "serveralias")
NewServer = "newserver.domain.local"
strComputer = "."
Set StdOut = WScript.StdOut
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
strComputer & "\root\default:StdRegProv")
Sub ChangeDSN(Root)
    Dim arrSubKeys, strDBName, subkey, bMatchDB, DB, bMatchServer, Server, strServerName
    oReg.EnumKey HKEY_LOCAL_MACHINE, Root, arrSubKeys
    If IsNull(arrSubKeys) Then Exit Sub
    For Each subkey In arrSubKeys
        'StdOut.WriteLine subkey
        ' Check if the DB name is in the list of moved names
        oReg.GetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Database", strDBName
        oReg.GetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Server", strServerName
        'StdOut.WriteLine "DBName is " & strDBName
        bMatchDB = False
        bMatchServer = False
        strDBName = LCase(strDBName)
        strServerName = LCase(strServerName)
        For Each DB In DBs
            If LCase(DB) = strDBName then bMatchDB = True
        For Each Server in Servers
            If LCase(Server) = strServerName Then bMatchServer = True
        If bMatchDB And bMatchServer Then
            ' Yes, so make dure the DB name is set correctly
            StdOut.WriteLine subkey & "  - Matched"
            oReg.SetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Server", NewServer
        End If
End Sub

Now, put this on an available share and just execute this on all the workstation using your management software or use psexec to run this on all the machines you need:

C:\Users\dafonso\Desktop> psexec \\* -u domain\user  cscript.exe //nologo \\share\ChangeDSN.vbs

That’s it.