Excel not getting data from SQL Server

Question

We have dedicated cloud server hosting SQL Server 2014 and Microsoft Dynamics CRM 2016 which is working without issues.

I have got a query that gets data out of the FilteredOpportunities view that I can run in SQL Server Management Studio as a windows user but not a SQL User (displays only column names), when I try to run it in SQL Server Management Studio on my laptop using the sql user (can't use windows authentication as it is not on the same domain), it only returns the column names and nothing else.

The account has every permission that you could possibly think of granted to it to try and get the data out but it wont budge.

Answer 1

You cannot query CRM's filtered views with a SQL-user. For that you will need to use a windows user.

If you want to query directly with a SQL-user, you can use the underlying dbo.OpportunityBase-table. This will not apply the CRM security model.

Instead, you might want to consider using one of the CRM webservices for supported access to data with the security model enforced. If your purpose is to get data into Excel, it is directly supported to use the web services. See Export to an Excel dynamic worksheet.

Answer 2

I have found the answer. A bit clunky bit it works. I can conenct to the database with a SQL user (because i cant use windows authentication as I am on a different domain) then I can add code to the top of my exsisting code to impersonate a user with the rights to read the data. For those wondering the code is:

DECLARE  @uid uniqueidentifier

SET @uid = convert(uniqueidentifier, '((UID of the user you want to impersonate))')

SET CONTEXT_INFO @uid