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

[AlbertoAloe]

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

[RexRobichaux2]

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:

1. Created a new test viewer database user named "tenfiveone" in a SQL Server 10.4.1 (and 10.5.1 geodatabase)
2. Mapped this user to the 10.4.1 and 10.5.1 (testing for consistency) geodatabases in SQL Server
3. Granted the tenfiveone user select privileges within each gdb
4. 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.
5. 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

[AlbertoAloe]

Rex,

more or less I did a similar test.

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

Ciao

Alberto

[RexRobichaux2]

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!

[RexRobichaux2 · Accepted solution]

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 

[DougGreen]

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.

[RexRobichaux2]

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!

[DougGreen]

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

[RexRobichaux2]

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!

[DougGreen]

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.