Access 2013 - Linked Table to SQL Server - Binary Data Displays as Asian/Chinese Characters

Question

I have a SQL Server 2012 database (standard, not express). When I create an ODBC connection to the database and link the tables, binary columns/fields are being displayed as what appears to be Asian characters. The data appears correctly in SQL Server Management Studio and retrieves properly in Visual Studio using System.Data.SqlClient.

The data looks like this is SQL Server Management Studio (look at columns: PasswordHash and PasswordSalt):

The same data looks like this in MS Access 2013:

Why is MS Access incorrectly changing the correlation of binary data so it looks like Asian characters? The encoding in the database is: SQL_Latin1_General_CP1_CI_AS

Answer 1

Why is this a problem? You would never display this data to a user, would you?

Answer 2

Does the example show field containing a password hash and salt... yes. However, no it is not good that binary data is being displayed badly by Access 2013. That was just a sample that just happened to be data that would not normally be displayed to an end-user (true), but it is handy to use Access for development purposes and there are scenarios where binary data DOES need to be displayed accurately.

So, can we ignore ignore the context of the field data itself...  Does anyone know why Access does not display the actual binary data stored in the database? Only Access seems to exhibit this behavior.

Answer 3

Okay, I thought somebody would understand why Access is incorrectly auto-converting the encoding of binary data into an entirely different language. Really, there is no way to force proper encoding (display) of binary data.

Answer 4

Okay, I thought somebody would understand why Access is incorrectly auto-converting the encoding of binary data into an entirely different language. Really, there is no way to force proper encoding (display) of binary data.

I really don't think Access can display binary data. What is the data type of the problem columns in SQL?

Answer 5

It's binary (32). I am expecting Access to represent the characters based on the Latin1 Encoding that was configured for the datbase. Instead Access somehow is auto-converting binary data to some form of Asian encoding as the images demonstrate. SQL Server displays it properly as well as the .NET client API. It is only Access that has this problem. So, the database and the binary data is not corrupt (they are accurate via the other sources).

There seems to be (IMO) a bug in the ODBC driver for Access.

NOTE: I come from manufacturing where binary data is often used. What I want should be reasonable: the characters displayed in SQL server should be exactly the same characters displayed within Access.