I have spent days trying to find a way to save users' open Excel 2007 files before a shutdown.exe or psshutdown.exe is issued. I'm using Windows 7 Pro 64-bit machines w/UAC enabled, including remote restrictions. I don't want to bypass these security measures.
I have developed vbscript that can do the job, but it only runs locally. The code is shown below:
Option Explicit
'--------------------------------------------------
'*** Dimension Local Variables ***
Dim objXL, msg, i, WshShell, strShutdownMessage, strMsg
'*** Enable Error Handling ***
On Error Resume Next
'*** Display Server Emergency and Computer Shutdown Message ***
Set WshShell = WScript.CreateObject("WScript.Shell") 'Creates an instance of the Windows Scripting Host shell (WshShell)
'--- Popup Message - Automatically Closes After 15 Seconds ---
'--- (the "4112" nType value is the sum of 16 - Critical window type + 4096 - display on top!!)
WshShell.Popup "AN UNEXPECTED SERVER EMERGENCY HAS OCCURRED." & Chr(13) & Chr(13) &_
"THIS COMPUTER WILL AUTOMATICALLY SHUT DOWN 30 SECONDS FROM WHEN THIS MESSAGE DISAPPEARS. " &_
"PLEASE SAVE YOUR DATA AND CLOSE ALL OPEN APPLICATIONS IMMEDIATELY!", 15, "SERVER EMERGENCY SHUTDOWN WARNING!",4112
'*** Debugging Exit ***
'WScript.quit
'*** Wait 30 Seconds Before Saving Data, Closing Applications, and Shutting Down Computer ***
WScript.Sleep 30000
'*** Set Excel Object Variable ***
Set objXL = GetObject( , "Excel.Application")
'*** Trap Error And Shutdown Computer If No Open Excel Workbooks ***
If Err.Number > 0 Then 'No open Excel workbooks
'MsgBox ("ErrorLevel ... = " & Err.Number)
'Wscript.quit
WshShell.run("c:\windows\system32\shutdown.exe /f /s") 'Run shutdown command
End If
'*** Save Data And Close Each Open Excel Workbook ***
For Each i In objXL.Workbooks 'Close each open Excel workbook
i.Save
i.Close
Next
'*** Shutdown Computer ***
WshShell.run("c:\windows\system32\shutdown.exe /f /s")
The problem I'm having is that a 429 error is thrown when the code executes the following statement:
Set objXL = GetObject( , "Excel.Application")
I have tried psexec, runas, vmrum (the remote machine is a VM), and even the Task Scheduler to work around this problem. I even figured out how to determine the session number of the logged-in user on the remote VM so I could pass it to psexec so it could run interactively with the remote VM user. The shutdown warning message shows up correctly on the remote VM active user's desktop. But I always get the 429 error - unless I'm logged on at both the local and remote computers with the remote VM's local Administrator account (this is a workgroup, not a domain, BTW). I initially thought it might be a psexec problem, but I now think it's either an Office 2007 or Windows 7 UAC permissions issue (thanks Harry Johnston in an answer to my original post):
GetObject() 429 Error When Using PSExec To Run WScript File On Virtual Machine
Is there some way to save open Excel files on a remote machine BEFORE a shutdown is issued? I don't trust the Office AutoSave. My clients' data is too important to lose in a power outage or due to their inability to use AutoSave properly.
The short answer to this problem is that the vbscript I ran on the remote machine was not being run within the security context of the remote machine's logged-on user. Because of this, Office 2007 wouldn't allow the creation of the objXL object from the Excel.Application ActiveX component (hence the 429 error message, "ActiveX component cannot create object") in the following line of code:
Set objXL = GetObject( , "Excel.Application")
The challenge, then, was to be able to figure out how to run my vbscript (or any application, for that matter) within the security context of the remote machine's logged-on user. I ultimately settled on PSEXEC as the utility that I use to run my vbscript on the remote machine. PSEXEC has three issues that I had to deal with in order to get my vbscript to execute successfully without the 429 error (if you use something other than PSEXEC your list of issues may be different). These issues exist ONLY on the remote machine:
Solving the first issue requires enabling the RPC Engine on the remote computer. To do this you have to configure and start four (4) Windows services AND configure a registry key to enable remote RPC. Configure the registry key to enable remote RPC as shown below:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TerminalServer
The four Windows services should be configured as follows:
The second issue, UAC remote access restriction, prevents any user with an account on a Windows 7 machine from accessing the machine remotely using remote PRC (which is the mechanism used by PSEXEC to run applications on remote machines) EXCEPT WHEN USING THE MACHINE'S BUILT-IN ADMINISTRATOR ACCOUNT. The Windows 7 built-in Administrator account is not affected by UAC remote restrictions (this explains why my vbscript executed without the 429 error when I used the Administrator account). However, every other user account is affected.
After days of testing I learned that applications can only be successfully executed on a remote machine if they are run within the security context of the remote machine's logged-on user. That means that PSEXEC must use the login credentials of the remote machine's currently logged-on user in order for the application to access the security context of the remote machine's logged-on user and not throw the 429 error. This was the key piece of learning that helped me configure this solution to solve the 429 error problem.
But how could I use the remote machine's logged-on user's login credentials (assuming I had some way to obtain the credentials; more on this later) to execute my vbscript within the security context of the logged-on user if the remote machine's UAC remote access restrictions denied me access to the remote machine in the first place? The only way I could get this to work was to turn off the remote machine's UAC remote access restriction. This was not a trivial decision, because turning off UAC remote access restriction opens up the remote machine to "loopback attacks." But I had no other option if I was going to get PSEXEC to run my vbscript on the remote machine in the security context of the remote machine's logged-on user. To turn off the UAC remote access restriction, modify the following registry key on the remote machine:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
A value of "0" (zero) ENABLES UAC remote restriction; a value of "1" DISABLES UAC remote restriction. Change this value to "1" to DISABLE the UAC remote access restriction. This will allow PSEXEC access to the remote machine to use the login credentials of the remote machine's logged-on user to run its application payload.
The final, and most challenging issue, was how to determine which user was logged-on at the remote machine, then use their username and password to run PSEXEC within the logged-on user's security context. The solution to this problem was to use QWINSTA to query the sessions data on the remote machine. This involved creating a Windows 7 command (.cmd) file that interrogated the remote machine using QWINSTA and determined which USERNAME was associated with the remote machine's Active session along with the ID of the Active session.
Using a series of DOS compare ("==") statements present within the Windows 7 .cmd file, the QWINSTA USERNAME value is compared to a list of remote machine users and their passwords to determine the remote machine's logged-on user's password. The username, password and session ID are then passed as expanded DOS variables to the PSEXEC statement. The PSEXEC application payload can then be run on the remote machine in the correct user session (this was also a tough problem to solve), with the logged-on user's credentials. It's the combination of correct user session and logged-on user login credentials that "connects" the PSEXEC application to the logged-on user's security context and allows it to run without generating a 429 error.