Note:This topic was updated for 9.3.1.
The following information is covered in this topic:
As mentioned in the topic
Operating system authentication, operating system (OS) authentication is a method for identifying an individual user with credentials supplied by the operating system of the user's computer. Since SQL Server databases only run on a Windows OS, OS authentication for SQL Server is also referred to as Windows authentication.
Windows authentication offers some advantages over SQL Server (database) authentication. One of these advantages is that Windows authentication is generally more secure than SQL Server authentication since it uses a certificate-based security mechanism.
Windows-authenticated logins pass an access token instead of a name and password to SQL Server. The access token is assigned by Windows (Active Directory Domain or local operating system) when the user logs in. It contains a unique security ID (SID) for that user and the SIDs of any local or domain Windows groups to which the user belongs. This token's SIDs are compared to all the SIDs in either the sysxlogins table (SQL Server 2000) or the sys.server_principals system view (SQL Server 2005 and 2008). Based on the results of this comparison, the login is either granted or denied access to SQL Server.
Another advantage to using Windows-authenticated users is that it is usually easier to configure, because you can add existing Windows logins to the SQL Server. Finally, at connection time, the user is generally not required to enter a user name and password. A single sign-on at login provides access to all services that support Windows authentication.
Windows authentication is always the method used to connect to ArcSDE geodatabases for SQL Server Express. This is all done behind the scenes; no configuration is required on your part.
For ArcSDE geodatabases for SQL Server licensed through ArcGIS Server Enterprise, you have the option to use a SQL Server user name and password or a Windows-authenticated login to connect to the geodatabase. In both cases, there is some configuration needed. This topic covers the configuration for Windows-authenticated users.
Restrictions when using Windows-authenticated users
You should take into consideration the following restrictions if you use Windows-authentication with ArcSDE:
- You cannot connect through ArcSDE as a Windows user different from your present login. If you logged in as TERRA\Ian, you cannot make a Windows-authenticated connection as TERRA\Sylvia.
- Any Windows users who need to be able to create data must be granted access individually (not through a Windows group).
- The domain or server name and user name combination are unique. For example, TERRA\Sylvia is not the same login as MYCOMPUTER\Sylvia. If Sylvia is logged into her computer with the local login MYCOMPUTER\Sylvia, she will not be able to connect to remote databases that have her domain login, TERRA\Sylvia, listed as a user.
- You cannot restrict access to the %SDEHOME%\bin directory on the server if you connect through the application server. The application server spawns processes in the security context of the connecting Windows user. To correctly spawn the process, this directory must not be restricted to the connecting user. This is not a concern for direct connect Windows-authenticated connections.
- Not all ArcSDE clients support single sign-on, meaning some applications require you to type your user name to connect to ArcSDE services. Both ArcSDE and ArcGIS 9.2 and later releases support single sign-on.
- Every user in the Windows group that will create data must have a corresponding schema that is named the same as their Windows login; you cannot set up one schema for the whole group.
Connecting to an ArcSDE service as a Windows login
When you connect with Windows authentication to an ArcSDE service, you must be presently logged in as the ArcSDE service user. If not, your connection will fail.
With some ArcSDE clients, you can connect to an ArcSDE service without providing a user name or password. If you do not provide the –u /–p options when you issue the ArcSDE administration commands, Windows authentication is assumed and the command will be executed using the current Windows login. For example:
C:\>sdetable –o create –t authentication_test –d "colA string" –i 5151 –D Hydro
Successfully created table authentication_test.
The above command will create a table that will be owned by the currently connected Windows login. When no –u /–p options are provided, ArcSDE automatically passes the current authentication token to SQL Server instead of connecting with a SQL Server login. All ArcSDE administration commands function in this way.
Using Windows groups with ArcSDE geodatabases
A Windows group is a collection of logins who inherit their permission from membership in the group. Built-in groups exist locally, such as Administrators or Power Users, and user-defined groups can be created at the local and domain levels as well. Granting a Windows group access to SQL Server rather than individual Windows logins can simplify security. Rather than adding the logins to SQL Server individually, the group is added. All members of the group can log in to SQL Server. Their permission on the server and in individual databases is inherited from their group membership.
For example, the assessor's department wants to allow all members of the GIS department read-only access to some of their data. All the members of the GIS department belong to a domain group called TERRA\GIS. Rather than adding each member of the GIS department to the assessor SQL Server instance and configuring their permissions through a database role, you could add the TERRA\GIS group to the server and configure permissions for that group. Any member of the group could log in to SQL Server and view the data that the group had been given permission to see. If someone leaves the GIS department, their login is removed from the TERRA\GIS role and they can no longer access the assessor's SQL Server instance without having to make security changes on that server.
As noted above, if the users in the group will be creating data in the geodatabase, each login that will be creating data has to have its own schema in which to store the data. Each user's schema must have the same name as the user's login. You cannot create one schema that is named the same as the Windows group and have all the group members' data stored there.
ArcSDE 9.2 and later releases fully support authentication via Windows group membership. Logins who authenticate this way can perform the same operations as an individual Windows login.
There is no difference between creating an individual Windows login and adding a Windows group, so the how-to steps below apply to both.
In SQL Server Management Studio, perform the following tasks to add a login:
- Connect to your SQL Server Instance.
- Navigate to the Security folder, right-click Logins, and choose New Login.
- On the General page, type the full name of the Windows login you want to add or click the Search button to browse for a local or domain login or group. The Search button opens the Select User or Group dialog box, allowing you to query for local or domain users and groups. The user or group that you select on this dialog box will automatically be added to the Login-New dialog box.
- Optionally, you can configure the login's default database. By default, this is set to the Master database. A login's default database is what the login will connect to automatically if no other database name is supplied on connection.
- Optionally, you can map the login to a user in other databases. Under Select a page, choose User Mapping. Here, you can add the login as a user to one or more databases.
- Optionally, you can configure server-level permissions, including fixed-server role membership. To add the login to fixed-server roles
- Click Server Roles and select the role to which you want to add the user or group.
- To configure finer-grained server permissions, click Securables.
- Click the Add button on the Securables page.
- Select The server_name on the Add objects dialog box, and click OK. A list of explicit server permissions will appear. You can grant, revoke, or deny individual permissions to a login.
NOTE: No server-level permissions are required for ordinary logins or groups accessing ArcSDE geodatabases.
In SQL Server Enterprise Manager, perform the following tasks:
- Connect to your SQL Server instance.
- Navigate to the Security folder, right-click Logins, and choose New Login.
- Type the full name of the login or group you want to add, or click the ellipsis button next to the Name text box to open the domain user browser form.
- Select your domain or local machine in the List Names From drop-down list, and scroll to the Windows login or group listed in the Name text box.
- Click Add and click OK.
- Optionally, you can configure the login's default database. By default, this is set to the Master database. A login's default database is what the login will connect to automatically if no other database name is supplied on connection.
- Optionally, you can map the login to a user in other databases. Click the Database Access tab. Select the database or databases to which you want the new login to have access.
- Optionally, you can configure fixed-server role membership. To add the login to fixed-server roles, click the Server Roles tab and choose the role to which you want to add the user or group.
NOTE: No fixed-server role membership is required for ordinary logins or groups accessing ArcSDE geodatabases.
If you use an SDE schema geodatabase, you can use a Windows SDE login instead of a SQL Server-authenticated SDE login. Keep in mind that the ArcSDE Post Installation wizard will always create a SQL Server-authenticated SDE login. To work with a Windows SDE login, you must complete most of the postinstallation setup manually.
To use a Windows SDE login
- Create a Windows SDE login. This can be a domain login or a local Windows login on the server where SQL Server resides. Add this login to the SQL Server logins collection on your SQL Server instance that will host ArcSDE. This account will be the login that connects to SQL Server when the ArcSDE service starts.
- Using a SQL Server client, create a database. Add the SDE login to this database as a user.
The database user must be named SDE. When you create the user, make sure to remove the DOMAIN\ text from the user name.
- Grant the SDE user CREATE TABLE, CREATE VIEW, CREATE STORED PROCEDURE, and CREATE FUNCTION privileges.
- If you are using SQL Server 2005 or 2008, you must create a schema named SDE for the SDE user. This must be the SDE user's default schema.
For example:
CREATE USER sde FOR LOGIN WORLD\SDE WITH DEFAULT SCHEMA = SDE;
- Create the SDE repository. You must be logged in as the Windows SDE account to perform this step. Either log in to Windows using the SDE account, or if your operating system allows, right-click the Windows command prompt shortcut and choose Run as. Choose The following user and type in the login credentials of the Windows SDE login. Next, use the sdesetup command to create and populate the sde repository. For example:
sdesetup –o install –d SQLSERVER –D SDE –s gis1 –l license.ecp
The above command will create a repository in a database named SDE on a server named gis1. No user name or password was provided, so the connection to the SDE database using Windows Authentication. The command was run by the Windows SDE login, so all the objects created in the database will be on the SDE user's schema. The -l option allows you to specify a keycode file. If you do not already have a keycode file you can use the ArcSDE Post Installation wizard to perform the software authorization.
- Once the repository has been created and your software is authorized, start the ArcSDE Post Installation wizard.
- When the wizard opens, click the Custom button and click Next. The Select ArcSDE Setup Wizard Option dialog box opens.
- Deselect the first three options. Only the Create ArcSDE Service should be selected. Click Next.
- Choose DBO Schema on the Select schema dialog box. Even though you are creating a service that connects to an SDE schema geodatabase, you must create the service as if it were a dbo schema, providing the name of the Windows SDE login rather than the SDE schema default of a SQL Server SDE login.
- Proceed to the ArcSDE Service Information dialog box and type your service name and port. For ArcSDE DBA Login, type the full name of the Windows SDE login (e.g. DOMAIN\SDE). For ArcSDE DBA Password, type the password of the Windows SDE login. Type the database name you created in step 2 and verify that the remaining information is correct. Click Next.
- Click Finish when the service is created and started successfully.
