This is the third post in the series and I am going to dedicate this post to how to access GP data in terms of describing some of the options and factors to consider. Very little of this will have "how to" information about Power Query, but I will continue that in the next post.
The previous posts in the series so far are:
In my last post, “Starting from scratch”, I used exported SmartLists as a data source to demonstrate that we don’t necessarily need to connect Excel or Power BI to our SQL data directly. While that approach works, it’s cumbersome because it relies on exporting new versions of the SmartList to update the data when we want to “refresh” it. Starting with today’s post, I will talk about how to connect to GP data directly to be able to refresh with the click of a button instead and save ourselves the time of running a SmartList and exporting it first.
When it comes to accessing data, there are three primary things to discuss:
- Data source options in Power Query
- Authentication options, for a given data source
- Data security considerations, who should be able to see what
Data source options
In both Power BI and Excel, the 3 most common options to connect to Dynamics GP data are:
- From Database > SQL Server - connect directly to data in SQL
- From Other Sources > OData feed - if OData has been configured in the GP environment
- From Other Sources > Web - if APIs have been created to access the data
I will be focusing on Database (SQL Server) connections as I imagine it is the method most customers would choose. OData is an option that needs to be installed and configured and provides some other options I may delve into after this series is done. I will not be covering APIs in the context of Dynamics GP at all.
Screenshot 1 shows where the option is to Get Data from SQL. On the Data menu, click on Get Data, then From Database, then From SQL Server Database.
Screenshot 2 shows the SQL Server database settings window.
- In the Server field, type in the server and instance name in the format of "server\instance".
- The Database field is optional.
- Leave it blank to browse all the databases available, otherwise, enter a database name.
- Advanced Options
- I chose not to expand the Advanced Options section in this screenshot. In there, there is an option to provide the query to use in Power Query.
- Leaving this blank will allow me to browse the different tables and views in the databases that I have access to.
Sometimes when I am connecting to SQL, I enter a database and also expand the Advanced Options section in this window to enter an actual SQL query, I just won't be doing that in this example.
After clicking OK, an Encryption Support message may appear (the first time connecting to a given SQL server). The message is a warning that the data source is not encrypted. In my experience, most customers do not have an encrypted connection set up for their Dynamics GP SQL server, and thus, they would get this message. Clicking OK tells Power Query it is ok to access the data source via an unencrypted connection.
SQL authentication options
The three basic authentication options for connecting to SQL Server are Windows (aka using the user's network login), Database (aka SQL Login & Password), or Microsoft Account. I can't think of an example of an on-premises SQL server where the Microsoft Account option would be relevant so I won't be covering that.
Screenshot 1 shows the credentials window, with Windows, Database and Microsoft Account listed on the left-hand side. This specific screenshot uses the Windows authentication option, most often the default choice of "Use my current credentials" is the option to select. This is what I recommend in most circumstances as it ties access to whoever is logged into that particular machine or workstation.
Screenshot 2 shows the same window, but displays the Database option, aka SQL Login and Password. This would be appropriate if it's a report on a shared device, as the "logged-in user" may not be the user with access to the data. In general, I dislike this option unless the data being shared is neutral and safe for any audience since it's very easy for a report to be shared with users who were not intended as the audience.
One thing to note: Dynamics GP user logins are encrypted so a user is unable to authenticate in an external tool with their Dynamics GP login and password. That means a GP user cannot use their GP login as the User Name/Password in this case. It will fail to authenticate. A Database login means a separate login created in SQL based on SQL Authentication.
Screenshot 3 applies to either of the above scenarios, the "Select which level to apply these settings to" drop-down list. This is a common thing one will see in various credentials windows in Power Query as it is not specific to SQL Server. If a database name was entered on the previous window, two options will appear in this drop-down list and it is effectively asking at which level the provided credentials are valid: at the server level or the server and this specific database level. My recommendation is to connect at the highest level available (in the context of SQL Server) as a user will only see the SQL objects to which they have access anyway. By highest level, I mean "Server", vs. "Server;Database".
To demonstrate that Power Query only shows a user what they have access to, here is a comparison of what the results are for two different users.
Example 1 - authenticating with a sysadmin server role
In example 1 below, I have authenticated via a user with sysadmin privileges on the server (the highest permission possible on SQL Server). I can see every non-system database here (GP or otherwise) and every object beneath that if I were to expand each database. I have expanded the bottom database as an example, a GP company database with standard tables and views which may look familiar to readers.
Example 2 - authenticating with limited access
In example 2 below, I've authenticated with a user with limited access. In my case, this user only has access to the GP-related databases (DYNAMICS, TWO, TWOCA), and within "TWO", they only have access to the Payables Transactions SQL view. What is interesting is I see all of the databases, regardless if I have access to any of them in SQL; however, I can only expand the ones I have access to, and it will only show me the SQL objects that I have been permitted to view.
Nuances to note here:
- The "expand/contract" triangles to the left of the various elements of the hierarchy visually show what is expanded or contracted.
- I have "expanded" DYNAMICS, TWO and TWOCA databases, indicated by the black triangle, and see nothing in 2 of the 3 of those databases because I have not been granted access to any SQL objects there.
- I am unable to expand the "MISC" database, as indicated by the white triangle.
- If I try to expand "MISC", it eventually returns me to the authentication window above to request what credentials should allow me to view objects in that database. So, even though I can see the database listed, I cannot view data in it.
Data Security considerations
For the sake of the remainder of this discussion, what I am about to say applies to whichever method of authentication is chosen above. The data security considerations related to what the user could see in the above "limited user" example, depend on how access is granted. I think in terms of "if a user read this post, did what I described above, what would they see and am I ok with what they can access?". That is the context I think about when designing reports and access to data for a client. Ultimately it's not my data but I/we have a responsibility to be proper custodians for who can access it.
Permissions in SQL for something like reporting are typically referred to as "SELECT" permission which is the ability to read the contents of a table. In some SQL views, even in some out-of-the-box Dynamics GP SQL views, there are functions utilized which at times will require "EXECUTE" permission to retrieve the value from the function. My simplistic example above with the user who can only select from 1 view gets this error when attempting to load that view's data. "SELECT" permission on that view is not enough to allow them to get the data. In this example, I could grant "EXECUTE" permission on the object "encodeUrlString" in the DYNAMICS database and then they could use the view (assuming there are no other functions embedded beyond this one).
There are many options for actually managing security.
- Granting a user a server role (not recommended, I believe it is too broad for this purpose)
- Granting the user one or more database roles in specific databases (this also may be too broad for a report consumer, but may be a valid option for report creators/authors)
- Granting access specifically to the objects in the report as needed. I use this option where possible.
Risk tolerance
Many clients I work with are Dynamics GP customers that also run HR or Payroll. That immediately makes data access an important topic to discuss and understand. Payroll data is not the only confidential data in an ERP, but it’s one of the more obvious ones. Everyone’s level of acceptable risk will differ, and it's a conversation to have with the client or with the custodians of the data.
I am not going to get into PII (Personally Identifiable Information) in this post, but that helps define what is and is not confidential data in the context of employment records. For some, anything in HR or Payroll might be off-limits regardless if it is PII or not.
I try to use the principle of least privilege when designing or granting access to data or reports. That means where I can, only the bare minimum is assigned to the user in terms of access rights, and in this context that would mean only granting access to the specific tables or views in the report and nothing else. The downside is that it can be more difficult to maintain.
The other consideration I take into account is whether a report needs to access tables containing PII or other sensitive data even if I don't need to use that specific data. The question to ask is if the query has one or more tables in it that contain sensitive data, and a user figures out how to query the entire table outside of the context of what the provided query is, is that acceptable? For example… I have written reports that are for supervisors reviewing time (labour) booked to jobs or projects, or absence-related data for information to help them approve timesheets for example. That type of report requires some level of employee data, but I tend not to grant them access to the underlying employee table directly, which would allow them to theoretically query the raw table and pull out a SIN/SSN, or other data if they so chose to do that.
In those instances, I recommend creating an SQL View with the data needed and using the View in Power Query instead of building the query from multiple tables in Power Query directly. It is easier to limit what fields of data are visible and only grant access to the view itself, without granting access to the underlying table(s).
Identifying who needs what
I often lean towards using groups to manage access vs. granting individual users access to read SQL data. I try to look at the reporting needs, see if there are logical groupings I could recommend based on common areas or common data requirements, then, I grant the access to the group. (In this context I am referring to Active Directory (A/D) groups).
In SQL itself, the A/D group can be added as a "SQL Login" using Windows Authentication, and then permissions can be assigned to that group via roles or direct grant statements as necessary. The users in those A/D groups inherit the permissions assigned, and can then view whatever has been granted.
For report authors or those managing the server and security, one approach to keep in mind is to include a method to check access others have from time to time. For simplicity, what I tend to do is have an SQL login or two that has the same permissions as one of the groups I'm writing reports for. When I create a report with my own permissions, I want to test that report with the login of someone who will be running it to make sure it works first and foremost, but also to make sure there are no unintended data leakages where they can see more than they should.
Standard database roles
Out of the box, there are standard database roles a login can be granted, such as db_datareader. This seems "safe", it's just "reading" data, right? Yes, if there is no sensitive data, that role may be simplest to grant. It does not, however, address the situation above where some standard views use functions that require EXECUTE permission on them. It would work well if all views were created without functions or if the reporting relies only on tables.
Dynamics GP creates database roles within the company and system databases which can be considered. DYNGRP is the key role for all "GP" logins to have, which allows the ERP to operate - but that role grants all functions to a user and should not be granted to a non-GP login in my opinion (at least not if the use case is reporting). A user with DYNGRP is granted permission to create, update, delete, insert etc. in all GP tables.
The other Dynamics GP roles are rpt_* roles in the company and system databases. These are a better option as a starting point. I wrote about those here, specifically how to figure out what is included in those roles. Those roles are what could be assigned to a user or group login in SQL and also give access to the out-of-the-box SQL (SSRS) reports or Excel refreshable reports for example.
Technically the rpt_* roles could be edited to remove things where there may be access concerns and grant other things to it, keep in mind that every time the environment is updated/upgraded with service packs, there is a chance those roles can be re-written back to the "out of the box" versions they started as. Ideally for the most secure scenario, either create specific database roles and/or grant permissions as needed to the proper users. Roles are easier to manage overall and are more visible for other users to see who has what.
Summary
Once again, this post is getting long so I will stop at this point. There is a lot to consider when it comes to data access and I'm sure I've barely scratched the surface here. I may or may not have answered questions with this post, chances are readers might now have more to think about!
