May 31, 2019 at 6:26 pm
As our first foray into TDE (corporate mandate, so please don't post "don't do it"), we're playing with a sandbox server (fortunately) and I was given wrong instructions. We're using a third party encryption key that SQL Server is hooking into. We installed it, got halfway through the instructions and SQL Server blew up at me.
The instructions had us adding the credential to a login that none of us have the password to, which meant that I couldn't create the asymmetric key because I didn't have permissions. So we tried to walk back and undo things, dropping the credential from the other login, adding it to my login (temporarily), and trying again. Got the key created but couldn't create a login from it. More errors ensued. Finally I tried walking back the whole thing. Dropping the asymmetric key, dropped the credential from me, dropped the credential and dropped the provider. Even dropped all the logins. Started over from scratch. But now when I try to create the asymmetric key again, I'm getting the below error:
Msg 33046, Level 16, State 1, Line 1 Server principal 'public' has no credential associated with cryptographic provider
I'm not trying to hook the key to the public principal. And Google doesn't find this error message. Any thoughts on what's gone wrong?
As an FYI: I've been able to do a clean install of everything in the correct order on 2 other instances. I just can't seem to clean up the one instance that got screwed up from the start.
May 31, 2019 at 7:05 pm
I might be wrong.. but wouldn't restoring the master database clear this up? if you have a recent backup and you havent done anything else with that database since.. seems like a pretty quick solution.. though I would only do this if everything else on that instance was unencrypted and keys dropped first.. just in case.
June 3, 2019 at 1:31 pm
Hrmmm. Maybe it would, but restoring master is kind of drastic and labor-intensive. If I'm going to go that far, I might as well uninstall and reinstall the entire instance. Both of which I'm trying to avoid.
Surely there's another way to deal with this error? Something I could repeat (if necessary) on an instance where there are user databases.
I'm sort of the corporate guinea pig for this. We're building a document for all others to use. It would be nice to write up a "how to back out if something screws up" doc for all other users and they might not have the luxury of restoring master or reinstalling the instance.
June 11, 2019 at 7:06 am
Honestly, I'd open a support ticket with the provider. The way that third party key management solutions work with credentials can be tricky. They aren't well documented and I've struggled at times with ensuring that a login/user from SQL can properly access the keys on the device.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply