Renewing (or enabling) Windows Remote Management (WinRM) over HTTPS

This post became possible due to the work done by these 2 persons:
Laurie Rhodes and of course Vadims Podans
So don’t thank me for the “hard work” thank me only for the little adjustments that needed to be done to make this working…

So, the problem: Set up WinRM over HTTPs, so that you can securely remote manage a window server with WinRM and Powershell. Since we are sometimes cheap, we like to use a self signed certificate and work with firewalled servers so that not every1 can connect to the WinRM if they like to.
We once have set up WinRM on our remote server with a self signed certificate, but that worked for only 1 year and a few weeks/months. I say AND a few weeks/month because of the Spooky Certificate issue.
So today we ran into the issue that when trying to connect to our remote server we get this error:
WinRM testing failed with the following error:

Trying to renew this certificate is not easy, to I search together with my friend Google for a #HowToFixThis 🙂

Firstly, you need to remove the WinRM listener using the expired certificate :

  • Open an elevated command prompt or PowerShell prompt.
  • View the currently existing listener with the following command:
  • The CertificateThumbprint will match what is seen on the certificate.
  • To remove the listener use the following command:

    Secondly: Remove the expired certificate with MMC
  • Click Run, then type MMC.
  • Go to File > Add/Remote Snap-in.
  • Select Certificates then click Add.
  • Select the Computer Account option.
  • In the left-hand pane, expand Certificates > Personal > Certificates.
  • Right-click the certificate and click Delete.

Now, creating the certificate: I need to use the Enhanced version of the, due to limitations in the Windows 2012 New-SelfSignedCertificate Powershell Module.
Download New-SelfSignedCertificateEx.zip
Extract in to a folder somewhere (eg: D:\Tools)
Open and run in an Admin PS console:

Create a 2nd file D:\Tools\CreateWinRMCert.ps1 with the following content:
Note: change 2 things in this script if wanted:
* On line that start with: New-SelfSignedCertificateEx
-NotAfter (Get-Date).AddMonths(60)
to a value that you like. By default, not adding this variable, yournew certificate wil be valid for 12 months only.
* At the end of the script, change your export password
-ExportPassword "S3cr3tP4ssw0rd"

Run the script in Powershell:

So that your Certificate will be created.
Powershell output should be something like:

Use the PFX generated in C:\Users\Administrator\AppData\Local\Temp\2 to import in your Client Server, using the Password provided above.
After Generating this Certificate, you need to configure the WinRM to use this certificate:

Test and enjoy WinRM again 🙂

MsSQL Database: Drop connections

If you need to restore a Microsoft SQL Database, you might run into the problem that there are still users connection to the database.

It’s not easy to drop those connections… lots of info that I find on google use

And this doesn’t always seem to work on SQL2008 or SQL2012.

But… here’s a handy script that I found on Stackoverflow:

Replace the DB_Name variable with the name of your DB for which you want to drop the connection:

 

Puppet on Windows: set Administrator password to never expires

When maintaining windows server with puppet, it could be interesting to set your Administrator password to never expires.
Since you want to manage your passwords through puppet, and not manually by some hyper active sysadmin, this comes in handy.

Note the fact that it is wise to change passwords now and then 😉

On Windows you can only manage passwords through puppet… not any other expire settings.
You can read here that puppet on Windows does not support manages_password_age.

Therefor, after searching and testing a lot, I came with this class:

You’d think that instead of using WMIC USERACCOUNT, you can use a simple ‘net user administrator /expires:never, but that does not seem to be the case. Although your puppet agent reports that the setting has been modified, it did not… I only got it working with the WMIC command.

Also, when using | in the unless, you need to put the cmd.exe /c in your command. This is intended behaviour because of this:

Exec: Execute external binaries on Windows systems. As with the posix provider, this provider directly calls the command with the arguments given, without passing it through a shell or performing any interpolation. To use shell built-ins – that is, to emulate the shell provider on Windows — a command must explicitly invoke the shell