HowTo: Run (Signed) Powershell Scripts as Scheduled Tasks with Minimal Rights

This is meant to be a straightforward howto: on the topic. While this sort of thing is quite common, I often times see people missing a few critical details that end up resulting in alot of unnecessary troubleshooting time. I will also cover steps needed to run signed scripts.

Assumes:

• Powershell 2.0
• Windows Server 2008 or R2 (will work on others but steps will be slightly different) 
• If signing script you know how to get a cert and basics about managing certs

Creating a signed script (optional) 

1. Ensure you have a valid code signing cert installed in your user store and that you're logged on as that user. This is easiest if your organization has PKI with a code signing template enabled. This user should NOT be the service account user, but rather the author of the script. (you I'm guessing)
2. After finishing your script, execute: 
1. $cert=@(Get-Childitem cert:\CurrentUser\My -codesigning)[0]  (assuming you have only one code signing cert) 
2. Set-AuthenticodeSignature .\myscriptname.ps1 $cert

Note: whenever you modify the script you'll need to re-sign it. 

Stage Server/Account 

1. Create the service account. To be controlled correctly this should be a domain account with no additional privileges above that of a standard user. For additional security, set it so the user can only log on to the computer you intend to run the script on. Use a  good password.
2. Give that user "Log on as a batch job" rights on the target server using the local security policy or group policy if applicable.
3. Create the directory the script will be stored in. Give the service account READ access to that directory.
4. Copy the script to the target directory
5. Variable step if signing or not:
1. If Signing: 
1. If signing execute as admin in powershell on the server: Set-ExecutionPolicy AllSigned
2. Log onto the server with the service account and run the script manually. This will prompt powershell to ask if you want to trust the publisher based on the cert. Select that you will always trust this publisher. (which is you!) If you can't log on locally or on a VM console you'll need to temporarily grant the service acct RDP access. 
2. If not signing, execute as admin: Set-ExecutionPolicy Unrestricted

 Setup Scheduled Task

1. Open Task Scheduler and navigate to the folder you would like the task to reside in. 
2. In the right plane, right click->Create New Task...
3. Give the Task a Name and Description. Don't skip the description, it'll save time having it there in the future. 
4. Select "Run whether user is logged on or not" and ensure the "Do not store password" box is NOT checked. Select "Run with highest privileges" ONLY if your script requires it. (Powershell itself does not) 
5. Click "Change User or Group" and enter the service account you created earlier. 
6. Select the "Triggers" tab and set triggers as appropriate
7. Select the "Actions" tab and click "New..."
8. Action: "Start a program"; Program/script: "powershell.exe" (full path shouldn't be necessary) ; Add arguments: "-NoLogo -NonInteractive -WindowStyle Hidden D:\path\to\your\script.ps1"
9. Change settings on the "Settings" tab if desired
10. Click "OK" and enter the password for the Service Account

That's it! If everything was done correctly it should run without problem. Depending on what the script does you may need to grant additional rights to filesystems, etc, but that's up to you to figure out since you wrote the script. :) I hope this post saved you some time getting your script up and running. 

No muss and/or fuss SQL DB Mirroring

So I've never really known what "muss" is, but this doesn't have any of that. It's sans muss. (without muss)

I must say that the SQL DB Mirroring error messages (at least as of 2008R2) leave something to be desired. Because of this, I figured I'd write a straightforward HOWTO on the topic. I've performed these steps on SQL 2008R2, but I suspect it will work on other versions as well.

This guide assumes the following:

• You have a DB on the Principal server that you wish to mirror
• That DB is NOT on the mirror target server (drop/delete it if so) 
• You do NOT intend on using a witness server.... using one however won't invalidate this guide, but we don't address the extra couple steps required to do so. 
• Your database is using a "Full" recovery model. (not possible otherwise) 
• You have sa access to both the Principal and Mirror server

Here are the steps, in order:

1. Log onto the Principal server
2. Perform a full backup to disk of the database in question. Ensure this is a new backup that doesn't include any previous backups.
3. Perform a tranlog only backup on the same database. While this step shouldn't be required, I've found that restoring a tranlog backup after the full results in a successful mirror pairing substantially more often. 
4. Copy the backup files to the Mirror server
5. On the Mirror server, perform a full restore of the desired database:
1. On the "General" page, ensure the "To database" is set to the exact same database name
2. On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"
6. Right click the newly restored DB and select "Tasks"->"Restore"->"Transaction Log"
1. On the "General" page, select the appropriate file
2. On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"
7. On the Principal server, select "Tasks"->"Mirror"
8. Click "Configure Security"
9. Select "No" for "Include Witness Server" and click "Next>"
10. Leave the ports default unless there is a reason to do otherwise. Make sure this port is properly opened on all firewalls (including the local firewall) between the to servers!
11. Select the target server, set the settings per your requirements (default are fine) and click "Next>". Double check that all firewalls are open on and between both servers. 
12. Enter the appropriate service account for this replication. While it is a lesser secure configuration, using the SQL instance domain service account (if you have one) works without issue. NOTE: If your SQL service instance is running as a local user, (including system) you must use certificate authentication which is NOT covered here.
13. Click "Next","Finish" then "Start Mirroring". All should work at this point. Note it is normal for the state on the Mirror server to be "Synchronized/Restoring".

Troubleshooting:
Explicitly grant connection rights to the "Mirroring" endpoint (note that's a name not a concept) to a given service account:
GRANT CONNECT ON ENDPOINT::Mirroring TO "Domain\Username"


Note that if you re-create the mirror it does not delete the endpoint. To delete the endpoint on each server: (assuming you took the default name of "Mirroring")
DROP ENDPOINT MIRRORING



References:
http://msdn.microsoft.com/en-us/library/ms189047(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms189127.aspx

Good Luck!