October 30, 2020 at 1:11 am
My workplace uses a 3rd party key management system as an encryption provider for TDE. Most of the time, things go swimmingly, but apparently this morning I totally FUBARed one of our non-prod servers. I enabled the EKM provider stetting, created the Cryptopgraphic provider, created the initial credential, created a windows login that I added the initial credential to, created a user in the master database for that user, granted create asymmetric key to that user, logged in as that user to create the asymmetric key, then created another credential using a new identity, created the sql login, and added the new credential to that login.
I've done this a few times, but for some reason on this one server it failed. I think my issue is that I misremembered the password entered for the cryptographic provider. I went to reverse everything and that's when the problems started.
SELECT * FROM sys.credentials;
SELECT *
FROM sys.server_principal_credentials spc
INNER JOIN sys.server_principals sp
ON spc.principal_id = sp.principal_id
INNER JOIN sys.credentials c
on spc.credential_id = c.credential_id;
I run the above code and I can see the second credentials with the associated SQL user. I go to ALTER LOGIN MySQLLogin DROP CREDENTIAL MySecondCredential; and get
Msg 15151, Level 16, State 1, Line 2
Cannot alter the login 'MySQLLogin', because it does not exist or you do not have permission.
I can't recreate the login from the asymmetric key, because it already exists. And I can't drop the credentials without the ALTER LOGIN because they are mapped to a login still. I'm trapped in a vicious loop of "doesn't exist" "does exist".
Has anyone ever encountered this issue? Any thoughts on how to resolve it so I can remove all these credentials, reinstalled the provider, and get my databases encrypted?
"
October 30, 2020 at 2:36 pm
So I've managed to unwind most of this build. Unfortunately, I was left 2 credentials created with an asymmetric key that are orphaned (not sure how I did that). They have no associated logins on them. Not sure how that happened. But once I dropped the associated asymmetric key, I was able to run DROP CREDENTIAL without any issues.
That was a bit of a pain, but my issue is mostly resolved.
Generally speaking:
ALTER LOGIN MyLogin
DROP MyAsymmetricCredential;
DROP LOGIN MyLogin;
DROP CREDENTIAL MyAsymmetricCredential;
DROP ASYMMETRIC KEY MyAsymmetricKey;
ALTER LOGIN MyWindowsSecurityAccount
DROP MyInitialCredential;
/* DROP LOGIN MyWindowsSecurityAccount --if it's unnecessary */
DROP MyIntialCredential;
--Here's where to drop orphaned credentials
--DROP CREDENTIAL Orphan1;
DROP CRYPTOGRAPHIC PROVIDER MyProviderName;
--useful if uninstalling and reinstalling the cryptopgraphic provider
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy