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.

 

Fixing the randomly stopping WsusPool IIS Application pool and Windows Updates failed 0x80244022 error

I was recently assisting a client with an upgrade of their Configuration Manager (SCCM) environment up to the latest release of 1702 and as part of that we’re also going over it’s currently deployed functionality and making sure it all works. They recently noted that clients were no longer receiving updates and ran the Software Update functionality of ConfigMgr, which funnily enough also relies on WSUS to sync up to Microsoft (but not push out the updates themselves).

So when troubleshooting Updating issues, you need to check both the Software Update Point and WSUS, and lowe and behold the WSUS console kept on crashing. After checking the Update Services service, I then turned to IIS and noticed the UpdatedServices Application pool had stopped, so I started it and went back into the console but soon noticed it had died again. After reading some advice on the TechNet forums they suggested raising the memory limit of the Wsus Application Pool, which is done by selecting the Application Pool Name and then clicking Advanced on the right hand side or right click menu. Scroll down to the bottom of the Advanced Settings Window until you see Private Memory Limit (KB) and increase this from the default of 1843200 (which is 1.8 GB) to 4194304 (which is 4 GB). Make sure you have enough RAM allocated to the machine, if you are feeling courageous you can also set the value to 0 which does not set a limit.

After increasing the available memory to the AppPool, WSUS synchronized successfully and clients were now receiving updates

An error has occurred when trying to access IdpInitiatedSignOn.aspx to test SAML authentication on AD FS 4.0 (Windows Server 2016)

A working example of IdpInitiatedSignOn.aspx

So usually one of the first things I do after initially setting up an AD FS environment (among others) is to test the Metadata (navigate to https://your.adfs.server/ federationmetadata/2007-06/federationmetadata.xml which should return valid XML) and sign-in functionality using the IdpInitiatedSignOn.aspx method. For Server 2012/2012 R2 this page enabled by default but if we navigate to this page on Server 2016 we get the following error;

An error occurred

The resource you are trying to access is not available. 
Contact your administrator for more information.

The reason being is that this is disabled on Server 2016 and there is an extra step that needs to take place to enable it.

We can check to see the current status by issuuing the following command in a PowerShell window.

Get-AdfsProperties | fl

Scrolling down you’ll eventually see enableIdPInitiaedSignonPage which should currently be set to false, to change this issue the following command

Set-Adfsproperties -enableIdPInitiatedSignonPage $true

This enables the IdPSignonPage and allows us to test a login process for a SAML authentication supported application. Once this works I know I can safely begin to provision my applications to authenticate users against this AD FS server.

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.

Watch out when you enable DNS Scavenging and have a DirectAccess environment

So we had recently enabled DNS scavenging for a large environment who also had a DirectAccess server. The next day we were getting help desk calls about remote users not able to connect and those who were in the office unable to use their devices. One of the cornerstones of DirectAccess is DNS and the Network Location Awareness this provides to the clients. We had to re-create the DNS records for DirectAccess manually on one of their DNS server.

  • directaccess-corpConnectivityHost which includes both A and AAAA records when deployed on IPv4-only networks. Basically the Loopback addresses for both IPv4 and IPv6.
  • directaccess-WebProbeHost this includes only A records and resolves to the IPv4 address assigned to the internal network interface of the DirectAccess server.
  • directaccess-NLS should point to the server hosting the Network Location Service, which should be Highly available.

So when building your DirectAccess infrastructure, always remember to set the DNS entries as Static.

Extracting Reporting data from your DirectAccess Server to CSV using PowerShell

I recently had to extract some data from our DirectAccess server to get information about a particular user and their number of connections during a time period along with data transferred. The Remote Access Management Console allows you to view these details but not extract or save them. So I turned to PowerShell and used the following snippet to extract what I needed.

Get-RemoteAccessConnectionStatistics –StartDateTime "1 April 2017 12:00" –EndDateTime "8 April 2017 12:00" | Export-Csv –Path "C:\Temp\DAConnections.csv"

I then cleaned the data up in Excel to give me only the user I was after along with date and times and amount of data transferred. You can also use Get-RemoteAccessConnectionStatisticsSummary and Get-RemoteAccessUserActivity which further drill down into what a particular user has been up to while connected to DirectAccess.

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.