Category: Microsoft

Microsoft Technologies

Power BI Gateway SSL Issues with managed AWS RDS SQL Server instances

John Leave a comment

I had a customer call up and explain their Power BI reports had stopped working, they’re not managed so after working a few things out we got to work and jumped into their Datawarehouse environment in AWS. After a quick look we could see that their RDS SQL Server instance had TLS turned on and that no one had managed or bothered to rotate the certificate which had now expired. This is a very quick and painless process (since it’s a managed instance) and simply requires a reboot. Once loaded, refreshing a report would result in an error of Something went wrong, and looking in the details we could see The certificate chain was issued by an authority that is not trusted.

You also need to ensure to load the AWS RDS Root certificates onto their Power BI Gateway and Reporting server, downloading them from AWS here and then loading them into the trusted certificate authority in the Windows Certificate store. Once done, we could see reports refreshing and pulling data as expected.

Moral of the story? Just because it’s managed doesn’t mean it’s set and forget…

Time Hierarchy in Active Directory

John Leave a comment

Time is more critical in Active Directory than many admins realise. Time inaccuracy can cause logs to mismatch or things like license failures for anything with DRM. Larger time differences can begin to cause authentication failures since Keberos relies on accurate time or affect replication health.

By default, all AD member machines synchronise with any available domain controller, and in turn domain controllers will synchronise with the PDC Emulator of that domain. This article by Microsoft explains most of the above along with a similar overview of setting up Time Sync correctly. Whenever doing a large audit for an on-premise AD customer or have the chance to build out a new AD Forest, we always recommend ensuring that the PDC gets it’s time from an accurate external time source, and I’ll usually go as far as setting up a set and forget GPO to manage this to ensure newer PDCs get this treatment.

To ensure we target only our PDC emulator we can create a WMI filter that we can use against the Group Policy object that we’ll be creating shortly. The following WMI query will filter a PDC Emulator in an AD environment

select * from Win32_ComputerSystem where DomainRole = 5

Next step is to create the Group Policy Object, in this case I’ve create a PDC External Time Sync GPO, open it up and go to Computer Configuration > Policies > Administrative Templates > System > Windows Time Service > Time Providers. We now want to configure the following settings as follows

Set Configure Windows NTP Client to Enabled
For NtpServer enter your NTP servers details: ntp.nml.csiro.au,0x9 ntp.monash.edu,0xa
For Type set to NTP
Set Enable Windows NTP Client to Enabled
Set Enable Windows NTP Server to Enabled

It’s also important to ensure you’re specifying the correct flags to ensure reliable time, with my example I’ve specified a primary and secondary time source to minimise any potential drift when Windows decides to synchronise. By making the primary NTP server flag 0x9, we made it “Client 0x08 + SpecialInterval 0x01” and as for the second NTP time server.
By making the secondary NTP peer flag 0xa, we made it “0x08 Client + 0x02 UseAsFallbackOnly”.The following options are available to use with w32tm.

0x01 SpecialInterval
0x02 UseAsFallbackOnly 
0x04 SymmatricActive 
0x08 Client

The final GPO should look something like this with the WMI filter attached an linked to an OU with your DCs.

Since I’m based in Australia I’ll tend to use au.pool.ntp.org or use the Australian Governments NMI NTP service, which requires you to get your public IP whitelisted but is unlikely to be poisoned or attacked unlike the NTP Pool project.

Hope that helps.

Bypass Windows 11 TPM Setup Checks

John Leave a comment

Quick one – I’m doing some testing in my home lab environment with Windows 11 and this box doesn’t have a TPM (so it’s not enabled in Hyper-V). Booting up the Windows 11 iso and trying to install will tell you that it’s unsupported. To get around that, load the setup as normal and once you reach the language and time screen press Shift+F10 to bring up the command prompt.  Type regedit and hit enter to launch Reg Edit for the pre-install environment.  Navigate to the HKEY_LOCAL_MACHINE\SYSTEM\Setup registry hive and create a new Key called LabConfig.  Now under LabConfig create two DWROD (32-bit) values, one BypassTPMCheck and the other BypassSecureBootCheck and set both of these to a value of 1.  If you don’t have enough RAM allocated you can also add a DWORD of BypassRAMCheck and value of 1.

Once you’re done close up Regedit and the Command Prompt and you can start the setup process and install as normal.

AWS and Windows Activation

John Leave a comment

Quick one today where I was on a client server hosted in AWS that wasn’t activated and trying to activate it via Settings App throws and error. Like most large scale cloud vendors (except Azure), AWS use KMS to activate their windows machines, however sometimes the servers need some help to reach the internal KMS servers at Amazon – especially so when using your own DNS servers.

Open an administrative PowerShell console and enter the following commands

Import-Module "C:\\ProgramData\\Amazon\\EC2-Windows\\Launch\\Module\\Ec2Launch.psd1"
Add-Routes
Set-ActivationSettings

Then perform an activate online command as per a normal KMS activation (or you can wait…)

slmgr /ato

And that should get it activated and the watermark removed.

RDP to Windows Login Screen

John Leave a comment

I was recently resurrecting an old demo environment in AWS which consisted of a few EC2 virtual machines, however upon trying to login, I quickly realised that the account password had expired and by default Windows Remote Desktop doesn’t have an ability to change passwords since you’re not presented with the logon screen.  We didn’t have console access nor was there any other remote access like ConnectWise Control and  since the only credential we had expired, we had to think outside the box.

Luckily RDP can fall back to authentication via the logon screen and ask for login details after you connect. To achieve this, we firstly need to disable Network Level User Authentication or NLA on the remote machine, by tweaking the following registry key (this can also be done remotely).

Set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp' -Name "UserAuthentication" -Value 0

Once you’ve applied that setting one way or another (using something like Amazon SSM or Azure Virtual Machine Run Command) we then need to create an RDP file, open up Remote Desktop connection, enter the IP and hit Save As to create a file.  Open it up in Notepad or your favorite text editor and add the following line to the end of the file

enablecredsspsupport:i:0

This disables the Credential Security Service Provider or CredSSP support and forces your connection to authenticate via the logon screen.

This setting is also handy for RDP farms or hosts that require interactive logins.  Just remember that NLA needs to be turned off for this to work.

See Supported RDP properties with Remote Desktop Services on Microsoft Learn (RIP MSDN) for more info and supported parameters.

Hide the Bing button in Edge

John Leave a comment

I’m not a fan of the Bing button that now appears in Edge. Microsoft seem to have rushed it out as there isn’t an easy way to remove it. We’ll do this via a registry key (that can also be deployed via Group Policy)

Close out of Microsoft Edge completely and open the Registry Editor and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft. Right-click on the Microsoft folder and select New > Key from the context menu to create a new Key and name it Edge. Enter the Edge folder and then right-click an empty area on the right and choose New> Dword (32-bit) value, name this value HubsSidebarEnabled . Its default value is 0, which is correct. Now close Regedit and open Microsoft Edge again and navigate to edge://policy and click on the Reload Policies button that appears – the button should disappear.

As of Edge version 114 (Edge Dev Channel) users can do this via Edge settings and navigate to Sidebar > App and notification settings > Discover and then Disable the Show Discover toggle at the top.

Queries for troubleshooting the Database Mail (dbmail) function of Microsoft SQL Server

John Leave a comment

So just a quick one today. I was recently working on a SQL Server, running through some Database Mail setup and testing (see Microsoft Docs) with one of our applications.  I needed a way to see what e-mails were being sent out as well as what wasn’t.  The below queries will give you the info I was after, the first one shows any items that have run through DB Mail and their details for the last day (you can customise the WHERE statement to your needs.  You will want to run them against MSDB, do this by selecting it or issuing a USE MSDB statement.

SELECT p.name, i.send_request_date, i.sent_date, i.recipients, i.subject, i.body
FROM sysmail_mailitems AS i inner join sysmail_profile as p on p.profile_id = i.profile_id
WHERE sent_date > DATEADD(DAY, -1,GETDATE())

Bare in mind that I’m using Aliases to shorten the query a little (see this article).  Now this next one simply shows failed items as well as error responses if any from a mail server.

SELECT i.subject, i.recipients, i.copy_recipients, i.blind_copy_recipients, i.last_mod_date, l.description
FROM sysmail_failedi AS i LEFT OUTER JOIN sysmail_event_log AS l ON i.mailitem_id = l.mailitem_id
WHERE (i.last_mod_date > DATEADD(DAY, - 1, GETDATE()))

Hope that helps.

Enabling BitLocker with Group Policy and backing up Existing BitLocker recovery keys to Active Directory

John 11 Comments

So getting BitLocker enabled in an Active Directory environment is fairly painless and helps to get your end user devices more Secure.  I’ll outline the steps you need to take to enable it as well as get the recovery keys stored in Active Directory.  I’ll also dive into replicating this setup on Azure AD/Intune in a future post.

First thing is to create a new GPO (i.e. Configure – BitLocker) – Edit it and navigate to Policies > Administrative Templates > Windows Components > BitLocker Drive Encryption. Enable the following Options:

  • Choose drive encryption method and cipher strength (Windows 10 Version 1511 and later)
  • Choose drive encryption method and cipher strength (Server 2012, Win 8.1 etc…)
  • Choose how users can recover BitLocker protected drives
  • Store BitLocker recovery information in Active Directory Domain Services

Then go down one folder into Operating System Drives and enable the following:

  • Choose how BitLocker protected operationg system drives can be recovered

Once you’ve set this all up, it should look something similar to the image below.

Now target the GPO to some machines and if you’re running 1809 (from what I’ve discovered so far) or later you’ll notice them start the BitLocker process to encrypt automatically.  If not then you may need to check and ensure the TPM is enabled for the device (as we haven’t specified to encrypt devices without a TPM in this case).

What happens if you have already enabled BitLocker but now want to store the recovery keys in Active Directory? With this GPO set it will allow windows to write the recovery key to AD however we need to use the manage-bde utility, that is a command based utility that can be used to configure BitLocker

manage-bde -protectors -get c:
for /f "skip=4 tokens=2 delims=:" %%g in ('"manage-bde -protectors -get c:"') do set MyKey=%%g
echo %MyKey%
manage-bde -protectors -adbackup c: -id%MyKey%

I saved that as a batch file and ran that on the machines that had already been encrypted prior to rolling out the GPO.  Once run, it escrows the key into Active Directory.

The last bit you will need to do so you can actually see the keys in the Properties tab or via the Search function in Active Directory Users and Computers, ensure that the BitLocker RSAT is enabled in Server Features and Roles.

Windows 10 May 2019 or 1903 Software Update Management Changes for WSUS and Config Manager

John Leave a comment

We’ve started to deploy the latest release of Windows 10 and it’s interesting to note that Microsoft have released with little fan-fare some changes to the way Updates are deployed for the 1903 release.

Microsoft are now pushing updates through what is called the Unified Update Platform (see this RPC Mag article). Anyway, the main thing is there is now a new product category for WSUS and Config Manager that needs to be configured before your clients will being to receive updates.

You’ll see there is now a Windows 10, version 1903 and later product – make sure that is ticked on your Update Management Tool for updates to by synchronised. Once we had that ticked, for Config Manager you may need to tweak your Automatic Deployment Rule to include additional filters based on how you have it setup.  Microsoft have also blogged about these changes here.

Copying files from one server to another as a different user (two separate domains) using PowerShell

John Leave a comment

I’ve been working on needed to copy a number of files from one client site to another, my issue is that they have separate Active Directory domains and there is no trust between them. Using PowerShell, we can save a user credential and then use that to map a network drive with them and perform our copy.

We will setup the credential to be stored in a text file, although a cool feature of PowerShell it’s very limited in that it can only be decrypted by the user who created it on the same local machine (which is fine for our needs). The following cmdlet will prompt for a string to encrypt, which in this case is our password.

Read-Host -ASSecureString | ConvertFrom-SecureString | Out-File E:\Scripts\password.txt

Once done, we will build up our PowerShell script that will read the file, map a network drive via PowerShell which will use the secure credentials and then copy across our files.

$password = Get-Content E:\Scripts\password.txt | ConvertTo-SecureString
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "domain\username",$pass

New-PSDrive -Name Z -PSProvider filesystem -Root "\10.15.2.5\Baseline$" -Credential $creds
Remove-Item -Path Z:\ -filter *.bak
Copy-Item -filter *.bak E:\Backup -Destination Z:\ -ErrorAction SilentlyContinue -ErrorVariable A
Remove-PSDrive Z

The script copies files, and is a quick and dirty way to get files from a server in one domain to another without any sort of trust.