Can Active Directory groups be used for SDE database roles in 10.2.2? (SDE schema)

[FionaRenton1]

Can Active Directory groups be used for SDE database roles in 10.2.2?

We are using SLQServer 2012 with the SDE schema.

[RebeccaStrauch__GISP]

It may help to share this with Geodatabase  since, although ArcSDE is grouped with ArcGIS Server, I think the Geodatabase people might have more info on SQL permissions.

[SusanJones]

Yes, AD sure is supported, Best make sure SQL Server is set up for mixed mode authentication.

Active Directory should be encouraged ahead of sql logins because of security concerns and audits.

Susan Jones

Auckland Transport

[FionaRenton1]

It appears that you can use AD for GIS readers, but not control editors or table creators this way.

We needed to create GIS database roles and schemas. If anyone else has managed to use AD for editing using the SDE schema let me know. It seems you can only use AD to control access to the .sde connection file for editing and writing GIS files.

[ShelleyNuth]

We have set up AD Groups for GIS editors in order to allow them to edit a group of feature classes stored in a feature dataset.  We've created Logins and Users in SQL (SQL Server 2012) which tally with the name of the featuredataset.  I have set the privileges at the featuredataset level in ArcCatalog.

We give editors access to the edit version of our the geodatabase through layer files (created from a Windows Authentication SDE connection file. For one editor in one AD group this is working well.  But I have an issue with another user in a different AD group.  This other AD group contains two users plus myself and a testuser, one user and my testuser (and me as admin) can edit through the layer file but one user can't.   I've checked and rechecked everything several times so cannot work out why one user loads the layer file and gets a red exclamation mark, so it's not even pulling the data from the version into ArcMap.  All our GIS users have read only access to the geodatabse through layer files and the user can see the data in those, and this user is able to see data as read only, which means the connection to the default database is fine.  It appears that the WinAuth connection which should give the editable access to a version of the default is not working. 

I ran event profiler in SQL and compared to a test user with the same AD group membership and can see differences in the event items.   One event shows a connection to the vesion.sde and the other doesn't.  I can't figure out how to resolve this issue.  Does anyone have any suggestions?

[MartinAmeskamp · Accepted solution]

Hi Fiona,

I came across the same problem (SQL Server 2012, ArcGIS 10.3) and decided that it is difficult to map AD groups to SQL Server entities. Here's what we came up with:

• Using AD groups for read access is not problem. Actually, we decided to map AD groups to SQL Server users rather than roles since the grouping that database roles provide is already there courtesy of AD.
• Using a group login as SDE owner (either through dbo or through user SDE) can't be done: SQL Server won't allow a group login to be database owner, and if you try to map a group login to user owner, SQL Server goes and creates a AD login for the actual AD user that's accessing the database through the group login.
• Using group logins to create db objects (e.g. tables) can be done, but again, SQL Server goes and enters the AD user rather than the AD group as owner, so that you'll probably run in to all sorts of privilege issues when using this approach.
• We came to the conclusion that we have to use database logins for SDE ownership and for table creation, and AD groups for editing and viewing access.

Martin