Empty Business Data Lists and no search results

Sep 22, 2009 at 5:47 PM

Hi guys,

I'm trying to get the Enterprise Search Accelerator for Microsoft Dynamics CRM to work in my architecture but so far I haven't been able to get any data displayed in a web part or to find any contact by using the search engine.

Here is basically my architecture:

  • sqlserver - MS SQL Server 2008 SP1 (Win 2008 R2)
  • crmserver - MS Dynamics CRM 4.0 w/ update rollup 6 (Win 2008 SP2)
  • mossserver - MOSS 2007 SP2 (Win 2008 R2)

I imported the application definition XML file successfully. Below are the settings I used. For information my MOSS server is using Kerberos as Authentication Provider (hence the PassThrough).

<LobSystemInstance Name="Microsoft Dynamics CRM">
      <Properties>
        <Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>
        <Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>
        <Property Name="RdbConnection Data Source" Type="System.String">sqlserver</Property>
        <Property Name="RdbConnection Initial Catalog" Type="System.String">MyOrg_MSCRM</Property>
        <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
        <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
      </Properties>
</LobSystemInstance>

I was then able to add a new content source. The CRM is almost empty, I only have a couple of test users and a few activities and contacts. The crawl seems to work as it was able to find 17 records.

I also created the new search scopes and added then to my site collection.

 

Since this seemed to be ok I was expecting two things:

  • To be able to find a CRM contact (for instance) using the search engine with the new scope
  • To get a list of my contacts or activities using the Business Data List Web Part

Unfortunately neither of these is working as expected:

I tried to search for contacts, users or activities using the new scope and didn't any results

  • I added Data List Web Parts for Activities and Contacts, they both display a nice yet frustrating message "There are no items to show."
  • I attempted to add a Business Data Item Web Part to display one contact however once I've selected the type (Contact) I cannot find any item (drop down list empty and no search results)

 

I'm connected with an account which has SharePoint farm Administrator and CRM System Administrator privileges. When I connect the CRM web site from the Sharepoint server I can see all the contacts and activities.

Since I don't get any errors, I'm not sure where I should look... I initally got an error in the web part which was due to Kerberos but I (think) I fixed it since it's gone. Any though?

 

Thanks!

Sep 23, 2009 at 2:46 AM

sounds similar to my issue.  only the account that is a service account can view anything

 

Sep 23, 2009 at 3:00 PM

Hi David,

Which account are you refering to? If you tell me me a bit more about it I can give it a try and see if it also works for me with this account.

Thanks

Sep 23, 2009 at 4:48 PM

Current scenario:

Everything is running under one user domain\search

This user is a normal user on the domain,  CRM admin, dbo in MSSQL

So all the services and the crawl run under this user.  I had to make this user a CRM user just so the crawl wouldn’t fail.

I can log in as a domain admin with full rights to everything in MOSS and everything the user above has and no results are displayed.

I am not using a VPC, we have a sandbox environment that mirrors production. 

~Withers

From: ThibaultB [mailto:notifications@codeplex.com]
Sent: Wednesday, September 23, 2009 8:01 AM
To: david@withersdavid.com
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

Hi David,

Which account are you refering to? If you tell me me a bit more about it I can give it a try and see if it also works for me with this account.

Thanks

Read the full discussion online.

To add a post to this discussion, reply to this email (crmaccelerators@discussions.codeplex.com)

To start a new discussion for this project, email crmaccelerators@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Sep 23, 2009 at 6:23 PM

This is the URL that is comes back from a successful search from http://mossintranet which I created as an enterprise search page

http://sspadmin/ssp/admin/Content/Lead.aspx?leadid=e6e134f4-b244-de11-8065-003048799a41

When I paste that URL into the browser of another user I receive the following error:

You do not have permission to access Lead in Microsoft Dynamics CRM.

Since the user that is getting that error is a CRM ADMIN and SQL server admin, I’m guessing that something isn’t  being passed correctly.

~Withers

From: ThibaultB [mailto:notifications@codeplex.com]
Sent: Wednesday, September 23, 2009 8:01 AM
To: david@withersdavid.com
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

Hi David,

Which account are you refering to? If you tell me me a bit more about it I can give it a try and see if it also works for me with this account.

Thanks

Read the full discussion online.

To add a post to this discussion, reply to this email (crmaccelerators@discussions.codeplex.com)

To start a new discussion for this project, email crmaccelerators@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Sep 23, 2009 at 6:54 PM

Thanks for the details Withers.

I followed your indications and added a new CRM account for the service account used by my SharePoint Application Pool: when I give full access to this new account, I can see my data, which basically means that it is indeed using this account to access the data. No matter which user is connected to SharePoint I get the same results, which makes me bend towards an Impersonation issue.

I read quite a few articles about kerberos and the double hop issues and but maybe I missed something and some settings in my IIS or in SharePoint are not right... Any ideas on what it could be?

 

Regarding the search, I still don't get any result. For instance I have in the CRM an activity of type "Task" with a subject of "Task number 1", and when I search for "task" or "task number 1" it doesn't find it.

Thanks

Sep 28, 2009 at 10:23 AM

I managed to get it to work a few days ago: I actually had a couple of little issues, most of them related to Kerberos authentication.

Basically to solve this problem I made sure

  • The Authentication Provider and the SSP was set up to use Kerberos
  • The correct SPNs were set on my SQL server computer account and my SharePoint AppPool account
  • The SharePoint AppPool account was trusted for delegation
  • The Sharepoint website was in the Local Intranet so that IE would pass the credentials (default settings)
  • The Sharepoint Content account had read permissions in Sharepoint (on the BDC application), in the CRM (crawling account) and on the Database

Once all those criteria were met it started working as expected and I was able to access the data through the Web Parts and search on it.

Sep 30, 2009 at 4:43 PM

A few question:

What are the correct SPN for the SQL server computer account?

As I’m still stuck at the crawl user is the only one returning results.  Even with me having full permissions on every site and bdc.

~Withers

From: ThibaultB [mailto:notifications@codeplex.com]
Sent: Monday, September 28, 2009 3:24 AM
To: david@withersdavid.com
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

I managed to get it to work a few days ago: I actually had a couple of little issues, most of them related to Kerberos authentication.

Basically to solve this problem I made sure

  • The Authentication Provider and the SSP was set up to use Kerberos
  • The correct SPNs were set on my SQL server computer account and my SharePoint AppPool account
  • The SharePoint AppPool account was trusted for delegation
  • The Sharepoint website was in the Local Intranet so that IE would pass the credentials (default settings)
  • The Sharepoint Content account had read permissions in Sharepoint (on the BDC application), in the CRM (crawling account) and on the Database

Once all those criteria were met it started working as expected and I was able to access the data through the Web Parts and search on it.

Read the full discussion online.

To add a post to this discussion, reply to this email (crmaccelerators@discussions.codeplex.com)

To start a new discussion for this project, email crmaccelerators@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Sep 30, 2009 at 6:04 PM

Assuming you are running the SQL Server services using the Network Services account it should work with the following SPNs associated to the computer account:

  • MSSQLSvc\sqlserver.domain.tld
  • MSSQLSvc\sqlserver.domain.tld:1433

Take a look at both Sharepoint and SQL Server logs (enable login auditing) to see at what point you get login attempts as Anonymous User instead of the logged in user.

Sep 30, 2009 at 6:12 PM

Setspn.exe –A MSSQLSvc\sqlserver.domain.tld

Correct?

~Withers

From: ThibaultB [mailto:notifications@codeplex.com]
Sent: Wednesday, September 30, 2009 11:05 AM
To: david@withersdavid.com
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

Assuming you are running the SQL Server services using the Network Services account it should work with the following SPNs associated to the computer account:

  • MSSQLSvc\sqlserver.domain.tld
  • MSSQLSvc\sqlserver.domain.tld:1433

Take a look at both Sharepoint and SQL Server logs (enable login auditing) to see at what point you get login attempts as Anonymous User instead of the logged in user.

Read the full discussion online.

To add a post to this discussion, reply to this email (crmaccelerators@discussions.codeplex.com)

To start a new discussion for this project, email crmaccelerators@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Sep 30, 2009 at 6:18 PM

Actually it should be more like this.

  • Setspn -A MSSQLSvc/sqlserver.domain.tld DOMAIN\sqlserver
  • Setspn -A MSSQLSvc/sqlserver.domain.tld:1433 DOMAIN\sqlserver

I actually haven't had to run these commands since the SQL Server setup took care of it. You can also use ADSI Edit.

Sep 30, 2009 at 6:44 PM

Still no luck, the only user that is able to see search results of the BDC is the crawl user.  It’s not machine specific either.  As I can select login as different user and it will work for the crawl user but fail for myself.  I have assigned myself full control to everything in SharePoint that I can find

~Withers

From: ThibaultB [mailto:notifications@codeplex.com]
Sent: Wednesday, September 30, 2009 11:19 AM
To: david@withersdavid.com
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

Actually it should be more like this.

  • Setspn -A MSSQLSvc/sqlserver.domain.tld DOMAIN\sqlserver
  • Setspn -A MSSQLSvc/sqlserver.domain.tld:1433 DOMAIN\sqlserver

I actually haven't had to run these commands since the SQL Server setup took care of it. You can also use ADSI Edit.

Read the full discussion online.

To add a post to this discussion, reply to this email (crmaccelerators@discussions.codeplex.com)

To start a new discussion for this project, email crmaccelerators@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Oct 1, 2009 at 8:15 AM

Make sure your users not only have access to the Sharepoint site but also have the appropriate permissions on the CRM BDC Application (in SharePoint). You can also check that when these users directly connect to the CRM they have access to the information.

Oct 1, 2009 at 2:26 PM
We changed from authenticated users to domain users with view or read rights EVERYWHERE. Also rebuilt the content. Magically working now!

David Withers


From: ThibaultB
Date: 1 Oct 2009 01:15:55 -0700
To: <david@withersdavid.com>
Subject: Re: Empty Business Data Lists and no search results [crmaccelerators:69742]

From: ThibaultB

Make sure your users not only have access to the Sharepoint site but also have the appropriate permissions on the CRM BDC Application (in SharePoint). You can also check that when these users directly connect to the CRM they have access to the information.