Security

By default, users can view Excel reports and data connections only if they have administrative credentials on the server that is running the SQL Server and if they have access to the network share. Since this isn't a normal setup, users typically need reporting privileges in the SQL Server before they can view the Microsoft Dynamics GP data that is displayed in data connections and Excel reports.

There are three areas of security around Excel reports deployed to a network share or local drive:

• Security to the network share/local folder
• Security at the database level
• Security around Excel

We'll spend a few minutes on each one.

Network share security

Realistically, network share security is normally going to be set by a network administrator. To make a shortcut for administrators, the minimum required security on the shared folder is:

• Change option for the share tab
• Read option for the security tab

Now, for those of you who want the version that is longer than a (as Mark Polino would say) Latvian wiener dog, follow these steps:

1. In Windows Explorer, right-click on the folder where you deployed the Excel reports and then click on Sharing and Security…
2. On the Sharing tab, click on Advanced Sharing… and select Share this folder.
3. Click on Permissions.
4. If the user or group already exists in this window, you can skip to the next step. Otherwise, follow these steps:
   • Click on Add…
   • In the Select Users, Computers, or Groups window, enter the group or users to whom you want to provide access to the shared reports
   • Click on OK
5. Select the user or group to apply permission to in the Group or user names area.
6. Select the Allow checkbox for the Change permission and then click on OK. The Change permission is the minimum required permission.
   
7. Click on the Security tab.
8. In the Groups or user names area, click on Add.
9. If the user or group already exists in this window, you can skip to the next step. Otherwise, follow these steps:
   • In the Select Users, Computers, or Groups window, enter the group or the users to whom you want to provide access to the shared reports
   • Click on OK
10. In the Groups or user names area, select each group or user, and then click on the permission that you want the group or the user to have. The minimum required permission is Read.
11. Click on OK.

Tip

These instructions will vary depending on the version of the Windows server used on the network or the user's version of Windows on a local drive. If you are unsure about setting this up, consult your IT department.

By default, Dynamics GP 2016 deploys reports related to each company and each functional area in their own network folder. This makes it easy to apply different permission levels to sensitive areas such as payroll.

Database-level security

Access to information in the Dynamics GP 2016 database is handled a little differently. A set of fixed security roles is created automatically in the SQL Server when Excel reports are deployed. All of these roles start with rpt_. These roles provide access to the underlying tables and views. The process to assign security is to add a user or group to the SQL Server and give them access to the appropriate roles. The users that get added are not Dynamics GP users. They are either SQL Server users (different from the GP login IDs) or active directory users and groups.

Tip

To connect the SQL role with an Excel report to ensure that a user has appropriate access, you really need the spreadsheet from Microsoft that links the two together. You can find it at https://mbs.microsoft.com/fileexchange/?fileID=e4bb6958-0f07-4451-b72c-f02784e484df.

This spreadsheet is from version GP 10, but it still works for GP 2016.

In our example, we need access to the Account Summary Default Excel sheet. This sheet uses the Account Summary view. On the spreadsheet, we see a number of roles that include the appropriate access:

For our example, we'll give a user access to the rpt_accounting manager role. In practice, it's not unusual to add all GP users to a single active directory group and give that group access to all the fixed reporting roles. This is particularly true for companies that don't use payroll and that don't have other sensitive reporting requirements.

To grant database permission using the built-in roles, we have to add the user or group to the SQL Server and then assign the appropriate role(s).

To add a user to SQL Server, follow these steps:

1. Open SQL Server Management Studio and log in using either Windows Authentication or SQL Server Authentication.
2. Go to Security | Logins.
3. Right-click on Logins and select New Login…
4. Click on Search.
5. Enter the domain and user you want to add or enter the group that you want to add to the SQL Server. For my example, I'm entering my domain and user name—Njevity\ballen. This could also be a group of users such as GPUSERS, for example:
   
6. Click on Check Names to validate the entry and click twice on OK to finish.

The user has now been added to the SQL Server. Our example used a domain user, but you can also set up a SQL user. In general, a domain user is preferred, because it eliminates the need for the user to manage multiple logins and passwords for reporting. Using a domain login also provides additional control to administrators. If an employee leaves, for example, removing them from the domain removes both their network access and their reporting access in one step.

To grant access to the reporting roles, follow these steps:

1. Go to Security | Logins, double-click the user or group that you just created.
2. Select User Mapping on the left-hand side.
3. In the upper-center section labeled Users mapped to this login:, select the box next to the company that you want to grant report access to. For our example, select TWO.
4. In the lower-center section named Database role membership for: TWO, select the box next to rpt_Accounting Manager:
   
5. Click on OK to continue.

The user now has rights to access the TWO AccountSummary default report that we've been working with and any other reports available as part of the rpt_Accounting Manager role.

Excel 2016 security

As you connect with database connections in Excel, a security bar may pop up with the message SECURITY WARNING External Data Connections have been disabled:

This is an Excel security feature designed to prevent malicious code from running without the user's knowledge. In our case, however, we deployed the reports. We are now running them on our network and controlling access. This is about as secure as it's going to get, and the message is really annoying for users. Let's turn it off.

To disable the Excel security message for these files, follow these steps:

1. Open Microsoft Excel 2016 and go to File | Options | Trust Center.
2. Go to Trusted Center Settings | Trusted Locations.
3. Click on Add new location.
4. Browse to the location where you deployed the Excel reports. In my example, I used C:\GP2016XL. Click on OK.
5. Select the box marked Subfolders of this location are also trusted and click on OK:
   
6. Click on OK twice to exit.

Now, when you run the Excel reports in the next section, the reports will open in Excel 2016 without the security warning.

Note

Microsoft offers a great knowledge base article on Excel reports and security at http://support.microsoft.com/kb/949524 for GP 10, but this portion of security remains the same.