How to easily Check your SPN and Delegation settings for SQL Server in an Active Directory environment

I was recently setting up some Linked SQL Servers for a customer to perform queries against a database on one server through another. One of the things you need to get right when setting up linked servers when using Service accounts in Active Directory is SPNs (or Service Principal Names) and Authority to Delegate (for Kerberos authentication) which can sometimes be quite cumbersome through ADUC or ADSI edit.

I then stumbled upon a little tool from Microsoft called the Microsoft Kerberos Configuration Manager for SQL Server. Running this little tool on the two SQL servers I could quickly and more easily see the SPNs (see picture to the right) and Delegation permissions.  As one server was quite old (and before my time) I could easily see that the SPNs configured for that particular service account were incorrect and the tool even allows you to fix this by generating the correct SPN. Hope that helps save some time in the future.

 

Get a list of users in Active Directory who have not logged in for specified number of days using PowerShell

A client is currently in the planning stages of doing a migration to Azure AD and Office 365 and one of the things we needed was a list of users who have not logged on in the last few months but are still active in our AD.

Well it’s PowerShell to the rescue again (with Visual Studio Code my IDE of choice) with the following snippet of code which will query an AD environment looking for accounts which haven’t been touched in this case for 90 days and give me a nice CSV of their name and last logon timestamp.

import-module ActiveDirectory $domain = "adatum.com.au" $DaysInactive = 90
$time = (Get-Date).Adddays(-($DaysInactive))

# Get AD Users with lastLogonTimestamp less than time specified and is enabled
Get-ADUser -Filter {LastLogonTimeStamp -lt $time -and enabled -eq $true} -Properties LastLogonTimeStamp |

# Output Name and lastLogonTimestamp attributes into CSV
select-object Name,@{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp).ToString('yyyy-MM-dd')}} | export-csv Inactive_Users.csv -notypeinformation

Save the above into a PS1 and then run this on a server which has the AD PowerShell modules (usually one of your DCs) and will then create a CSV located where the script is with a list of all the users who are still enabled but haven’t logged on in your environment.

Remote Desktop is Blocked in Windows Firewall even though Group Policy Setting is set to allow

So I’m going through and trying to automate a lot of things in our environment (one thing you should always try and do as a SysAdmin is to automate repetitive tasks) and to help me achieve this I’m using Group Policy, step one is enable Remote Desktop to all of our Servers automatically. Created the Group Policy Object, allowed Remote Desktop Connections and also setup a list of IP Addresses to allow connections from.

After a while I added another IP Address to the exemptions and the next morning I found that I was no longer able to RDP directly to some of my servers, wondering what had happened I logged into our Hyper-V host (where RDP was still working) and I logged onto one of the affected servers. I firstly went and checked to ensure that RDP was still enabled, yup sure is, I then went and checked the Firewall and I spotted a Block and Deny All rule that I was sure I didn’t create. So I went back over the GPO that I had applied, went into the IP exceptions and turns out there was a SPACE separating one of the IP Addresses after the comma. Removed that space, performed a GPUpdate on the affected machines and Remote Desktop started working again.

 

How to allow an Active Directory Certificate Authority to generate Certificates with a Subject Alternative Name attribute

Starting with Google Chrome 58 no longer trusts certificates without the Subject Alternative Name attribute, so this makes it a little troublesome for those with internal CAs where you rely on them for Software Development. We noticed last week that some end users couldn’t hit an internal application over HTTPS, but was fine in Firefox and IE. After a quick search, I’d found the culprit was a change in the behavior of Google Chrome to adhere more stringently to RFC 2818. So I went to work on our CA in enabling certificates to be requested with the Subject Alternative Name Attribute.

Start an administrative command prompt on one of your intermediate CA server and issue the following command;

certutil -setreg policy\EditFlags +EDITF_ATTRIBUTESUBJECTALTNAME2

You’ll then need to restart Certificate Services. Once done, best thing to do is to create a new Template (ours is called Dev Web Servers) along with giving the right permissions to allow users or machines to enroll and begin issuing the new certificates. We do this manually at the moment via the Web interface so when requesting a certificate you need to fill in the attribute text field with the following (like the image to the left)

san:dns=hostname1&dns=hostname2&dns=devweb2

Fill in the dns= part until you cover off all of the sites you need. Complete the request to install the certificate onto your server and adjust the SSL bindings to use the new certificate.

Fixing The trust relationship between this computer and the primary domain failed Error when restoring a Snapshot or Old Virtual Machine

I had a VM snapshot/checkpoint running for several months in my test lab and after reverting the snapshot back I went to login to the machine and got the dreaded Trust relationship between this computer and the primary domain failed error message.  So I logged in as a local admin onto that machine, opened up an Administrative PowerShell window and entered the below commands.

$cred = Get-Credential – (enter domain admin account when prompted)
Reset-ComputerMachinePassword -Credential $cred -Server ada-dc-02.adatum.com.au

Replace the server at the end with a FQDN of one of your Domain Controllers.  As I wasn’t authenticated to the domain, I needed to enter my domain admin credentials by using the Get-Credential cmdlet and then using those stored credentials to issue the Reset-ComputerMachinePassword cmdlet.

So there you have it, no more re-joining a computer to the domain.

Specify ClickView Server IP settings via the Registry using Group Policy with an ADMX Template

So I was recently going through our Group Policy and found that we were applying a reg file to specify the Server IP for our ClickView Players.  I don’t really like this approach anymore and tend to try and have everything nice and neat so I set about creating a ADM file to store the setting, I also wanted the ability to disable the School Bag feature for our Labs.

Whilst creating the file I relied on the Language Reference for Administrative Templates on MSDN.  I find it much quicker to build it in Notepad with old Syntax then to go around making an XML based ADMX file.

Once I was done, I then got my hands on Microsoft’s free ADMX Migrator (download here) and used it to convert my ADM file.  After a quick clean up, imported them into one of our Domain Controllers and I was then able to manage our ClickView Player a little easier and without having to rely on a reg file.

[ddownload id=”1148″ text=”Download the ADM/ADMX files here”] [ddownload_filesize id=”1148″].

Hope that makes life easier for someone.

Wireless Projection / Miracast option disappears from Microsoft Surface Pro 3 / Windows 8.1 after joining a Active Directory Domain

We recently deployed some Microsoft Surface Pro 3s (love these devices) for our Executive team along with some new equipment in meeting / board rooms with Wireless Display capabilities. During testing with my personal SP3 (not domain joined) the wireless display worked a treat, so I expected it to work fine on the ones we would be deploying.

After about a week, one of the managers shot me an e-mail saying he couldn’t see the option for wireless display, that night he had performed a reset of the device, came in and had the option, but once we joined it back to the domain, it disappeared. I started digging around Group Policy to see what was causing it to disappear and found that these devices were getting an old “XP” based Wireless Network Policy.  I upgraded the policy which then gave us some extra options, including allowing / disallowing Wi-Fi Direct.upgraded policy

Computer Configuration > Policies > Windows Settings >Security Settings > Wireless Network (IEEE 802.11) Policies

The setting Don’t allow Wi-Fi Direct groups which needs to be disabled can be found under the “Network Permissions” tab. From what I can see, any Wireless policy configured for XP doesn’t have this option and a machine will simply disallow it.

How is Miracast or WiDi related to Wi-Fi Direct?  Well basically Wi-Fi Direct allows devices to connect directly to each other, without the need for a Wi-Fi AP, which is exactly what your surface is doing when attempting to stream the display straight to a TV / Projector.

Once we had this option turned off, ran a gpupdate on the machine and viola, wireless display showed up and began working.