Saturday, 13 March 2010

Over time I've been asked to review various existing CRM implementations. One thing I often like to check is whether there are any attributes that are not in use. To this end I created the stored procedure at the end of this post.

The stored procedure lists the attributes in an entity along with how many records contain data in that attribute. The parameters in the procedure allow you to limit the attributes to custom attributes, and to specify a maximum count of values (above which the attribute data won't be output).

Notes:

The procedure as written would be created in the MSCRM organisation database. You could create it in a different database, and add the databasename.schemaname. prefixes if you want

The procedure uses the supported route of querying the filtered view for an entity. If you have SQL dbo permission, and don't mind deleted records being included in the counts, then you can run the query against the base view, which is considerably faster. To do this, remove the string 'Filtered' from the procedure definition

Thursday, 11 March 2010

I've recently been working with CRM and Reporting Services in environments where AD delegation has been required, and hence it's been important to configure Service Principle Names (SPN).

A good overview of SPNs and how they are set can be found in this document for CRM 3. Although some of the steps in that document have been superseded by the use of the CRM 4.0 Reporting Services connector, the document is still a good reference for the main aspects of setting up SPNs.

There are two further considerations that are not covered in the above document though; ensuring Kerberos is used, and getting permission to modify SPNs.

Ensure Kerberos is usedThere are 2 Windows authentication mechanisms: NTLM (aka Challenge/Response) and Kerberos. Only Kerberos supports delegation. By default, Kerberos will be used if the client supports it (this setting is known as 'Negotiate'), but this behaviour can be changed.

For web applications hosted in IIS (such as CRM and Reporting Servers 2005), this is controlled by the NTAuthenticationProvider setting in the IIS metabase. Note that this can be set at a few different levels, and you'll need to check them all to determine the current configuration. When you install CRM, the Environment Diagnostics Wizard requires that Negotiate is the default setting, though it could have been changed after install.

Reporting Services 2008 does not use IIS, and the authentication settings are configured in the rsreportserver.config file. Information about these settings can be found here in SQL Server 2008 Books Online.

Permissions required to modify SPNsIt's all very well knowing what SPNs need to be created or modified, but you may not have permission to do this, especially in high security environments. SPN data is stored in Active Directory, hence you need appropriate permissions in Active Directory. There are two approaches that you can take to get the SPNs setup if you don't have permission:

Ask a Domain Administrator to grant you the permissions. The second half of this technet article describes the permissions you need, and how they are assigned

The individual service accounts can be granted permission to create their own SPNs. This is more applicable to SQL Server SPNs, but can be used by any code that can register it's own SPN. This is well described here

Feeds

Who I am

Professionally:I'm a founder member of Excitation Ltd, a Microsoft Gold Partner in the UK that specializes in Microsoft CRM, and I've been the technical lead in over 50 CRM implementations since the release of CRM 1.2.This is a personal blog, and any views expressed here do not necessarily reflect those of Excitation; sometimes they will, but that should be treated as a happy coincidence rather than a normal state of affairs.

Personally: We'll see if I get onto this in the blog; if so, I expect it will include some permutation of mountains, snow and gravity