PoshCode Logo PowerShell Code Repository

Get-SqlSpn by Chad Miller 29 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3234"></script>download | new post

Gets MSSQLSvc service principal names from Active Directory

  1. #######################
  2. <#
  3. .SYNOPSIS
  4. Gets MSQLSvc service principal names (spn) from Active Directory.
  5. .DESCRIPTION
  6. The Get-SqlSpn function gets SPNs for MSQLSvc services attached to account and computer objects
  7. .EXAMPLE
  8. Get-SqlSpn
  9. This command gets MSSQLSvc SPNs for the current domain
  10. .NOTES
  11. Adapted from http://www.itadmintools.com/2011/08/list-spns-in-active-directory-using.html
  12. Version History
  13. v1.0   - Chad Miller - Initial release
  14. #>
  15. function Get-SqlSpn
  16. {
  17.     $serviceType="MSSQLSvc"
  18.     $filter = "(servicePrincipalName=$serviceType/*)"
  19.     $domain = New-Object System.DirectoryServices.DirectoryEntry
  20.     $searcher = New-Object System.DirectoryServices.DirectorySearcher
  21.     $searcher.SearchRoot = $domain
  22.     $searcher.PageSize = 1000
  23.     $searcher.Filter = $filter
  24.     $results = $searcher.FindAll()
  25.  
  26.     foreach ($result in $results) {
  27.         $account = $result.GetDirectoryEntry()
  28.         foreach ($spn in $account.servicePrincipalName.Value) {
  29.             if($spn -match "^MSSQLSvc\/(?<computer>[^\.|^:]+)[^:]*(:{1}(?<port>\w+))?$") {
  30.                 new-object psobject -property @{ComputerName=$matches.computer;Port=$matches.port;AccountName=$($account.Name);SPN=$spn}
  31.  
  32.             }
  33.         }
  34.     }
  35.  
  36. } #Get-SqlSpn

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me