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;
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?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.