How to get all Domain Controllers in Active Directory Forest with Powershell

Question: I need to configure some applications to use some Domain Controllers . To establish the address - I need to list the Domain Controllers within the Active Directory Forest. Is there a method in Powershell to create a list of Domain Controllers ? Answer: Powershell has a flexible group of Powershell cmdlets to get information about the Active Directory . Combining Get-ADForest and Get-ADDomainController will create a recordset Here is a sample code snippet : (Get-ADForest).Domains | % { Get-ADDomainController -Discover -DomainName $_ } | % { Get-ADDomainController -server $_.Name -filter * } | Select Name, Domain, Forest, IPv4Address,... Read more →

Quick access to SQL Server AlwaysOn Availability cheatsheet

These are queries to manage SQL Server AlwaysOn Availability Groups Using sys.fn_hadr_is_primary_replica to identify if the database is primary replica SELECT sys.fn_hadr_is_primary_replica ('my_ag_db'); Display All availability groups visible to this server when the Server is the Primary replica SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica = @@Servername; Display All availability groups visible to this server when this Server is a Secondary replica SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica != @@Servername; Display All Databases Across all Availability groups within the... Read more →

List the differences between Kerberos and NTLM

There is a message found in SQL Server Error Logs similar to The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. The line I'm interested in reviewing is Failure to register a SPN might cause integrated authentication to use... Read more →

How to find Active Directory users with Get-ADUser search filter

Question: How can I find a list of Active Directory users with a specific search filter on the name column using Powershell? In this case I want to find all Active Directory users in the default OU group with a search string. Answer: Assuming you are in the current Domain group \ OU group , find below an example of the Powershell cmdlet Get-ADUser which will filter on the search string "dba". In this example seaech - the GetADUser cmdlet iterates through the user list looking for any user with the string "dba" in any part of the name. Get-ADUser... Read more →

How to use STRING_AGG to concatenate strings with grouping

Question: Below is table and expected result. What is the query to achieve this result Table : Test ID LOT 7065161 4 7065212 1 7065212 4 7065203 1 7065203 2 7065203 3 Expected Result of query ID LOT 7065161 4 7065212 1_4 7065203 1_2_3 Answer: Previous to SQL Server 2017 - I use to use the COALESCE function , but since SQL Server 2017 - a new function was introduced : STRING_AGG . According to MS documentation "Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string" There... Read more →