How do you implement "headless" users on enterprise geodatabases in SQL Server?

MKF62 · ‎12-07-2023

I am doing a lot of reading about how to properly set up and configure user accounts in an SQL Server instance of an enterprise geodatabase, namely these three documents:

We will be implementing an SDE-schema geodatabase. The SDE user will only be responsible for system tables and updates (and have the db_owner role). They will not be loading data and I'm trying to figure out how to create a "headless" user for that purpose. That way, if someone leaves our organization, the data sitting in our database isn't directly tied to that person's login. I believe this is possible, but I'm struggling to wrap my head around how SQL Server logins vs database users (and schemas) work.

This is what I'm envisioning. I would like a user on the database that can create and "own" all the data, this is called "DataOwner". There are three people who have their own logins, Billy, Bob, and Joe. I think those logins should be mapped to DataOwner so whenever they login, if they create a new feature class, it ends up being named MyGISData.DataOwner.FeatureClassX.

If I included making different roles for creation, editing, and viewing data, the diagram would end up looking more like this. You could have any number of SQL server logins mapped to a database user which is mapped to a role that has a particular set of privileges:

Is this possible? Is it advisable? If it's not, how do you get ahold of the data owned by someone else when that person leaves the organization? Or are people just creating logins and users like below and just letting multiple people use the same credentials (doesn't seem like a good security practice)?

I think most people set up their database something like this, but then you wind up with ownership of feature classes being tied to specific person which I'm trying to avoid:

George_Thompson · ‎12-07-2023

The simplest way I would recommend to accomplish this the following:

Recommend removing db_owner role from SDE, except for upgrade process
- May want to grant VIEW DEFINITION to SDE if not already done
- https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/privileges-sqlserv...
Create a Database user (Not a domain account - see comment from @MarceloMarques ) with the GP tool: https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/create-database-user.htm - you must use a connection that SYSADMIN server role
- This can be done for multiple data owners as needed
For the users that will view / edit the data, I recommend using Active Directory (AD) groups so that you do not have to manage user names / passwords for each person.
- You just add the group in ArcGIS Pro with the correct permissions (View or Edit)
- If they leave your company their account is turned off and you do not have to do anything.

You just need to make sure that you secure the PW to the SDE and Data Owner accounts. If a person leaves the company, it may be best practice to update the PW for those users as needed.

This should prevent issues with data ownership being a "free for all" in your organization.

All the "management" of the tables will be done by the data owner, to simplify administration of these tables.

There may be some other ways, but this has been successful in the past.

Hope this helps!

--- George T.

MKF62 · ‎01-17-2024

In this scenario - if not sde, who would have the db_owner role? The non-domain account you create to own the data?

George_Thompson · ‎01-17-2024

I usually do not assign any user the db_owner role. I only provide it to SDE user for upgrades.

--- George T.

MarceloMarques · ‎12-07-2023

Hello @MKF62

I do not recommend using a domain headless account as a data owner user in the SQL Server Enterprise Geodatabase, there are many reasons for that, but let's just say this is not a good practice for security and maintenance purposes, instead I always recommend to my customers to create a normal SQL Server user to be the data owner user, and if you want users to connect to the Geodatabase with their Windows Domain Login, then you can add those logins to SQL Server and add each domain login to the database and grant a database role with permissions to edit and view the data. This is a much better approach.

Note, you can also create an Active Directory (AD) Group for editor users and another for viewer users, then add the AD Group as a SQL Server Login, yes this is possible, then you add the AD Group as a user in the SQL Server Database, then in the database you create a database role for editor and another for viewer and then grant the proper role to the user. This way if someone needs editor access or viewer access you just need to go to the AD Group and add the user Domain Login into the proper AD Group. I have done this setup before and it works fine with Geodatabases in SQL Server.

You can find these best practices and a lot more in my community.esri.com blog below.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Read the Production Mapping Database Guide Book for SQL Server.

You will also find my Database Template Scripts for SQL Server that you can download and use as a template to create your own configuration.

Also read the Database Connections Best Practices, there is important information there.

You can also use Contained Database Users instead of SQL Server Database Logins, this way you will not need to map SQL Server Logins to SQL Server Database Users anymore, read more in the article below that I have published.

How to Move the SQL Server Enterprise Geodatabase with a database backup

DanaNolan · ‎12-28-2023

In my very secure environment, password-based connections are considered gross security risks. Our Active Directory is kept up to date, seriously so, so no one who leaves or violates policy keeps rights. There are also firewalls everywhere.

So when I talked to Esri many moons ago about the SDE account (database authentication), we discussed setting up an Operating System Authenticated service account that could function as SDE. Then the network people would just change the name/directory credentials associated with that account if needed. But that account would have to own the schema, which would have meant rebuilding the database. Maybe some day.