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.

Are you saying SSMS never returns the correct results when run on any machine/account, or is only your laptop that experiences the problem? — Dan Guzman, Dec 20 '16 at 11:00
No matter the account SSMS only returns the column names. I don't have another machine available to test it on. — Sean Mullen, Dec 20 '16 at 11:08
Are you authenticating with SQL Server using the same credentials when running this on the server (when it works) and when running this on your laptop (when it doesn't work)? — 3N1GM4, Dec 20 '16 at 11:16
I have just tried to use it on the server under the user I want and it won't work. My apologies I seem to recall that it worked. For clarification I have a user called crmadmin that is a windows user with owner permissions that can get the data and a view user that is a sql user with owner permissions. — Sean Mullen, Dec 20 '16 at 11:27
I have corrected my question above to reflect the comment I have just made. — Sean Mullen, Dec 20 '16 at 11:28
@SeanMullen, based on your updated question, this appears to be due to application level security that filters data based on the account being used. I know nothing about Microsoft Dynamics CRM to suggest a fix since this is related to the application security rather than SQL Server security. — Dan Guzman, Dec 20 '16 at 11:32
@DanGuzman I would love to think it was CRM based however I can't even select top 1000 out of the view. — Sean Mullen, Dec 20 '16 at 11:36
@SeanMullen, I think you can't select from the view because data are being filtered based on account by one or more of the objects referenced by the view. — Dan Guzman, Dec 20 '16 at 11:41
The CRM Filteredview's query is applying security trimming. You need to be connected to SQL with a Windows account that is configured as a user in CRM in order to get any records to return. — Nicknow, Dec 20 '16 at 23:19

Henrik H · Accepted Answer · 2016-12-20T13:07:55.720

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.

score 0 · Answer 2 · edited Sep 01 '17 at 21:07

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

Excel not getting data from SQL Server

2 Answers2