LUA to change sqlserver passwords

[David Givens]

Hi all,

We have a requirement to change passwords on 50+ sqlserver connections due to the password being reset on an interval.

I have tried to get a lua script working but this is failing. I wonder if its anything simple.

requirements.

1. read the list of target robots from robot_list.txt (format /domain/hub/robot/ eg; /uim/uim/sql)
2. check is defined user exists, then update password, if it does

I know the passswords are encrypted, but that is a different challange.

I am wondering is these sections are actually exposed ? Maybe I am looking for the wrong section.

Thanks

code is:

-- Path to the robot list and sqlserver configuration
local file_with_robots = "C:\\a\\robot_list.txt" -- Input file containing robot addresses
local target_username = "UIM" -- Username to check before updating the password
local new_password = "NewSecurePassword123" -- The new password to update in the SQL Server probes
local sqlserver_probe_name = "sqlserver" -- Probe name to target

-- Function to read the robot list from a file
local function read_robot_list(filename)
    local robots = {}
    local file, err = io.open(filename, "r")

    if not file then
        print("? Error opening file: " .. filename .. " - " .. err)
        return robots
    end

    for line in file:lines() do
        line = line:gsub("%s+", "") -- Trim spaces
        if line ~= "" then
            print("? Adding robot: " .. line) -- Debugging output
            table.insert(robots, line)
        end
    end

    file:close()
    return robots
end

-- Function to check if the SQL Server probe exists using `probe_list`
local function check_probe_exists(controller_path)
    print("?? Checking if sqlserver probe exists on: " .. controller_path)

    local success, probe_list = pcall(nimbus.request, controller_path, "probe_list", {})

    if not success or not probe_list then
        print("? Failed to fetch probe list from: " .. controller_path)
        return false
    end

    -- Check if sqlserver probe is in the list
    for probe, _ in pairs(probe_list) do
        if probe == sqlserver_probe_name then
            print("? sqlserver probe found on: " .. controller_path)
            return true
        end
    end

    print("?? sqlserver probe NOT found on: " .. controller_path)
    return false
end

-- Function to update the SQL Server probe configuration for a robot
local function update_sql_probe(robot_address)
    print("?? Reading robot: " .. robot_address) -- Debugging

    if not robot_address or robot_address == "" then
        print("?? Error: Empty robot address. Skipping.")
        return
    end

    -- Extract domain, hub, and robot name
    local domain, hub, robot = robot_address:match("^/*([^/]+)/([^/]+)/([^/]+)$")

    if not domain or not hub or not robot then
        print("? ERROR: Could not extract domain, hub, and robot from: " .. tostring(robot_address))
        return
    end

    -- Construct controller path
    local controller_path = "/" .. domain .. "/" .. hub .. "/" .. robot .. "/controller"

    -- Check if the sqlserver probe exists
    if not check_probe_exists(controller_path) then
        print("?? Skipping robot as sqlserver probe is missing: " .. robot_address)
        return
    end

    -- Construct path for the sqlserver probe directly
    local sqlserver_probe_path = "/" .. domain .. "/" .. hub .. "/" .. robot .. "/sqlserver"
    print("?? Requesting 'connections' section from sqlserver probe: " .. sqlserver_probe_path)

    -- Request the 'connections' section from the sqlserver probe directly
    local success, config = pcall(nimbus.request, controller_path, "probe_config_get", {path=sqlserver_probe_path .. "/connections"})

    if not success or not config then
        print("? Failed to fetch config from sqlserver probe for: " .. robot_address)
        return
    end

    -- Check if the username matches before updating the password
    if config.connections then
        for _, connection in ipairs(config.connections) do
            if connection.user == target_username then
                connection.password = new_password  -- Update the password
                print("? Updated password for " .. target_username)
                print("   New password: " .. connection.password)
            end
        end
    else
        print("? No connections section found for " .. robot_address)
        return
    end

    -- Attempt to save the updated configuration back to the sqlserver probe
    local set_success, set_error = pcall(nimbus.request, controller_path, "probe_config_set", config)

    if not set_success then
        print("? Failed to update the configuration on the sqlserver probe for " .. robot_address .. ": " .. set_error)
        return
    end

    print("? Configuration updated successfully for " .. robot_address)
end

-- Main script execution
local function main()
    local robots = read_robot_list(file_with_robots)

    if #robots == 0 then
        print("?? No robots found in the list. Please check the file: " .. file_with_robots)
        return
    end

    for _, robot_address in ipairs(robots) do
        update_sql_probe(robot_address)
    end

    print("? Script execution completed.")
end

main()

Error message is:

----------- Executing script at 30/01/2025 13:05:50 ----------

 

  ? Adding robot: /uim/uim/sql

  ?? Reading robot: /uim/uim/sql

  ?? Checking if sqlserver probe exists on: /uim/uim/sql/controller

  ? sqlserver probe found on: /uim/uim/sql/controller

  ?? Requesting 'connections' section from sqlserver probe: /uim/uim/sql/sqlserver

  ? Failed to fetch config from sqlserver probe for: /uim/uim/sql

  ? Script execution completed.

[Garin Walsh]

Couple things here to comment on.

First, it's interesting to wrap your nimbus calls in pcall. I nave never found that necessary and I think that it is part of your issue because in doing so, you might be losing control over the data types that are passed to the function. The types of the data in the arguments to nimbus.request is critical because there's no type coercion done within nimbus.request. 

Second, the calls to nimbus request take a PDS but it appears you are trying to pass a table.

From your example

pcall(nimbus.request, controller_path, "probe_config_get", {path=sqlserver_probe_path .. "/connections"})

{path=sqlserver_probe_path .. "/connections"} will be a table, not a PDS.

Here's the excerpt from the documentation on the request call

nimbus.request ( NimBUSAddress, Command, Arguments [, Wait [, ReturnAsPDS]] )
Returns the result of the command targeted for the provided nimbus component. The command-arguments are expected to be a PDS (returned by pds.create). The result is placed into a table unless the ReturnAsPDS parameter is set to true.
Please note that this is an associative table (not indexed), meaning that a PDS sections will be referenced by its section-name.

Typically your calls to nimbus.request should look something like:

local probecmd = pds.create()

pds.putInt(probecmd, "details", 0)

response, retcode = nimbus.request("/hub", "tunnel_get_info", probecmd)

pds.delete(probecmd)

And third, your call to get the probe config isn't right (unless it's doing more that I have tried in the past) - It will need these specified:

where name is the probe name (sql_response in your case), robot can be left blank, and var, if specified, needs to be the path and key name of an item in the cfg file. The password in the sql_response cfg file is going to be /connections/NAMEOFCONNECTION/password.

If you leave var blank, you will get a PDS containing a table of the entire config file.

Also, in your for loops, you can use pairs() or ipairs() - suggest using pairs instead of ipairs if only for habit because ipairs is only going to handle tables with sequential integer indexes which will generally not be the case in an arbitrary table.

And there's a function call pds.convert() which is handy to use to convert the PDS you build or get as a return to a table.

And to dump a table:

function tdump(t)
   local function dmp(t, l, k)
      if type(t) == "table" then
         print(string.format("%s%s:", string.rep(" ", l*2), tostring(k)))
         for k, v in pairs(t) do
            dmp(v, l+1, k)
         end
      else
         print(string.format("%s%s:%s", string.rep(" ", l*2), tostring(k), tostring(t)))
      end
   end
   dmp(t, 1, "root")
end

[Garin Walsh]

And apologies for the formatting, for whatever reason all my attempts at making my response into something other than a block of text failed. Weird.

And you said sqlserver, and I had in my mind sql_response - ideas are all the same but the probe name and path to password key will be different.