In part 1 I searched for a specific property of an agent and found it buried inside some xml in a binary field. After jumping through some hoops I ended up with a technique to query this field directly. The obvious question was, what else is hidden in similar ways?

Query:

SELECT TABLE_NAME,
       DATA_TYPE,
       COLUMN_NAME
 
FROM INFORMATION_SCHEMA.COLUMNS
 
ORDER BY TABLE_NAME, DATA_TYPE

Then CTRL-C -> click -> whirl -> PivotMagic.Apply() to make it look pretty:

The bit, char, datetime, int, and varchar are to be expected. Every table has one or more uniqueidentifiers and in most tables it is (part of) the primary key. Maybe more on that later, let’s stick with the binary data types for now. We find a varbinary column in the tblFiles table. Going by the name I expect this is where the resources are saved so I don’t expect to find any hidden properties. But what is that varbinary column called binData doing in the tblSettings table?

There is only one row in the table with non NULL data in the binData column, at least in my test setup. Can we cast it like we did with the image field before?

SELECT strValue
      ,CAST(CAST(tblSettings.binData AS VARBINARY(MAX)) AS NVARCHAR(MAX)) AS binData
      ,CAST(CAST(tblSettings.imgData1 AS VARBINARY(MAX)) AS NVARCHAR(MAX)) AS imgData1
      ,CAST(CAST(tblSettings.imgData2 AS VARBINARY(MAX)) AS NVARCHAR(MAX)) AS imgData2
  FROM tblSettings

I have truncated the output because the XML holds all information from the license and activation files, not very interesting in and of itself. The binData seems to be some unfinished Japanese sentence that even google translate can’t make heads or tails out of. But wait, that is highly unlikely so, look at the original data again:

0x4544394239374135363837413746413841353932413336363639393339 *snip*

Remember the alternating NULL bytes from Part 1? they are not in here! So casting to unicode might not make sense, let’s try a VARCHAR (8-bit codepage):

ED9B97A5687A7FA8A592A36669939F98 *snip*

Now that looks better, I guess, but what is it? It could be something completely different, maybe it is not even character data but some proprietary data structure. It definitely piqued my interest though. The column definition is VARBINARY(100) holding this 76 character string containing only [0-9A-F] or so it seems. Probably just the license key itsself (this is why I truncated the data), in any case not the topic of this particular post. Let’s continue looking at the image fields.

Can we somehow just get all information stored in all columns with data type image from all tables? Then try to cast is as unicode text so we can browse for the interesting bits? Yes, but it took me more than 1 try to get it right, mostly because my T-SQL skills are virtually non-existent. After quite a bit of fiddling in the query editor I came up with this:

DECLARE @ColumnID NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @Cursor CURSOR
DECLARE @cmd NVARCHAR(MAX)
 
-- Placeholder for querystring
SET @cmd = ''
 
-- Get all columns of datatype image
SET @Cursor = CURSOR FOR
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME)
      ,TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'image'
 
-- Open a cursor and fetch the first result
OPEN @Cursor
FETCH NEXT
FROM @Cursor INTO @ColumnID, @TableName
 
-- Iterating over all the ColumnIDs we found
-- selecting all rows and then
-- build one big querystring.
-- Throw a bunch of UNION statements in there
-- to shove all the results in a single table.
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd =  @cmd 
      + 'SELECT '''
      + @ColumnID 
      + ''' AS Source, CAST(CAST('
      + @ColumnID 
      + ' AS VARBINARY(MAX)) AS NVARCHAR(MAX)) AS Value FROM '
      + @TableName 
      + ' WHERE '
      + @ColumnID  
      + ' IS NOT NULL'
     
    FETCH NEXT
    FROM @Cursor INTO @ColumnID, @TableName
 
    IF @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = @cmd + ' UNION ALL '
    END
END
 
-- and finally execute this Frankenquery!
EXEC (@cmd)

I have no clue on how this performs on any decent size environment, it is definitely not an exercise in optimization techniques but that’s all irrelevant in a small test environment and it serves our purpose pretty well. I have selected some results to get an idea what kind of information is hiding where, and how it is stored.

Whoa Batman! To keep it readable the table only has truncated data but what is going on here? Variety is the spice of life and all but I count 4 or 5 different forms of XML.

• No declaration at all (technically it is optional in 1.0)
• A declaration but without explicit encoding
• Encoding specified as UTF-8
• Encoding specified as UTF-16
• Maybe not different per se but formatted and unformatted

So what does this tell us really? I find it difficult to draw any conclusions but at the very least there is a lack of standardization in how these bits of xml are stored. There does not seem to be a common interface through which this is pushed into the database. Now it is true that both dispatchers and consoles have access to the DB, so the different codebases could account for some of this but still. We saw in part 1 that the information stored inside the XML would probably be better of directly in a column. Might this have to do with backwards compatibility? A change in properties does not require a schema change in your DB if you store binary blobs of XML. But the default behavior of AM is such that all components are upgraded more or less simultaneously.

Apart from the blobs of XML we also see some binary data which does not seem to hold character data. This will require some special investigation, and I don’t have much experience with it; so I will put that off to some undetermined future, if I ever do it at all. Would be fun though, maybe just quickly check for some magic numbers in an upcoming post. ZLIB is a good clue, could be many things as it seems to be widely used.

Pending further investigation I will do a follow-up more directed towards queries to do some actual, useful reporting.