TDE and CRM

Although the title of this post is "TDE and CRM" the reality is that it could be titled "TDE and SharePoint" or "TDE and Any App Running on SQL Server". The point is that the CRM database like any other application running on SQL can be encrypted using TDE. There really are no specific settings as far as the requirements for setting up TDE for CRM.

TDE stands for Transparent Data Encryption and became available in SQL 2008 R2 Enterprise. TDE essentially protects your "data at rest" i.e. it protects your database from being stolen. As in backing up the database and restoring it in some other location where the contents can be analyzed via SQL.

Essentially when you apply TDE you are applying a certificate used to encrypt your data to the back-end database files (it only encrypts to the database files, when you access a TDE database via SQL there is no difference to the usual experience). You will only be able to restore the database to another database server if that server has the same TDE certificate applied. Otherwise you will be prevented from restoring the database - you will receive the "cannot find certificate with thumbprint" error:

The backed up database file will be similarly unintelligible.

As the name itself implies, TDE is transparent. Meaning this is configuration is applied on the database itself and any applications with back-end SQL databases running on top of it will be oblivious to the TDE. The only impact will be a slightly increased level of CPU utilization on the database server as it handles the data encryption for the SQL transactions.

In light of this, it is difficult to not recommend using TDE. It is simple to apply, it is completely transparent to applications, it protects your data... with the only downside being higher CPU utilization which for most databases won't make a difference (assuming the old adage "use it or lose it") and easily compensated in cases where the additional churn pushes your CPU over the edge.

Below are some scripts for setting up TDE on a SQL 2008 R2 Enterprise server:

/* If you wish to copy the certificate from an old server you will need to back it up */
--Backup the certificate from the 4.0 database server:

USE master
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\TDECRMCERT.BAK'
WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

/* On a new server you will need to create a certificate using the backed up one from the old server.
   This procedure assumes you copied the backed up certificate to C:\ root on the new server
 */


-- Create a master key. NB: Record the password for future reference.
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

-- Backup the master key. NB: Store the backup file and record the password for future reference.
USE master;
BACKUP MASTER KEY TO FILE = 'C:\Master_Key.bak'
ENCRYPTION BY PASSWORD = 'MasterKeyBackupFilePassword'

-- Create a certificate by importing the certificate backed up in the first step.
USE master
CREATE CERTIFICATE TDECRM 
    FROM FILE = 'C:\TDECRMCERT.BAK'
    WITH PRIVATE KEY (FILE = 'C:\TDE.key',
    DECRYPTION BY PASSWORD = 'EncryptionPassword')

-- Backup the certificate. If you’re using the imported certificate this step is optional since the file 
-- you used to create the new certificate is already the backup file so you’ll just be recreating this file. 
-- NB: Store the backup file and record the password for future reference.

BACKUP CERTIFICATE TDECRM
TO FILE = 'C:\TDECRMCERT.BAK'
 WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

-- For each database you wish to restore to from a TDE enabled database you will need to apply the certificate
-- and enable encryption.
USE Vanilla_MSCRM
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
Encryption By Server Certificate TDECRM
GO

ALTER DATABASE Vanilla_MSCRM
SET ENCRYPTION ON

Cannot sign in to CRM Online: LiveDevice.xml

Ok. I have stumbled on this too many times. Each time I think I'll remember for the next time and then, lo and behold, I spend time scratching my head trying to figure out what the issue is. So it's time I wrote it down - if nothing else for my own sake as a reference post (hopefully I'll remember that I wrote up this post!).

What am I talking about?

Trying to connect to CRM Online. It happens on occasion that I am denied access to CRM Online when trying to connect from a 3rd party application when I'm certain that my credentials are correct. For example, below is what happens when trying to connect using the Visual Ribbon Editor tool.

Usually I spend time thinking that maybe my URL is incorrect. Maybe I should have left off the http/https. Maybe I should use the Organization Unique Name (appearing under Developer Resources in CRM) instead of the "friendly name" in front of the crm.dynamics.com etc. etc.

The solution of course is simply to delete the LiveDevice.xml file from under the Root\Users\<user>\LiveDeviceID folder and then to try and sign in again... Voila!

Perhaps now I'll remember for next time? Let's hope so...

Reassigning personal views for disabled CRM users

The ability to share personal views in CRM with other users in CRM is a powerful tool. It allows for a central admin users to create or assist other users with creating views and thereafter they can be assigned to individual users or to a group of users. When sharing these views the user has the ability to limit the permissions on the shared views. For example, you may want to share a view but prevent the users who this view has been shared with from deleting the view. That can make sense especially in cases where the shared view is shared amongst a group of users and you do not want to give any individual user the arbitrary opportunity to accidentally or intentionally delete that view and inadvertently have that action ripple across the organization.

This all works very nicely... until the power user who has assigned all his/her views to other users leaves the organization and you want to manipulate these shared views in cases where that manipulation (write, delete, share to others) has been limited. One obvious solution is to have the IT admin temporarily update the AD credentials for this user so you can log into CRM as this user and re-assign the views to a new power user.

But the above action is not always an option. And fortunately there is another better option. Enter the Microsoft CRM 4.0 Administration Console. This console has a number of useful functions including tools for managing security roles but we are just going to focus on the ability to copy personal views for the specific scenario mentioned in this write up.

Below are the steps to copy the personal views:

• Download the console from codeplex
• Launch and enter the credentials and select the "Manage Views" utility

• In the ensuing form, first select "Get Users/Clear Cache". You can click just click Ok to select all users in the organization or you can filter by Business Unit

• Then perform the following steps to assign views from one user to another user
• In View Types ensure only "Main Application View (MAV)" view and "- Filter Active Views" are selected
• Right Column: Select the user you wish to assign from
• Middle Column: Select the view or views you wish to assign (use Ctrl and Shift for multi-select)
• Right Column: Select the user or users you wish to assign the views to (use Ctrl and Shift for multi-select)
• Click "Activate Views for Selected Users" and Confirm

Troubleshooting permission issues

In a previous post I gave a brief overview of the security model in MSCRM. And there definitely is more to be said about best practices when it comes to setting up security roles etc. which I will hopefully cover at some point.

That said, there are times when we encounter a permission error whereby a user cannot perform a certain action. Some times it is obvious such as not having "Create" permission on a given entity and sometimes it is less obvious when dealing with some of the more esoteric permissions in CRM that have dependencies on some other lesser known permissions in order to perform the action.

In these cases you will likely be presented with the "logged-on user does not have the appropriate security permissions to view the records or perform the specific action" message.

So what is the best way to troubleshot this type issue when it occurs?

Well first of all, you should consider which entities are involved in this action. When performing an update or assign action, there could be 2 entities involved. Both the one being assigned and updated and the parent entity that it is being assigned/updated to. That's because there is both the "Append" and "Append To" permissions and if these are not set up correctly it could result in a permission error even when trying to perform an action on a child entity (i.e. the permission problem could be indirect rather than directly on the entity you are trying to perform the action). A discussion of the "Append" and "Append To" privileges is outside the scope of this post, but once again I hope to address both in terms of explaining the difference between these somewhat confusing privileges along with best practices in terms of setting them.

If the above analysis does not lead you to the resolution of your permission error, you should employ the use of the CRM Diag Tool. As an aside, knowing how to properly analyze the log info produced by this tool can save you many support calls with Microsoft support (which will in turn save you time and money).

A good summary for the steps required for analyzing the log info is provided at the bottom of this post. Here is another rendition:

• Run the CRM Diag Tool and enable tracing
• Reproduce the error
• Disable trable
• Open up the log file and run a search on "Error Message" or "Error Number". You should find a section that looks like the following:

Stack Trace Info: [CrmSecurityException: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId: ef295fca-91bf-dc11-99aa-0016353b5824 and PrivilegeId: c2aff40c-6e68-4437-a631-488a354a1860]

• The key piece of information is the Guid of the PrivilegeId
• Take this Guid and plug it into the following query:

select Name from PrivilegeBase where PrivilegeId = 'c2aff40c-6e68-4437-a631-488a354a1860'

...and you should be the returned with the name of the offending privilege: prvReadIncident

That is, in this case, the user does not have sufficient Read privileges on the Incident (aka Case) entity.

• Make some changes to this permission (e.g. increase scope from "user" to "BU") and your problem should be resolved

CRM Security Model Part 1

The CRM security model is in my opinion the most complex aspect of CRM that new administrator users of CRM will need to digest. It is not that it is unnecessarily complex but rather complex due to its robustness. That is, CRM can be configured from a security perspective not only in terms of the actions (CRUD along with some other CRM specific options - share, append, append to) that an end user can take on the various CRM entities but also in terms of the scope that a user may take on a given entity. For example, it may be that a user can update account records in CRM (action), but they may be limited to updating only a certain group of contact records (scope). Similarly, a user may be able to view accounts in CRM (action) but may be limited to viewing only a certain group of accounts and not say "corporate accounts" (scope). The same is true for the other actions (known as permissions in CRM speak) such as delete and insert (although insert is decidedly a rarer need in terms of the scope factor).

There is both a hieararchical as well as looser "team" based security model. And when deploying CRM, it is important to understand the structure of the organization in order to understand which model to apply. They are not mutually exclusive and can be combined to facilitate the required security model. Or team based sharing can be automated based on "attributes" of your CRM data in order to create a security model that is more "tag" based. So for example, you could use the team model to make accounts categorized as "investors" to only be view-able or update-able etc. by an "investor" team. You would want to automate such  a feature to ensure that such classification always happens and not to burden end users with extra "sharing" steps.

Yet, even with all the robustness of the security model there are still some gaps. The one that is most often encountered is the Field Level Security issue. For example, although you can control who can view say an account, once the account is view-able, you cannot control (in CRM 4.0 out of the box that is) what fields on the account can be viewed. Of course there are a number of 3rd party tools available to address this shortcoming along with other tricks of the trade that make it possible to surmount this shortcoming in 4.0. Happily, in CRM 2011 this shortcoming has been addressed out of the box so the above is more for the sake of posterity rather than being relevant if you are working with a CRM 2011 installation.

Remember though that MSCRM is an application that has been designed to be generic enough to be applied in all kinds of organizations across the world. And therefore it is pretty likely that a given organization will only require a certain facet of the security model. Consequently the complexity of the CRM security model may vary depending on how you intend to use it. For example, it might be that a deployment only has a need for a single root Business Unit and if so, it will not be necessary to be concerned about the "BU" or "parent-child" aspects of the security model. Or it may be that an organization does not have the concept of "ownership" for records and therefore you can perhaps dispense with the "user" level security. To be sure, I am not advocating ignorance in this regard - it is certainly possible and even likely that a company's implementation of CRM and therefore security model will evolve over time - and therefore it is always recommended  to have a good idea of the capabilities of the security model. However pragmatically speaking - like anything else - your design of the CRM security model should be tailored to an organization's needs and where-ever possible it should be simplified to the extent possible to enable easy administration. At the end of the day, a walk through document relevant to an organization's specific implementation of the CRM security model should be produced to facilitate such an imperative.

This blog entry is titled "part 1" as with this being somewhat of an introduction, I expect that there will be a sequel or two as we delve deeper into understanding the security model and its real-world application.