Entering a backslash when using UK keyboard layout on US keyboard

This is more for my own reference than anything else but today I was needing to enter a backslash key while using a US keyboard but with UK keyboard settings.

After trying pretty much every key on the keyboard in all combinations, I realised there was no key combination that would do this directly. Curiously it’s a common problem and I found untold blog and forum entries that were not helpful. They basically said to change your input to US when using a US keyboard.

I figured there is always a way of entering a character by its code and of course this works. If you have a keypad, you can enter <Alt>92 and you’ll get a backslash. That’s all I needed in this case.

Step one in producing Shared Access Signatures for SQL Server Data Files in Azure – Generating a SHA2 256 HMAC Hash using Native T-SQL

One of the projects I have been working on as time permits is to construct a way to create a shared access signature using native T-SQL. Shared access signatures are needed when working with SQL Server data files in Azure.

Mostly, DBAs would use a programmatic utility such as Azure Storage Explorer to generate these signatures.

It is also straightforward to do so with SQL CLR based code but this is not enabled in all environments.

So I set about trying to create the required signatures using native T-SQL. I’m not concerned about the slow cryptographic performance as this won’t be a regular operation.

I found some excellent code from Ryan Malayter on github: https://gist.github.com/rmalayter/3130462 that does a SHA –1 or SHA – 512 hash. I’ve modified and enhanced it a bit so that it does SHA2-256 and performs the functions I need.

After the function code, there is a set of test code that shows how the function satisfies the HMAC_SHA256 test vectors. Hope that helps someone. I’ll post more as I complete the next step in building a SAS generator.

USE tempdb;

GO

 

ALTER FUNCTION dbo.HMAC_SHA256

(

    @HashKey varbinary(8000),

    @ValueToHash varbinary(8000)

)

RETURNS binary(32)

AS

BEGIN

 

    — Based on concept code from Ryan Malayter: https://gist.github.com/rmalayter/3130462

    DECLARE @k_ipad_partial bigint = CAST(0x3636363636363636 AS bigint);

    DECLARE @k_opad_partial bigint = CAST(0x5C5C5C5C5C5C5C5C AS bigint);

    

    IF LEN(@HashKey) > 64

    BEGIN

        SET @HashKey = CAST(HASHBYTES(‘SHA2_256’, @HashKey) AS binary (64));

    END ELSE BEGIN

        SET @HashKey = CAST(@HashKey AS binary (64));

    END;

     

    DECLARE @k_ipad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_ipad_partial) AS binary(8));

   

   

    DECLARE @k_opad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_opad_partial) AS binary(8));

    

    RETURN HASHBYTES(‘SHA2_256’, @k_opad + HASHBYTES(‘SHA2_256’, @k_ipad + @ValueToHash));

END;

GO

 

— Test with RFC4231 test vectors

 

DECLARE @KeyToUse varbinary(4000);

DECLARE @ValueToHash varbinary(4000);

DECLARE @HashedValue varbinary(32);

DECLARE @ExpectedResult varbinary(32);

 

— Test 1

 

SET @KeyToUse = 0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b;

SET @ValueToHash = CAST(‘Hi There’ AS varbinary(1000));

SET @ExpectedResult = 0xb0344c61d8db38535ca8afceaf0bf12b881dc200c9833da726e9376c2e32cff7;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 1 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 1 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 2

 

SET @KeyToUse = CAST(‘Jefe’ AS varbinary(4));

SET @ValueToHash = CAST(‘what do ya want for nothing?’ AS varbinary(1000));

SET @ExpectedResult = 0x5bdcc146bf60754e6a042426089575c75a003f089d2739839dec58b964ec3843;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 2 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 2 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 3

 

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = 0xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd;

SET @ExpectedResult = 0x773ea91e36800e46854db8ebd09181a72959098b3ef8c122d9635514ced565fe;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 3 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 3 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 4

 

SET @KeyToUse = 0x0102030405060708090a0b0c0d0e0f10111213141516171819;

SET @ValueToHash = 0xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd;

SET @ExpectedResult = 0x82558a389a443c0ea4cc819899f2083a85f0faa3e578f8077a2e3ff46729665b;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 4 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 4 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 5

 

SET @KeyToUse = 0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c;

SET @ValueToHash = CAST(‘Test With Truncation’ AS varbinary(4000));

SET @ExpectedResult = 0xa3b6167473100ee06e0c796c2955552b;

SET @HashedValue = CONVERT(varbinary(16),dbo.HMAC_SHA256(@KeyToUse, @ValueToHash));

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 5 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 5 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 6

 

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = CAST(‘Test Using Larger Than Block-Size Key – Hash Key First’ AS varbinary(4000));

SET @ExpectedResult = 0x60e431591ee0b67f0d8a26aacbf5b77f8e0bc6213728c5140546040f0ee37f54;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 6 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 6 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

 

— Test 7

 

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = CAST(‘This is a test using a larger than block-size key and a larger than block-size data. The key needs to be hashed before being used by the HMAC algorithm.’ AS varbinary(4000));

SET @ExpectedResult = 0x9b09ffa71b942fcb27635fbcd5b0e944bfdc63644f0713938a7f51535c3a35e2;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘>>Test 7 passed’;

END ELSE BEGIN

    PRINT ‘>>Test 7 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ‘ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),’NULL’);

    PRINT ‘ ‘;

END;

Solving Errors with Existing BLOB Leases When Using BACKUP TO URL

BACKUP TO URL was introduced as an add-on in Cumulative Update 2 for SQL Server 2012 Service Pack 1 and as a built-in feature for SQL Server 2014. I previously described this in a blog post.

We have been using this in a variety of ways from on-premises systems:

For example, it is an easy way to distribute a backup of a database to a large number of systems. Imagine you have a chain of retail stores that needs product and other reference information updated regularly. You can keep this data in a separate database at the head office, back it up to an Azure Storage account, and have each store download it separately.  This has major bandwidth and reliability improvements over other solutions such as having each store maintain a VPN connection to the head office.

As another example, we have clients who simply aren’t able to gain enough space on their SANs to keep enough local copies of their backups.

The more common scenario though is to use it for backups from Azure Virtual Machines that are running SQL Server. Rather than performing a backup to a virtual machine virtual disk, we achieve better performance by bypassing the file-system on the virtual disk and backing up directly to a URL. Both end up in Azure storage but backing up directly to storage gives us both better performance, and can help to avoid the drive limit and size restrictions for virtual machines.

Regardless of why you are using BACKUP TO URL, one of the problems that you are likely to run into at some point is the dreaded:

                Msg 3271, Level 16, State 1, Line 60
                A nonrecoverable I/O error occurred on file
                “https://somestorageaccount.blob.core.windows.net/backups/somedatabase.bak: “ Backup
                to URL received an exception from the remote endpoint. Exception Message: The remote
                server returned an error: (412) There is currently a lease on the blob and no lease ID was
                specified in the request…
                Msg 3013, Level 16, State 1, Line 60
                BACKUP DATABASE is terminating abnormally.

Applications using Azure storage can take leases on files that are held in the storage containers. This avoids issues with other applications concurrently changing (or even deleting) files that the application needs. The BACKUP TO URL feature in SQL Server takes an infinite lease on the backup file that it creates. That lease is removed when the backup completes. (The process of dealing with leases in Azure Storage is described here).

However, if you interrupt a backup (or network issues interrupt it for you), and this is a prolonged interruption, the lease can remain and when you try to overwrite that backup blob (or even delete it), you’ll see the error above.

Now, the design feature that makes this a bit easier to deal with is that the BACKUP TO URL command always uses a well-known lease ID: BAC2BAC2BAC2BAC2BAC2BAC2BAC2BAC2

What is needed to delete it then, is a tool that can break leases, or to run a PowerShell  script as described in this article. You should also take this as yet another hint to learn about PowerShell if you haven’t done so already.

Details about other aspects of BACKUP TO URL troubleshooting are given in this article.

Fix for SQL Server 2012 SP2 Data Corruption Issue Now Available

Microsoft recently announced service pack (SP) 2 for SQL Server 2012. However, we were aware of a potential data corruption issue related to online index rebuilds, so we had been recommending that customers avoid installing it until the fix was available.

That hotfix is now available: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2969896&kbln=en-us 

Note that there is also a fix for SQL Server 2012 SP1 and for SQL Server 2014 RTM.