How to fix SQLIOSim error “Failed to create random hash”

Hi there! If you have been working with SQL Server for any length of time, I am sure you know how crucial I/O is to the overall reliability of your SQL Server instance. Many years ago (over a decade now, actually!) we released SQLIOSim, which has been included in the out-of-box installations for SQL Server for many releases now. You can find this utility in the same folder that SQLServr.exe is (typically something like C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Binn).

This post is not about how to run SQLIOSim and what is the right usage for the tool (for example, it is not appropriate to use this for performance benchmarking, rather it is a pass / fail type verification of how reliable your storage is under load.) This post is about a very specific (and very rare) error which I recently hit on a server. In that case, SQLIOSim was running and the server had to be hard-rebooted due to I/O stack problems (the RAID controller in that server was faulty.) After reboot and fixing the issue with the RAID controller, I tried to restart SQLIOSim, but it would fail to run and complain with an error as below:

Error: 0x8009000F Error Text: Object already exists. Description: Failed to create random hash

This was not an error which I’d seen earlier so I had to debug it a bit. After some searching I found some very interesting facts:

  • SQLIOSim uses Microsoft CryptoAPI to generate random values for the synthetic test data that it uses
  • CryptoAPI in turn uses something called a ‘key container’ which the cryptographic provider in turn uses to perform the actual work of generating random numbers

Now, one user account in Windows can have multiple ‘key containers’. SQLIOSim uses the so-called ‘default key container’ for the user account which is running the SQLIOSim utility. So the theory I started forming was that somehow I had lost access to the default key container for my user account.

Before you proceed, I must warn you that the steps below are potentially dangerous if executed wrongly. Here are two regulatory disclaimers I must present, and by reading further you agree to these disclaimers and absolve me of any liability. If you need help and have questions on the steps below, please consider contacting Microsoft Support for professional assistance.

Disclaimers Please note that the third-party links are provided as-is and Microsoft does not offer any guarantees or warranties regarding the content on the third party site. Using tools like KeyPal, and / or editing NTFS permissions on System folders / files incorrectly can cause serious problems that may require you to reinstall your operating system. Use the tools at your own risk. This Information is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS INFORMATION IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

Disclaimer noted, now to investigate the issue further, I first used the KeyPal utility which can be quite useful for low-level CryptoAPI investigations, and located the default container for my account. The default key container is identified by the name of the user account, which in this case happens to be ‘myuser’. KeyPal prints out the details about the ‘unique container’ name (the GUIDs below) which correspond to a file which actually stores the key pairs for the default container. You can read more about the gory details at this page and here.

Keycontainer 50 : myuser
 Uniquecontainer: 4f274a21613b2c41c894534e8d09a8cd_0e489cc1-3b9a-49dc-b00b-6c6697e27995

FYI, the number ‘50’ mentioned above is the ordinal of the key container. Now that I located the Uniquecontainer name for the default key container for my user account, I used Windows Explorer to locate that file in the OS disk. In my case for example it was found (full path below) at:

c:\users\myuser\AppData\Roaming\MicrosoftCryptoRSAS-1-5-21-<somenumbers>-<somenumbers>-<somenumbers>-<somenumbers>\4f274a21613b2c41c894534e8d09a8cd_0e489cc1-3b9a-49dc-b00b-6c6697e27995

When I looked at the NTFS permissions for this file in Windows Explorer, it showed no entry for myuser. I then edited the NTFS permissions to grant Full Control to ‘myuser’. Once I did that, SQLIOSim executed correctly as it could then access the key container and then perform the necessary cryptographic random number generation for the test!

Advertisements

SQL Server 2017 Setup: the step ‘sql_inst_mpyConfigAction_upgrade_confignonrc_Cpu64’ seems stuck, or slow? Here’s why!

EDIT 8 March 2019: The progress reporting of these steps has greatly improved in SQL Server 2019. Individual sub-steps (downloading, installing etc.) are now reported. Thank you for your feedback.

SQL Server 2017 is one more step further in Microsoft’s philosophy of bringing advanced analytics capabilities closer to where the data resides. With Python now supported as an ‘external script’ language (adding to the existing support for R), customers have the full power of Python at their disposal, including external packages like Tensorflow.

Now, what we have seen with some cases is that when the option to install Python has been selected, setup seems to ‘be stuck’ with the string sql_inst_mpyConfigAction_upgrade_confignonrc_Cpu64 being displayed on UI for a while. This is actually not a hang, it is just the setup taking a longer time. The reason for this is that when we install Python with SQL, we have to download and install the Python runtime. That step can take a while, because of the size of the associated packages. If you look at our documentation, you will notice that the size of the Microsoft Python Open component (circled in the screenshot below) is 850MB+). Depending on the speed of the Internet connection that download can take a while!

image

On top of the time it would take to download, there is an additional step of extracting the various files from within the CAB file into their actual folders within the SQL Server binary folder. That step too takes a while, typically because of disk speeds and also sometimes anti-virus scanning overheads. You can see the progress of that extraction step if you periodically look at the below file (replace the 20170804_162723 date-time stamp folder name with the actual one on your server):

C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20170804_162723\RSetup.log

When you look at this file, you will see lines indicating setup is extracting several files. When you close and open the file again, you should notice the timestamp increasing. That will prove to you that SQL Python Setup is indeed progressing and not ‘hung’. In my experience, the overall installation time for this step can be order of magnitude of a few minutes – not too bad, but it can be anxious moments if you just rely on the UI!

In summary:

  • SQL Server 2017 Python setup can take a while, due to file download and file extraction times. A few minutes is normal.
  • To save on download times, pre-download the CAB files mentioned above and pass the path to those files using the MRCACHEDIRECTORY command line parameter as shown in this blog post

Happy scripting with Python and SQL Server 2017!