Error handling response - admin/mssql/mssql_sql - unsupported token and invalid number of columns

[Himself132]

Steps to reproduce

How'd you do it?

1. Use valid credentials to connect to a Microssoft SQL database and run various sql commands using the module admin/mssql/mssql_sql. Some commands result in the error below, most do not.
2. SQL used set sql select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t
3. Error observed showing hundreds of lines showing unsupported token: followed by an integer, e.g. 0, 53, alternating between 0 and a number:

[*] SCRUBBED_IP_ADDRESS:1433 - SQL Query: select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t [*] SCRUBBED_IP_ADDRESS:1433 - Row Count: 5 (Status: 16 Command: 193) [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 53 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 44 ...SNIPPED... [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 10 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - unknown column type: {:utype=>0, :flags=>9, :type=>99, :id=>:unknown, :msg_len=>254, :name=>"\xFF\xFF\x7F\t\x04\xD04\x14sys.dm_exec_sql_text\x04text\xD1\x10dummy textptrdummyTS(\x02(@1 int,@2 int,@3 int,@4 int,@5 numeric(8,1),@6 numeric(8,1),@7 varchar(8000),@8 int)UPDATE [SCRUBBED] set [deal_num] = @1,[tran_num] = @2,[currency] = @3,[tran_status] = @4,[SCRUBBED] = @"} [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 16 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 100 ...SNIPPED... [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - Auxiliary failed: Rex::RuntimeError Invalid number of columns! [-] SCRUBBED_IP_ADDRESS:1433 - Call stack: [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.55/lib/rex/text/wrapped_table.rb:194:in 'add_row' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.55/lib/rex/text/wrapped_table.rb:186:in '<<' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:58:in 'block in mssql_print_reply' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:57:in 'each' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:57:in 'mssql_print_reply' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client.rb:577:in 'mssql_query' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/msf/core/exploit/remote/mssql.rb:201:in 'mssql_query' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/modules/auxiliary/admin/mssql/mssql_sql.rb:41:in 'run' [*] Auxiliary module execution completed

This section should also tell us any relevant information about the
environment; for example, if an exploit that used to work is failing,
tell us the victim operating system and service versions.

Victim:
Microsoft SQL Server 2019 (RTM-CU24) (KB5031908) - 15.0.4345.5 (X64) Dec 4 2023 14:44:16 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Attacker:
Linux SCRUBBED_HOSTNAME 6.6.9-amd64 #1 SMP PREEMPT_DYNAMIC Kali 6.6.9-1kali1 (2024-01-08) x86_64 GNU/Linux

Were you following a specific guide/tutorial or reading documentation?

No

Expected behavior

Output returned properly as observed in wireshark capture

What should happen?
Resultant output parsed properly and displayed in the msfconsole prompt

Current behavior

What happens instead?
Errors parsing responses as shown above

Metasploit version

Framework: 6.3.55-dev
Console : 6.3.55-dev

Additional Information

Module/Datastore

The following global/module datastore, and database setup was configured before the issue occurred:

Collapse

[admin/mssql/mssql_sql]
PASSWORD=SCRUBBED
USERNAME=SCRUBBED
RHOSTS=SCRUBBED
SQL=select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t
loglevel=3
WORKSPACE=
VERBOSE=false
RPORT=1433
CPORT=
CHOST=
SSL=false
SSLServerNameIndication=
SSLVersion=Auto
SSLVerifyMode=PEER
SSLCipher=
Proxies=
ConnectTimeout=10
TCP::max_send_size=0
TCP::send_delay=0
NTLM::UseNTLMv2=true
NTLM::UseNTLM2_session=true
NTLM::SendLM=true
NTLM::UseLMKey=false
NTLM::SendNTLM=true
NTLM::SendSPN=true
USE_WINDOWS_AUTHENT=false
HEX2BINARY=/usr/share/metasploit-framework/data/exploits/mssql/h2b
DOMAIN=WORKSTATION
KrbCacheMode=read-write
Mssql::Auth=auto
Mssql::Rhostname=
DomainControllerRhost=
Mssql::Krb5Ccname=
Mssql::KrbOfferedEncryptionTypes=AES256,AES128,RC4-HMAC,DES-CBC-MD5,DES3-CBC-SHA1

[framework/ui/console]
ActiveModule=auxiliary/admin/mssql/mssql_sql

Database Configuration

The database contains the following information:

Collapse

Session Type: Connected to msf. Connection type: postgresql.

ID	Hosts	Vulnerabilities	Notes	Services
1 (Current)	0	0	0	0
Total (1)	0	0	0	0

History

The following commands were ran during the session and before this issue occurred:

Collapse

0      history
1      creds
2      search mssql
3      use 21
4      info
5      use PASSWORD SCRUBBED
6      set PASSWORD SCRUBBED
7      set USERNAME SCRUBBED
8      set RHOSTS SCRUBBED
9      info
10     set SQL "select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t"
11     run
12     set sql "select @@version"
13     run
14     version
15     set loglevel 3
16     set SQL "select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t"
17     run
18     debug

Framework Errors

The following framework errors occurred before the issue occurred:

Collapse

[03/18/2024 11:20:41] [e(0)] core: Module PASSWORD not found, and no loading errors found. If you're using a custom module refer to our wiki: https://docs.metasploit.com/docs/using-metasploit/intermediate/running-private-modules.html
[03/18/2024 11:21:56] [e(0)] core: Auxiliary failed - Rex::RuntimeError Invalid number of columns!
[03/18/2024 11:39:18] [e(0)] core: Auxiliary failed - Rex::RuntimeError Invalid number of columns!

Web Service Errors

The following web service errors occurred before the issue occurred:

Collapse

msf-ws.log does not exist.

Framework Logs

The following framework logs were recorded before the issue occurred:

Collapse

[03/18/2024 11:20:41] [e(0)] core: Module PASSWORD not found, and no loading errors found. If you're using a custom module refer to our wiki: https://docs.metasploit.com/docs/using-metasploit/intermediate/running-private-modules.html
[03/18/2024 11:21:56] [e(0)] core: Auxiliary failed - Rex::RuntimeError Invalid number of columns!
[03/18/2024 11:39:18] [e(0)] core: Auxiliary failed - Rex::RuntimeError Invalid number of columns!

Web Service Logs

The following web service logs were recorded before the issue occurred:

Collapse

msf-ws.log does not exist.

Version/Install

The versions and install method of your Metasploit setup:

Collapse

Framework: 6.3.55-dev
Ruby: ruby 3.1.2p20 (2022-04-12 revision 4491bb740a) [x86_64-linux-gnu]
OpenSSL: OpenSSL 3.1.4 24 Oct 2023
Install Root: /usr/share/metasploit-framework
Session Type: Connected to msf. Connection type: postgresql.
Install Method: Other - Please specify

[adfoster-r7]

@Himself132 Could you try this again with the latest version of Metasploit?

I believe this might have been fixed by #18872

[Himself132]

Looks like it is still an issue with this version - up to date on apt get update && apt get upgrade && apt get dist-upgrade on Kali.

Framework: 6.4.0-dev Console : 6.4.0-dev

Here is the meaningful error output, the alternating unsupported token with 0 and integer alternating is also still present:

[*] SCRUBBED_IP_ADDRESS:1433 - SQL Query: select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t [*] SCRUBBED_IP_ADDRESS:1433 - Row Count: 3 (Status: 16 Command: 193) [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 95 ...SNIPPED... [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 10 [-] SCRUBBED_IP_ADDRESS:1433 - unsupported token: 0 [-] SCRUBBED_IP_ADDRESS:1433 - Auxiliary failed: Rex::RuntimeError Invalid number of columns! [-] SCRUBBED_IP_ADDRESS:1433 - Call stack: [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.56/lib/rex/text/wrapped_table.rb:196:in 'add_row' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.56/lib/rex/text/wrapped_table.rb:188:in '<<' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:82:in 'block in mssql_print_reply' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:81:in 'each' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:81:in 'mssql_print_reply' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/rex/proto/mssql/client.rb:587:in 'query' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/lib/msf/core/exploit/remote/mssql.rb:208:in 'mssql_query' [-] SCRUBBED_IP_ADDRESS:1433 - /usr/share/metasploit-framework/modules/auxiliary/admin/mssql/mssql_sql.rb:48:in 'run' [*] Auxiliary module execution completed

[adfoster-r7]

@Himself132 Thanks for taking a look! We've got a PR up to make error detection improvements here - would you mind applying this patch and letting us know what the error is this time:

#19016

[adfoster-r7]

The pull request is merged in now, but it will actually be multiple days until it's available by default on Kali's repository to download

If you want to apply that patch to your Kali instance locally, it should be pretty safe to do so 💯

[Himself132]

Can you please provide insutrctions on how to do that? i just unzipped it and tried ./msfconsole and got errors about missing gems.... I know I'm missing something obvious but dont live in this space - or a link with instructions

[adfoster-r7]

If you copy the code here:

https://github.com/rapid7/metasploit-framework/blob/9af16b243945773aa9b5113adb4fbe613f0ef301/lib/rex/proto/mssql/client_mixin.rb

Into this file on your Kali machine:

/usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb

Then close and reopen metasploit-framework - then the module code should have been updated with better error reporting that we can investigate 🤞

[Himself132]

this is what was put in the console, do you need more, or was the hope that the more verbose or helpful errors were recorded elsewhere?

msf6 auxiliary(admin/mssql/mssql_sql) > run
[*] Running module against SCRUBBED_IP_ADDRESS

[*] SCRUBBED_IP_ADDRESS:1433 - SQL Query: select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t
[-] SCRUBBED_IP_ADDRESS:1433 - Unsupported column type: 99. 
[*] Auxiliary module execution completed
msf6 auxiliary(admin/mssql/mssql_sql) > 

[Himself132]

Looks like it is dying on

NTEXTTYPE = %x63 ; NText

Wireshark packet shows column type field is populated with 63 for what i suspect is where it is failing and the number 99 in the error.

[Himself132]

If you modify your file and add when 99 to the line that interprets the response as string you get this error:

when 173
col[:id] = :hex # binary(2)
col[:max_size] = data.slice!(0, 2).unpack('v')[0]
when 99, 231, 175, 167, 239
col[:id] = :string
col[:max_size] = data.slice!(0, 2).unpack('v')[0]
col[:codepage] = data.slice!(0, 2).unpack('v')[0]
col[:cflags] = data.slice!(0, 2).unpack('v')[0]
col[:charset_id] = data.slice!(0, 1).unpack('C')[0]
else
col[:id] = :unknown

SCRUBBED_IP_ADDRESS:1433 - unsupported token: 52. Previous states: [:mssql_parse_tds_reply]

I'm guessing it has something to do with the Collate charset ID: which shows as 52 in wireshark

[Himself132]

this is the value for the collate charset ID in the database I'm querying which is master according to wireshark which I think equates to 52

SQL_Latin1_General_CP1_CI_AS

[adfoster-r7]

Thanks for the extra details! 💯

@zgoldman-r7 has put this PR up now, does this patch work for your environment?
#19054

[Himself132]

i did not use the PR, but i copied and pasted the code into my client_mixin.rb file and ran it and it worked like a charm thank you so much this is great!!! That query is abusing view server privileges to see the last SQL queries run and can be set to poll and collect sensitive information, so this is terrific.

[Himself132]

Sorry I jumped the gun, looks like if you set sql to declare @counter INT; set @Counter=1; WHILE ( @COUNTER <= 100) BEGIN select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t; set @counter = @counter + 1; END traffic is returned and the following error happens:

[*] Running module against SCRUBBED_IP_ADDRESS

[*] SCRUBBED_IP_ADDRESS:1433 - SQL Query: declare @counter INT; set @Counter=1; WHILE ( @COUNTER <= 100) BEGIN select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t; set @counter = @counter + 1; END
[-] SCRUBBED_IP_ADDRESS:1433 - Auxiliary failed: Rex::RuntimeError Invalid number of columns!
[-] SCRUBBED_IP_ADDRESS:1433 - Call stack:
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.57/lib/rex/text/wrapped_table.rb:196:in `add_row'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/vendor/bundle/ruby/3.1.0/gems/rex-text-0.2.57/lib/rex/text/wrapped_table.rb:188:in `<<'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:82:in `block in mssql_print_reply'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:81:in `each'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/lib/rex/proto/mssql/client_mixin.rb:81:in `mssql_print_reply'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/lib/rex/proto/mssql/client.rb:587:in `query'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/lib/msf/core/exploit/remote/mssql.rb:208:in `mssql_query'
[-] SCRUBBED_IP_ADDRESS:1433 -   /usr/share/metasploit-framework/modules/auxiliary/admin/mssql/mssql_sql.rb:57:in `run'
[*] Auxiliary module execution completed

[adfoster-r7]

That's interesting, thanks! Looks like that would have to be fixed.

As a temporary work around, would the repeat command work for you?

msf6 auxiliary(admin/mssql/mssql_sql) > repeat -n 100 run rhost=127.0.0.1 username=sa password=P4$$w0rd123 database='' sql="select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t"
[*] Running module against 127.0.0.1

[*] 127.0.0.1:1433 - SQL Query: select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t
[*] 127.0.0.1:1433 - Row Count: 1 (Status: 16 Command: 193)
Response
========

 text
 ----
 select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sq
 l_text(r.sql_handle) as t

[*] Auxiliary module execution completed
[*] Running module against 127.0.0.1
[*] 127.0.0.1:1433 - SQL Query: select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as t
[*] 127.0.0.1:1433 - Row Count: 1 (Status: 16 Command: 193)
Response
========

 text
 ----
 select t.[text] from sys.dm_exec_requests as r cross apply sys.dm_exec_sq
 l_text(r.sql_handle) as t

[*] Auxiliary module execution completed
msf6 auxiliary(admin/mssql/mssql_sql) >

There's also resource scripts if you need to sleep between each module run

[Himself132]

that workaround does work thank you!

[github-actions]

Hi!

This issue has been left open with no activity for a while now.

We get a lot of issues, so we currently close issues after 60 days of inactivity. It’s been at least 30 days since the last update here.
If we missed this issue or if you want to keep it open, please reply here. You can also add the label "not stale" to keep this issue open!

As a friendly reminder: the best way to see this issue, or any other, fixed is to open a Pull Request.

[adfoster-r7]

Fixed by #19054 - thanks!