Decoding Binary Data in WIN-PAK 4.7 MSSQL Database

Question

WIN-PAK made some"questionable" schema changes in their back-end sql server db and it has broken my queries. What was an nvarchar(35) has been changed to a varbinary(MAX). In some add-on's I've written I need to be able to convert it back to a human readable string. Trouble is, I don't know how they are doing the encoding in the first place.

How do I convert 0x004E2B296D0F5707CA3D0EDA6FBC05CB010000007FFBED343A41DB3016798FA2B6FAFE8A4460E1ACB58CBA05BBE34AA0A133C6B8BE0F2F95C153CB658EABF4EFA09931EC

back to the string "PILLOW" ?

I've contacted Honeywell WIN-PAK support but since I'm not a licensed dealer they won't talk to me. I've tried the obvious convert and cast statements with no luck. I've done the usual forum lurking. I'm hoping someone that has knowledge of these changes can chime in, or someone who works alot with converted data can explain how 6 characters can become that messy binary.

Answer 1

Looks like they are using Microsoft SQL Servers encryption

Here is a good overview:

https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

I was able to decode the CardHolder table using the following (assuming its a generic encryption key):

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'WPMasterKey!@#'; 

--CREATE CERTIFICATE WPEnctCertificate WITH  SUBJECT =   'WP Personnel Data Ids'

OPEN SYMMETRIC KEY WPEnctSymmetricKey DECRYPTION BY CERTIFICATE WPEnctCertificate
SELECT TOP (1000) [RecordID]
      ,[AccountID]
      ,[SubAccountID]
      ,[TimeStamp]
      ,[UserID]
      ,[NodeID]
      ,[Deleted]
      ,[UserPriority]
      ,CONVERT(nvarchar, DecryptByKey([FirstName])) as FirstName
      ,CONVERT(nvarchar, DecryptByKey([LastName])) as LastName     
      ,[FirstName]
      ,[LastName]
      ,[Note1]      
  FROM [WIN-PAK PRO].[dbo].[CardHolder] 

I think the WPEnctCertificate should already be in your system, but if not, uncomment that line and run it.