How to change the "Administrator Access" password in Connectwise Automate using a Script

Connectwise Automate has a password manager where a specific set of credentials can be selected and used to run scripts/changes for systems that are a member of an Automate Location.

This is found at Connectwise Automate > "Browse" in the sidebar > Click the "+" beside the Client > Right-click the Location > "Open" > "Deployment & Defaults" tab > "Agent Deployment Settings" section > "Login to use for Administrator Access"



Because of the potentially large amount of locations you have in your Automate, it may prove too hard to individually update already the set password at every single location. This is where you will be able to use Automates built-in scripting functionality to update the password at any of the locations in a single or bulk manner. This KB article will explain how to use Automates scripting feature to create a password and use that new password in place of the existing "Administrator Access" password.

Creating the Script

1. Open the desktop version of Automate Control Center (thick-client).
   
   
2. Navigate to Automation > Scripts > View Scripts > Click a script folder > Click the "+ Add" button to create a new script.
   
   
3. Give the script a name.
   
   
4. Ensure "IF is set to "True". This will allow the script to automatically start when it runs in the future.
   
   
5. Move your mouse to the "Then" section of the screen, right-click and click "Add"
   
   
   
   
6. Click the drop-down menu beside "Function" and tap "S" on your keyboard, then scroll down to the entry "SQL Execute"
   
   
7. In the SQL statement field paste the following information and replace it with the values you wish to use for the username and password.
   
   

   Insert into Passwords (ClientID,LocationID,Title,UserName,URL,Password,Notes,Expiredate) 
   Values(15,14,'matchtopasswordtitle','Neostar153','',AES_ENCRYPT('PasswordNeostar22',SHA(' 16')),'',NULL);
   
   UPDATE locations
   SET locations.passwordID = (Select PasswordID FROM passwords WHERE title = 'matchtopasswordtitle') WHERE locationID=14;

   Download the text version here
   
   Explanation of Query:
   
   Creating a new password
   Insert into Passwords (ClientID,LocationID,Title,UserName,URL,Password,Notes,Expiredate)
   Values(15,14,'passwordID153','Neostar153','',AES_ENCRYPT('PasswordNeostar22',SHA(' 16')),'',NULL);
   
   You are telling the Automate database you are going to create a new password specifically for one target location under a parent client. You are also specifying values that normally would be set with the user interface of the thick client.
   
   The first row of the SQL query indicates the field names:
   ClientID, LocationID, Title, UserName, URL, Password, Notes, Expiredate.
   
   The second row, "VALUES" is the information you usually would have interacted with or specified in with the thick client.
   
   Note: To find your ClientID and LocationID for your target location, you may navigate to the client and Location directly in the thick client, open them and view the titles of the popups that display for the Client and Location.
   
   
   
   
   Passing the new password value with AES
   
   AES_ENCRYPT('PasswordNeostar22',SHA(' 16'))
   
   Here the actual password value PasswordNeostar22 will be sent and encrypted with Automate.
   
   SHA(' 16')
   
   The number 16 is required for the encryption process with Automate.
   
   Note: To determine this value, use 1 digit higher than the ClientID that's the parent of your target LocationID.
   
   In our example, the ClientID of 15 is being used, so the value we needed to use for the password insert will be 16.
   
   Swapping out the old password to the new password
   
   UPDATE locations
   
   The "locations" table is actually where the password ID for the current "Login to use for Administrator Access" is being held.
   
   UPDATE locations SET locations.passwordID = (Select PasswordID FROM passwords WHERE title = 'matchtopasswordtitle') WHERE locationID=14;
   
   We want Automates DB to retrieve the password ID of the newly created password then set it as the deployment password for the location. This will be accomplished by searching for the password title "matchtopasswordtitle", retrieving its passwordID, then setting it for for the location (locationID=14)
   
   Note: You must use a unique password title for this to be successful!!!
   
   
   
8. Click the "Save Step" button, then click the "Create" button.
9. Change the "Target" of the script to "Client", Click the "Save" button then Click the "X" button on the top right.

Running the Script

1. In the sidebar click "Browse" > Right-click the Client > "Scripts" > "Client Scripts" > Navigate to the new script you created. If you don't see it, close and reopen the Automate Control Center to refresh things
   
   
2. When the run script pop-up appears, change the time to 2 minutes into the future, then click the "OK" button to run the script.
   
   
   
   
3. Verify the password changed by navigating to "Browse" in the sidebar > Click the Client > Right-click the Location > "Open" > "Deployment & Defaults" tab > "Agent Deployment Settings" section > "Login to use for Administrator Access"
   
   

Note: At the time of writing this article, it took about two minutes after the script time set in step 2 for the new password changes to be visible in Automates user interface.

Creating a Script that can update multiple locations

Previously in the document, we explained how to create a password and update the "Administrator Access password" for a single location.

To action updating multiple locations with one script all you need to do is copy and paste the INSERT, VALUES, UPDATE, and SET rows once for the new location.

- Then update the clientID and location ID values in the new VALUES row

- Set your new unique password title, password values, new SHA value that's one digit higher than the clientID

- Set your new password title and locationID in the SET row.

EG:

Insert into Passwords (ClientID,LocationID,Title,UserName,URL,Password,Notes,Expiredate) 
Values(15,14,'matchtopasswordtitle','Neostar153','',AES_ENCRYPT('PasswordNeostar22',SHA(' 16')),'',NULL);

UPDATE locations
SET locations.passwordID = (Select PasswordID FROM passwords WHERE title = 'matchtopasswordtitle') WHERE locationID=14;


Insert into Passwords (ClientID,LocationID,Title,UserName,URL,Password,Notes,Expiredate) 
Values(1,2,'matchtopasswordtitleforlocation2','Neostar154','',AES_ENCRYPT('PasswordNeostar24',SHA(' 2')),'',NULL);

UPDATE locations
SET locations.passwordID = (Select PasswordID FROM passwords WHERE title = 'matchtopasswordtitleforlocation2') WHERE locationID=2;

Finally action this on a test environment before proceeding with your production Automate Server.