A bit of SQL - finding CRM attributes with no data

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:

1. 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
2. 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

Use the following SQL to create the stored procedure:

create proc pExcCheckColumnValues @entityName nvarchar(64), @customOnly bit = 0, @maxThreshold int = null
as
declare @attrName nvarchar(50), @sql nvarchar(1024)
declare cur cursor fast_forward for
select a.name from attribute a
join entity e on a.entityid = e.entityid
where e.name = @entityName and a.IsLogical = 0
and ((@customOnly = 1 and a.IsCustomField = 1) or isnull(@customOnly, 0) = 0)
create table #tmp (EntityName nvarchar(64), AttributeName nvarchar(50), ValueCount int)
open cur
fetch next from cur into @attrName
while @@fetch_status = 0
begin
set @sql = 'insert #tmp select ''' + @entityName + ''', ''' + @attrName + ''', count(*) from Filtered' + @entityName + ' where ' + @attrName + ' is not null'
+ case when @maxThreshold is not null then ' having count(*) <= ' + cast(@maxThreshold as nvarchar) else '' end
exec (@sql)
fetch next from cur into @attrName
end
close cur
deallocate cur
select * from #tmp
go

To give an example of how to use the procedure, the following will return all custom attributes of the account, where no more than 10 account records have a value in this field:

exec pExcCheckColumnValues @entityName = 'account', @customOnly = 1, @maxThreshold = 10

Service Principle Names and Security

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 used
There 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 SPNs
It'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:

1. 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
2. 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