topic Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ? in Geodatabase Questions

ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

AlbertoAloe — Fri, 14 Jul 2017 14:20:45 GMT

Hi guys.

I'm experiencing something weird when connecting a fresh ArcGIS 10.5.1 installation to a geodatabase in sql server. Basically, the geodatabase does not retain previously set privileges on database objects according to the following scenario:

A user (data viewer or data creator with his own schema), able to access geodb objects owned by other users from ArcGIS 10.4/ ArcGIS 10.5, cannot access them any more despite permissions at sql server level are ok (the user can access them through SSMS).

A user with SELECT privilege at schema level (let's say for dbo) cannot view item in ArcGIS 10.5.1 that were previously consumed from ArcGIS 10.4/ ArcGIS 10.5

Further details as follows:

It does not apply to dbo
Enforcing SELECT privilege at ArcGIS level (in ArcCatalog) works fine and makes the object available to the user
This behavior does not depend on geodatabase schema version but only on ArcGIS client version (10.5.1)
ArcGIS Pro 2 behaves the same way while ArcGIS Pro 1.4 was working fine

I'm using ArcGIS 10.5.1 /ArcGIS Pro 2 against sql server 2014

thanks

Alberto

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

RexRobichaux2 — Fri, 14 Jul 2017 20:46:09 GMT

Hi Alberto,

Thanks for bringing this behavior to light. After a quick test, it appears I can reproduce this behavior in 10.5.1 / SQL Server 2014 geodatabases. Below is the simple workflow I used to test this out- please let me know if you see any inconsistencies or differences from an equivalent workflow that you have used:

Created a new test viewer database user named "tenfiveone" in a SQL Server 10.4.1 (and 10.5.1 geodatabase)
Mapped this user to the 10.4.1 and 10.5.1 (testing for consistency) geodatabases in SQL Server
Granted the tenfiveone user select privileges within each gdb
Created a connection in ArcCatalog 10.3.1, and 10.5 for the tenfiveone user to each database- could see all feature classes visible once connected.
Created a connection in ArcCatalog 10.5.1 for the tenfiveone user to each database- no feature classes were visible until select was granted explicitly by the data owner within ArcCatalog to the data.

Notes from further testing:

This doesn't appear to be reproducible in Oracle geodatabases as the privilege grants are object based by default.
As noted this behavior was reproducible in Pro 2.0 as well
granting object based permissions on the DBMS side for example "grant select on [sde].[TESTFC] to tenfiveone;" opposed to "grant select to tenfiveone;" has the same effect as ArcCatalog dataset level grants, therefore, object based grants workaround the issue.

Kind regards,

Rex R

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

AlbertoAloe — Mon, 17 Jul 2017 07:57:34 GMT

Rex,

more or less I did a similar test.

I imagine that the issue will be fixed at next release (10.6)...

Ciao

Alberto

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

RexRobichaux2 — Mon, 17 Jul 2017 14:07:16 GMT

Hello Alberto- thank you for that confirmation. In response to this behavior which you highlighted- the following defect has been logged:

BUG-000106595: ArcGIS Pro 2.0 and 10.5.1 Desktop require explicit object-level select privileges for users to view SQL Server geodatabase data in the Catalog tree.

Thanks again for bringing this behavior to light- please feel free to reach out to me if you have any questions or additional concerns!

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

RexRobichaux2 — Wed, 30 Aug 2017 18:55:40 GMT

For anyone experiencing this issue- it has been addressed as part of a new ArcGIS 10.5.1 (Desktop, Engine, Server) SQL Server Permissions Patch: ArcGIS 10.5.1 (Desktop, Engine, Server) SQL Server Permissions Patch

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

DougGreen — Fri, 23 Feb 2018 22:49:22 GMT

We have this same issue. However, our situation is that we have some features in the database that are not to be viewed by those that have not been made aware of a non-disclosure agreement. So we use active directory groups to grant viewing privileges to different sets of data. I just got off of the phone with support and she indicated that this patch may only work for explicit user permissions and not groups. I tested by adding an explicit permission to a feature class for a particular user and she was correct. Using group permissions nothing is visible but with a specific user granted permission, the only thing visible in that database is that one feature class. Is this also being addressed? We are not able to move to 10.6 because of other extensions that are not yet compatible. So that would mean a rollback to 10.5.

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

RexRobichaux2 — Tue, 27 Feb 2018 16:08:25 GMT

Hello Doug,

Thanks for highlighting this behavior in relation to AD Groups in SQL Server at 10.5.1. I just wanted to confirm from your post- do you currently still have a support ticket open? I believe this behavior is being investigated further and it appears some customers are experiencing issues using AD Group logins within SQL Server geodatabases, although the exact culprit has not yet been identified. It also appears some customers are not experiencing issues after applying the patch with AD Groups so it is likely due to environment-specific permissions (groups / role membership permissions, etc.). This is still being actively investigated and the hope is to have more information shortly. I hope this information helps!

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

DougGreen — Wed, 21 Mar 2018 21:43:39 GMT

Hi Rex,

Thanks for your reply. It is good to know it is actively being researched. Our support ticket #02071470 was closed, due to the fact that we could explicitly grant each user permissions to each individual feature class or table and no other fix was available. For us, that meant that we rolled back to 10.5 since our other engineering extensions did not support 10.6. For now, this has got the majority of our users up and running. However, we are now seeing this behavior in the latest release of ArcGIS Pro (2.1.2). I have to explicitly grant permissions to each user for each feature class or table. That's not really going to work for us moving forward because that's hundreds of items to manually a good number of users to. If you have any pull to get this issue resolved, that would be really helpful.

Just to more clearly state the issue: gdb items stored in sql server with privileges set to an AD group are not visible to members of that group when connecting to the database in ArcGIS Pro 2.1.2. We are in SQL Server 2012. Our GDB is still at version 10.4.1 because we are not able to upgrade until a few remaining clients are able to upgrade.

Thanks,

Doug

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

RexRobichaux2 — Thu, 22 Mar 2018 12:49:28 GMT

Hi Doug,

I'm, sorry to hear you are still hitting some permissions issues at 2.1.2. Just to confirm- have any modifications been made to the public server role in this geodatabase? Specifically, do you know if any privileges have been manually revoked from the public role? There was a specific defect logged recently for AD / SQL Server permissions, however after further investigation, it was found that the geodatabases where this issue was reproducible had modified/revoked permissions from the public role. I just want to ensure that's not the case here.

If there have been no privileges modified for the public server role in this geodatabase, it sounds like a new defect needs to be logged for Pro 2.1.2 (and possibly for 10.5.1 or 10.6). I'd recommend either requesting to reopen the #02071470 case- citing that this is still an issue at Pro 2.1.2, or (it may be faster) to request to have a new case created specifically for this behavior. I hope this is helpful, and please just let me know if I can be of any help with the process!

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

BrianFausel — Thu, 22 Mar 2018 15:51:39 GMT

For what it's worth, here are some observations possibly related to this issue that I had in our antique and unsupported environment:

SQL Server 2008 R2 geodatabase is not supported in ArcGIS Pro, but one can add LYR files with pointers to SQL 2008 feature classes and they show up and work in Pro version 2.0 and earlier.
Starting in Pro 2.1 and also in 2.1.2, these LYR files stopped working (layers appear with red exclamation point and claim invalid data source).
Our LYR files were originally created using a database-authenticated connection, connecting through a generic "GIS_User" login. It's a bad legacy design we are planning to move away from.
If I create a new LYR file with OS-authenticated login connection using ArcMap, this LYR file does indeed come in without issues into Pro version 2.1.2. In this case, I didn't even have to explicitly grant permissions to my login on each feature class.
We are not affected by this problem in ArcMap versions 10.2.2 or 10.3.1 and have not yet upgraded to 10.5.1 or higher. We also do not have general users in Pro yet -- we are waiting until the database can be upgraded to a supported version before rolling it out.

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

DougGreen — Fri, 23 Mar 2018 21:35:22 GMT

Rex Robichaux‌, thank you for your additional info. If I understand you correctly, you wanted to confirm that we did not modify the default database role "public"? Correct, it appears as shown below:

I likely will create a new case for this.

To further the confusion, there are other enterprise geodatabases in the same instance using the same active directory group, that we are able to view data in. That started me thinking it was something to do with the database. Then I tried importing a map into ArcGIS Pro that contained layers from the database that we cannot view layers in and it still shows all the layers without red exclamation points. However, it does take a bit longer to open a map containing layers from that database.

Re: ArcGIS 10.5.1 & ArcGIS Pro 2 bug connecting to sql server ?

DougGreen — Tue, 19 Jun 2018 15:45:59 GMT

I found a solution to our problem and felt the need to share it here.

What ended up fixing the issue was to drop and recreate the login for the AD group at the instance level and then also drop and recreate the user at the database level for that AD group. I am not exactly sure how this solved the issue but I needed to do it for another issue and we discovered it fixed this issue as well.