Schedule Script on an Encrypted DB

[mgallardo]

Hello A5 veterans and gurus,

I have an idea and would like thoughts/suggestions before I invest too much time testing/implementing.

Brief overview of our setup: Running A5 over network share (\\DedicatedSvr\LiveA5NetSharedDB\) from a dedicated server. DB encrypted using A5 encryption. Using A5 security for individual user logins.

I would like to run some maintenance scripts / reports after hours using script_play / script_schedule(). It is my understanding an A5 user account needs to be logged in to the DB to unlock the encryption so tasks such as script_play() or script_schedule() function/execute. So what I need is an A5 account that is always logged in and preferably one that can automatically log in.

So here is my idea:

Copy the workspace on the dedicated a5 server from C:\LiveA5NetSharedDB\ to C:\MaintDB\
Drop all the tables from the MaintDB
Add all tables from the Live DB (C:\LiveA5NetSharedDB\) as "foreign folder" tables to the MaintDB
In the A5 security frame work of the maintDB enable "use Windows Logon"
make a matching Windows and A5 user account (AlphaMaintUser) so the A5 "use windows logon" feature automatically logs in the user
add the MaintDB workspace to the Windows User account "startup" folder

All I would need to do is make sure the AlphaMaintUser Windows user account is logged into after sever reboots (I am thinking I'll use PRTG to monitor the server to ensue the user is logged in and a5 is running under that account).

From there it would be a matter of adapting the batch schedule_scripts() solution from the A5 documentation to run on the MaintDB after hours.

Caveats/Roadblocks I am anticipating:

I have read it is not recommended to run A5 runtime/full version on the server that is actively sharing the DB files but I assume this precaution only applies if other users are in DB at the same time. Is this correct?

I will have to keep a watchful eye on my code when I reference tables, I may need to use fully qualified names in the MaintDB code.

What do you guys think? Is there a simpler way? Are there security risks that raise red flags for you? Security is a concern for us since we have HIPPA/Hitech Act concerns with our data.

Thanks!

- Marco

[Finian Lennon]

Re: Schedule Script on an Encrypted DB

A simpler approach might be to create the MaintDB in the same workspace as your application. Add all the tables/sets/libraries etc to that database. Put all your maintenance activities into xbasic that is run from the autoexec of MaintDB or into a script that can be run from the app startup command line. Then use the Windows Task Scheduler to run that application nightly using the "Start a program" option.

[Tom Henkel]

Re: Schedule Script on an Encrypted DB

We do a significant amount of "maintenance" nightly against numerous production tables. We use the method that Finian has described. As the users are NOT signed into the system (Hopefully) at some odd hour of the pre-dawn morning, there is no issue with bumping into users and open tables.

Tom

[mgallardo]

Re: Schedule Script on an Encrypted DB

Thanks for the suggestions and replies. I'll give that a shot! Simpler is better!

Ya, we should have no issues with users as we have scripts that kick users off after-hours. hmm. It might be prudent to write a powershell script to kill any open connections, just in case the alpha scripts fail or a users session is locked up and never releases the files.

[Finian Lennon]

Re: Schedule Script on an Encrypted DB

This may not be practical in all situations but we restart the server at 1:30 am and run the maintenance at 2:00 AM. Consequently there is almost zero chance of a production table being in use during maintenance.

[Tom Patten]

Re: Schedule Script on an Encrypted DB

I'm curious - how would you write a script to "kill any open connections"?

~ Tom

[mgallardo]

Re: Schedule Script on an Encrypted DB

Let me check...

I believe this should do the trick:

Openfiles /disconnect /ID *

that would kill all open files.

huh, I somehow expected it to be a little more involved than a single command. All the better!

[Al Buchholz]

Re: Schedule Script on an Encrypted DB

Let me check...

I believe this should do the trick:

Openfiles /disconnect /ID *

that would kill all open files.

huh, I somehow expected it to be a little more involved than a single command. All the better!

You are referring to this?

https://technet.microsoft.com/en-us/.../bb490961.aspx

[mgallardo]

Re: Schedule Script on an Encrypted DB

Indeed I am.

Thanks for clarifying! I forgot to mention it was a windows command.

[Al Buchholz]

Re: Schedule Script on an Encrypted DB

Indeed I am.

Thanks for clarifying! I forgot to mention it was a windows command.

Does this change with newer windows versions? ie win 10

[mgallardo]

Re: Schedule Script on an Encrypted DB

I have not tested the /disconnect argument and, until today, was largely unaware of it however I believe it should work fine in windows 10. I am currently on windows 10 and I have a few powershell scripts that use the openfiles command. I inherited a database that suffers from a set that constantly has one of its children locked up so I made some scripts to track down which users have a specified file open on the server(OS: Server 2008 R2). I run it from my local machine against the server using the /s argument.

As an example here is the script I mentioned. The code is a bit of a hack/sloppy (slapped it together really quick) and sometimes it errors out the first time I run it (not sure why. haven't had time to investigate) but subsequent executions are fine.

#powershell -executionpolicy bypass -file .\TableAccess_ByTable.ps1 TABLE
#TABLE optional - omit for all tables, separate multiple tables with a comma
Param([String[]]$tables
)

if ($tables.count -eq 0){
$tables = @(".")
}

$Reuslt = [B]openfiles.exe[/B] /query [B]/S SERVERNAME[/B] /fo CSV /v | 
foreach-object {if ($_ -match '","'){$_}} | 
convertFrom-csv |
where-object {$_."Open File (Path\executable)" -like "C:\PATH\TO\DATABASE\ON\SERVER\?*"} 

Foreach ($table in $Tables){

	$Reuslt | where-object {$_."Open File (Path\executable)" -match $table } |
	sort-object "Open File (Path\executable)","Accessed By"|
	format-table -auto -property "Accessed By","#Locks","Open Mode","Open File (Path\executable)" -groupby "Open File (Path\executable)"

}

[Finian Lennon]

Re: Schedule Script on an Encrypted DB

I keep meaning to dive into Powershell but keep not doing it. However, whatever I learned years ago about batch files is now running out of steam. So question, how do you call a Powershell script? Can it run from a cmd file?

[mgallardo]

Re: Schedule Script on an Encrypted DB

I hear ya. I am trying to force myself to use .vbs / powershell over batch but sometimes batch script is so quick and easy.

What I usually do is to open up a powershell shell "as administrator" navigate to the script location using CD then launch the script with .\

e.g.

PS C:\scripts> .\script.ps1

You'll have to dive into some windows settings to be able to just double click on a .ps1 file for it to launch. Microsoft seems to have implemented lots of security measures presumably because of the damage powershell can dish out by either by accident or maliciously.

I was going to send you a link to the technet / scripting guy videos I used to learn some basics.. but it looks as if MS took them down :( videos are too old I'm guessing.