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:


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:


which includes a screenshot of what it should look like.


1 Answer 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");

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);
    dest.Position = 0;
    ptext.Value = dest.ToArray();


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.