Sonoma Partners Microsoft CRM and Salesforce Blog

CRM security privileges for appointment creation?

Sometimes when you're adjusting Microsoft CRM security roles, you'll get an error message when trying to perform an action telling you that you don't have permission and you'll think to yourself..."what in the world are they talking about?!?!" After you review the security roles you wonder what privilege could I possibly be missing that would impact this?

As it turns out, this very scenario happened to me this morning as I was preparing for an important customer demo. I created a VERY streamlined security role that would just display Accounts, Contacts, Cases, Opportunities and a few custom entities. By removing all the other security privileges I knew that the prospect wouldn't get distracted by all of the other out-of-the-box entities such as Marketing Lists, Quotes, Orders, Invoices, and so on. I wanted a super streamlined user interface for my demo so I took off all the extra stuff.

However as I did a practice run, I noticed that I could NOT create a new Appointment. This was confusing because I was pretty sure I had the security roles setup correctly. The error message I received looked like this:

I decided to check the "Privileges by Message" documentation on MSDN to make sure I didn't miss anything. This website shows which security privileges you need to perform certain actions and many times the necessary privileges are not obvious! For example, I was looking at creating an Appointment and the MSDN website said I need these privileges:

  • prvAppendActivity
  • prvAppendToAccount
  • prvAppendToActivity
  • prvAppendToContact
  • prvAppendToContract
  • prvAppendToIncident
  • prvAppendToInvoice
  • prvAppendToLead
  • prvAppendToOpportunity
  • prvAppendToOrder
  • prvAppendToQuote
  • prvAppendToService
  • prvCreateActivity
  • prvReadActivity
  • prvShareActivity

Some of these really jump out at me, for example who would have guessed that you need Append To Contract privileges just to create an Appointment?!!?

So after reviewing the MSDN website I double-checked all the privileges in my security role and everything matched up per the spec, but I was still getting the error message. After reading the text of the error message a little more closely, the light bulb finally went off. Under the Service Management tab, there is a miscellaneous privilege titled "Search Availabilty" and that seemed to match up with the error message. I toggled this privilege on for the security role and Voila! I was able to create an appointment.

While of course I was happy to have solved my problem for the demo, I still find it a little confusing that this Search Availability privilege (on the Service Management tab no less!) is required to create Appointments...especially since this privilege was not listed in the MSDN article.

Kerberos and Delegation Tips

Have you ever seen the following error "NT AUTHORITY/ANONYMOUS LOGON" or "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection"?  Or maybe you've seen the "The request failed with HTTP status 401: Unauthorized." error accessing Microsoft CRM Reports?

Because these issues pop up from time to time with our Microsoft CRM customers, I spent some time recently researching this on the Internet and talking to various support engineers at Microsoft. The problems are usually caused by a Kerberos and/or delegation issue and they are very challenging to troubleshoot.

I am not an expert in the Kerberos/delegation topics domain, nor do I want to this post to delve into all possibilities/scenarios/etc. However, I wanted to share some of my learning to provide you a starting point when configuring or troubleshooting your own environments and save you some time when tackling this situation. Hopefully this post can save you some time and headaches!

Kerberos

In a nutshell, Kerberos is a secure ticket-based protocol for authenticating a service request. While Kerberos is not a Microsoft specific protocol, it is integral to the Active Directory security structure of Windows 2000/2003. Please see the Kerberos Explained article for a more detailed information.

Delegation & Service Principal Names (SPN)

Delegation is simply allowing another server/service to allow a Kerberos ticket to be created for another service on the originating user's behalf. This can be done at the computer level by using full delegation or with constrained delegation (Windows 2003 environment only). Constrained delegation means that the Kerberos delegation can only be executed against a limited set of services.

The Service Principal Name (SPN) is a name that uniquely identifies an instance of a service to a client within Active Directory. SPN's can not be duplicated in a given domain and must be specified for each name/alias that can be used to access the host.

Sample Scenario

Let's look at a common scenario for a web application accessing a database to retrieve information and return it to the client.

  1. A client requests a web page from a web server that retrieves some information from a SQL Server database.
  2. The web server and database server are on two separate machines in the same Windows 2000/2003 domain.
  3. The web page uses Windows authentication to connect to the database server. Note that a typical connection string might look like: server=DATABASE;database=MyCustomDatabase;Integrated Security=SSPI
  4. The client will access the web using any of the following: http://crmserver, http://crmserver.domain.local, http://crmalias

Configuration Requirements for the Sample Scenario

The scenario described above is also known as a "double hop" situation. The client's credentials will need to be passed to the web server (1st hop) and the forwarded onto the database server (2nd hop) for authentication and access. In order to properly display data from the database, we will need to ensure the following:

Client

  • Must be member of a trusted domain
  • Ensure the URL being used is part of the Internet Explorer trusted sites
  • Internet Explorer must be set to use Integrated Authentication (In IE, click Tools > Internet Options > Advanced > scroll to Security)

  • The domain user for the application must have the "Account is sensitive and cannot be delegated" option unselected in Active Directory.

Web/Database Servers

  • Both servers needs to support Kerberos (Windows 2000 and 2003 both do)
  • Both servers are a member of a trusted domain and have a valid Computer account
    Note: When a computer is attached to the domain, a computer account should be created in Active Directory and a HOST service principal name should also be added automatically.
  • The web server (1st hop server) must be trusted for delegation.
  • SQL Server allows the user's account access. (If not, you will usually get a different error about not have valid access to the database).
  • SPN's are properly setup for each name that the client will access.

Validating & Creating SPN's

Only a domain administrator will be able to view and create SPN's. There are multiple ways to manage SPN's, and I will be showing examples using the setspn.exe (Setspn.exe Download). This simple command line tool can be installed on any machine in the domain, but you will need to execute as a domain administrator.

For example, you can list all SPN's for a computer account by using the following:
setspn -l computer

In the sample scenario above, you would use the following to list all SPN's for the web server:
setspn -l crmserver

To create a new SPN for the HTTP service, you would use the following syntax:
setspn -a http/<name> <server netbios name> (if the identity is running under the computer account)
setspn -a http/<name> <domain\name> (if the identity is running under a domain account)

When creating a new SPN for our CRM web server, you will need to first determine the account running IIS by reviewing the identity of the Application Pool (IIS 6.0). If that account is local system or network service, then it is running under the computer account. You would also need to determine the name your clients will access this service.

In our sample scenario, you would need to do the following:
setspn -a http/crmserver crmserver
setspn -a http/crmserver.domain.local crmserver
setspn -a http/crmalias crmserver

Note, if your service is running under a domain account, then you will need to specify that account in your setspn -a statement:
setspn -a http/crmserver.domain.local domain\user

You will also need an SPN for the SQL service (usually this is created automatically when you install SQL Server). The usual reason this is configured incorrectly is when the account that is running SQL Server changes after installation. If you do need to create a new SPN for the SQL service, use something similar to:
setspn -a mssqlsvc/database.domain.local domain\user

See Setspn Syntax for more information on the syntax and usage of the setspn tool.

Troubleshooting Tips

  • Ensure the client's IE configuration is correct (see client configuration above), verify from where are accessing the web site(LAN, VPN, Internet) and what URL they are using.
  • In most instances I have seen, a misconfigured SPN is the issue. Determine your IIS and SQL Server service's accounts. Double check that you have a valid SPN for each service account account for the access method used (netbios, FQDN, any alias/CNAME used, etc).
    NOTE: I recommend creating an SPN for the fully qualified domain name (FQDN) if one doesn't exist. I have seen a situation where the netbios name was used to access CRM, but under the covers it gets submitted as the FQDN...in this case the user had a DNS suffix added to their network properties.
  • Try to determine if your client is accessing the web server with Kerberos instead of NTLM. I find that this is easiest to check the event viewer on the web server and look at the error thrown. In the details, it will tell you which protocol was used to authenticate. If it is Kerberos, then your problem will typically be with an SPN or delegation, and your client configuration/access is fine.
  • Check the delegation from the web server to the SQL service SPN. If you are using constrained delegation, ensure you have the correct service setup.
  • Check the time on the client versus the servers. They need to be in sync (typically within a few minutes) for a Kerberos ticket to be granted. Most machines on the domain usually get their time from the domain controller, so I haven't seen this to be the issue in most cases.
  • For the more obscure issues and more advanced users, capture the network trace from the 1st hop machine (typically the web server). This can be done with a tool like Wireshark. Filter the trace file by Kerberos and look for these common errors:
    • time_skew - There is likely a w32time error or difference between the machines
    • s_principal_unknown - Indicates an SPN error
    • access denied - Try rebooting the client and reviewing the event viewer on the domain controller for any errors. Use KerbTray on the client to see if you have any valid Kerberos tickets.
  • If all else fails, then contact Microsoft Support and use their trained engineers to help troubleshoot the root cause. Prepare to allocate some time and have someone with domain admin rights available to assist the Microsoft engineer.

Additional Notes

  • Kerberos/delegation is very important to Microsoft CRM 3.0 because of its integration with Reporting Services AND because filtered views will not return records from a connection string using SQL authentication.
  • Kerberos requires the client computer to be on a trusted domain. It will not work if you are accessing over the Internet as the domain will not be able to trust you. If you were to access the web page or report over the Internet and receive the standard challenge/response dialog, you will typically be authenticating over NTLM, and as such unable to pass a Kerberos ticket to SQL server in a double-hop scenario.
    NOTE: NTLM can be used to authenticate to SQL server if the web is on the same server (ie it doesn't need to delegate to another server).
  • You can get Kerberos to work if you connect with most VPN software, provided the computer you are using has been previously registered on a trusted domain (typically applies to a laptop situation).
    For instance, when I try the reports page through CRM at home, I get a "The request failed with HTTP status 401: Unauthorized." error. My home desktop can't access our reports through CRM even when I have established a VPN connection to the office. Our CRM architecture has reports/SQL/CRM on separate machines AND my computer is registered on my home network (which isn't trusted by my office), so I won't have a Kerberos ticket to forward to SQL Server.  However, if I VPN into my office using my laptop (a member of my office domain), I can access the reports fine. I am told that not all VPN software will allow you to get a Kerberos ticket, although most I have tried seemed to work.
  • Small Business Server should not see any of these problems (even when accessing over the Internet) because all components are on the same server. Therefore, delegation is not required, and an NTLM authentication is sufficient. Likewise, if you installed CRM/SQL Server/SRS on the same server, you would also avoid this problem (although you would have other ones to address).
  • It is possible for the web server to convert an NTLM request to a Kerberos ticket by using a technique known as Protocol Transition. I am told it is quite challenging to make this work, and have included a link to an article in the Additional Resources section if you are curious/ambitious.
  • If your service account is a domain user (instead of network service or local system), then this user also needs to have its trust setup for delegation in Active Directory. If you don't see the delegation tab, then it means there is no SPN setup for that account. Setup an SPN for the account, then try user properties dialog again and you should see the tab.
  • A Kerberos hotfix does exist for cases where a client Kerberos ticket is expiring on Windows XP SP2. Unfortunately, I haven't personally seen this hotfix work in the cases where I have this situation. It is limited distribution, so contact Microsoft Support if you need it. I am told that if you have an XP SP2 machine that has an expired Kerberos ticket (use KerbTray to verify), and it is corrected by simply logging off and back in, then that hotfix should work for you.

Additional Resources

Troubleshooting Kerberos Delegation

Kerberos Explained

How to configure an ASP.NET application for a delegation scenario

Kerberos Protocol Transition and Constrained Delegation

Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server

Setspn.exe Download

Setspn Syntax

Security Account Delegation for SQL Server

Kerbtray.exe Download

Wireshark

Microsoft CRM quote report from the Dynamics Sandbox gets some decent looking quotes

I was doing some prep work tonight for one of our prospects who is looking for a better solution to generate quotes using Microsoft CRM. Of course, everyone that's looked at the printable version of the default Microsoft CRM quote knows that it's uh...how do I say...open for improvement. The two most glaring needs in my opinion are:

  • Customers can't customize the formatting to match their desired output
  • The print layout doesn't display the line items contained in the quote

Here is a sample of the default quote print output:

I remembered that someone posted Quote "reports" on the CRM Sandbox a while ago so I thought I would check them out. Here's what I found in the Downloads section:

Quotes Orders Invoice Form Reports __Corrected for Product ID__ (QOI Reports (With Regional Number Formats).zip, 24k, 1945 downloads)
Posted:  03/03/2006 07:25 PM
This is a set of Quote, Order and Invoice reports that you publish in CRM and expose through the individual forms. They are a better alternative than the Print Preview offered by the stock CRM package and the nice thing is that the reports are context sensitive and are visible and run from the QOI forms; Additionally, if your customer resides in another locale where the number format is different, these report allow you to change the number format on the fly. Install doc included.This version corrects for product ID display.

I downloaded the Zip file, uploaded the two RDL files and tried them out. There are two RDL files because there's a report file and a sub-report, but the instructions explain the exact steps on how to install them both if you're not sure. (By the way, how easy is it to deploy custom reports in Microsoft CRM? This report deploy took approximately 30 seconds!).  Here's how I configured the custom Quote report:

Now when you browse to a Quote record, you'll see the Report icon and the Quote report appear in the toolbar  (we renamed the Quote entity to Proposal in our internal CRM system if you're wondering why it says Proposal):

 

When a user runs this report, they'll get a report output that looks like the following:

So you can see that this Quote report addresses one of my original complaints because it displays the line item details of the products bundled in the quote. In addition because this is just a SQL Server Report Services (SRS) Report, we can EASILY modify the design and layout of the report to meet the needs of our prospect.

Lastly, I know this will be a big "win" for our prospect because it's very simple to use as well. Users just click one button and the report generates. If the user wants, they can also use the built in SRS functionality to quickly export this quote to PDF or Word.

NOTE: if you download this sample code for yourself, make sure you get the version dated November 2006 instead of the March 2006 upload. The description of the latest version looks like this (QOI stands for Quote Order Invoice by the way):

QOI Reports with IDs (QOI Reports (With Regional Number Formats).zip, 22k, 470 downloads)
Posted:  11/27/2006 03:05 PM
Updated reports with QOI ID's right below the QOI name.

Being super impressed with these awesome CRM Sandbox reports, I decided to try and find out who posted this to the website. It turns out that these reports were posted by Ed Martinez who is one of the development managers on the Microsoft CRM product team. Thanks Ed!