SQL Server, SDE and Active Directory

3340
4
10-28-2013 04:32 AM
RoyMartin
New Contributor
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.

Or, is it still an issue that ArcSDE still maintains a 1:1 relationship in object ownership, as mentioned in this thread?
0 Kudos
4 Replies
MarcoBoeringa
MVP Regular Contributor
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.


I think you need to map that AD group to a SQL server database level role, not the SDE user / schema. You should never store your user data in the SDE schema.

I think Vince Angelo gave enough hints in that other thread you linked to get you going, especially:

"Keep in mind that they're tables, not files. Tables have to have an owner. The group
grants access, but the user owns the tables. Best practice is to create one or more users
to own the tables, then grant access to users (or groups) through roles.
"


and:

"Local security guidelines would of course take precedence, but the owner account would
generally be a "headless user"
, and therefore better map to a DBMS login (named for
the theme, department, or data source, e.g., "Basemap", Transportation", "USGS").
The password would be closely held, to make sure that it was only used by the data
librarian for changes to content and database design that have been approved for
use within the system."


And as the Help page Adding Windows-authenticated logins or groups to a SQL Server database also states about the specific case of Windows groups:

"For Windows groups, a matching schema will be created for any group member the first time the user creates data in the geodatabase. The schema name will be the same as the login name, not the name of the group. This is done automatically; you do not have to create the schema ahead of time."

So for Windows groups, a user schema will be automatically created once a user that is part of the group, creates data in the geodatabase.

Lastly, and especially, see Shannon Shields remarks regarding the SQL Server specific issues in this post in that other thread.
0 Kudos
RoyMartin
New Contributor
Thanks for your reply...However, my inquiry was only for the sde user and not for data owners.

But, to elaborate further, we would like to set up data owners in the same fashion...AD Group at instance for authentication, then mapped to headless db user/schema at the db level.
0 Kudos
MelissaJarman
Esri Contributor

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. Please see her example:
http://forums.arcgis.com/threads/74560-Feature-Names-in-SDE-Geodatabase?highlight=sql+server+active+...

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...


0 Kudos
RoyMartin
New Contributor
Thanks Melissa!

This is good information for our IT staff!

Regards,

Roy
0 Kudos