Use windows authentication instead of SDE when publishing Geodatabse

[SC_OC1]

Hello, I am not an end user but have been tasked with having users publish Geodatabases using windows authentication versus using the SDE username and password. Apparently, there is no ESRi article on how to do this as it is a Microsoft thing but support says this is the steps to be followed. In SQL Server Management Studio, navigate to Security\Logins. Right click on SDE and select new login, locate the user and ok to add. Is there any particular reason to right click on SDE when creating a new user when right clicking on Users and selecting new user brings up the same options? 

Not sure if this is in the scope of this posting but how is everyone assigning the user permissions when using Windows authentication? We found this KB but it does not seem to work for us. Hoping someone can post their script or query that they are using. It is for 10.8 so it may not even be relevant.

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/windows-authenticated-sde...

Windows 10 workstations with ArcGIS Pro 3.3.0. Windows Server 2022 with SQL Server 2019.

Thank you!

[George_Thompson · Accepted solution]

The easiest way is to create an AD group of the users that you want to assign view and / or edit permissions to; https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/add-users-sqlserve...

"You can grant a Windows group access to SQL Server rather than individual Windows logins, which simplifies creation and management of logins. All members of the Windows group can log in to SQL Server. Server, database, and dataset privileges granted to the group automatically apply to every member of the group. However, be aware that you cannot create one schema to store the data created by all the group members. Each user in the group who creates data in the geodatabase must have his or her own schema in which to store the data. SQL Server creates a user and schema in the database the first time a group member creates data. This happens automatically; do not create a schema and user manually."

I would also recommend that the data is NOT owned by the Windows users as it causes some weird naming conventions and issues if they leave the organization.

I am not following your workflow for using the SDE user to add additional logins. Is this related to assigning permissions to the users?

[SC_OC1]

Hi George,

Thank you for the information.

In regards to SDE, the end users are all sharing the same SDE user account so when they publish there is no way for us to track it back to the user. 

Steve

[George_Thompson]

Roger. I would not recommend sharing the SDE user credentials. This is not a best practice and gives the users elevated privileges.

[SC_OC1]

Agreed, that is why we are trying to get away from it. Just looking for someone that is already doing it. Maybe just put in a brief summary of what they did and how to apply the permissions to the database based on the following article...

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/windows-authenticated-sde...

[George_Thompson]

Look at the link I sent for how to do this....... the link you have above is for adding and SDE user for enabling an Enterprise Geodatabase.

You basically add the AD group in SSMS, map it to the DB you want. Then in the Pro client, right click on the FC's you want to, and assign the permissions to the AD groups that you just added.

[SC_OC1]

Thank you George. My apologies, what is FC by chance? I've checked with a couple of Pro users and they also seem unsure. Thank you!

[George_Thompson]

FC = Feature Class 😁

[MarceloMarques]

Hello @SC_OC1 

You can find more details on how to setup the SQL Server Geodatabase in my database guide book for Production Mapping and if you download my database template scripts for SQL Server you will find even more advanced configuration examples, like setting up a Windows Domain Account to access the SQL Server Geodatabase as an editor user or viewer user. I do not recommend using the Windows Domain Account to be a Geodatabase data owner. Please, see my community.esri.com blog below.
Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Now, if you want to publish an ArcGIS Server Service that will connect to the SQL Server Geodatabase using a Windows Domain Account via Windows Authentication, then you need to create a generic domain account and then have the ArcGIS Server Windows Service to run under that domain account, that is the first step.
ArcGIS Server account—ArcGIS Enterprise | Documentation for ArcGIS Enterprise

Then, in the SQL Server Geodatabase grant access to your domain account and to the ArcGIS Server Domain Account.

Example:

USE pm
GO
/*-------------------------------------------------------------------------
-- Create ACME\greg6313 as editor
-------------------------------------------------------------------------*/
CREATE LOGIN [ACME\greg6313] FROM WINDOWS WITH DEFAULT_DATABASE=[pm], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_adduser 'ACME\greg6313'
GO
EXEC sp_change_users_login 'update_one','ACME\greg6313','ACME\greg6313'
GO
EXEC sp_addrolemember N'pm_editor', N'ACME\greg6313'
EXEC sp_addrolemember N'raster_editor', N'ACME\greg6313'
GO
---------------------------------------------------------
/*-------------------------------------------------------------------------
-- Create ACME\greg6313 as viewer
-------------------------------------------------------------------------*/
CREATE LOGIN [ACME\greg6313] FROM WINDOWS WITH DEFAULT_DATABASE=[pm], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_adduser 'ACME\greg6313'
GO
EXEC sp_change_users_login 'update_one','ACME\greg6313','ACME\greg6313'
GO
EXEC sp_addrolemember N'pm_viewer', N'ACME\greg6313'
EXEC sp_addrolemember N'raster_viewer', N'ACME\greg6313'
GO
---------------------------------------------------------

Next, you will create a Geodatabase Connection File using the Windows Authentication option.

Then, you will create the ArcGIS Pro map document and will add the layers using the Windows Authentication Geodatabase Connection File.

The last step is to publish the ArcGIS Server Service.

I hope this clarifies your question.

[SC_OC1]

Thank you both for taking the time to respond. End up having to bite the bullet and get support to hold my hand through the process. But your responses were helpful.