TDE Drama

  • 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?

    "

     

    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.

  • 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

    • This reply was modified 4 weeks ago by  Brandie Tarvin. Reason: Asymmetric really should have a K in there somewhere given how many times I keep adding it in. Sigh

    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.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply