Pi-hole

A coworker recently setup an advertisement blocking software called Pi-hole on his home network. I didn’t think much of it at first, but his excitement about it was compelling and he did a quick demonstration of it.

• It blocks malware and advertisements at the DNS level. Which means you don’t need Adblock Plus. This also means it can block advertisements outside of browsers, like ads in cell phone apps.
  
• It runs on a tiny Raspberry Pi, so you can nestle it next to your other networking equipment.
  
• It comes with an awesome admin interface which you can use to configure it, customize it, and turn it off when you run into any issues (haven’t yet, fingers crossed).
  
  
  

Quick How-To for Windows Users

Raspberry Pi’s baseline operating system, Raspbian, is a Linux/Debian based kernel, so it can be a little nerve-racking for a Windows user to go into that world. My experience with Raspbian was great. I found it easy to use and it let you get trivial things done without having to spend time researching how to do it.

Purchasing the Hardware ~ $100

The hardware you need is pretty inexpensive, but it’s easy to forget something (I did). The majority of my purchases were from PiShop.us, but there are a number of other retailers that specialize in Raspberry Pi products.

• Raspberry Pi Model 3 B+ (pishop.us) – $35
  
  The way I understand it, this is currently the most popular version of the board. There are smaller ones, but the Model 3 B+ still smaller than the palm of your hand. It has wired and wireless network connectivity, it has 4 USB ports, HDMI output, a micro SD slot and a power connection slot. It has other connectors, but they won’t be needed for Pi-Hole.
  
• microSD card with Raspbian on it (pishop.us) – $10
  
  I forgot to buy this the first time. The cards are more expensive when you buy them from a store like Best Buy or Walmart; and you have to install the Raspbian operating system yourself if you do that. It’s not difficult to flash a microSD card (Etcher) with the operating system, but it is easier to just buy a preconfigured card.
  
• Power supply (pishop.us) – $9
  
  I thought this would come with the board. It’s didn’t, but it wasn’t very expensive to buy.
  
• Case (pishop.us) – $8
  
  Optional. This is definitely not required, but it makes it easier to place the board near your networking equipment. There are a number of cases and options on that site; search for one that you like.
  
• Networking Cable
  
  You probably already have a number of these lying around.
  
• USB Keyboard (pishop.us) – $17 / USB Mouse (pishop.us) – $8
  
  It’s surprisingly difficult to find a wired keyboard and mouse at a low price sometimes.
  
• HDMI Cable & Monitor – $10
  
  Make sure you have a monitor that supports HDMI before you start. I thought my monitors supported HDMI, but I was wrong. Luckily I had a television sitting around, so that saved me from having to get too creative with a work around.
  
  PiShop has a few adapters for VGA which can help. But, plan ahead on this one.
  

Installation and Configuration

1. So, there is an awesome Setting up your Raspberry Pi tutorial on their website, which I won’t repeat.
2. I will point out the list of helpful guides for configuring your Raspberry Pi. But, I want to also point out that under Preferences > Raspberry Pi Configuration … Interfaces is a whole slew of easy configurations that you can do through the GUI.
   
   
   

3. Pi-hole’s installation was incredibly easy, with just one command:
   
   curl –sSL https://install.pi-hole.net | bash

4. Log into your router and setup your Raspberry Pi to have a static IP address assigned in the DHCP tables.
            
   

5. While in your router, also setup the primary DNS for your local internet to point to your Raspberry Pi/Pi-Hole.
        
   
   
   This will setup your router to forward DNS requests to the Raspberry Pi. You don’t have to do any other configuration to your network.
   
   (For me, my laptop and cell phone immediately started using the new DNS server. However, my main computer didn’t. I think I was logged into a VPN at the time, which might have taken over responsibility for DNS resolution.)

That’s about it.

Parse a SQL Script Before Execution

Recently, we’ve added support for running database scripts as a part of our deployment pipeline. One of the steps added to the pipeline was a check to ensure the sql script that would be executed parses correctly. This is a useful evaluation far before a deployment, not just to prevent problems, but to give fast feedback to our teams so they can correct the issue.

The solution turned out to be much more generic than we had imagined. Because we didn’t yet know how generic the solution was we built some custom code for executing sql commands against a database. However, if we were to go back and do it a second time, we would probably use an open source tool to perform the execution of the scripts:

• dbatools
  
  This is an amazing powershell module of database related commands and tools. It is designed to be used in DevOps pipelines and to make DBA’s lives easier.
  
  We looked at this module when first implementing the parsing functionality, but we wanted to get the output (including normal vs error output) in a particular way and we would need more time to get it to fit our exact needs.
  
  But! It’s an absolutely amazing collection of tools and we will definitely revisit it.
  
• Invoke-SqlCmd2 (script)
  
  This script/module is a healthier and more secure version of Invoke-SqlCmd. It adds in functionality that makes your life easier, without adding on soo much that it becomes confusing to use.
  
  We started out using this, but then had some extra needs for error handling and output parsing that forced us to explore alternatives.
  
• sqlcmd.exe
  
  This is what we ended up using. It’s a command line utility so it’s doesn’t have native powershell output. But, the output feels a lot like the output from SQL Server Management Studio. Because of that comfort, it made it the easiest to wrap our heads around for handling error and output parsing.

The Parsing Script

For Microsoft SQL Server, parsing is made possible by wrapping your SQL query in the set parseonly directive.

From there, you just need to send the script over to the SQL Server and execute it. If SQL Server sends back no output, then it parsed successfully. It’s nice and straight forward.

Lessons Learned

QUOTED_IDENTIFIERS

SQL Server Management Studio has a number of default settings which you are generally unaware of. One of those is the directive QUOTED_IDENTIFIER is set to ON. When using the sqlcmd.exe utility, you will need to use the –I parameter in order to turn on the same functionality.

The Invoke-DbUpdateScript.ps1 below uses the parameter.

The error message looks like this:

Msg 1934, Level 16, State 1, Server xxxxxx, Line 14

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Linked SQL Servers & Transactions

We required that all queries which are run through the deployment pipeline must be wrapped in a transaction. This guarantees that if any error occurs (which will end the connection to the server) will be rolled back automatically. However, this could result an error message of: “Could not enlist in a distributed transaction.”

To work around the problem change the transaction isolation level from SERIALIZABLE to READ COMMITTED.

Could not enlist in a distributed transaction.

Scripts

Replacing Invoke-WebRequest with HttpClient

I’ve written before about how frustrating the Error handling for Invoke-WebRequest and Invoke-RestMethod can be. But, there is another way to make web requests which will never update the global $Error object: write your own wrapper around HttpClient.

This method is much much more complicated than using Invoke-WebRequest, Invoke-RestMethod, or even Invoke-WebServiceProxy. But, it will give you complete control over the request and the response. And as a nice side effect, it’s cross platform compatible (runs on linux and windows).

Below is an example use of HttpClient to call Apigee’s OAuth Login endpoint.

(PS. The idea for using an HttpClient came from David Carroll’s PoShDynDnsApi powershell module. Which works with two implementations that use HttpClient (.NET Core and .NET Full Framework). The reason for two implementations is that DynDns requires one of their calls to perform a non-standard GET request with a body. Microsoft’s HttpClient implementation is pretty strict about following the rules and does not allow a body to be sent in GET requests. So, he had to use reflection to inject a body into his request. Each version of .NET had a different internal class structure that had to be set differently. It’s a pretty amazing work around.)

Don’t update IIS’ applicationHost.config too fast

IIS’ applicationHost.config file is the persistent backing store for a servers IIS configuration. And, a running IIS instance will monitor that file for any changes on disk. Any changes will trigger a reload of the configuration file and an update to IIS’ configuration, including application pool updates. This is a really nice feature which allows for engineering teams to perform updates to IIS hosts using file operations; which makes it much more flexible to alternative configuration management solutions (hand written scripts, chef, puppet, etc).

Unfortunately, there is a risk involved with updating applicationHost.config outside of the standard appcmd.exe or powershell modules (webadministration and iisadministration). Because the file is read in from disk after each update, a series of rapid updates can cause a pseudo race condition. Even though the file system should prevent reads from occurring when a write is occurring, there seems to be a reproducible problem that IIS may only read in a partial XML configuration file (applicationHost.config) instead of the full file as intended. It’s almost as if updating the file either prevents the reading to finish, or it starts reading in the changes half way through. This only happens sometimes, but if your IIS server is busy enough and you perform enough writes to the applicationHost.config file you can get this error to occur:

The worker process for application pool ‘xxxxxxxxxxxxxx` encountered an error ‘Configuration file is not well-formed XML’ trying to read configuration data from ‘\\?\C:\inetpub\temp\apppools\xxxxxxxxxxxxxx\xxxxxxxxxxxxx.config’, line number ‘3’. The data field contains the error code.

An odd thing to note is that the error message has an unusual value for the .config file name. It uses the application pool name instead of the normal ‘web.config’ (ie. ‘\\?\C:\inetpub\apppools\apppoolname1\apppoolname1.config’).

This is pretty easy to make happen with a for loop in a script. For example:

To prevent the reading problem from happening, there are a number of ways:

• Use appcmd.exe as Microsoft would suggest
• Use the Powershell modules that Microsoft provides along with the Stop/Start-*ComitDelay functions
• WebAdministration: Start-WebCommitDelay / Stop-WebCommitDelay
• IisAdministration: Start-IisCommitDelay / Stop-IisCommitDelay
• Or, put the script to sleep for a few seconds to let IIS process the previous update. This is the most flexible as you can perform updates using a remote network share; where the others requires an active RPC/WinRM session on IIS server. (Example below)