Category Archives: powershell

Finding SQL Server Installs with Powershell, Again

But the SQL Listener isn’t started!

Yep, I heard you loud and clear. There is more than one way to find SQL Server on your network. This time we will dig deep into the TDS protocol docs again along with a bit of guess work.

Old Dog New Protocol Plus PowerShell

Last time we used PowerShell and the .net framework to assemble a UDP packet and send it to port 1434. This works out just fine if you have the SQL Browser running. If you are running a single standalone instance on the default port the browser service isn’t needed and may not even be enabled. This time we will be using TCP instead of UDP. This gets us around the browser service issue but also limits the information we can get back. As we dig through the documentation we can send what is known as a PRELOGIN packet. The server will respond with a few bits of information including the version of SQL Server that you are trying to log into. My problem is a simple one. I’m really not sure I’m decoding the return packet correctly. The only thing the docs say about it is it is a valid PRELOGIN response. It isn’t a complete LOGINACK packet, I’m not sure what it is. As soon as I know I’ll update this post. UPDATE: It is a PRELOGIN packet with a 0x04 header type and should conform to the PRELOGIN packet specification. What it does do though is prove if SQL Server is listening on a specific TCP port. As far as I can tell the version I return should be accurate for SQL Server 2005 and above. UPDATE: I have tested this on SQL Server 7.0 through SQL Server 2012 and it works for SQL Server 2000 and above. The version number is correct if the version number minor is above 255. I’m still working to decode the minor version number correctly. This module allows you to pass in a server name and any TCP port to check for SQL Server listening so even if someone has moved from the default of 1433 you can still scan other ports at your leisure  I would warn you that scanning every port on a machine will get you a visit from your network security folks. If you plan on doing a security sweep you should get everyone involved up front. Trust me on this one, they don’t think it is funny when you scan every IP and port on the network looking for SQL Server installs!

UPDATE: Thanks to Kendal Van Dyke(blog|twitter) a real PoSH guru AND SQL Server guy I’ve renamed the function following the PoSH standards of verb-noun.

<#
.SYNOPSIS
    Sends PRELOGIN to TCP port to get SQL Server Version.
.DESCRIPTION
    Does what it says.
#>
function Get-SQLServerVersionTCP{
    [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = "low"
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer,
        [Parameter(
            Mandatory = $False,
            Position = 1,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [int]$Port,
        [Parameter(
            Mandatory = $False,
            Position = 2,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [int]$ConnectionTimeout
    )

    begin {
        $ErrorActionPreference = "SilentlyContinue"
    }
    process {
        #get the ipaddress of the server
        $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        #build and connect to TCPSocket
	    $TCPSocket = new-object System.Net.Sockets.TcpClient
        $TCPSocket.Connect($IPAddress, $Port);

        #attach socket to network stream
        $TCPStream = $TCPSocket.GetStream();

        # Build the prelogin packet see notes below
        $PreloginPacket =   0x12,0x01,0x00,0x34,0x00,0x00,
                            0x00,0x00,0x00,0x00,0x15,0x00,
                            0x06,0x01,0x00,0x1b,0x00,0x01,
                            0x02,0x00,0x1c,0x00,0x0c,0x03,
                            0x00,0x28,0x00,0x04,0xff,0x08,
                            0x00,0x01,0x55,0x00,0x00,0x00,
                            0x4d,0x53,0x53,0x51,0x4c,0x53,
                            0x65,0x72,0x76,0x65,0x72,0x00,
                            0x01,0x00,0x00,0x00;

        #send the prelogin packet
        $TCPStream.Write($PreloginPacket, 0, $PreloginPacket.Length);

        #wait a bit for the stream to build up
        Start-Sleep -m 1000

        # Buffer to store the response $ReturnData
        $ReturnData = new-object System.Byte[] 255

        # Read the TcpServer response
        $BytesReturned = $TCPStream.Read($ReturnData, 0, $ReturnData.Length)

        [string]$VersionNumber = $ReturnData[29].ToString()+"."+$ReturnData[30].ToString()+"."+(($ReturnData[31]*256)+$ReturnData[32]).ToString()
        return $VersionNumber
        $TCPStream.Close();
        }
}
Export-ModuleMember -Function Get-SQLServerVersionTCP

And for those who are REALLY curious my notes on assembling the PRELOGIN packet. This should work with SQL Server 2005 and above.

Packet Header
0x12 packet type 12 is prelogin
0x01 status 01 is end of message meaning this is the only packet
0x00,0x34 packet length
0x00,0x00 SPID the client spid for debugging from sql server to client
0x00 packetId unsigned char packetid is incramented by 1 modulo 256 this is currently ignored??
0x00 window currently not used just set it to 00
End Packet Header
Option Stream
0x00, This is the version number packet
0x00,0x15, 21st position
0x00,0x06, 6 bytes long
0x01, This is the encryption option
0x00,0x1b, 27th position
0x00,0x01, 1 byte long
0x02, INSTOPT the instance name
0x00,0x1c, 28th position
0x00,0x0c, 12 bytes long. This will vary currently hard set to MSSQLServer
0x01, ThreadId, currently hard set to 1
0x00,0x28, 40th position
0x00,0x04, 4 bytes long
0xff, Packet Terminator
End Option Stream
Begin Tokenless Stream
0x08,0x00,0x01,0x55, Major build number 0x00,0x00, Minor build number
0x00, No encryption
0x4d,0x53,0x53,0x51,0x4c,0x53,0x65,0x72,0x76,0x65,0x72,0x00, Instance name if known should be set if not default to MSSQLServer
0x01,0x00,0x00,0x00; ThreadId
End Tokenless Stream

The End?

Not quite. I’m working on another method that doesn’t rely on TCP/IP at all. I’ll let you guess what it might be.

ALL HAIL POWERSHELL!

Parsing Glenn Berry’s Diagnostic Information Queries With PowerShell

Do It In Bulk

I usually don’t run queries on just one server. Most of the time I need to get some piece of information from dozens if not hundreds of servers at a time. I also really like Glenn’s diagnostic queries and use them regularly with some modifications and additions of my own. I’m also really digging PowerShell too.

You Got Your Queries In My PowerShell

PowerShell is a very powerful way to gather information across lots of servers. I’ve got a set of templating files that drive my PowerShell scripts and thought I’d apply some of that to Glenn’s queries. The result is a function that takes a the file name of Glenn’s queries and outputs just the server level or the database level queries as an array that you can then loop through to query any number of servers or databases! It currently works with all three sets of files.

<#
.SYNOPSIS
Parse Glenn  Berry's DMV Diagnostic Queries into arrays http://sqlserverperformance.wordpress.com/
.DESCRIPTION
Does what it says so you can then loop through the array and execute the queries against a server ro database!
.PARAMETER FileName
FileName the name of the file you wish to process
.PARAMETER QueryType
QueryType ether server level or database level queries

.EXAMPLE
parseGABDMV '<path to sql file>\SQL Server 2005 Diagnostic Information Queries(December 2012).sql' server
#>
function parseGBDMV{
	[cmdletbinding(
		DefaultParameterSetName = '',
		ConfirmImpact = "low"
	)]
	Param(
		[Parameter(
			Mandatory = $True,
			Position = 0,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$FileName,
		[Parameter(
			Mandatory = $True,
			Position = 1,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$QueryType
	)
	Begin {
		$ErrorActionPreference = "SilentlyContinue"
		$found = $false
		$endquery = $false
		$startDatabaseQueries = $false
		$ServerQueries = @()
		$DatabaseQueries = @()
		$Query = "";
	}
	Process {
		Get-Content $File | foreach-Object {
			$_ = $_ -replace '\r\n$', "" #strip out empty lines
			$_ = $_ -replace '\t+', ""
			if ($_.Length -ne 0){
				if ($_.trim() -ne ""){
					if ($_.trim() -ne "`t"){
						$String = $_.toString();
					}
					else{
						$String = ""
					}
				}
				else{
					$String = ""
				}
			}
			else{
				$String = ""
			}

			if($found) {
				if($String -notlike "--*"){
					if($String.Trim() -ne ""){
						if($String.Length -ne 0){
							$Query += $String + "`r`n"
						}
					}
				}
			}

			if($String -like "--*"){
				if($String -cmatch '\(Query'){
					$found = $true
					$endquery = $true
					$Query = $Query -replace '\r\n$', ""
					if ($String.trim() -ne ""){
						if ($startDatabaseQueries){
							if (!$Query.Contains("USE YourDatabaseName")){
								$DatabaseQueries += $Query
							}
						}
						else{
							$ServerQueries += $Query
						}
					}
					$Query = ""
				}
			}

			if ($endqery) {
				$found = $false
			}
			if ($String -ne $null){
				if ($String.Contains("-- Database specific queries")){
					$startDatabaseQueries = $true
				}
				if ($String.Contains("-- Switch to user database *******************")){
					$startDatabaseQueries = $true
				}
			}
		}
		#dunno why always have a blank line on the first server query list
		$ServerQueries = $ServerQueries[1 .. ($ServerQueries.count-1)]
		if ($QueryType.ToUpper() -eq "SERVER"){
			return $ServerQueries
		}
		if ($QueryType.ToUpper() -eq "DATABASE"){
			return $DatabaseQueries
		}
	}
}

It may not be the cleanest PowerShell file parser on the planet but it seems to work and speed really isn’t an issue since the files are so small.

Enjoy!

Finding SQL Server Installs Using Powershell

Old Dog, New Tricks

I’ve been writing tools for SQL Server for a lot of years. Some of these tools were never completely released. Some of them were just for me. Some of them overlapped other tools already on the market and free for all. Recently, I started updating my bag of tricks and tools. This seemed like a great time to get back into PowerShell. I decided to pull out a bit of C# code I cobbled together nine years ago as part of a tool to find SQL Server instances on a network. I never really got around to making it a “production” ready tool since there was already a most excellent one on the scene in the form of SQLPing from Chip Andrews. SQLPing is a fantastic scan tool and can scan many more things than the method covered here.

Hello Operator?

When Microsoft implemented named instances with SQL Server 2000 they had to have a way to direct incoming traffic pointed to a single IP to the correct TCP port. So, they built in a traffic cop. When SQL Server 2005 came around it was pulled from the core network listener and put into its own service, the browser service. There was little documentation on how the browser worked. Luckily for poor sods like me using a network packet sniffing tool it was pretty easy to figure out how to talk to the browser. Once I figured out how to get the browser service to tell me what instances it knew about it was trivial to implement. These days Microsoft is being much more open about these kinds of things and actually have released documentation on how the browser service and the SQL Server Resolution Protocol works.

The Basic Mechanics.

As most of you know SQL Server’s default instance is on 1433 and the browser service is on 1434. Our goal is to send a UDP packet to port 1434. According to the docs we only need to send a single byte containing the number two. This prompts the listener to give us a list of instances and what port they are bound to. When I wrote my implementation it really was that simple. I dug around and figured out how to get PowerShell to send a UDP packet. I tested it and lo’ it worked, on my machine….

It’s Never So Easy.

When I tested it on my lab VM cluster with multiple nodes and multiple instances it would fail! I just didn’t get it. My C# code from the stone age worked just fine. My PowerShell code was a hit or miss. I started troubleshooting the problem just as I had in the beginning. I fired up my network sniffer and watched the traffic flow back and fourth. Again, I saw exactly what I expected, or more accurately what I wanted to see.  A single packet with the hex number two in the first byte position. I ran the test several times over the next hour or so. Eventually, I just had to walk away from it. The next day I started over again. I read the documentation, it still said the same thing. I ran the test, still a two in the first byte position. Then I spotted it. The packet wasn’t one byte long. I went back and read the document again. It gives an upper boundary of 32 bytes but no lower limit. The packet I saw come through wasn’t one byte long it was always more than that. Armed with that I started big and worked my way down until I got errors. Now I know that a packet of three bytes always triggers a response. a  two with two zeros.

Meet QuerySQLListener.

Here is the function I put together. It takes a server name and returns an array of strings with the fun bits in it.

function QuerySQLListener{
    [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = &quot;low&quot;
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer
    )
    Begin {
        $ErrorActionPreference = &quot;SilentlyContinue&quot;
        $Port = 1434
        $ConnectionTimeout = 1000
        $Responses  = @();
    }
    Process {
        $UDPClient = new-Object system.Net.Sockets.Udpclient
        $UDPClient.client.ReceiveTimeout = $ConnectionTimeout
        $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        $UDPClient.Connect($IPAddress,$Port)
        $ToASCII = new-object system.text.asciiencoding
        $UDPPacket = 0x02,0x00,0x00
        Try {
            $UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
            $UDPClient.Client.Blocking = $True
            [void]$UDPClient.Send($UDPPacket,UDPPacket.length)
            $BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
            [string]$Response = $ToASCII.GetString($BytesRecived)
            $res = &quot;&quot;
            If ($Response) {
                $Response = $Response.Substring(3,$Response.Length-3).Replace(&quot;;;&quot;,&quot;~&quot;)
                #$i = 0;
                $Response.Split(&quot;~&quot;) | ForEach {
                $Responses += $_
            }
            $socket = $null;
            $UDPClient.close()
        }
        }
        Catch {
            $Error[0].ToString()
            $UDPClient.Close()
        }
    }
    End {
        return ,$Responses
    }
}

 

It Isn’t Perfect But It Works.

I”m sure there is a cleaner way to implement it but I’m really just getting into PowerShell again after several months of tinkering with it last time. If you have any suggestions or improvements I’ll gladly take them!