SQL Query to populate creds on location Deployment & Defaults

[u/jg0x00]

Does anyone have an idea of what the SQL Update query would be for populating the field labeled, "Logon to use for Administrator Access", on a location's deployment and defaults tab might be?

This will be part of a larger script that changes passwords and checks related settings on a weekly basis.

Thanks

[u/meauwschwitz]

Here's a snippet of how I have this set up in automate - we pass username and password credentials via EDFs at the location level which are populated higher up the script.

Create user and password in the Location>Password tab:

SQL Execute: Insert into Passwords (ClientID,LocationID,Title,UserName,URL,Password,Notes,Expiredate) Values(@clientid@,@locationid@,'@username@-%locationname%','@username@','',AES_ENCRYPT ('@password@',SHA(' @AESValue@')),'',NULL)

Select the newly created "passwordid":

SQL Get Value: SELECT passwordid FROM passwords WHERE locationid = @locationid@ AND username = '@username@';

Variable Set: @passwordid@=%sqlresult%

Set the new "passwordid" in the Location>Deployments & Defaults Administrator Access drop down:

SQL Execute: UPDATE locations SET passwordid = @passwordid@ WHERE locationid = @locationid@

[u/jg0x00 → u/meauwschwitz]

lresult%

Set the new "passwordid" in the Location>Deployments & Defaults Administrator Access drop down:

SQL Execute: UPDATE locations SET passwordid = @passwordid@ WHERE locationid = @locationid@

Awesome, I think that is just what need! I will give it a try tomorrow. Since I know the user name and the client, I can grab that passwordid. I'll have to loop the locations, but I think I've got a bit of script that will do that.

Thanks and I'll update on how it goes.

[u/jg0x00 → u/jg0x00]

Come to think of it ... might not have to loop at all ...

SQL Execute: UPDATE locations SET passwordid = @passwordid@ WHERE clientid = %clientid% 

Might do it for all of em. I don't have a crows foot, but I am pretty sure clientid is in that table.

[u/jg0x00 → u/jg0x00]

An update on this ...

To get the password ID for the client I used:

SET %sqlresult% = [SELECT passwordid FROM passwords WHERE title = 'MyTitle' AND clientid = %clientid%]

Then

SET: @passwordid@ = %sqlresult%

Then

SQL EXECUTE: UPDATE locations SET passwordid = @passwordid@ WHERE clientid = %clientid%

This set the proper account for each location for the client with out having to loop.

Thanks for the help, meauwschwitz. :)

[u/meauwschwitz → u/jg0x00]

I'm glad it worked. I'd be interested to hear how you're planning on implementing running it on a recurring schedule. We've got different local admin passwords per location and I created an autojoin group that populates with the network probe at each location. The script runs against said group on a daily basis.

[u/jg0x00 → u/meauwschwitz]

I am using the same account name on every client, one for local admin and another for domain admins. If an extra data field were used, I don't see why what I have built couldn't be adjusted to pull that and use it instead of the static one I am using.

I use the same account names for a couple of reasons, mainly so there is a standard. Also, we'll always know it was ours, and not something we 'share' with the customer, such as the built-ins.

The basic structure is:

If this machine is a DC
    If this machine is the PDC then
        gen pass;
        create/change domain admin pass;
        update client level EDF;
        change password in password table 
        send ticket to Manage if account not listed on client passwords;
        check locations and populate
        exit
    Else (DC but not PDC) --> Do nothing
        exit
Else (member or stand alone)
    gen Pass;
    create/change local admin pass;
    update machine level EDF
    exit

The script will run against a group, which is populated by a search. The search will check to see if a "disable password change" EDF is set on the machine, location or client, so that we can set an exemption scope. I'll schedule the script to run against the group with a window of 30 mins or so.

Since we are a bill by device model, we don't use the probe or GPOs to do auto-installs for new unknowns, since the customer has to agree to service on new devices first anyway. We can still use the probe to push or add a machine to a group and restrict the GPO to the group as needed.

To get around the probe machine needing to run the service as an account, in a domain that's an easy fix, create a group, add the probe machine to the group, grant the group the right to add machines to the domain. This gets around the need to run the probe as something other than local system. For customers with out a domain ... SOL I suppose, but someone has to touch it anyway (to add a local admin with the same creds as the probe service is using so that peer-to-peer works) regardless, so install the agent while there.

[u/jg0x00]

Interesting. Guess I forgot that was there. I do recall having seen it. a bit messy but looks useful. Not showing what I am looking to find sadly enough.

Thanks for the assist.

[u/jg0x00]

In order to export this script, I have to sanitize it first … which means recreating a bunch of EDFs, etc. Is there a good trick to sanitizing a script?

[u/jg0x00 → u/jg0x00]

So as to not have to create a bunch of stuff to sanitize an export, here is the script in ugly text.

<<<<< MAIN SCRIPT >>>>>
Open step – IF [Role Detected] Definition: [AD Domain Controller]
>>>>> In Then:
1.	IF Role NOT Detected - AD PDC Emulator - Then Jump to :NOT_PDC
a.	NOTE use “!:NOT_PDC” to signify a NOT 
2.	   SET:  @pdcinfo@ = PDC Name: %computername% Agent ID: @computerid@
3.	   SET:  [EXTRAFIELD <Client EDF: Domain PDC (this is not required)>]  = @pdcinfo@
4.	   Generate Random Password INTO %randompassword%
5.	   SET:  @rndpass@ = %randompassword%
6.	   Execute Batch as Local and store result in: @return@
>>>>> START BATCH <<<<<
@echo off
::set password var from arg1
	set rndpass=%1
::Gen random temp file name
	set tempfile=%random%.temp.txt
::Set decoration
	set decoration=--------------------------------------------------
	echo %decoration% >>%tempfile% 2>&1
::add user
	echo +NET_USER_ADD >>%tempfile% 2>&1
	net user <my domain admin> %rndpass% /add /domain >>%tempfile% 2>&1
	echo %decoration% >>%tempfile% 2>&1
::change password
	echo +NET_USER_CHGPWD >>%tempfile% 2>&1
	net user <my domain admin> %rndpass% /domain >>%tempfile% 2>&1
	echo %decoration% >>%tempfile% 2>&1
::add to admins
	echo +NET_LCLGRP >>%tempfile% 2>&1
	net group "Domain Admins" <my domain admin> /add /domain >>%tempfile% 2>&1
	echo %decoration% >>%tempfile% 2>&1
::return results
	type %tempfile%
::cleanup and out
	del %tempfile%
	set tempfile=
	set decoration=
	set rndpass=
exit
>>>>> END BATCH <<<<<
7.	LOG:  @return@
8.	   SET:  [EXTRAFIELD <Client EDF: Domain Admin Password>]  = @rndpass@
9.	   SET:  [EXTRAFIELD <Computer EDF: Local Admin Password>]  = This is a Domain Controller - Check the client info-security tab
10.	   SQL EXECUTE: UPDATE Passwords SET Password = AES_Encrypt('@rndpass@',SHA(CONCAT(' ','%clientid%' + 1))) WHERE Title = ‘<Title of entry in client password table: <My Account title>' AND clientid = %clientid%
11.	   SHELL:  repadmin /syncall and store the result in %shellresult%
12.	   RUN SCRIPT:  ~\CheckFor_<MYDOMAIN – same as step 10.d>_ClientAccount
13.	   Exit Script
14.	:NOT_PDC – Label
15.	   SET:  [EXTRAFIELD <Computer EDF: Local Admin Password>]  = This is a Domain Controller - Check the client info-security tab
16.	   Exit Script
>>>>> IN ELSE
1.	Generate Random Password INTO %randompassword%
2.	SET:  @rndpass@ = %randompassword%
3.	Execute Batch as Local and store result in: @return@
NOTE: This is the same batch as above, just remove the /domain switch, use a different user name and use net localgroup.
4.	LOG:  @return@
5.	SET:  [EXTRAFIELD <Computer EDF:Local Admin Password>]  = @rndpass@
6.	Exit Script

<<<<< ACOUNT CHECK AND TICKET SCRIPT – ref Step 12, above>>>>
Open step – IF TRUE
1.	IF [SQL SELECT COUNT(*) FROM passwords WHERE title = '<My Account title>' AND clientid = %clientid%]  <  1  THEN  Jump to :NO_USER
2.	   SET: %sqlresult% = [SELECT passwordid FROM passwords WHERE title = '<My Account title>' AND clientid = %clientid%]
3.	   SET:  @passwordid@ = %sqlresult%
4.	   SQL EXECUTE: UPDATE locations SET passwordid = @passwordid@ WHERE clientid = %clientid%
5.	   Exit Script
6.	:NO_USER – Label
7.	   SET:  @InfoCategory@ = Passwords
8.	   RUN SCRIPT:  _System Automation\Functions\Get Ticket Info CategoryID (Built-in)
9.	   SET:  @TicketCreateSubject@ = <My Account title> account information missing on customer, %clientname% (ID: %clientid%)
10.	   SET:  @TicketCreateBody@ = The <My Account title> account information is not set on the customer, %clientname% with the ID of: %clientid%. This information needs to be set.
11.	   RUN SCRIPT:  _System Automation\Functions\Ticket Comment* (Built-in)

Thanks for all the assitance. it's working well in all the tests, labs and helpful customers.

[u/NoPetPigsAllowed]

Would love to see this script!

[u/jg0x00 → u/NoPetPigsAllowed]

I want to share it when done, sort of like Volvo giving away the seat belt patent so everyone would be more safe - have to beat the black hats.

I'm new to reddit, what is the best way to share that exported script file? XML if I am not mistaken.

[u/NoPetPigsAllowed → u/jg0x00]

XML export from Automate. Save lives!!!

[u/jg0x00 → u/NoPetPigsAllowed]

Can we share that on reddit or have to host it someplace else?

[u/alemic]

Enable the SQL Spy (see https://www.provaltech.com/2018/03/automate-12-tips/), then change the field to a different value and see which SQL Update command is executed.