Oracle Technology Network Blog (aka TechBlog)

Posted by: Lava Kafle

This year, Oracle OpenWorld will be held Sept 19-23rd in San Francisco.

Also this year, JavaOne and Oracle Develop will be held Sep 19-23d in San Francisco.

How can that be?

Simple. Oracle has acquired the city of San Francisco.

OK, not all of it. But an awful lot of it.

And it didn’t actually acquire the city of San Francisco. It just sorta borrowed it.

via Oracle Technology Network Blog (aka TechBlog).

Assuming we know the filename or, at least, have a list of files our key is stored in. There’s one more piece needed to recreate the key – the previous key’s thumbprint. The thumbprint is a unique identifier of the key that stored on the database backups files. Fortunately, if we don’t have it, this is relatively easy to find.

The first, and perhaps simpler, method to finding the thumbprint of our key would be to attempt a restore of the encrypted backup. This will return an error message with the thumbprint it is looking for:

Cannot find server asymmetric key with thumbprint ’0x58914660BBC7630245F92290BD1CE5F7EAD4EC22′.

Another way we could find the thumbprint would be to restore the backup of the master database, under an alternate name, and view the sys.asymmetric_keys table for the row that has our key’s name and its corresponding thumbprint]

At this point, we have completed recreating the key from the file stored on the HSM device, and we’ve updated it’s thumbprint to match the thumbprint the encrypted backup file is looking for. Lastly, we have to create a login from the asymmetric key and give it the credentials to connect to the HSM device to allow SQL Server to read the key file and decrypt the files. The following SQL statements complete this step:

1. Create a login from asymmetric key.

create login TDELogin
 from asymmetric key SQL_EKM_RSA_2048_KEY

2. Create the credential that has the access information for EKM provider.

 WITH IDENTITY = 'EKM_User_Name',secret = 'EKM_Password'

3. Add the credential to the login


The other presenters escaped. But the slide decks from several of the presentations are now available on Slideshare:

I’ll post more Architect Day presentations as soon as I track them down.

A special thank you to Oracle ACE Directors Jordan Braunstein, Billy Tong, and Kai Yu, who were on hand in Dallas, and to fellow ACE Directors Basheer Khan and Floyd Teter for their participation in the Anaheim event.  (Floyd and his iPad came through again, allowing me to record the Anaheim panel discussion via Skype while sitting in my home office in Cleveland.)

That audio, as well as audio from the panel discussion and a roundtable from the Dallas event, will be available soon as ArchBeat podcast programs.

If you attended one of these events, a big thanks. Your active participation, your questions and input, are what these events are all about.  As new cities are added to the tour, we expect more of the same from the OTN architect community. And did I mention that the food is free?

san francisco united states of america USA US North America City of Oracle java

 ADD CREDENTIAL TDEProviderCredential

If we had the correct key file, the database restore (or attach) will be successful, and we have (once again) saved the day. If not, we might have recreated the asymmetric key from the wrong file. If so, we’re going to have to try each file on the HSM repeating the steps until we find the right one. Also, don’t forget to restart SQL Server in normal mode (without the –M option) to allow normal access.

Oracle Technology Network Blog (aka TechBlog) was last modified: February 12th, 2015 by Lava Kafle

Blog Comments

  1. peterlee

    Thank you for sharing excellent information. Your website is very cool. I am impressed by the details that you have on this blog. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for more articles. You ROCK! I found just the info I already searched everywhere and just could not come across. What a perfect site.

  2. javaoraclesanfranciscousa

    why should not I go to CA to get that opportunity to participate in worlds largest conference on java sun oracle heheh

Post Your Comments:

Your email address will not be published. Required fields are marked *