web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Dynamics Database - Di...
Microsoft Dynamics CRM (Archived)

Dynamics Database - Direct Access?

(0) ShareShare
ReportReport
Posted on by Community Member

What are the pros/cons of directly accessing the Dynamics SQL Database as a means of "integratting"?

Rather than crafting plugins, striving to use REST or using WCF etc, can we open a connection to the DB and run queries etc directly against the databse?

I know this is not recommended but does anyone do it and if so are there any web articles about this?

Thanks

Korp.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: Dynamics Database - Direct Access?

    Hey Korp,

    Reading from SQL isn't a big deal, but I'd recommend against writing to it. Here are my thoughts:

    Reads:

    - You want to make sure to query with "no lock". This will prevent locks in the database which will hurt performance

    - Reading directly from SQL won't take into account security roles. This may not be an issue for you depending on your setup.

    - you'll get incredible speed vs the SDK

    Writes:

    - You can totally do it, but there are a million ways to corrupt your data. There are a ton of fields the CRM sets that you'd need to account for. If you miss a field or set a picklist to a invalid number, you'll get errors in CRM that will be really tricky to find.

    I know users who have done SQL just for some reads, some who have written entire websites pulling directly via SQL. None of them are writing records as part of an ongoing process though.

    If this helps, I'd appreciate if you could mark this as a verified answer.

    Thanks,

     Aiden

  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: Dynamics Database - Direct Access?

    If you were to query SQL directly, it's really simple. There are Views for each entity so the SQL you'd end up writing will be really straightforward, something like:

    select firstname, lastname, account.name
 from contact
  left join account
   on contact.parentcustomerid = account.accountid


    The only place where things get difficult are around activities and activity parties (all the to/cc/bcc values).

    Good luck!

  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: Dynamics Database - Direct Access?

    The only issue with using the filtered views directly in SQL is that you need to authenticate to SQL as the user who is logically requesting the data, in order to limit records they don't have permission to access. If you're using a standard connection string then it would give you permissions to the records corresponding to that user every time.

    EDIT: edited to fix a typo

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics Database - Direct Access?

    Can we create our own views and use them to retrieve data?

    harihar

  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: Dynamics Database - Direct Access?

    Yes. It's just a sql db so you could add anything that sql allows.

  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: Dynamics Database - Direct Access?

    @Guido, absolutely agree. I was answering in the context of the original post, what's recommended vs what's possible.

    @harihar, one big downside of customizing the database directly is that it can make your next CRM upgrade more difficult because it may not know how to handle any changes it's not expecting.

  • Community Member Profile Picture
    Community Member on at
    RE: Dynamics Database - Direct Access?

    Thank you Guido and Aiden for your feedback on what is supported /recommended.

    @Guido, one follow up on the index - what type of create/updates are supported?

    Thank you both once again.

    harihar

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Community Member Profile Picture

Community Member 2

#1
HR-09070029-0 Profile Picture

HR-09070029-0 2

#1
UllrSki Profile Picture

UllrSki 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans