March 24, 2020 at 10:38 am
So the last time I did this was over 9 months ago and was part of an experiment to map everything out and get it documented for the people who were doing the whole thing. Now I have to do it for real and I can't find my notes or my script. ARGH.
We are implementing TDE on our servers with a third party KMS. We install the key provider on the server, then run the SQL commands to encrypt the databases. Where I'm getting hung up is on the identity part of the code.
CREATE CREDENTIAL MyCredentialName
WITH IDENTITY ='SQLKMS_User',
SECRET = 'MyPassword'
FOR CRYPTOGRAPHIC PROVIDER MyProviderName
GO
I am trying for the life of me to remember if I'm supposed to use my login to do this part or the service account to do this part. I'm pretty sure "Identity" isn't a SQL login and I do remember that later on in the process, I need to alter the login we're actually using for credential management and assign it to that login (non-interactive login).
Does anyone have any ideas on this? I really don't want to hose our real UAT servers while doing this.
Second question, we have two standalone servers that I'm encrypting, one of which gets backups restored from the other. I know I need to install the credentials from server A to server B, but I read somewhere that a user can't have multiple credentials assigned to it. Am I going to need multiple AD accounts on server B to manage this or can I have server B's credentials and server A's assigned to the same user on server B?
March 24, 2020 at 2:58 pm
The Credential object holds the login information for SQL Server to use when communicating with the KMS. "Identity" is the user name on the KMS, and "SECRET" is the password. I'll provide a little detail, then answer your questions.
CREATE CREDENTIAL MyCredentialName
WITH IDENTITY ='User name on KMS device',
/* omit the SECRET parameter if KMS authentication is with certificate files */
SECRET = 'Password for that user'
FOR CRYPTOGRAPHIC PROVIDER MyProviderName
GO
Once you have created the Credential, you will associate it with your SQL Server Login so you may create a reference in SQL Server that points to a key in the KMS. The Credential info will be used to log in to KMS when you make the call. After you have created the Asymmetric Key, you will switch the Credential from your Login to one created from the Asymmetric Key.
/* 1. If you are using an AD login, that login must be explicitly created on the instance.
If you gain access via AD Group membership, then first create your personal login
and add it to the sysadmin role. */
CREATE LOGIN [MyDomain\MyUsername] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [MyDomain\MyUsername];
/* 2. Create the Credential with the KMS user/password */
CREATE CREDENTIAL [new_cred_name] WITH IDENTITY = 'KMS_Login_name',
SECRET = 'KMS password' FOR CRYPTOGRAPHIC PROVIDER [crypto_provider_name];
/* 3. Assign the Credential to your Login */
ALTER LOGIN [your_login_name] ADD CREDENTIAL [new_cred_name];
/* 4. Create the Asymmetric Key */
CREATE ASYMMETRIC KEY [...]
/* 5. So SQL Server may communicate with KMS, create a SQL Server Login
from the Asymmetric Key, and switch the Credential from your Login
to the Login created from the key */
/* 5a: Create a Login from the Key */
CREATE LOGIN [new_key_login_name] FROM ASYMMETRIC KEY [key_name_from_step_#4];
/* 5b: remove the Credential from your login and add it to the Key's Login */
ALTER LOGIN [your_login_name] DROP CREDENTIAL [new_cred_name];
/* 5c: assign the Credntial to the Login created from the Key */
ALTER LOGIN [new_key_login_name] ADD CREDENTIAL [new_cred_name];
/* Key is now ready to use for TDE. */
Note that the Login created from the Key in Step #4 is not an AD user, nor is it a traditional SQL Server Login; it has no password and cannot be used to log in to the instance. It exists to hold the Credential necessary to communicate with KMS.
And for the second question:
Second question, we have two standalone servers that I'm encrypting, one of which gets backups restored from the other. I know I need to install the credentials from server A to server B, but I read somewhere that a user can't have multiple credentials assigned to it. Am I going to need multiple AD accounts on server B to manage this or can I have server B's credentials and server A's assigned to the same user on server B?
You will need one Credential for each Asymmetric Key on each instance.
You will create one SQL Server Login from each Asymmetric Key and assign a Credential to it. No AD accounts are used. The only things that need to match between the two servers in order for Server B to read and decrypt backups from Server A are the Asymmetric Keys from KMS. The Credential and Login names on Server B do not have to match Server A, just the Asymmetric Keys. As long as Server B can access the same keys, everything just works.
Eddie Wuerch
MCM: SQL
March 24, 2020 at 5:36 pm
Here's a couple queries you can use to inspect things. Note the relationship between, master.sys.asymmetric_keys, sys.server_principals, sys.server_principal_credentials, sys.credentials, and sys.cryptographic_providers.
/* for each KMS Asymmetric Key, show the associated Credentials and Logins */
SELECT k.name AS [AsymKeyName], p.name as [Key_SQLLogin],
c.name as [CredName], c.credential_identity AS [CredIdentity],
cp.name AS [ProviderName], cp.dll_path AS [ProviderPath]
FROM master.sys.asymmetric_keys k
JOIN sys.server_principals p ON p.sid = k.sid
JOIN sys.server_principal_credentials pc ON pc.principal_id = p.principal_id
JOIN sys.credentials c ON c.credential_id = pc.credential_id
JOIN sys.cryptographic_providers cp ON k.cryptographic_provider_guid = cp.guid
WHERE k.pvt_key_encryption_type = 'CP' -- 'CP'='Cryptographic Provider'
/* Show all Credentials and to which Login, if any, they are mapped */
SELECT c.name as [CredentialName], c.credential_identity AS [CredIdentity],
IsNull(p.name, 'Unassociated') as [LoginName]
FROM sys.credentials c
LEFT JOIN sys.server_principal_credentials pc ON c.credential_id = pc.credential_id
LEFT JOIN sys.server_principals p ON pc.principal_id = p.principal_id
Eddie Wuerch
MCM: SQL
March 24, 2020 at 5:55 pm
Thanks, Eddie.
March 24, 2020 at 8:51 pm
I just realized you gave me a lot of info I already had but didn't actually answer my first question. It was very specific to the CREATE CREDENTIAL part of this process.
You said "WITH IDENTITY ='User name on KMS device',"
Okay. So that's the part I'm struggling to remember. Do I just put in a random name? A SQL Server login? The name of the account currently logged into SQL Server running the CREATE CREDENTIAL statement?
EDIT: To be clear, I don't have access to the KMS device. A license key was created on it using our server names. Now I need to run this code and I think this is why I actually do need an AD account later on in the process (their instructions specifically reference it) is so SQL can connect to the KMS server as needed to encrypt / decrypt the databases.
March 25, 2020 at 3:21 pm
What provider are you using?
Eddie Wuerch
MCM: SQL
March 25, 2020 at 8:00 pm
Vormetric.
March 30, 2020 at 2:58 pm
I just realized you gave me a lot of info I already had but didn't actually answer my first question. It was very specific to the CREATE CREDENTIAL part of this process.
You said "WITH IDENTITY ='User name on KMS device',"
Okay. So that's the part I'm struggling to remember. Do I just put in a random name? A SQL Server login? The name of the account currently logged into SQL Server running the CREATE CREDENTIAL statement?
EDIT: To be clear, I don't have access to the KMS device. A license key was created on it using our server names. Now I need to run this code and I think this is why I actually do need an AD account later on in the process (their instructions specifically reference it) is so SQL can connect to the KMS server as needed to encrypt / decrypt the databases.
None of the above. The credential to use in the WITH IDENTITY clause is the account that SQL Server will use to request the public key from the KMS. This should be an account with permissions that is limited to using keys; many KMS platforms will have an account type that is used for applications so it cannot be used interactively. I haven't gone through Vormetric's implementation process personally, but generally, a security admin would create this account on the KMS and give you the credentials required.
Ed
Ed Leighton-Dick | Consultant | Microsoft Data Platform MVP | MCSE | PASS Regional Mentor
March 30, 2020 at 3:26 pm
I dug around for a bit, but couldn't find any docs for Vormetric.
What I did find is that there are a couple different Vormetric encryption products for SQL Server, and they work differently. Check with your crypto admin to see if they can locate the config docs for SQL Server for the Vormetric product you're using.
One product of theirs encrypts folders in a file system, and you move your database files to protected folders to get the encryption. There's nothing to configure on SQL Server, and SQL Server is unaware that the files are encrypted on disk.
The other appears to be traditional KMIP-based EKM. These traditionally function by having a text configuration file alongside the SQLEKM files in which you list the connection settings to reach the KMS. The docs that explain how to configure that file should also explain what goes into the Credential.
If you have a link to the docs, I can review them for the key items.
Eddie Wuerch
MCM: SQL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply