Upgrade TFS 2017 to TFS 2018 – Walkthrough

How we upgraded TFS from 2017 to 2018 in 3 steps. It was very easy and straight forward but if you haven’t done this before, I hope this post helps.

Upgrading TFS can be very easy if you keep it current or very difficult if you don’t keep it up to date and delay the upgrade until there are many versions between where you are at today and where you want to get to. Luckily, our company has kept our TFS infrastructure up to date every year. Don’t quote me but as I understand it, Microsoft updates TFS quarterly and major updates every year. So, you should plan on updating TFS at least one a quarter. As far as I know, this is the TFS feature timeline: https://docs.microsoft.com/en-us/vsts/release-notes/

To update from TFS 2017 to 2018, here are the steps high level. It was a 3 step process for us. Out infrastructure looks like this: 1 VMWare virtual TFS server and 1 VMWare virtual MS SQL Server with reporting and the TFS Database on it.

  • Disable old work item form (Not really a step. Just check the box)
  • Backup and Upgrade SQL to 2016 SP1
  • Upgrade TFS from TFS 2017 to TFS 2018
  • Upgrade SQL from 2016 SP1 to SQL 2017

Step 1 – Upgrade SQL to to version 2016 SP1

There is usually an overlapping version of SQL between TFS versions. Microsoft has designed TFS this way to easily upgrade to new versions and keep a common Database version on the back end. For us, when we started looking at TFS 2018, we found that we were on SQL 2012 (I believe) and we needed to upgrade to SQL 2016 SP1. Both TFS 2017 and TFS 2018 support SQL 2016 SP1. This was our first step. To do this, here was our procedure. This took from start to finish almost 2 hours:

  • Shut down TFS Server
  • Snapshot TFS Server while off
  • Backup existing TFS databases manually (We didn’t use TFS to do the backup)
  • Once the snapshot and databases were backed up, We essentially had a backout plan (Which we used twice until we got this right. I won’t go into detail here but it worked flawlessly).
  • Shutdown SQL Server and take a VMWare snapshot of it
  • Turn on SQL Server
  • Update compatability mode of all databases to 2016
  • Turn on TFS
  • Update SQL 2016 configuration of the new 2016 features
  • Verify that upgrade was successful

Roll back of SQL upgrade plan

In case the SQL upgrade didn’t go well…

  • Turn off TFS and revert snapshot
  • Restore SQL Manual backups
  • Turn on TFS

Step 2 – Upgrade TFS from 2017 to 2018

This was a bit confusing as there was TFS 2018 RTW and TFS 2018 Update 1 RC. Please make sure you understand the difference here. RTW means “Release to Web” and RC means “Release Candidate” Unless you’re living on the edge, I wouldn’t upgrade your production TFS to a RC version. We went to the RTW version.

We waited 1 week between upgrading SQL to 2016 and upgrading TFS to 2018. This was to ensure we were only making one change at a time. This will ensure you are sure what the procedure you performed is the issue. We did not have any issues after upgrading SQL to 2016 before our TFS 2018 upgrade.

This is where we had problems. First, I couldn’t find anywhere on the web that someone write instructions for the actual upgrade from TFS 2017 to TFS 2018. Since there were no clear instructions, I decided to write these 🙂 Also, our steps listed below worked like a charm. Our restore process had to be executed twice. We were not sure why our data tier couldn’t authenticate the account that was performing the upgrade, we backed out. After we determined the correct rights, We proceed without issues. So you know, the account that you’re using to perform the upgrade MUST be a TFS administrator AND at lease “ServerAdmin” role. We gave my account temporary SA rights to perform the upgrade. Then removed them when done.

  •  Shutdown TFS and take a VMWare snapshot
  • Perform a SQL backup (This is a SQL backup) and should be done following best practices. our SQL administrator performed these steps.
  • Power on TFS
  • Perform TFS Update
  • Test TFS

Backout plan to roll the failed TFS 2018 upgrade back to TFS 2017

  • Shut down TFS
  • Use VMWare snapshots to revert to the snapshot you took above
  • Restore the 2017 SQL backup
  • Turn on TFS
  • Test

Step 3 – Upgrade SQL from SQL 2016 to 2017

As of this writing, we haven’t upgraded SQL from 2016 to 2017. Here are our procedures as of this writing. When we complete it. I’ll update this blog post (I hope).

  • Shut down TFS
  • Snapshot TFS
  • Backup existing TFS user databases manually before SQL upgrade
  • Stop SQL services
  • Take a VM snapshot of SQL server
  • Start SQL services
  • Upgrade the comparability mode of all databases to 2017 Update SQL 2016 configuration to take advantage of new 2017 features
  • Turn on TFS
  • Verify successful upgrade (Both in TFS and SQL)

How to Create SSL Certificates using OpenSSL with wildcards in the SAN

Below are the basic steps to use OpenSSL and create a certificate request using a config file and a private key. You will first create/modify the below config file to generate a private key. Then you will create a .csr. This CSR is the file you will submit to a certificate authority to get back the public cert. Once you have the private key and the resulting public certificate, you will chain them all together (including the CA’s public certs) to create a certificate that you will install on a linux server, load balancer or even convert it to a .pfx file for windows. I’ve been doing this for years and it works very well.

Some gotcha’s… If you are creating wildcard certificates, you always have to have the root domain and the *.domain in the certificate.
For example, if you wan to create a certificate with domaina.com and domainb.com, and support all names like www.domaina.com, abc.domaina.com AND domaina.com, you need to have both the *.domaina.com and domain.com in the SAN area of the config file. Look carefully at the file below. you will see both in it.

Create a config file

Modify this config file to use to create your certificate.

Generate a key for the new certificate.

You could re-use a key but that’s not so secure so always generate a new key every time!

If you created a password, you have to remove it by typing (Adjust pem filenames as necessary):

Note: It Will Ask for a PassPhrase. This is the one you generated when you created the private key file

Create the CSR request.

Use the .cnf you created in step 1

Submit your CSR

Open your .csr file in a text editor (Never use Notepad) and copy the contents. Then submit it to an authority (internal for our sake) and then down load your certificate as a base64. If you are going to submit it to an online authority, use a repeatable authority like DigiCert. Don’t use Thawte or Symantec. They are going away soon.

Merge your public key

Merge your public key, intermediate cert, root cert, and private key (in that order) and save it as a .pem file

Should look like this (replace your public key and private keys at the top and bottom)

If necessary, convert your cert from PEM to PFX

If you need to convert your cert from .PEM to .PFX. If you dont need a friendlyname, omit that:

Copy your certificate over to your server and install it. You can either install it using the Certificates MMC or import it into IIS. If you are using a .pem file and working on linux, you can import the cert into your load balancer of linux server of choice.

How to set screen saver lock screen local policy on a non domain server

After being tasked to set up a screen saver password or a lock screen for inactivity on servers that are not joined to a domain, I decided to post this so it’s easier to find when others are searching for this.

To be PCI compliant, this is a requirement for any servers that are in-scope for your payment system.

For Microsoft Windows 2008 and 2012, it is easy to do but you have to set all three settings below for it to become active. This will enable a screen saver policy that locks your screen after a set time of inactivity. For PCI controlled servers, this is a requirement and must be less than 15 minutes.

This is easily done if the computer is part of an Active Directory domain but not as easily done if they are members of a workgroup.

How to Set the Screen Saver Lock Screen

The procedure is to open MMC snapin and add the Local Computer Policy snapin. To do this, click on the Windows button, and then simply type in MMC. For Windows 2012, select MMC snapin (mmc.exe) and not the Embedded Lockdown Manager.

Then navigate to User Configuration >> Administrative Templates >> Control Panel >> Personalization (as seen in the graphic I’ve attached).

Set the following settings:
Enable Screensaver: Enabled
Password protect the screen saver: Enabled
Screen saver timeout: Enabled with a value of 600

No need to reboot. Just log out and back in and the setting will be applied. Then wait 10 minutes to verify that your screen locks.

Here is a graphic of what needs to be set:

How to set a local policy to activate the lock screen on servers.

How to add a AD CA certificate to Windows 2012 RDP for PCI compliance

For PCI compliance, a requirement is to have RDP port 3389 connect with TLSv1.1 or TLSv1.2 and have the certificate have the same name as the server. Most servers issue self signed certificates which is not acceptable with PCI compliance.

To fix this, you have to re-issue new certificates from a trusted CA. You should use an internal CA within your company to avoid the cost of purchasing and maintaining certificates for RDP services.

How to issue and import a certificate:

Issue a certificate from your Root CA. You can go through the process I’ve outlined to create a certificate using OpenSSL

Import the certificate to the store using MMC and add the Certificates snapin

Move the Root and Intermediate to the correct store

Get the SHA1 thumbprint off of the certificate by opening up the certificate, go to the “Details” tab and scroll down to below the Thumbprint algorythm and selecting the “Thumbprint”
highlight the thumbprint
copy the thumbprint into a Command Shell and remove the spaces and the hidden character at the beginning.

Put the thumbprint in this command and run it on the server

Restart Remote Desktop Services Note: this will disconnect you from your RDP session.

Log back in and you should not be prompted.

Retest by scanning your computer with Nessus or another approved scanning software.

How To: Powershell Get Local Administrators and Active Directory Nested Groups – SOX

For the past few years, I’ve been tasked with ensuring that our in-scope servers for Sarbanes-Oxley (SOX) have the correct users and groups in them. Before we would have to screen shot the members of the local admins and all the groups that are in the local admins. I decided to take the time to create a script that gets the local admins of the server and write the output to a transcript file.

Script to get Server Local Administrators:

I named the script Get-ServerLocalAdministrators:

The steps the PowerShell script goes through are:

  • Get domain and server
  • Import PowerShell Modules
  • Get Date and Time
  • Get the working directory
  • Convert the domain and computername to upper and lower for pattern matches
  • Start the transcript in the current directory and use some variables for naming the transcript
  • Get the server’s local administrators object using Get-WmiObject and a where cause for the group component
  • Get the members of the local admins and determine if it’s a user or group.
  • If user, put it in the transcript and tag the user as uer
  • If Group, put the group in a variable to loop through later
  • In the transcript, I’m doing a Write-Host to tag each group. If the group has a sub group, (nested group), then put that in another variable to loop through that as well

Since we have Group Memebers that are cross domain, I had to include the -Server paramater get the object’s distinguished Name and use that for -Server.
The only line you have to change in this script (because I was lazy) is to change line 32 to your domain. So, if your server FQDN is server.subdomain.domain.com you would call this “Domain.com” if it was simply “server.domain.com” then you would call this “com”.

Limitations: This script only loops through to levels of Active Directory groups on the server. So, if you have nested groups more than that, it won’t continue deeper. I’m going to rewrite this to do full recursion in a function so you can look for that post in the future.

Adding Folder Permissions to IIS_IUSRS via Powershell

For you scripting guys that want to automate everything, For website deployments, you can modify your permissions using the NTFSSecurity Module. This is a simple way to modify ACL’s with powershell. Much easier to use the NTFSSecurity module…

First download the module from here: https://ntfssecurity.codeplex.com/

I would put it in your modules directory on the server. Then import the module with Get-Module -ListAvailable and Import-Module NTFSSecurity.

Now, on to the simple and easy code:

You can also use the following AccessRights. Actually, you should be able to get any name as the parameter but these are the most common:

Also, as a note, IIS_IUSRS is a special internal group that you shouldn’t/can’t prefix with the computer or domain name.

For the Network Service or IUSR, you have to use “NT AUTHORITY\NETWORK SERVICE” OR “NT AUTHORITY\IUSR”

Install / Setup Docker on Windows Server 2016

I’ve been working on installing Docker on Server 2016. Here are the steps I’ve followed and some issues I ran into:

First, you have to have Windows Server 2016.

Run Powershell as Administrator (Right click on PowerShell and RunAs Administrator) – yes, you also have to be a local administrator of the box.

Commands in order:

Then you want to see what operating system container images are available:

This step wasn’t in the instructions I was following but is necessary and was raised on the GitHub site as well. The server will reboot after the below command is executed. After it reboots, you need to run the command again. Ugh. I thought it was done and tried to install Windows Server Core and it failed after about 30 minutes.

This is installing and then enabling the Docker Container feature on Windows

As stated above, run the command again. Once done, it will actually tell you that it’s Online and True:

Install Windows Server Core Container Image

Install the WindowsServerCore Container Image by typing the following command below. This does take a while as it downloads Windows Server Core Container Image:

I went to have a bite to eat, took a nap, Surfed Reddit for a while, went to the bathroom and then it was done!

You can check if the image was downloaded by running looking in the directory:

The output should look like this:


Now we’re going to install Docker in Windows Server 2016

First, you want to download the Update-ContainerHost.ps1 powershell script. Here is the command:

Run the command. I actually ran this to download the script while the above install of the Windows Server Core was running:

To be continued…


How To: Modify Trusted Hosts to connect via Powershell

We have to connect to untrusted machines via PowerShell. To do this, you have to add the machines,

First, if you have settings in there run this command to get a backup:

Then to get them to modify:

copy that out, add your hosts to it with comma delimited and add it with powershell:

To use it in code (so it doesn’t ask you if you’re sure), you can force it by using the -Force switch

then check again:

[Resolved] OnePlus 3 3T Screen Goes Blank or Locked when Dialing Someone

I had a problem last week where my phone started to lock the screen the moment I clicked on dial. It seemed that the proximity sensor on the OnePlus 3T was stuck and it assumed that I had the phone up to my face. I contacted OnePlus about this issue and their solution (As Always) was to reinstall the entire operating system. Basically wipe the phone.

Well, the simple fix is to toggle double tap!

So, to do this, go into your settings and find Gestures. In Gestures, turn off Double Tap. At this point, I rebooted my phone, made a phone call and verified that the problem was gone.

Then I turned on Double Tap again and made another call.

Problem resolved!

PowerShell How To: Find all users where account is inactive

I was recently asked asked to find all the users in Active Directory where their account was inactive.

There is a PowerShell commandlet called Search-ADAccount that you can use to find if the account is inactive by using the parameter -AccountInactive.

This is kind of crude but works well. I couldn’t figure out how to get the headers into the csv so I simply did a write-output for the first section.