How to decrypt zipped data in MSSQL table

All we need is an easy explanation of the problem, so here it is.

We’re trying to import (reverse engineer) some data from someone else’s MS SQL database, without any vendor support.

In the past the data has been stored in either plain text or RTF so easy to extract. But this database has some ‘encrypted’ looking content. Where data is in this format there is another column ‘zipped’ which = 1. I have been told they were compressing data to keep under the 10GB limit for SQL Express.

It looks a bit like this:

-Ëj„@D÷
þC}€7!2‹<Pɾí¾êÍôCn·ÿ~¦6UÅ©–F

Any thoughts on how to decode it? There are other rows in the same table that are plain text or RTF but many are this format.

Here is a sample export of the database:

https://1drv.ms/u/s!Au6oldAhXo2M5Xt_bE9Q5iA0WdfF?e=IsuYdQ

which includes a screenshot of what it should look like.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The data is not encrypted; it’s simply compressed using Deflate, which isn’t directly available in TSQL. TSQL COMPRESS uses Gzip, which is similar, but not strictly compatible. So in C# something like:

using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;

using var con = new SqlConnection("server=.;database=spmcopy;integrated security=true;multipleactiveresultsets=true");
con.Open();

var cmd = new SqlCommand("select counter, cast(cast([text] as varchar(max)) as varbinary(max)) ZippedText from notescopy where Zipped=1 ", con);
var cmdWrite = new SqlCommand("update notescopy set [text] = cast(cast(@t as varchar(max)) as text), Zipped = 0 where counter = @c",con);
var ptext = cmdWrite.Parameters.Add(new SqlParameter("@t", System.Data.SqlDbType.VarBinary, -1));
var pCounter = cmdWrite.Parameters.Add(new SqlParameter("@c", System.Data.SqlDbType.Int));

using var reader = cmd.ExecuteReader();

while (reader.Read())
{
    pCounter.Value = reader.GetInt32(0);
    var compressedStream = reader.GetStream(1);

    var dest = new MemoryStream();
    var decompressedStream = new DeflateStream(compressedStream, CompressionMode.Decompress);
    decompressedStream.CopyTo(dest);
    dest.Position = 0;
    ptext.Value = dest.ToArray();

    cmdWrite.ExecuteNonQuery();
}

Also, it looks like you can prepend a standard 10-byte header to the Deflate stream to make it GZip compatible, so

select cast(decompress(cast(0x1F8B0800000000000400 as varbinary(max)) + cast(cast([text] as varchar(max)) as varbinary(max))) as varchar(max)) d
from notescopy

As it looks like DECOMPRESS ignores the CRC32 and ISIZE trailer fields if they are absent, which is compliant but not mandatory decompressor behavior.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply