SQL Server, SDE and Active Directory

Question

Hello,

We are in the process of setting up several SQL Server Geodatabases; and, are faced with IT restrictions at the SQL Server security level.  Namely, no database/mixed-mode authentication.  All connections must come via Active Directory.

The approach we would like to take, given these restrictions, is as follows:

For the SDE geodatabase owner:
1.  At the instance level, grant an AD group access to the instance.
2.  At the db level, map that AD group to the sde user/sde schema in the db/gdb.

Active Directory users could then be mapped to the AD group and administer the geodatabase.  If their role changed, they could be removed from the AD group.

AndreaB_ · Answer

This is a great post! However, it's 11 years old. Does anyone have any current knowledge they could point me to - we're looking into the same sort of workflow - we're changing from logging in as sde as the db authentication to using Windows log-ins to our sql geodatabase. I'm wondering if we need to create a brand new sql geodatabase and load or copy/paste in the data. We're using ArcGIS Enterprise 11.1 with ArcGIS Pro 3.1. Thanks.

RoyMartin · Answer

Thanks Melissa!

This is good information for our IT staff!

Regards,

Roy

MelissaJarman · Answer

Roy -

As stated in the forum posting "Feature names in SDE geodatabases" toward the bottom, Shannon references having to explicitly add the AD login to the instance and then mapped to the database with matching user/schema names. She notes that you can't do this with Windows groups.

For the SDE user: 
We found out through our Support incident work that you are using one of our 'Alternative setup scenarios' where his windows login is mapped to the sde user/schema within the database to populate the system repository tables - You just need to ensure that this account stayed mapped to the sde user/schema in that database to populate repository records as data was loaded into the geodatabase. I suggested a dedicated windows login for this that would not be changed at a later date - we needs something dependable to be mapped to sde user/schema for these databases/geodatabases. I do not believe AD groups will be a solution for the mapping to SDE user/schema and an explicit login will be needed to be mapped to this user/schema for the system repository tables.

Using a Windows-authenticated sde login in SQL Server - 10.2
http://resources.arcgis.com/en/help/main/10.2/index.html#/Using_a_Windows_authenticated_sde_login_in_SQL_Server/002q0000002t000000/