locked
What is the correct method to access data from Filtered Views via queries located on linked servers? RRS feed

  • Question

  • What is the correct method to access data from Filtered Views via queries located on linked servers?

    Currently, we have Custom CRM reports on APACSQL06 which need to modified to substitute data from MSCRM.

    Attempting to access Filtered Views from APACSQL06 yield no data.

    Currently we have set up CRM DB SQL Server APACSQL07A as a linked server to APACSQL06 (Report Server Front End).

    We have set up an account dbLinkedServer which can access data from the tables in MSCRM, however this user has no access to the Filtered Views.

    Is there a method to pass the credientials from the acutal user running the report to MSCRM, or should we bypass security and allow a generic user account full access to all data? When MSCRM attempt to access and run the custom reports published thru MSCRM web interface the following error is generated. “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’”

    I have reviewed this article below which looks very similar to the issue we are experiencing.

     

    http://www.changingera.com/2009/08/13/how-to-run-filtered-views-in-ms-crm-through-asp-net/

     

    Testing Steps to Reproduce via SQL Queries:

    From APACSQL07A: select count(*) from apacsql07a.cmg_mscrm.dbo.FilteredNew_profitcentre Result: 134 rows.

     

    From APACSQL06: select count(*) from apacsql07a.cmg_mscrm.dbo.FilteredNew_profitcentre Result: 0 rows.

    Monday, August 8, 2011 8:20 AM

Answers

  • It should be possible to pass the users' credentials through the Linked server, but this will require AD delegation to be setup - the error “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” implies that you don't have delegation setup yet.

    Alternatively you could setup the linked server to use a generic account. This may be easier to setup, but is less secure. For this to work, the generic account will have to be a CRM user with one or security roles to allow it the read CRM data


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Monday, August 8, 2011 12:45 PM
    Moderator

All replies

  • Are you using the same active directory user for the calls from the different servers?  

    Otherwise, I am wondering what credentials the one server is really using to call the other server because ultimately this is going to boil down to security because the filtered views are doing a check on your active directory credentials to determine what you have access to before displaying the information.  For this reason, I would think that if you are using a SQL account like SA that it wouldn't work for sure and that you would have to use windows authentication only and the AD user would have to have access to the specific data in CRM.  


    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Monday, August 8, 2011 10:46 AM
    Moderator
  • It should be possible to pass the users' credentials through the Linked server, but this will require AD delegation to be setup - the error “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” implies that you don't have delegation setup yet.

    Alternatively you could setup the linked server to use a generic account. This may be easier to setup, but is less secure. For this to work, the generic account will have to be a CRM user with one or security roles to allow it the read CRM data


    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Monday, August 8, 2011 12:45 PM
    Moderator