locked
connect to crm database views RRS feed

  • Question

  • I am having trouble connecting to the crm database.  I can do it through sql manager no problem.

    When i try and pass a connection string i always get no results.  guessing it is something to do with the crnm security -- any suggestions on how to get around this

    Tuesday, February 23, 2010 3:49 PM

Answers

All replies

  • It seems that your requests are made under Network Service account. Try to query from simple view (not filtered) or try to change execution context. Check following:

    http://msdn.microsoft.com/en-us/library/ms188354.aspx
    http://msdn.microsoft.com/en-us/library/ms181362.aspx
    Truth is opened the prepared mind

    My blog (english)
    Мой блог (русскоязычный) free counters Locations of visitors to this page
    Tuesday, February 23, 2010 3:57 PM
    Moderator
  • I need to connect to the views

    I went with integrated security and impersonated a crm user and it worked

    s there a better way?

    Wednesday, February 24, 2010 4:09 PM
  • When you talk to the views did you use dbo.filteredview ???
    When you do this you must have admin rights to contact the sql database.
    In this case remove dbo. and just use filteredview.
    The mind i just like a parachute, it only works when open.
    Friday, February 26, 2010 10:45 AM
  • Im linking the crm database with another database in a stored procedure so i have to use the dbo.filteredview -- any way around this?
    Friday, February 26, 2010 2:45 PM
  • You can try this

    select * from FilteredAccount
    inner join newDB.dbo.yourTable on ....

    and You need to create database role in your database newDB CRM_ReportReader with member YOUR_DOMAIN\ReportingGroup and alter permission on newDB.dbo.yourTable
    Friday, February 26, 2010 2:56 PM
  • Maybe this helps:

    When I connect as 'sa' to the database, I receive no results from the FilteredViews because of the security context.
    To run the query in a context of an existing user in CRM you can do:

    -- Change context

    IF SUSER_SNAME() = 'sa'

    BEGIN

      DECLARE @BinVar varbinary(128);

      SELECT @BinVar = CAST(SystemUserId as varbinary(128))

        FROM SystemUserBase WHERE FullName = 'A Username';

      SET CONTEXT_INFO @BinVar;

    END;

     

    -- Your query

    SELECT TOP(100) * FROM dbo.FilteredAccount;

    Friday, February 26, 2010 3:34 PM