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