PoshCode Archive  Artifact [50dee0cb82]

Artifact 50dee0cb82520050b5c0e122f2c31afb5e88b04de9ecada7f68a66f2d0f85395:

  • File Get-SqlSpn.ps1 — part of check-in [6cddf6cf91] at 2018-06-10 13:22:39 on branch trunk — Gets MSSQLSvc service principal names from Active Directory (user: Chad Miller size: 1605)

# encoding: ascii
# api: powershell
# title: Get-SqlSpn
# description: Gets MSSQLSvc service principal names from Active Directory
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# function: Get-SqlSpn
# x-poshcode-id: 3234
# x-archived: 2012-02-18T15:44:22
# x-published: 2012-02-14T18:28:00
#
#
#######################
<#
.SYNOPSIS
Gets MSQLSvc service principal names (spn) from Active Directory.
.DESCRIPTION
The Get-SqlSpn function gets SPNs for MSQLSvc services attached to account and computer objects
.EXAMPLE
Get-SqlSpn
This command gets MSSQLSvc SPNs for the current domain
.NOTES 
Adapted from http://www.itadmintools.com/2011/08/list-spns-in-active-directory-using.html
Version History 
v1.0   - Chad Miller - Initial release 
#>
function Get-SqlSpn
{
    $serviceType="MSSQLSvc"
    $filter = "(servicePrincipalName=$serviceType/*)"
    $domain = New-Object System.DirectoryServices.DirectoryEntry
    $searcher = New-Object System.DirectoryServices.DirectorySearcher
    $searcher.SearchRoot = $domain
    $searcher.PageSize = 1000
    $searcher.Filter = $filter
    $results = $searcher.FindAll()

    foreach ($result in $results) {
        $account = $result.GetDirectoryEntry()
        foreach ($spn in $account.servicePrincipalName.Value) {
            if($spn -match "^MSSQLSvc\/(?<computer>[^\.|^:]+)[^:]*(:{1}(?<port>\w+))?$") {
                new-object psobject -property @{ComputerName=$matches.computer;Port=$matches.port;AccountName=$($account.Name);SPN=$spn} 

            } 
        }
    }

} #Get-SqlSpn