Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We need to create SHA1 hashes that match the Microsoft (and probably the standard?) implementation.

E.g I do this in MS SQL Server 2008:

     declare @myPwd nvarchar(128);
     declare @mySalt nvarchar(128);
     declare @hashThis nvarchar(128);
     declare @hashedPwd binary(20);

     set @myPwd = 'secret';
     set @mySalt = 'random123';

     set @hashThis = @myPwd+@mySalt;
     set @hashedPwd = HashBytes('SHA1', @hashThis);

     select Convert(nvarchar(128), @hashedPwd, 2); -- 7D0DF1998892DB320D334EB1F0AB090E6422DE34

The same in C# produces the same result:

class Program
    static void Main(string[] args)
        // our password + salt
        string hashThis = "secretrandom123";

        // make it a byte array and hash
        byte[] data = System.Text.Encoding.Unicode.GetBytes(hashThis);
        SHA1 sha = new SHA1CryptoServiceProvider();
        byte[] result = sha.ComputeHash(data);

        // convert hashed byte array into a hex string so we can display it
        string hashed = BitConverter.ToString(result).Replace("-", String.Empty);

        Console.WriteLine(hashed);  // 7D0DF1998892DB320D334EB1F0AB090E6422DE34

Here comes SQL Anywhere 16:

     declare @myPwd nvarchar(128);
     declare @mySalt nvarchar(128);
     declare @hashThis nvarchar(128);
     declare @hashedPwd nvarchar(128);

     set @myPwd = 'secrect';
     set @mySalt = 'random123';
     set @hashThis = 'secretrandom123';

     set @hashedPwd = Hash(@myPwd+@mySalt, 'SHA1');

     select @hashedPwd   // ce0c0bd5d60846c2bfb18478fceda1296f783edb

The hash code produced by SQL Anywhere is different. I'm sure I'm missing something here. Always thought that SQL Anywhere Hash() properly converts the input string to a byte array in the background. Anyway, whatever I try, I can't make Hash() to return the same hashcode as SQL Server HashBytes or the .NET SHA1CryptoServiceProvider().

Does anyone see my mistake?



asked 26 Nov '13, 11:23

Michael%20Fischer's gravatar image

Michael Fischer
accept rate: 25%

edited 02 Dec '13, 03:31

Volker%20Barth's gravatar image

Volker Barth

set @myPwd = 'secrect'; -- not 'secret'

Is that a spelling mistake that might explain the difference?

(26 Nov '13, 12:10) Volker Barth

FWIW, in case you are doing new development and do need the hash for security purposes, the general recommendation would be to use SHA-2 instead of SHA-1 - cf. this summary.

For SQL Anywhere, you can use that with HASH(..., 'SHA256') or the according FIPS-based version.

(27 Nov '13, 06:10) Volker Barth

The reason for this is the subtle differences in what you are hashing.

The .NET System.Text.Encoding.Unicode.GetBytes call returns the characters of a string as a UTF-16 byte stream. When using the UTF-16 encoding each character is represented by 16-bits or two bytes. Because of this, the byte array 'data' in the C# program actually contains 30 bytes. It's also worth noting that the order of these bytes can very depending on the implementation (in .NET it's always little-endian) or the presence of a BOM character.

UTF-16 is a Unicode encoding that is not backwards compatible with ASCII (which is traditionally 7 bits, but usually represented by one byte per character). UTF-8 on the other hand, is a variable width encoding that is completely backwards compatible with ASCII. That is to say, that the UTF-8 encoding of any traditional ASCII character is identical to it's original ASCII encoding. UTF-8, like UTF-16 is an encoding that can represent any Unicode character.

In SQL Anywhere, the NVARCHAR data type contains characters encoded using UTF-8 (so normal ASCII characters are one byte in size).

In the C# program, you can change the Unicode.GetBytes() call to UTF8.GetBytes() or ASCII.GetBytes() to get the results you are expecting. The fact that Unicode = UTF-16 in this case is arbitrary, UTF-8 is also a Unicode encoding.

Your SQL Server code uses the NVARCHAR data type to store the characters. Unlike SQL Anywhere, SQL Server stores NVARCHAR data as UTF-16 and does not support UTF-8. If you store the string using the VARCHAR data type, it will be encoded in a fixed length representation determined by the database encoding. The default SQL Server character encoding is ISO 8859-1 (sometimes called Latin-1). This is an ASCII-compltible 8-bit encoding that adds an extra 128 characters to take advantage of the extra bit vs traditional 7-bit ASCII.

If the characters you are hashing all exist in the ASCII character set, you should change the data type in SQL Sever to VARCHAR to resolve this issue.

permanent link

answered 01 Dec '13, 12:00

Mikel%20Rychliski's gravatar image

Mikel Rychliski
accept rate: 32%

edited 01 Dec '13, 12:54


"you should change the data type to VARCHAR" ...wise words with wide worthiness :)

(01 Dec '13, 15:29) Breck Carter

If I fix the spelling error that Volker mentioned and then run your code, I get the value d3c1becf5f8a9c37de3b5826a3392fd61cc2e2ae. If I try any of several SHA-1 online calculators (here, here, here, or here), I get exactly the same result.

I can't speak to why your other examples are not working but as far as I can tell, the SQL Anywhere implementation is working fine.

permanent link

answered 26 Nov '13, 13:09

Graeme%20Perrow's gravatar image

Graeme Perrow
accept rate: 54%

So Microsoft is calculating different?

(27 Nov '13, 02:48) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 26 Nov '13, 11:23

question was seen: 8,738 times

last updated: 02 Dec '13, 03:31