Microsoft SQL Useful Database Role to Execute Stored Procedures: db_executor

I find it convenient to create a db_executor database role to give users the ability to execute stored procedures. This way you can look at the user's roles and just know they have the ability to execute stored procedures. Otherwise you have to go look at the permissions at the database level and most people forget or don't know to look there. You create the role as follows:

-- Create a db_executor role
CREATE ROLE db_executor 

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor 

Then add the user to the role as follows, replacing <UserAccount> with the name of the user:

EXEC sp_addrolemember N'db_executor', N'<UserAccount>'

or if you have a newer version of SQL server:

ALTER ROLE [db_executor] ADD MEMBER [<UserAccount>]

MSSQL Database/Server User/Login Mapping

This is just a quick post, mainly so that I can refer back to it when I forget the correct syntax.

In Microsoft SQL server there two separate principals. There is a principal (user) at the database level and there is a principal (login) at the server level. A login is mapped to one or more users in one or more databases. A password or an windows account is associated to the login. When you move a database or restore from one server to another, this mapping breaks and must restore it. People all too often drop the user and login and then recreated them both. If you have permissions associated to the user though, you'll have to recreate those. The best thing to do is just update the mapping (which just updates the associated SIDs in the appropriate system tables.)

USE [DatabaseName]
ALTER USER [UserAccount] WITH LOGIN = [LoginAccount]

It's simple, but since I'm not always in DB land, I find myself forgetting the syntax.

GP Update Remotely on Entire Domain

I stumbled across this when rolling out the Enhanced Mitigation Experience Toolkit (EMET 4.1.)

Group Policy is updated in the background every 90 minutes, with a random offset of 0 to 30 minutes, this means that it can take up to 2 hours for the update to actually apply to your client machines. 

 If you have a Window 2012 Domain Controller and you have not done this already I recommend you use a New feature in Windows 2012 Domain Controllers that is a templates that will open the correct ports on remote windows machines so as to be able to force a GP Update remotely, this could prove quite useful when one has to push changes in a quick manner like when responding to an incident. On a Domain Controller open PowerShell and run the following command to create and link the GPO, modify the DN so it will match your environment in this case it is for my lab domain acmelabs.com:

New-GPO –Name "Configure firewall rules for remote gpupdate" –StarterGpoName "Group Policy Remote Update Firewall Ports" | New-GPLink –target "dc=acmelabs,dc=com" –LinkEnabled yes 

Once it is linked and the policy has been applied to all machines in the domain you can invoke a Group Policy update across my domain from a Windows 2012 machine as Domain Admin in PowerShell: 

Get-ADComputer –filter * -Searchbase "dc=acmelabs,dc=com" | foreach{ Invoke-GPUpdate –computer $_.name -force}

One could modify the search base and limit the subset of computers being updated.  Very useful!

Small error/typo in MS-SMB2 documentation

In section 3.3.5.13 Receiving an SMB2 WRITE Request of the MS-SMB2 documentation it says the following:

"If the server implements the SMB 3.02 dialect, SMB2_WRITEFLAG_WRITE_THROUGH is set in the Flags field of the request, SMB2_WRITEFLAG_UNBUFFERED is not set in the Flags field of the request, and Open.CreateOptions doesn't include the FILE_NO_INTERMEDIATE_BUFFERING bit, the server MUST fail the request with STATUS_INVALID_PARAMETER."

SMB2_WRITEFLAG_UNBUFFERED should be SMB2_WRITEFLAG_WRITE_UNBUFFERED 

I emailed Tom Talpey at Microsoft about this.  He confirmed it was an error/typo and cc'd the dochelp@microsoft.com people.  They emailed me and let me know the update will appear in a future release of the MS-SMB2 document.

What's the Purpose of the User's Third-Party Root Certification Authorities Store?

I stumbled across something that I did’t think functioned as it should with regard to the Microsoft certificate store.  I’m no expert, so it’s quite possible I’m missing some bigger piece to the puzzle?

The short explanation:

If you place a root certificate in the user’s third-party root certification authorities’ store, certificate chaining validation to that root does not work.  The certificate does not show up in the user’s trusted root certification authorities’ store.  So what's the purpose of the User's Third-Party Root Certificate Authorities Store?

The long explanation:

I was tasked with pushing out a partner’s root CA certificates and a partner’s intermediate CA certificates via GPO.  Since I am only somewhat familiar with how the Microsoft Certificate Store worked, I decided to dig in a bit. 

I’m going to summarize briefly my understanding of the components involved.  

First, the certificate store is made up of logical stores which are made up of physical stores.  This separation allows an abstraction that allows for better certificate management.  The logical stores we’re concerned about here are the “trusted root certificate authorities”, the “intermediate certificate authorities” and the “third-party root certification authorities.”

One can manage certificates by user, computer or service.  For this discussion, we will only concern ourselves with user and computer.  The user and computer both have the three logical stores we’re concerned about; the root, the intermediate CA and the 3^rd party root.  The computer’s root, intermediate and 3^rd party logical stores are made up of different physical stores than the user’s root, intermediate and 3^rd party logical store.

To view this arrangement, add the certificate snap-in to the mmc console twice; once for the current user account (“My User Account”) and once for the computer account.  For each snap-in added be sure to right-click the certificate node and select View -> Options.  Then check “Physical certificate stores”.  The following link will show you how to do this and describe the physical stores in a bit more detail: http://blogs.msdn.com/b/muaddib/archive/2013/10/18/understanding-certificate-stores-and-publishing-certificates-for-smart-card-logon.aspx

Here is a screenshot for reference:

The directions from the partner seemed a little vague.  They tell you to put the root certs in the trusted root certification authorities' store.  From what I've read, it would seem to make more sense to put the partner’s root certs in the 3^rd party cert store.  It seems as though the trusted root store is meant to be for Microsoft root certs and your organization’s root certs.  It makes sense to put some other organization’s root certs in the 3^rd party root store to keep things more organized.  The logical trusted root CA store appears to contain the 3^rd party root CAs, so what’s the difference?  It appears that if you put the root cert in the 3^rd party CA store that it will show up in the trusted roots CA store.

To test things out before using GPO, I imported everything manually and stored things in the registry physical stores.  I sat there with two questions though. 

The first question was, should the partner's root certificates go in the third-party root certification authorities' store or the trusted root certification authorities' store?  I’m thinking the 3^rd party root store, but I’ll test both out.

The second question was, do we distribute the partner’s certs to the user stores or the computer stores?  For our scenario, technically it’s just the users that need the partner’s root certs, not the computers. 

The partner’s intermediate certs were pretty straight forward. I figured if we were going to push these certs out by user then they would go in the user intermediate store.  If we were going to push these certs out by computer, then they would go in the computer intermediate store.

So there were four places to try placing the partner’s root certs.

	Trusted Root	3rd Party Root
User	Worked	Didn’t work
Computer	Worked	worked

When I view a certificate that was chained to one of the partner’s roots, the chain appeared valid when trying the root in all stores except the 3^rd party root of the user.

Below is a diagram of how I inferred the physical stores are related to the logical stores:

If you place a cert in the 3^rd party root CA of the computer, it will show up in the trusted root CA of the computer, the trusted root CA of the user and the 3^rd party root CA of the User.

If you place a cert in the trusted root CA of the computer, it will show up in the trusted root CA of the User.

If you place a cert in the 3^rd party root CA of the user, it will not show up in the trusted root CA of the user.  As a result, certificate chaining does not appear to work when the root cert is stored in the user's 3rd party root CA store.

I've tested this and chaining validation does not work when the root cert is in the user's 3rd party CA store.  I don't get it.  Is this the way it’s supposed to work?  Why?  Wouldn't you want the certificate to show up in the user’s trusted root certification authorities’ logical store if you put it in the user’s third-party root certification authorities’ logical store?  Should there be a link in the user’s trusted root CA logical store to the user’s third-party root CA store?

What’s the purpose of the user’s third-party root certificate authorities store??

Is the whole third-party root CA store a relic from the past that exists only to not break compatibility?

So I started exploring this further.  

I know from my digging that the computer third-party root CA store is still in use.  Microsoft uses it to place the root certificates you download from them when you browse a secure site.

"Root certificates on Windows Vista and later versions are distributed through the automatic root update mechanism. That is, they are distributed through the root certificate. When a user goes to a secure website (by using HTTPS SSL), reads a secure email message (S/MIME), or downloads an ActiveX control that is signed (code signing), and then encounters a new root certificate, the Windows certificate chain verification software checks Microsoft Update for the root certificate. If the software finds the root certificate, the software downloads the current Certificate Trust List (CTL). The CTL contains the list of all trusted root certificates in the program and verifies that the root certificate is listed there. Then, it downloads the specified root certificate to the system and installs the certificate in the Windows Trusted Root Certification Authorities Store. If the root certificate is not found, the certificate chain is not completed, and the system returns an error."

Quote from here: http://support.microsoft.com/kb/931125

So I know the computer 3rd-party root CA store is still in use.  What about the user equivalent?  Logged in as a non-admin, I browsed to a site secured with https.  The certificates downloaded from Microsoft by browsing to this site went into the computer's 3rd-party root CA registry physical store.  Nothing in the user's 3rd-party root CA registry physical store.

I started to look at the GPO options available.  Well it doesn't even look like one can deploy to either the user or computer third-party root certification authorities store via GPO?  Even though the physical view of the certificate stores would lead one to believe otherwise.  Is this something that was deprecated yet still lingering around?

There are some GPO settings under Computer Configuration\Windows Settings\Security Settings\Public Key Policies\Certificate Path Validation Settings that are interesting to note though:

• There is an option to disable users from installing certs into their trusted root CA store.  Unchecking "Allow user trusted root CAs to be used to validate certificates" will removed the physical registry store link from the user's trusted root CA logical store. 
• There is an option to disable trust of third party root CAs.  Under "Root CAs the client computer can trust" select "Only Enterprise Root CAs."  This removes the third-party physical store link from the trusted root CAs logical store on the computer.  Which intern removes those third-party CA's from the user's trusted root CA store.  Effectively rendering all root certificates downloaded from ms useless.

These two settings would effectively make it so that users/computers in the enterprise would only be able to trust the certificates that you explicitly pushed out.

Back to the user's 3rd-party root CA store.  If you look at the physical stores under the user's 3rd-party root CA store, they are Registry, Group Policy and Local Computer.  The registry store doesn't appear to be used and the group policy store doesn't appear to be used.  That leaves the local computer store.  This appears to be a link from the computer's 3rd-party root CA registry physical store.  So at the very least we could say that the user's 3rd-party root CA logical store exists so that non-admin user's can view what 3rd party root certs have been downloaded from Microsoft.

After all this, I still can't seem to find a solid purpose/explanation for the user's third-party root certification authorities store.  All I can think of is it exists for backward compatibility and for a non-admin view into the computer's third-party root CA store.  Any body know?

System.Transactions.TransactionScope Timing out

When using the System.Transactions.TransactionScope there is a default machine setting timeout of 10 minutes.  If you have a long running transaction and it runs for over 10 minutes, it will timeout with the following exception message:

"The operation is not valid for the state of the transaction."

And an inner exception message of:

"Transaction Timeout"

There appear to be three ways to deal with this:

1.) Setting the system.transactions machinesettings maxtimeout in the machine.config on the server.  This is done by adding the following to the machine.config:

<system.transactions>

<machineSettings maxTimeout="00:20:00" />

    </system.transactions>

This is a machine wide setting and it may interfere with administrative policies.  It directly effects all applications running on the server.

2.) Setting allowExeDefinition="MachineToApplication" on the system.transaction section group in the machine.config.  The default is "MachineOnly".  See below for machine.config configuration:

        <sectionGroup name="system.transactions" type="System.Transactions.Configuration.TransactionsSectionGroup, System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null">

           <section name="defaultSettings" type="System.Transactions.Configuration.DefaultSettingsSection, System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null"/>

           <section name="machineSettings" type="System.Transactions.Configuration.MachineSettingsSection, System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null" allowDefinition="MachineOnly" allowExeDefinition="MachineToApplication"/>

        </sectionGroup>

This allows one to add the following to an application's app.config and override the machine wide setting (the default of 10 minutes or whatever setting is defined in the machine.config.):

<system.transactions>

<machineSettings maxTimeout="00:20:00" />

    </system.transactions>

Although this approach changes a machine wide setting, it does not change the machine wide maxtimeout for the machine.  One will be able to retain whatever value is set for the maxTimeout and one will be able to set whatever value is fit for the specific application in the app.config.  Thus, each app can override the machine wide maxTimeout setting and set its own maxTimeout.  I think this is better than the first approach as it is a bit more secure.  It doesn't change the machine wide maxTimeout setting which changing could consequently expose a DOS situation for other apps that may be on the server.  There may be a company policy that doesn't allow this option as it would give any application the ability to change the setting.

3.) Use reflection to change the setting via custom code.  This approach accesses private data members of Microsoft classes and thus could break in the future.  It doesn't require modifying the machine.config,  it doesn't open up other application to possible DOS situations and it circumvents any company policy.  Below is the code that does this:

    public static class TransactionManagerHelper

    {

        public static void OverrideMaximumTimeout(TimeSpan timeout)

        {

            //TransactionScope inherits a *maximum* timeout from Machine.config.  There's no way to override it from

            //code unless you use reflection.  Hence this code!

            //TransactionManager._cachedMaxTimeout

            var type = typeof(TransactionManager);

            var cachedMaxTimeout = type.GetField("_cachedMaxTimeout", BindingFlags.NonPublic | BindingFlags.Static);

            cachedMaxTimeout.SetValue(null, true);

            //TransactionManager._maximumTimeout

            var maximumTimeout = type.GetField("_maximumTimeout", BindingFlags.NonPublic | BindingFlags.Static);

            maximumTimeout.SetValue(null, timeout);

        }

    }

To use it call the following before creating the TransactionScope object:

TransactionManagerHelper.OverrideMaximumTimeout(TimeSpan.FromMinutes(5));

I've tested all three options.  They will all work.  I also verified that using the reflection method will change the setting for just the one application and not for other applications on the same server.

I propose using the reflection method and using a custom appSetting from the app.config to specify the timeout value passed to OverrideMaximumTimeout.  Another option would be to re-use the System.Transactions.Configuration.DefaultSettingsSection configuration section.  If a Timeout value exists for that configuration section in the app.config then pull the value from there and use it in the call to OverrideMaximumTimeout.

Deploying Signed PowerShell Scripts in the Enterprise the Free and Easy Way

Some time back I created a PowerShell script that goes through all the computers you have in a list, searches those computer's certificate store for expired/expiring certificates and emails you reminders.  The script was designed to run nightly on one central computer. 

Side note: I have a quite a few certificates from StartSSL installed on a bunch of computers.  If you've never heard of StartSSL, I highly recommend checking them out!  You can get free SSL certificates and they're trusted by pretty much all computers these days.

I had a problem with the script I wrote though.  It required that the security context in which the PowerShell script ran had access to all the remote computer's certificate store.  I didn't like this for a couple reasons.  First, there was no central AD group that allows for this.  Which makes sense if you think about it.  Sure you could run it with domain administrator access but you shouldn't be doing that.  Following the Principal of Least Privilege, you want to limit access to the most essential.  So I thought add the user account that runs the PowerShell script to the "Certificate Service DCOM Access" on each individual machine that I wanted to get certificate reminders for.  This seems bad to me.  If that user account gets compromised the attacker has access to all the certificates on all the other computers.  Also the attacker would have access to modify all those certificate stores. 

After talking it over with some people, I decided the best route was to modify the PowerShell script slightly and have it execute on every computer instead of one central computer.  The script would just check its own certificate store and email about its own certificates.  This way the user account running the script doesn't need access to all the other computer's certificate store.  The problems I saw with this approach was that we'd need to change the PowerShell execution policy on all the computers from the default of not allowing scripts to either unrestricted or signed.  Unrestricted execution was out of the question, AllSigned it was.  So off I set to figure out how to sign PowerShell scripts and deploy everything via GPO to make like easier.

I've concluded that there are three approaches to signing scripts.  Using an internal certificate server, using a 3rd party certificate server and manually creating your own certificates. 

There are a ton of articles on how to do it with your own internal CA or a 3rd party CA but I couldn't find one about the manual method.  I didn't have an internal certificate server, so that right away was kind of out of the question.  I wasn't looking to implement one at this time.  Although I'd like to in the future. 

So I started looking at 3rd party CAs.  Specifically I started looking at using StartSSL code signing cert that you get with their class 2 validation level.  I got things working with this but I was worried about what would happen after two years when the code signing certificate expires.  I knew that I could use a timestamp server when signing my code to extend the validity of the script past the certificate's lifetime but when I tried testing it, it didn't seem to work?  Exploring this some more, it turns out that I would need to get StartSSL's extended validation to be able to time stamp with a code signing certificate from StartSSL.  Long story short, the certs created by StartSSL have the Lifetime Signing (1.3.6.1.4.1.311.10.3.13) OID added to them unless you get the extended validation and this OID prevents time stamping from working.  Check out this forum if you'd like a further explanation.

Side note:  They should have called it "No LifeTime Signer" or something a little more intuitive.

I didn't want to spend the money to upgrade to the extended validation level and I didn't want to spend much more time on this.  So I decided to explore the manual route quickly and it turned out to be the best option.  The certificate expires in 2039 and everything can get deployed via GPO to make life easy.  So here are the steps I took.  I'll break it down into 4 broad parts: 

1. Modifying My Existing PowerShell Script and create Batch File
2. Creating the Certificates
3. Signing the PowerShell Scripts
4. Deploying Everything

Modify the Existing PowerShell Script and Create a Batch File

The original PowerShell script that emails certificate reminders can be found here. I changed a couple lines to have it run on all the computers instead of one.

Change the line:

$Computers = "server1", "server2", "server3" #list of servers to check

To:

$Computers = $env:COMPUTERNAME

This basically keeps the script as is but runs it with only itself in the list of computers.  I also changed the email subjects and body slightly to include friendly names on the certificates.  Here is the modified script, copy it and save it as ExecuteCertReminder.ps1.  Be sure to set your own SMTP server and email addresses in the script.

#this script is intended to be run on one computer, it should then be pushed out with GPO and scheduled to run on each computer

$Computers = $env:COMPUTERNAME #list of computer names "server01", "server02"

$SmtpServer = "smtpserver.domain.com"

$MailFrom = "mailfrom@domain.com"

$MailTo = "mailto@domain.com"

# Change above settings. No need to change anything below.

$DaysToExpire = 30   

# increase $DaysToExpire if you want more of a warning, decrease it if you would like less

$StoreName = "My" 

# change $StoreName above to one of the valuse below if you wish to check a store other then the personal store, for example if you're worried about intermediate CA certs expiring

# "AddressBook", "AuthRoot", "CertificateAuthority", "Disallowed", "Root", TrustedPeople", "TrustedPublisher"

$deadline = (Get-Date).AddDays($DaysToExpire)

ForEach ($c in $Computers) { 

    Try { 

        $store=new-object System.Security.Cryptography.X509Certificates.X509Store("\\$c\$StoreName","LocalMachine")

        $store.open("ReadOnly")

        # all certs

        #$store.certificates | Select *

        

        # will expire certs

        $store.Certificates | ? {$_.NotAfter -le ($deadline) -and $_.NotAfter -ge (Get-Date)} | Select *, @{Label="ExpiresIn"; Expression={($_.NotAfter - (Get-Date)).Days}} | % {

            $ExpiresIn = $_.ExpiresIn

            $NotAfter = $_.NotAfter

            $friendlyNAme = $_.FriendlyName

            $emailBody = "The following certificate on $c will expire in $ExpiresIn days on $NotAfter" + [System.Environment]::NewLine

            $emailBody += $_ | select * | Out-String

            Send-MailMessage -SmtpServer $SmtpServer -From $MailFrom -To $MailTo -Subject "Certificate about to Expire on $c - $friendlyNAme" -Body $emailBody

            Write-Host $emailBody

        }

           

        # expired certs

        $store.Certificates | ? {$_.NotAfter -lt (Get-Date)} | Select *, @{Label="ExpiredOn";Expression={$_.NotAfter}} | % {

            $ExpiredOn = $_.ExpiredOn

            $friendlyNAme = $_.FriendlyName

            $emailBody = "The following certificate on $c expired on $ExpiredOn" + [System.Environment]::NewLine

            $emailBody += $_ | select * | Out-String

            Send-MailMessage -SmtpServer $SmtpServer -From $MailFrom -To $MailTo -Subject "CERTIFICATE EXPIRED on $c - $friendlyNAme" -Body $emailBody

            Write-Host $emailBody

        }

    }

    Catch {            

        $emailBody = "Errors detected during certification reminder powershell script execution. " + [System.Environment]::NewLine + [System.Environment]::NewLine + "$($c): $($error[0])" 

        Send-MailMessage -SmtpServer $SmtpServer -From $MailFrom -To $MailTo -Subject "Certification Reminder Powershell Script Execution Error" -Body $emailBody

        Write-Host -foregroundcolor Yellow $emailBody #"$($c): $($error[0])" 

    } 

    Send-MailMessage -SmtpServer $SmtpServer -From $MailFrom -To $MailTo -Subject "ExecuteCertReminder ran on $c " -Body "ExecuteCertReminder ran on $c "

}

Next create a batch file called ExecuteCertReminder.bat containing the following:

powershell.exe -ExecutionPolicy Bypass -Command ". '\\network path will be determined later\ExecuteCertReminder.ps1'"

We'll use this batch file later when we deploy everything with GPO and create a scheduled task.  Note that we're going to deploy ExecuteCertReminder.ps1 and ExecuteCertReminder.bat with the GPO we create later.  They'll be stored in the domain's SysVol alongside the GPO we create later.  Thus we'll be coming back and editing this batch file once we know the path.

Creating the Certificates

I followed Scott Hanselman's blog about signing PowerShell scripts to create the certificates.  I modified his stuff slightly.

First go download the Windows SDK and install it on your workstation to gain access to the makecert executable.

On your workstation, open up the SDK Command Prompt with administrator privileges and run the slightly modified command from Scott's blog:

makecert -n "CN=Your Company Name PowerShell Certificate Root" -a sha256 -eku 1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer -ss Root -sr localMachine

A dialog should pop up asking for a password.  Create a password and remember it.

Enter the password you created in the next dialog box.

This creates a public and private key root cert pair that can only be used to make code signing certs and puts the public key in your localmachine root store.  It creates two files.  One with the public key and one with the private key.  Keep these two files some place secure.  You will need them to sign other certificates in the future.  Later we will take this certificate and deploy it to the root certificate store on all your computers in your domain.  Do not lose the private key file if you intend to create other certs later on with this root cert.

Next run the slightly modified command from Scott's blog:

makecert -pe -n "CN=Your company Name PowerShell Signing Cert" -ss MY -a sha256 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk -ic root.cer

A dialog will pop up asking for the password you created in the previous step.

This creates the certificate pair that you will using for signing your PowerShell scripts and puts it in your personal certificate store.  It's marked as exportable.  You'll want to export it to a PFX file and keep it secure. 

Open the MMC by tying "mmc" and hitting enter at a command prompt.  Once loaded, select File, Add/Remove Snap-In. A dialog opens.

Select "Certificates" and click Add.

Make sure the "My user account" radio is selected and click Finish.  Click Ok on the "Add or Remove Snap-Ins" dialog.

Expand "Certificates - Current User", Expand "Personal" and Select "Certificates".

Find the certificate you created in the right hand pane, select it and right click.  Select "All Tasks", "Export".

An export wizard comes up, click Next.

Select "Yes, export the private key" radio and click next.

Check the "Export all extended properties" check-box and click next.

Check the "Password" check box, enter a password and click Next.

Enter a name for the exported certificate file with a .pfx extension and click Next.  A summary dialog will appear, click Finish and the export should be complete.  You should not have three files.  The root private key, the root public key and the .pfx file containing both the signing certificates key pair.  Key these three files some place safe where no one will have access to them.

Signing the PowerShell Scripts

You could sign the scripts manually but I found a link that showed how to add a menu item to the PowerShell Integrated Scripting Environment.  For me, this simplifies the process of signing scripts.  Once it's setup it's easy to use.  I won't repost the information, please follow the link and read the blog about how to do it.  

I will summarize the process though. 

1. Download the script from the blog.

2. The file has a txt extension.  Rename the file to Sign-ISEScript.ps1.

3. If you have multiple code signing certs you may need to modify this file to select the correct certificate.

1. To check if you have multiple code signing certificates, run the following from the interactive shell at the bottom of the ISE application:
   Get-ChildItem -Path cert:\CurrentUser\My -CodeSigningCert

2. If only one certificate is listed, you can go on to step 4.
3. If more than one certificate is listed, make note of the thumbprint of the certificate you want to use.
4. You will need to change a line in Sign-ISEScript.ps1 to select the proper certificate.  Find the following line in Sign-ISEScript.ps1:
   

$cert=get-childitem -Path cert:\currentuser\my -CodeSigningCert

And replace it with

$cert=Get-ChildItem -Path cert:\CurrentUser\My -CodeSigningCert  | where {$_.Thumbprint -eq 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' }

Replace the F's with the thumbprint that you noted earlier.

4. Edit your ISE profile
1. In the interactive shell at the bottom, type "psedit $profile".  A tab will open called Microsoft.PowerShellISE_profile.ps1.
2. Add the following to this tab
   
   . C:\<path to downloaded file>\Sign-ISEScript.ps1
   $psISE.CurrentPowerShellTab.AddOnsMenu.submenus.Add(“Sign Script”,{Sign-ISEScript},$null) | Out-Null
   
3. Save the  file and reopen ISE.  There should now be a menu item called "Sign Script" under the "Add-ons" menu.
    

5. Open the file you want to sign (ExecuteCertReminder.ps1 in this case), select Add-ons, sign-script.  It should sign your script by adding a signature to the end of the file, save it and reopen.

Deploying Everything

The final major hurdle is to push everything out to the computers you want the script to run on.  I'm not going to get into GPO organization strategies or anything.  I'm just going to setup one GPO with all the settings required.  You can then link this GPO however you want to any OU you want.  Also, you can break this out into multiple GPOs if you so desire.  There are three basic things this GPO needs to do.

1. Push out the appropriate certificates
2. Set the PowerShell execution policy to AllSigned
3. Setup a scheduled task to execute the PowerShell script

Start by opening the Group Policy Management snap-in either on a domain controller or your workstation if you have the remote admin tools installed.  I assume you know how to make a new GPO.   You'll want to edit The GPO.  In the Group Policy Management Editor:

Right click Policy Object Name/Computer Configuration/Policies/Windows Settings/Security Settings/Public Key Policies/Trusted Root Certification Authorities and select import.  Use the root.cer file you created earlier.

Right click Policy Object Name/Computer Configuration/Policies/Windows Settings/Security Settings/Public Key Policies/Trusted Publishers and select import.  Use the "signing cert.pfx" file you exported earlier.

That's all you need to do to push out the certificates.

Continue editing the GPO.  Navigate to Policy Object Name/Computer Configuration/Policies/Administrative Templates/Windows Components/Windows PowerShell.  Double click "Turn on Script Execution" in the right hang pane.  Check the Enabled radio and change the drop down to "Allow only signed scripts".  Click Ok.

Now you'll need to setup the scheduled task to execute nightly.  First we'll copy ExecuteCertReminder.ps1 and ExecuteCertReminder.bat to the corresponding GPO in the SysVol on the domain.  In the Group Policy Management Snap-in find your new GPO and click on the details tab in the right hand pane. 

Note the GUID used for the Unique ID, yours will be different than the one above.  Open windows explorer and navigate to the SysVol share on one of your domain controllers.  There should be a subfolder with your domain name and under that a Policies folder.  Open this policies folder then find and open the folder with the GUID noted previously.  Then navigate farther to Machine\Scripts.  Copy ExecuteCertReminder.ps1 and ExecuteCertReminder.bat this this network location. 

Make note of the full path to the PowerShell script.  You'll need to edit the batch file and enter the full network path to the PowerShell script.

Make note of the full path to the batch file.  We'll need the full path when creating the scheduled task in GPO.

Back in the In the Group Policy Management Editor navigate to Policy Object Name/Computer Configuration/Preferences/Control Panel Settings/Scheduled Tasks.  Right click -> New -> Scheduled Task.  A dialog opens up. 

Set Action to Replace. 

Enter a Name of ExecuteCertReminder.

Enter the entire path to the batch file in the Run textbox.

Click on the schedule tab and setup whatever schedule you would like.  Hit Ok and you should be done.