DirectQuery SQL Server - Option Set Labels, and Visuals all Blank. Infrastructure help!

[Gingerjeans88]

Hi all,

Really need some help with this. 

Building a report for a client in Desktop, based on their on-premises CRM data. Their CRM is newly on a dedicated server and have a domain trust in place - we remote onto their powerbi server to build reports. 

They have a strong desire to connect using directquery (we advised of the limitations) when building reports themselves, and also to view all option set labels. A colleague, who has since left, advised them that to do the latter they needed the domain trust in place so they can authenticate with the SQL server using Windows credentials. We have made all of these changes, gone in to build the report, and the visuals are all blank. I can see that it has loaded the 'Filtered' tables, which is halfway to being what we want, but when I go to add one of the option set fields in as a filter I can't see any of the labels, all I get is 'ALL'. 

HELP! My questions are:

- Is it possible to see option set labels (not the values) with the above setup

- What level of SQL database access does a user, wanting to build a report, need to have? We have tried read/write and dbo owner. 

They are growing tired of waiting!

[v-lionel-msft]

Hi @Gingerjeans88 ,

Can this test user complete queries in the database by SQL code?
Can visuals be created in Power BI in "Import" mode?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gingerjeans88]

Hi there @v-lionel-msft  - yes, to both! I am utterly stuck. All of the other reports for this client are done in import mode with zero issues. 

[parry2k]

@Gingerjeans88 users need to have at least read access and the ability to connect to the SQ Server, it is like giving someone access to run the select statements in SSMS.

The query you are using in Power BI if you run the same query in SSMS or other ways, do you get any data? It is not very clear what the underline problem is? Can you share the M code for the table.

[Gingerjeans88]

Hi @parry2k , thanks so much for your response! 
The user has SQL server access and read permissions, as it's a test user so we were playing around with access. This is a dynamics CRM SQL server, does that make any difference if the report authors are permissioned CRM users? 
I'll share the M code in the morning. It's just so bizarre that I can connect to the server but not see anything. Should be plenty of access to enable visibility of data!

[Gingerjeans88]

@parry2k any further ideas?