http://www.sqlservercentral.com/blogs/sqlmanofmystery/2013/04/15/finding-sql-server-installs-with-powershell-again/

Printed 2014/12/21 08:34PM

Finding SQL Server Installs with Powershell, Again

By Wesley Brown, 2013/04/15

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 0×04 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
0×12 packet type 12 is prelogin
0×01 status 01 is end of message meaning this is the only packet
0×00,0×34 packet length
0×00,0×00 SPID the client spid for debugging from sql server to client
0×00 packetId unsigned char packetid is incramented by 1 modulo 256 this is currently ignored??
0×00 window currently not used just set it to 00
End Packet Header
Option Stream
0×00, This is the version number packet
0×00,0×15, 21st position
0×00,0×06, 6 bytes long
0×01, This is the encryption option
0×00,0x1b, 27th position
0×00,0×01, 1 byte long
0×02, INSTOPT the instance name
0×00,0x1c, 28th position
0×00,0x0c, 12 bytes long. This will vary currently hard set to MSSQLServer
0×01, ThreadId, currently hard set to 1
0×00,0×28, 40th position
0×00,0×04, 4 bytes long
0xff, Packet Terminator
End Option Stream
Begin Tokenless Stream
0×08,0×00,0×01,0×55, Major build number 0×00,0×00, Minor build number
0×00, No encryption
0x4d,0×53,0×53,0×51,0x4c,0×53,0×65,0×72,0×76,0×65,0×72,0×00, Instance name if known should be set if not default to MSSQLServer
0×01,0×00,0×00,0×00; 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!

The post Finding SQL Server Installs with Powershell, Again appeared first on .


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.