Strange data in MS SQL database

Forums › IT Pro and developers › Strange data in MS SQL database

1 | 2

Delphinus

611 posts

Ultimate Geek
+1 received by user: 274

#2908221 29-Apr-2022 11:34

I managed to get a sample of the data that is not confidential and can be shared publicly.

MS SQL backup can be found here: https://1drv.ms/u/s!Au6oldAhXo2M5XbZ7YcANsiPySh0?e=QJkOHl

Anyone able to work out how to uncompress that? Is it as simple as using DECOMPRESS somehow? I'm not familiar enough with SQL.

End result should look like this:

frankv

5705 posts

Uber Geek
+1 received by user: 3666

Lifetime subscriber

#2908236 29-Apr-2022 11:56

It's not a zip... they all start with PK. But maybe some other type of compression (7zip?) Do you have a sourcefile name?

I'm wondering if it's something to do with UTF-8 vs UTF-16 vs UTF-32.

Behodar

11099 posts

Uber Geek
+1 received by user: 6077

Trusted

Lifetime subscriber

#2908237 29-Apr-2022 11:57

Behodar:

Export one of the fields to a binary file then run the Unix file command on it and see whether it returns anything.

Well, that didn't work. It detected it as a compiled object file for the old MIPS version of Windows NT!

Delphinus

611 posts

Ultimate Geek
+1 received by user: 274

#2908247 29-Apr-2022 12:20

frankv:

It's not a zip... they all start with PK. But maybe some other type of compression (7zip?) Do you have a sourcefile name?

I'm wondering if it's something to do with UTF-8 vs UTF-16 vs UTF-32.

No sourcefile name. The non-zipped data just shows as plain or RTF text in the database. Eg:

Reference Number: 019852

Ordered From: Pacific Radiology, 21 Von Tempsky St, Hamilton East

Tests Ordered:
MRI - L spine

Comment: low back pain and sciatica after lifting injury

{\rtf1\ansi\ansicpg1252\uc1\deff0{\fonttbl
{\f0\fnil\fcharset0\fprq2 Arial;}
{\f1\fnil\fcharset1\fprq2 Arial;}
{\f2\fnil\fcharset1\fprq2 Courier New;}
{\f3\fswiss\fcharset0\fprq2 Arial;}
{\f4\froman\fcharset2\fprq2 Symbol;}}
{\colortbl;\red0\green0\blue255;\red255\green255\blue0;\red0\green0\blue0;\red34\green34\blue34;\red255\green255\blue255;\red36\green111\blue200;}
{\stylesheet{\s0\itap0\nowidctlpar\f0\fs24 [Normal];}{\*\cs10\additive Default Paragraph Font;}{\s17\itap0\nowidctlpar\sl0\slmult1\f1\fs24\sbasedon0 BODY;}{\s18\itap0\nowidctlpar\sb120\sa240\sl0\slmult1\fs36\b\sbasedon17 H2;}{\s19\itap0\nowidctlpar\sb93\sa186\sl0\slmult1\fs28\b\sbasedon17 H3;}{\s20\itap0\nowidctlpar\sb160\sa320\sl0\slmult1\fs48\b\sbasedon17 H1;}{\s21\itap0\nowidctlpar\sb73\sa146\sl0\slmult1\fs22\b\sbasedon17 H4;}{\s22\itap0\nowidctlpar\sb60\sa120\sl0\slmult1\fs18\b\sbasedon17 H5;}{\s23\itap0\nowidctlpar\sb46\sa93\sl0\slmult1\fs14\b\sbasedon17 H6;}{\s24\itap0\nowidctlpar\li720\ri720\sl0\slmult1\fs24\sbasedon17 BLOCKQUOTE;}{\*\cs25\ul\cf1\additive\sbasedon10 A;}{\*\cs26\b\additive\sbasedon10 STRONG;}{\s27\itap0\nowidctlpar\sl0\slmult1\f2\fs24\sbasedon0 PRE;}{\*\cs28\b\additive\sbasedon10 B;}{\s29\itap0\nowidctlpar\qc\sl0\slmult1\f1\fs24\sbasedon0 CAPTION;}{\*\cs30\f2\additive\sbasedon10 CODE;}{\*\cs31\strike\additive\sbasedon10 DEL;}{\*\cs32\i\additive\sbasedon10 EM;}{\*\cs33\i\additive\sbasedon10 I;}{\*\cs34\ul\additive\sbasedon10 INS;}{\*\cs35\f2\additive\sbasedon10 KBD;}{\*\cs36\cb2\chshdng0\chcfpat0\chcbpat2\additive\sbasedon10 MARK;}{\*\cs37\strike\additive\sbasedon10 S;}{\*\cs38\f2\additive\sbasedon10 SAMP;}{\*\cs39\strike\additive\sbasedon10 STRIKE;}{\s40\itap0\nowidctlpar\qc\sl0\slmult1\f1\fs24\b\sbasedon0 TH;}{\*\cs41\f2\additive\sbasedon10 TT;}{\*\cs42\ul\additive\sbasedon10 U;}}
{\*\generator TX_RTF32 17.0.540.501;}
\deftab1134\paperw11906\paperh16837\margl1440\margt1440\margr1440\margb1134\widowctrl\formshade\sectd
\headery720\footery720\pgwsxn11906\pghsxn16837\marglsxn1440\margtsxn1440\margrsxn1440\margbsxn1134\pgbrdropt32\pard\itap0\nowidctlpar\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\plain\f3\fs24\cf3\par\par\par 28 April 2022\par\par\par\par\par\pard\itap0\nowidctlpar\qj\sl220\slmult0\tx1133\tx2880\tx7938\tx7965\plain\f3\fs22\b\par\plain\f3\fs24\b RE:\tab Mr Mickey MOUSE DOB: 01.01.1980\par\tab 100 Acre Wood, HAMILTON \par\pard\itap0\nowidctlpar\qj\tx567\tx1134\tx1701\tx2268\tx2835\tx3402\tx3969\tab\tab Ph:M 027 156 9870 \par\tab\tab ACC No: KK12345 \par\plain\f3\fs24\tab\tab\plain\f3\fs24\b NHI No: \plain\f3\fs24\par\par\pard\s17\itap0\nowidctlpar\sb142\sa142\plain\f3\fs26\cf4\cb5\chshdng0\chcfpat0\chcbpat5 Make the purpose of your letter clear through simple and targeted language, keeping the opening paragraph brief. You can start with, \loch\f3\hich\f3\'93I am writing in reference to\'85\'94 and from there, communicate only what you need to say.\par The subsequent paragraphs should include information that gives your reader a full understanding of your objective(s) but avoid meandering sentences and needlessly long words. Again, keep it concise to sustain their attention.\par
{\shp{\*\shpinst\shpleft0\shptop41670\shpright4500\shpbotto

Delphinus

611 posts

Ultimate Geek
+1 received by user: 274

#2908309 29-Apr-2022 12:37

Behodar:

Well, that didn't work. It detected it as a compiled object file for the old MIPS version of Windows NT!

This doesn't actually surprise me. The software was originally built I believe in the 90's and has been tweaked since then with various 'updates'.

Website hasn't been touched since 2004!

frankv

5705 posts

Uber Geek
+1 received by user: 3666

Lifetime subscriber

#2908347 29-Apr-2022 13:48

Here's a screenshot of what it looks like in Notepad++

So it's some kind of binary file format.

Googling finds:

Microsoft Tape Format (MTF) is the tape format used by several backup tools for the Microsoft Windows platform. Notable examples include Microsoft's NTBackup program, Backup Exec and the backup utilities included in Microsoft SQL Server.

Several open source utilities have been written to read MTF on non-Windows platforms.[1][2][3]

So probably running RESTORE or something on it would extract the original contents.

Here's the first several lines in hex:

54415045000003008C000E010000000000000000000000000000000000000000 0000000000000000000000000000000002008705F6926A680500000001000000 010003000000000000000000000000002C005E00000400121F993AA8FA014D00 6900630072006F0073006F00660074002000530051004C002000530065007200 7600650072000000000000005241494400000000200000000000000000000000 3B052105D6050E874042B71BF7FCA3FACC0DAD0000F001000100010000000000 0100000053504144000000002603000000000000000000003417000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 53464D420000008000020E010000000000000000000000000000000000000000 0000000000000000000000000000000000001087370000000000000048000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 5353455400000000B8000E010000000000000000000000000000000000000000 000000000000000000000000150064000200D306040000000000000000120100 38007A0000000000000000000400B20000060000000000001F993AA8FA0E0030 00020000B99B27AFD5B09E4283AB0A33DC48DD47000000000000530050004D00 43004F00500059002D00460075006C006C002000440061007400610062006100 7300650020004200610063006B00750070007300610000005350414400000000 3203000000000000000000002017000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 564F4C420000000070000E010000000000000000010000000000000000000000 000000000100000000000000000000000200660C020000000400490008004D00 180055001F993AA8FA45003A0044006100740061005700410053002D00480041 004D002D00410050005000310000000053504144000000007A03000000000000 0000000068170000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000 4D5343490000000038000E010000000000000000020000000000000000000000 0000000002000000000000000000000002003A1B000000004D51434900000000 840B000000000000000000008A135343494EBE01000000020000010000000100 000000000000EB07000E09000000C8000000AF010000F500C800000014020000 0100C8000000AF010000F500000000000000000000001F993AA8441F993AA8FA 0000000000440001008C0700650300CA3100000000000E00900120009E010000 0000090400000100030038CCF305BF691D49840B8577AA6278E200000000C800 000014020000010000000A60AE7917C89D48A059A7554D15DD44000000000000 0000000000000000000000000000000000000000000000000000000000000000

Lenovo

Shop now for Lenovo laptops and other devices (affiliate link).

Lias

5655 posts

Uber Geek
+1 received by user: 3978

ID Verified

Trusted

Lifetime subscriber

#2908364 29-Apr-2022 14:27

frankv:

..stuff..

Your looking at the SQL backup. You need to import that into SQL, then you can see the table he's talking about. That's not an export of the contents of the column.

I'm a geek, a gamer, a dad, a Quic user, and an IT Professional. I have a full rack home lab, size 15 feet, an epic beard and Asperger's. I'm a bit of a Cypherpunk, who believes information wants to be free and the Net interprets censorship as damage and routes around it. If you use my Quic signup you can also use the code R570394EKGIZ8 for free setup. Opinions are my own and not the views of my employer.

Lias

5655 posts

Uber Geek
+1 received by user: 3978

ID Verified

Trusted

Lifetime subscriber

#2908381 29-Apr-2022 15:36

I had a bit of a play around, trying to use cast and decompress, got nowhere (hey I'm an infrastructure guy not an actual DBA lol). I did have a thought thou, you should be able to use the XEvent Profiler to see the actual query the software is using, which should tell you if it's doing anything in the query to convert that data within SQL of if it's just returning it back to the Application as is. I think anyways, I can use it to see my test queries, as before giant disclaimer I'm not a DBA :-)

Delphinus

611 posts

Ultimate Geek
+1 received by user: 274

#2908402 29-Apr-2022 16:56

Lias:

I had a bit of a play around, trying to use cast and decompress, got nowhere (hey I'm an infrastructure guy not an actual DBA lol). I did have a thought thou, you should be able to use the XEvent Profiler to see the actual query the software is using, which should tell you if it's doing anything in the query to convert that data within SQL of if it's just returning it back to the Application as is. I think anyways, I can use it to see my test queries, as before giant disclaimer I'm not a DBA :-)

Thanks for trying! I really appreciate it.

XEvent Profiler is a great idea. I'll try that when I can get access to the server again.

pih

667 posts

Ultimate Geek
+1 received by user: 359

Lifetime subscriber

#2908426 29-Apr-2022 19:32

I've also had a go but couldn't make sense of it. Tried various encodings, decompression tools, even looking for patterns at the binary level. No cigar, sorry.

gzt

18682 posts

Uber Geek
+1 received by user: 7822

Lifetime subscriber

#2908468 30-Apr-2022 08:12

@frankv pkzip was a popular file compression tool. It is still actively developed. It can be downloaded for evaluation here:

https://www.pkware.com/zip/products/pkzip

The company also provides masking utilities for compliance etc.

1 | 2