3

I need to generate a SHA256 base 64 hash from a table in SQL server but I can't find that algorithm in the list HASHBYTES arguments.

Is there a way to generate it directly in SQL Server?

Duplicate disclamer:

My question is not duplicate of SHA256 in T-sql stored procedure as I am looking for the SHA256 base 64 version of the algorithm which is not listed in the page.

Numeric Example

I have this query result in SQL Server

Start date,End date,POD,Amount,Currency

2016-01-01,2016-12-31,1234567890,12000,EUR

this give me the following string (using concatenate function)

2016-01-012016-12-31123456789012000EUR

whit this convertion tool I get the following hash

GMRzFNmm90KLVtO1kwTf7EcSeImq+96QTHgnWFFmZ0U

that I need to send to a customer.

Community
  • 1
  • 1
Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • Possible duplicate of [SHA256 in T-sql stored procedure](http://stackoverflow.com/questions/2954784/sha256-in-t-sql-stored-procedure) – Tanner Jan 31 '17 at 09:53
  • @Tanner, as written in the question I am looking for the SHA256 base 64 algorithm, not the SHA256 HEX. For this reason it is not duplicate. – Nicolaesse Jan 31 '17 at 09:57
  • Getting binary output from ordinary SHA256 and encode it into base64 doesn't fit? What is the difference, then? Can you provide a link to a more detailed description? – Roger Wolf Jan 31 '17 at 12:12
  • @RogerWolf I added a numeric example. At the moment I am writing users have an Excel file with VBA to generate the hash but I'd like to know if it is possibile to make it in SQL Server. – Nicolaesse Jan 31 '17 at 13:04
  • This isn't a separate algorithm, it's just getting the SHA256 bytes and then [encoding them in Base64](http://stackoverflow.com/a/32231832/4137916). – Jeroen Mostert Jan 31 '17 at 13:08
  • @JeroenMostert I've tried to convert the concatenated string to SHA256 and then to Base64 but I get a different hash. "18c47314d9a6f7428b56d3b59304dfec47127889aafbde904c78275851666745" from concat to SHA256 and "MThjNDczMTRkOWE2Zjc0MjhiNTZkM2I1OTMwNGRmZWM0NzEyNzg4OWFhZmJkZTkwNGM3ODI3NTg1 MTY2Njc0NQ=" from SHA256 to Base64. – Nicolaesse Jan 31 '17 at 21:23

1 Answers1

13

First, the generator link you provided outputs the base64 representation in not exactly correct format. Namely, it omits the padding sequence. Though theoretically optional, padding is mandatory in MS SQL Server (tested on 2012 and 2016 versions).

With this in mind, the following code gives you what you need:

declare @s varchar(max), @hb varbinary(128), @h64 varchar(128);

select @s = '2016-01-012016-12-31123456789012000EUR';

set @hb = hashbytes('sha2_256', @s);
set @h64 = cast(N'' as xml).value('xs:base64Binary(sql:variable("@hb"))', 'varchar(128)');

select @hb as [BinaryHash], @h64 as [64Hash];

Apart from the aforementioned padding, there is another caveat for you to look for. Make sure that the input string is always of the same type, that is, either always varchar or always nvarchar. If some of your hashes will be calculated from ASCII strings and some from UTF-16, results will be completely different. Depending on which languages are used in your system, it might make sense to always convert the plain text to nvarchar before hashing.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • I get null for both columns. I have MS SQL Server 2012 and when I execute this parts of code I get null value, because sql hashbytes function supports following hash algorythms: 'MD2', 'MD4', 'MD5', 'SHA' and 'SHA1'. – oknevermind Feb 13 '18 at 12:57
  • @oknevermind, are you testing it on the same input data? If not, make sure your input does not exceed 8000 bytes. – Roger Wolf Feb 13 '18 at 13:08
  • For the input I am using nvarchar type data. For example, I use input string 'ppp_001#Wd!!' – oknevermind Feb 14 '18 at 12:21
  • @oknevermind, I don't think you use 2012 version of the server. Docs state clearly that SHA2 is supported for 2012: https://msdn.microsoft.com/en-us/library/ms174415(v=sql.110).aspx Have you checked the `print @@version`, what does it say? – Roger Wolf Feb 14 '18 at 23:26
  • @@Roger, sorry, my mistake. You are right. Version is: Microsoft SQL Server 2008 R2 (SP1). But, version of SQL Management Studio on my work computer is 2012. I am read info of this link that you sent. Thanks for explanation. – oknevermind Feb 15 '18 at 07:57