Using the Profile Key Manager

Статья
01/18/2011

After you update the data stored in the SQL Server database, you use the Profile Key Manager in two possible scenarios:

The data has never been encrypted, and you want to encrypt it for the first time. In this scenario, the existing data has not been encrypted. For example, the Profiles service might not have been set up for encryption, and now the Web site administrators would like to offer new services to site visitors, and need to provide a higher level of security for user information.
The existing data in your data store has already been encrypted, and you want to re-encrypt that data. In this scenario, the existing data has been encrypted and you need to re-encrypt it with a new key pair, known as rolling the data. This scenario addresses the case where the Profiles service is already set up for encrypting sensitive profile properties. As a good security practice, the administrator has to change the encryption key periodically, depending on the criticality of the data, to avoid exposing keys to brute force attacks.

Procedures — Scenario 1

To create keys for first time usage

Deny updates. All applications that access the profile data should have the deny updates logic described in the topic Denying Updates. This has the effect of making the applications encryption aware.
Generate the new key. Execute the Profile Key Manager with the following options: ProfileKeyManager /kn /o. This causes a new key pair to be written to the file NewKeyPair_timestamp.xml in the current directory.
Update the application runtime. Update the application runtime with the new keys written to the file NewKeyPair_timestamp.key in the previous step.
Encrypt the data: Roll the data by running the following command:
```
ProfileKeyManager /kc /kfn NewKeyFile /i index /config MyXmlConfigFile /p MyProfile1.MyPropertyGroup1.MyProperty1 MyProfile2.MyPropertyGroup2.MyProperty2
```
NewKeyFile is the XML file containing the new key pair, and Index is the new key index. MyXmlConfigFile contains the connection strings. MyProfile1.MyPropertyGroup1.MyProperty1 is the MyProperty1 property in the MyPropertyGroup1 property group in the MyProfile1 profile, MyProfile2. MyPropertyGroup2.MyProperty2 is the MyProperty2 property in the MyPropertyGroup2 property group the MyProfile2 profile.

Note

Only profile properties of type String or Password can be encrypted.

Note

When encrypting properties for the first time, ensure that all profile properties are set for No Encryption. If some properties have already been encrypted and you are now encrypting newly created properties, you must use the procedure shown in Scenario 2.

Note

If errors occur while encrypting new properties of a profile, you can recover from the situation. Run the ProfileKeyManager tool again with the /kc command, use the same /i flag, no /p flag, and the same key file for the /kfn and /kfo parameters.

Changing the Key for a Large Number of Profiles

You can use certain environment variables to help reduce the memory footprint required to change the keys for a large number of profiles, and ensure that the tool runs to completion without running out of memory.

Note

In this scenario a large number of profiles is one million or more.

You can optimize the update process by choosing to partition the data based on the time when each profile was created. This assumes that the d_date_created column exists in the profile; if it does not, this optimization will not work. The ProfileKeyManager.exe tool partitions and re-encrypts the data in chunks by dividing profiles into fixed time interval groups according to their creation date, and processing these groups one at a time, starting with the newest group and ending with the oldest group.

The following diagram illustrates this process:

Dd451612.DirectionofKeyProcessing(en-US,CS.90).gif

The following table explains how the different environment variables can be used.

Environment Variable	Value	Recommended Action
ROLLKEY_TIME_INTERVAL_PARTITIONING	Format: True / False Default: False	Select "True" to use partitioning.
ROLLKEY_START_TIME (Must be less than end time.)	Format: mm/dd/yyyy hh:mm:ss (24-hour format) Default: 01/01/0001 00:00:00	1. Run the query: `SELECT MIN(d_date_created) FROM <ProfileTable>` 2. Round this value to the nearest hour less than this value. 3. Assign the result to the start time variable. ProfileTable is the physical table backing the profile you want to update. 4. If multiple profile tables exist, select the minimum time for all tables.
ROLLKEY_END_TIME (Must be greater than start time.)	Format: mm/dd/yyyy hh:mm:ss (24-hour format) Default: DateTime.Now (current time)	1. Run the query: `SELECT MAX(d_date_created) FROM <ProfileTable>` 2. Round this value to the nearest hour greater than this value. 3. Assign the result to the end time variable. ProfileTable is the physical table that backs the logical profile that you want to update. 4. If multiple profile tables exist, select the maximum for all tables.
ROLLKEY_TIME_INTERVAL (Must be an integer greater than 1.)	Format: mins Default: 60	Run the query: `SELECT DATEDIFF(Minute, Min(d_date_created), Max(d_date_created)) * <NUM_OF_PROFILES> / Count(*) FROM <ProfileTable>` ProfileTable is the physical table that backs the logical profile you are updating. NUM_OF_PROFILES is the number of profiles you want per partition. A reasonable number would be 50000.

Environment Variable

Value

Recommended Action

ROLLKEY_TIME_INTERVAL_PARTITIONING

Format: True / False

Default: False

Select "True" to use partitioning.

ROLLKEY_START_TIME

(Must be less than end time.)

Format:

mm/dd/yyyy hh:mm:ss (24-hour format)

Default: 01/01/0001 00:00:00

1. Run the query:

SELECT MIN(d_date_created)
FROM <ProfileTable>

2. Round this value to the nearest hour less than this value.

3. Assign the result to the start time variable. ProfileTable is the physical table backing the profile you want to update.

4. If multiple profile tables exist, select the minimum time for all tables.

ROLLKEY_END_TIME

(Must be greater than start time.)

Format:

mm/dd/yyyy hh:mm:ss

(24-hour format)

Default: DateTime.Now (current time)

1. Run the query:

SELECT MAX(d_date_created)
FROM <ProfileTable>

2. Round this value to the nearest hour greater than this value.

3. Assign the result to the end time variable. ProfileTable is the physical table that backs the logical profile that you want to update.

4. If multiple profile tables exist, select the maximum for all tables.

ROLLKEY_TIME_INTERVAL

(Must be an integer greater than 1.)

Format: mins

Default: 60

Run the query:

SELECT DATEDIFF(Minute, Min(d_date_created), Max(d_date_created)) * <NUM_OF_PROFILES> / Count(*) 
FROM <ProfileTable>

ProfileTable is the physical table that backs the logical profile you are updating. NUM_OF_PROFILES is the number of profiles you want per partition. A reasonable number would be 50000.

Note

Failure to round the ROLLKEY_START_TIME and ROLLKEY_END_TIME environment variables as specified may cause some profiles to be skipped during execution, resulting in loss of data.

Note

To set an environment variable, use the SET command. For example:

SET ROLLKEY_START_TIME=11/16/2002 18:00:00

Procedures — Scenario 2

To create keys for re-encryption

Deny updates. All applications accessing the profile data should have the deny updates logic described in the topic Denying Updates.
Generate a new key. Execute the Rolling Key tool with the following options:

        ProfileKeyManager /kn /o

This causes a new key pair to be written to the file NewKeyPair_timestamp.key in the current directory.

Note

If the number of rows is more than one million, you can reduce the memory requirements during the run of the Profile Key Manager by rolling the data in batches.

Warning

Be certain to ensure the safe storage and handling of the configuration and key files, as they contain security-sensitive information.

Rolling the Data in Batches

Rolling the data in batches reduces system requirements. Follow this procedure to roll the data in batches.

To roll the data in batches

Append the option /wc to the command:
```
ProfileKeyManager /kc /kfo OldKeyFile /kfn NewKeyFile /i index /config MyXmlConfigFile
```
For example, if you want to partition the profile data on logon_name (assuming a character type property), you would append the following to the command:
```
/wc UserObject "WHERE GeneralInfo.logon_name >='a' and 
GeneralInfo.logon_name < 'd'"
```
This will roll all the data that has a logon_name greater than or equal to a and less than d.
Write scripts to migrate data iteratively in batches.

Concurrent Execution

To run the tool concurrently against the same profile, you use the /wc option. This provides a degree of parallelism and speeds up the process. Follow this procedure to run the ProfileKeyManager tool concurrently.

This example shows you how to update the UserObject table, which has logon_name as the primary key. It runs two processes simultaneously against different sets of data.

To run the ProfileKeyManager tool concurrently

Append the option /wc to the command for the first set of data:

ProfileKeyManager … 
/wc UserObject "WHERE GeneralInfo.logon_name >= 'a' and GeneralInfo.logon_name < 'h'"

Append the option /wc to the command for the second set of data:

ProfileKeyManager … 
/wc UserObject "WHERE GeneralInfo.logon_name >= 'h' and GeneralInfo.logon_name <= 'ZZZ'"

Поделиться через

Using the Profile Key Manager

Procedures — Scenario 1

To create keys for first time usage

Changing the Key for a Large Number of Profiles

Procedures — Scenario 2

To create keys for re-encryption

Rolling the Data in Batches

To roll the data in batches

Concurrent Execution

To run the ProfileKeyManager tool concurrently

See Also

Other Resources

Дополнительные ресурсы