Library `mssql`

MSSQL Library supporting a very limited subset of operations.

The library was designed and tested against Microsoft SQL Server 2005. However, it should work with versions 7.0, 2000, 2005, 2008 and 2012. Only a minimal amount of parsers have been added for tokens, column types and column data in order to support the first scripts.

The code has been implemented based on traffic analysis and the following documentation:

SSRP Protocol Specification: http://msdn.microsoft.com/en-us/library/cc219703.aspx
TDS Protocol Specification: http://msdn.microsoft.com/en-us/library/dd304523.aspx
TDS Protocol Documentation: http://www.freetds.org/tds.html.
The JTDS source code: http://jtds.sourceforge.net/index.html.

SSRP: Class that handles communication over the SQL Server Resolution Protocol, used for identifying instances on a host.
ColumnInfo: Class containing parsers for column types which are present before the row data in all query response packets. The column information contains information relevant to the data type used to hold the data eg. precision, character sets, size etc.
ColumnData: Class containing parsers for the actual column information.
Token: Class containing parsers for tokens returned in all TDS responses. A server response may hold one or more tokens with information from the server. Each token has a type which has a number of type specific fields.
QueryPacket: Class used to hold a query and convert it to a string suitable for transmission over a socket.
LoginPacket: Class used to hold login specific data which can easily be converted to a string suitable for transmission over a socket.
PreLoginPacket: Class used to (partially) implement the TDS PreLogin packet
TDSStream: Class that handles communication over the Tabular Data Stream protocol used by SQL serve. It is used to transmit the the Query- and Login-packets to the server.
Helper: Class which facilitates the use of the library by through action oriented functions with descriptive names.
Util: A "static" class containing mostly character and type conversion functions.

The following sample code illustrates how scripts can use the Helper class to interface the library:

local helper = mssql.Helper:new()
status, result = helper:Connect( host, port )
status, result = helper:Login( username, password, "temdpb", host.ip )
status, result = helper:Query( "SELECT name FROM master..syslogins" )
helper:Disconnect()

The following sample code illustrates how scripts can use the Helper class with pre-discovered instances (e.g. by ms-sql-discover or broadcast-ms-sql-discover):

local instance = mssql.Helper.GetDiscoveredInstances( host, port )
if ( instance ) then
  local helper = mssql.Helper:new()
  status, result = helper:ConnectEx( instance )
  status, result = helper:LoginEx( instance )
  status, result = helper:Query( "SELECT name FROM master..syslogins" )
  helper:Disconnect()
end

Known limitations:

The library does not support SSL. The foremost reason being the awkward choice of implementation where the SSL handshake is performed within the TDS data block. By default, servers support connections over non SSL connections though.
Version 7 and ONLY version 7 of the protocol is supported. This should cover Microsoft SQL Server 7.0 and later.
TDS Responses contain one or more response tokens which are parsed based on their type. The supported tokens are listed in the TokenType table and their respective parsers can be found in the Token class. Note that some token parsers are not fully implemented and simply move the offset the right number of bytes to continue processing of the response.
The library only supports a limited subsets of datatypes and will abort execution and return an error if it detects an unsupported type. The supported data types are listed in the DataTypes table. In order to add additional data types a parser function has to be added to both the ColumnInfo and ColumnData class.
No functionality for languages, localization or character codepages has been considered or implemented.
The library does database authentication only. No OS authentication or use of the integrated security model is supported.
Queries using SELECT, INSERT, DELETE and EXEC of procedures have been tested while developing scripts.

Authors:

Patrik Karlsson <patrik@cqure.net>
Chris Woodbury

Copyright © Same as Nmap--See https://nmap.org/book/man-legal.html

Source: https://svn.nmap.org/nmap/nselib/mssql.lua

Script Arguments

mssql.username: The username to use to connect to SQL Server instances. This username is used by scripts taking actions that require authentication (e.g. ms-sql-query) This username (and its associated password) takes precedence over any credentials discovered by the ms-sql-brute and ms-sql-empty-password scripts.
mssql.domain: The domain against which to perform integrated authentication. When set, the scripts assume integrated authentication should be performed, rather than the default sql login.
mssql.password: The password for mssql.username. If this argument is not given but mssql.username, a blank password is used.
mssql.scanned-ports-only: If set, the script will only connect to ports that were included in the Nmap scan. This may result in instances not being discovered, particularly if UDP port 1434 is not included. Additionally, instances that are found to be running on ports that were not scanned (e.g. if 1434/udp is in the scan and the SQL Server Browser service on that port reports an instance listening on 43210/tcp, which was not scanned) will be reported but will not be stored for use by other ms-sql-* scripts.
mssql.timeout: How long to wait for SQL responses. This is a number followed by ms for milliseconds, s for seconds, m for minutes, or h for hours. Default: 30s.
mssql.instance-port: In addition to instances discovered via port scanning and version detection, run scripts on the instances running on these ports (number or list of numbers)
mssql.instance-all: In addition to instances discovered via port scanning and version detection, run scripts on all discovered instances. These include named-pipe instances via SMB and those discovered via the browser service.
mssql.protocol: The protocol to use to connect to the instance. The protocol may be either NP,Named Pipes or TCP.
mssql.instance-name: In addition to instances discovered via port scanning and version detection, run scripts on these named instances (string or list of strings)

Functions

[TokenType.Done] (data, pos): Parse done tokens
[TokenType.DoneInProc] (data, pos): Parses a DoneInProc token received after executing a SP
[TokenType.DoneProc] (data, pos): Parses a DoneProc token received after executing a SP
[TokenType.EnvironmentChange] (data, pos): Parse environment change tokens (This function is not implemented and simply moves the pos offset)
[TokenType.ErrorMessage] (data, pos): Parse error message tokens
[TokenType.InformationMessage] (data, pos): Parse information message tokens
[TokenType.LoginAcknowledgement] (data, pos): Parse login acknowledgment tokens
[TokenType.OrderBy] (data, pos): Parses a OrderBy token
[TokenType.ReturnStatus] (data, pos): Parses a ReturnStatus token
[TokenType.TDS7Results] (data, pos): Parse TDS result tokens
_GetSpLookupTable (self): Returns a lookup table that maps revision numbers to service pack and cumulative update levels for the applicable SQL Server version, e.g., {{1913, "RC1"}, {2100, "RTM"}, {2316, "RTMCU1"}, ..., {3000, "SP1"}, {3321, "SP1CU1"}, ..., {3368, "SP1CU4"}, ...}
_InferProductVersion (self): Using the version number, determines the product version
_ParseSsrpString (host, ssrpString): Parses an SSRP string and returns a table containing one or more SqlServerInstanceInfo objects created from the parsed string.
_ParseVersionInfo (self): Processes version data to determine (if possible) the product version, service pack level and patch status.
_ProcessResponse (host, responseData)
AddOrMergeInstance (newInstance): Adds an instance to the list of instances kept in the Nmap registry for shared use by SQL Server scripts.
Connect (self, host, port): Establishes a connection to the SQL server
Connect (self, host, port): Establishes a connection to the SQL server
ConnectEx (self, instanceInfo, host, port): Establishes a connection to the SQL server
ConnectEx (self, instanceInfo, host, port): Establishes a connection to the SQL server
ConnectToNamedPipe (self, host, pipePath, overrides, smbOverrides): Establishes a connection to the SQL server
Disconnect (self): Disconnects from the SQL Server
Disconnect (self): Disconnects from the SQL Server
Discover (host): Attempts to discover SQL Server instances by a variety of means.
DiscoverBySmb (host, port): Attempts to discover SQL Server instances listening on default named pipes.
DiscoverBySsrp (host, port, broadcast): Attempts to discover SQL Server instances using SSRP to query one or more (if broadcast is used) SQL Server Browser services.
DiscoverByTcp (host, port): Attempts to discover a SQL Server instance listening on the specified port.
DiscoverDACPort (instance): Queries the SQL Browser service for the DAC port of the specified instance
DiscoverInstances (host, port): Attempts to retrieve information about SQL Server instances by querying the SQL Server Browser service on a host.
DiscoverInstances_Broadcast (host, port): Attempts to retrieve information about SQL Server instances by querying the SQL Server Browser service on a broadcast domain.
FormatOutputTable (tbl, with_headers): Takes a table as returned by Query and does some fancy formatting better suitable for stdnse.format_output
FromBytes (bytes): Reads a byte-string and creates a PreLoginPacket object from it. This is intended to handle the server's response to a pre-login request.
GetDiscoveredInstances (host, port): Gets a table containing SqlServerInstanceInfo objects discovered on the specified host (and port, if specified).
GetInstanceVersion (instanceInfo): Attempts to connect to a SQL Server instance listening on a TCP port in order to determine the version of the SSNetLib DLL, which is an authoritative version number for the SQL Server instance itself.
GetLoginCredentials (instanceInfo): Returns a username-password set according to the following rules of precedence:
GetLoginCredentials_All (instanceInfo): Returns all of the credentials available for the target instance, including any set by the mssql.username and mssql.password script arguments.
GetName (self): Returns a name for the instance, based on the available information.
GetNamedPipeName (self): Gets the name of the name pipe, or nil
GetTargetInstances (host, port): Gets a table containing SqlServerInstanceInfo objects for the instances that should be run against, based on the script-args (e.g. mssql.instance)
HasNetworkProtocols (self): Indicates whether this instance has networking protocols enabled, such that scripts could attempt to connect to it.
InitScript (process_instance): Returns an action, portrule, and hostrule for standard SQL Server scripts
Login (self, username, password, database, servername): Authenticates to SQL Server.
LoginEx (self, instanceInfo, database, servername): Authenticates to SQL Server, using the credentials returned by Helper.GetLoginCredentials().
Merge (self, other, overwrite): Merges the data from one SqlServerInstanceInfo object into another.
ParseToken (data, pos): Parses the first token at positions pos
PopulateNmapPortVersion (self, port): Uses the information in this SqlServerVersionInformation object to populate the version information in an Nmap port table for a SQL Server TCP listener.
Query (self, query): Performs a SQL query and parses the response
Receive (self): Receives responses from SQL Server
Send (self, packetType, packetData): Send a TDS request to the server
SetDatabase (self, database): Sets the database used in authentication
SetInstanceName (self, instanceName): Sets the instance name of the target
SetIsClustered (self, isClustered): Sets whether the instance is in a cluster
SetPassword (self, password): Sets the password used for authentication
SetRequestEncryption (self, requestEncryption): Sets whether to request encryption (default = false)
SetRequestMars (self, requestMars): Sets whether to request MARS support (default = undefined)
SetServer (self, server): Sets the server's name used in authentication
SetTimeout (self, timeout): Sets the timeout for communication over the socket
SetUsername (self, username): Sets the username used for authentication
SetVersion (self, versionInfo): Sets the client version (default = 9.00.1399.00)
SetVersion (self, versionInfo): Sets the client version (default = 9.00.1399.00)
SetVersionNumber (self, versionNumber, source): Sets the version using a version number string.
TDS7CryptPass (password, decoder): Encrypts a password using the TDS7 *ultra secure* XOR encryption
ToBytes (self): Returns the pre-login packet as a byte string
ToString (self): Returns the authentication packet as string
ToString (self): Returns the authentication packet as string
ToString (self): Returns the authentication packet as string
WasDiscoveryPerformed (host): Returns true if discovery has been performed to detect SQL Server instances on the given host

Functions

[TokenType.Done] (data, pos)

Parse done tokens

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.DoneInProc] (data, pos)

Parses a DoneInProc token received after executing a SP

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.DoneProc] (data, pos)

Parses a DoneProc token received after executing a SP

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.EnvironmentChange] (data, pos)

Parse environment change tokens (This function is not implemented and simply moves the pos offset)

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.ErrorMessage] (data, pos)

Parse error message tokens

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.InformationMessage] (data, pos)

Parse information message tokens

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.LoginAcknowledgement] (data, pos)

Parse login acknowledgment tokens

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.OrderBy] (data, pos)

Parses a OrderBy token

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.ReturnStatus] (data, pos)

Parses a ReturnStatus token

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

[TokenType.TDS7Results] (data, pos)

Parse TDS result tokens

Parameters

data: string containing "raw" data
pos: number containing offset into data

Return values:

pos number containing new offset after parse
token table containing token specific fields

_GetSpLookupTable (self)

Returns a lookup table that maps revision numbers to service pack and cumulative update levels for the applicable SQL Server version, e.g., {{1913, "RC1"}, {2100, "RTM"}, {2316, "RTMCU1"}, ..., {3000, "SP1"}, {3321, "SP1CU1"}, ..., {3368, "SP1CU4"}, ...}

Parameters

self

_InferProductVersion (self)

Using the version number, determines the product version

Parameters

self

_ParseSsrpString (host, ssrpString)

Parses an SSRP string and returns a table containing one or more SqlServerInstanceInfo objects created from the parsed string.

Parameters

host
ssrpString

_ParseVersionInfo (self)

Processes version data to determine (if possible) the product version, service pack level and patch status.

Parameters

self

_ProcessResponse (host, responseData)

Parameters

host
responseData

AddOrMergeInstance (newInstance)

Adds an instance to the list of instances kept in the Nmap registry for shared use by SQL Server scripts.

If the registry already contains the instance, any new information is merged into the existing instance info. This may happen, for example, when an instance is discovered via named pipes, but the same instance has already been discovered via SSRP; this will prevent duplicates, where possible.