locked
Using ODBC link to connect MS Access to CRM 2011 RRS feed

  • Question

  • I need to be able to read data from the CRM 2011 SQL Server tables into MS Access tables so that we can check that the data in CRM is kept up to date with data in our "legacy" accounts system. I can set up an ODBC link on the server where the CRM is installed when logged in as administrator and use that to connect to the CRM SQLServer tables in MS Access, this works OK and I am able to copy data from CRM into an Access table. What I need to be able to do is to set up an ODBC link to read data in the tables from the users's PC (the user has a current CRM 2011 account)  however when I try to connect using ODBC the link fails I think because of permissions problems. I only need read access and want to ensure that the link does not have write access.

    Any help would be appreciated

    Thanks

    John

    Wednesday, June 27, 2012 5:31 AM

Answers

All replies

  • Hi,

    You will need to link to the Filtered Views (eg FilteredAccount) to ensure that users have access. These views implement security trimming so that they only return the records a user is allowed to see.

    Hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, June 27, 2012 5:41 AM
    Answerer
  • I understand that I need to use to the Filtered Views but I cannot establish the ODBC link from the PC which I need to do before I can see the filtered views  I need to select. On the server I can set up the ODBC link and see the Filtered Views but I cannot get it to work on the PC. Maybe I am not setting up the link properly. I am used to using MySQL and do not have any experience with SQL Server.

    Any further help would be appreciated

    John

    Wednesday, June 27, 2012 12:15 PM
  • Hi,

    What is the error you get when you try to create the connection? Also, does exporting to Excel as a dynamic view work? This creates a connection to the database within Excels connection manager.

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, June 28, 2012 7:26 AM
    Answerer
  • I have tried exporting to Excel as a dynamic view and I get a similar error message. Following is a copy. 

    Connection failed:
    SQLState: '01000'
    SQLServer Error: 10060
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
    (Connect()).
    Connection failed;
    SQLStat; '08001'
    SQL Server Error:17
    [Microsoft][ODB SQL Server Driver]SQL Server does not exist or access denied

    Thanks

    John

    Tuesday, July 3, 2012 9:11 AM
  • Hi John,

    Interesting - is your CRM SQL server in a DMZ or not accessible via routing firewall rules from client machines? Could that be why you can access it, but not your clients?

    You could try follow the suggestions in : http://support.microsoft.com/kb/328306

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Tuesday, July 3, 2012 11:40 AM
    Answerer
  • If I turn the firewall off on the server everything works OK so it looks like the problem is the firewall on the server, the server is a member server and is part of the domain.  I have looked at the article suggested but I don't fully understand how I should change the firewall settings to get this to work.

    Any assistance would be appreciated

    Thanks

    John

    Tuesday, July 3, 2012 2:55 PM
  • Hi John,

    Follow the instructions at: http://msdn.microsoft.com/en-us/library/cc646023.aspx to open the correct ports and protocols for remote SQL access.

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Marked as answer by JohnSwinson Thursday, July 5, 2012 1:39 PM
    Wednesday, July 4, 2012 7:50 AM
    Answerer
  • It is all working fine now, thanks for you help

    John

    Thursday, July 5, 2012 1:40 PM