Setting up users and roles in SQL Server

Microsoft SQL Server is one option for storing your users and roles. You can use either the full SQL Server (2000 or 2005 version) or the SQL Server 2005 Express edition. If you have a version of SQL Server installed, you can use it to store your users and roles. If not, an option is to install SQL Server Express, which is included with ArcGIS Server, as described below. Once installed, you can allow Manager to create a database within SQL Server for users and roles. If you already have a database with the correct schema for ASP.NET membership and roles, you can use that database when configuring users and roles in Manager.

Install Microsoft SQL Server 2005 Express

Although SQL Server Express is included with ArcGIS Server for the Microsoft .NET Framework, it is not installed with the software. If you installed the ArcSDE for SQL Server Express package, then SQL Server Express was installed as part of the process. If you do not yet have SQL Server Express installed, perform these steps to set up the software to use for storing users and roles. This step is not required if you already have the full version of SQL Server installed and want to use it for your users and roles.

Verify that Microsoft SQL Server 2005 Express is not installed:
1. Open Add or Remove Programs from the Windows Control Panel.
2. Scroll down to find an entry for Microsoft SQL Server 2005. If not present, close Add or Remove Programs and skip to the next step.
3. If this item is present, click the Change button. On the Maintenance panel that opens, check whether SQLEXPRESS: Database Engine is listed. If not, SQL Express is not installed. If it is installed, skip the rest of the steps for installing SQL Server. Close Add or Remove Programs.
On the installation disc for ArcGIS Server for the Microsoft .NET Framework, open the folder SQLServerExpress. (You can also download and install SQL Server Express directly from Microsoft, but those steps are not listed here.)
In the SQLServerExpress folder, run SQLEXPRE.EXE.
Follow the prompts on the installation wizard to install SQL Server Express. Change the default settings for location and other options if appropriate for your organization.
If your ArcGIS Server installation is on a separate computer from the SQL Server Express machine, ensure that the SQL Server Browser service is running and set to start automatically. To check, open the Services panel (click Start, then click Run, type in services.msc and click Enter), and scroll down to find the SQL Server Browser service. Start the service if it is not running. If the Startup Type is not Automatic, set it by right-clicking on the service and clicking Properties. In the Properties window, use the General tab to set the Startup Type to Automatic. Click OK to save the setting. You can then close the Services window.

Once SQL Server is installed and running, you must use Manager to configure security. You must also add users and roles, as described in Managing users and Managing roles.

Specify SQL Server as the location for users and roles

In order to use the settings wizard to specify SQL Server for your users and roles, the Manager user must have permissions for the SQL Server to list all databases in the instance, and if necessary, to create a new database and schema. Typically this means that the Windows account used to log in to Manager must also be added as a login for SQL Server, and be granted sysadmin or dbcreator status in Server Roles for the SQL Server. If you cannot log in to Manager with such an account, you may use the alternate instructions below for creating the database and specifying the membership provider manually.

Ensure that the user account for logging into ArcGIS Server Manager has permissions to create databases in the SQL Server instance. Typically, if using SQL Server Express, the user is an administrator on the computer, and Manager and SQL Server are on the same machine, the permissions will be already set. Otherwise, use the SQL Server Management Studio application to add the account as a login at the instance level of SQL Server, and grant the login sysadmin or dbcreator status in the Server Roles list. The connection in Manager to SQL Server uses Windows authentication rather than a SQL database login.
Start ArcGIS Server Manager, expand the Security tab in Manager and click Settings.
Click the Configure link.
On the Location for Users dialog box that opens, choose SQL Server. Click Next.
On the dialog box for specifying settings for SQL Server, type the database server name in the Server text box. If using SQL Server Express, add "\SQLEXPRESS" to the end of the name (or if you used a different instance name when installing SQL Server Express, use the appropriate instance name).
Click Connect to display a list of available databases and options.
- If the connection fails with a message similar to:
  
  Login failed for user \'<user name>\'.
  
  or
  
  Login failed for user \'\'. The user is not associated with a trusted SQL Server connection.
  
  then check that the the account used to log in to Manager has privileges to connect and administer databases on the SQL Server instance. For other errors, check to ensure your database server is running and available on the network.
After the connection succeeds, use the option Use Trusted Connection to define how applications, including the ArcGIS Server Web services, will connect to the SQL Server database. This connection is also used to create and edit users and roles in Manager. Note that this account is not used to create the database if you choose that option, or to create the tables used to store users and roles. Instead, the account used to start Manager is used for database and table schema creation.
- If Use Trusted Connection is checked, then applications will use Windows authentication to connect to the SQL Server. This is the standard method for SQL Server Express, and may also be used with full SQL Server. In this case, the account that runs the Web application will be used to connect to the database. In most cases, this will be the account that runs the ASP.NET process. On Windows 2000 and XP, this is normally the ASPNET account. On Windows Server 2003, Vista and Server 2008, this is usually the Network Service account. Manager will add this account to the SQL Server logins if necessary upon finishing the wizard. If the ArcGIS instance is configured to run in a fixed identity in an IIS application pool, then you may need to grant that user account privileges on the SQL Server database (at minimum, Membership_FullAccess and Roles_FullAccess role membership). Note that if the ArcGIS Server instance is on a computer running Windows 2000 or XP, and the SQL Server is on a different computer, trusted connections may not work. This is because the ASPNET account is a local account with different passwords on each computer. One symptom is if an error occurs when attempting to add or edit users or roles. In this case, you may need to re-run the security wizard and use a SQL Server login instead.
- If Use Trusted Connection is unchecked, then enter a SQL Server login that will be used to connect to the database. SQL logins are supported with full SQL Server. This SQL login must have already been created in the SQL Server instance. You do not need to grant this login sysadmin or other database-wide privileges. The login must have, at minimum, Membership_FullAccess and Roles_FullAccess privileges on the database used to store users and roles. You must grant these privileges with your SQL Server management tools. If the database does not yet exist, you can temporarily grant the account the sysadmin role, and then after Manager creates the database, remove the sysadmin role and then grant the Membership_FullAccess and Roles_FullAccess roles. If the database exists but does not yet have the ASP.NET user and roles schema set up, you can temporarily grant the account db_owner privileges on the database, and then after completing the security wizard, change the account's roles to include just Membership_FullAccess and Roles_FullAccess.
If you have not yet set up a database to store roles, click the option to create a new database and type a name (such as "aspnetdb"). If you already have a database set up for ASP.NET membership and roles, click the option to use an existing database, and select it from the drop-down list. If the database was not created by the Manager wizard, see the note below for Using an existing user or role database.
The Add anonymous role to database option adds three generic roles to the database: Everyone, Authenticated Users, and Anonymous. You can use these roles to enable anyone or, alternatively, only users with a valid login to access one or more services. Adding these roles does not automatically allow anonymous users but makes the generic roles available should you choose to permit them. For more details on using these roles, see Securing Internet connections to services. These roles are used only with Web services and not with Web applications. Click Next.
On the next panel, you have the option to configure a mail server for password recovery. If configured, these settings will enable users to recover or reset lost passwords. If no mail server is configured, the recover lost password link will not be available. For more information on the lost password functionality, see Managing users. To configure the mail server, type the name or IP address of a mail server available for sending password recovery messages. This server must operate with the standard Simple Mail Transport Protocol (SMTP). Set the port if necessary (default SMTP port is 25). If necessary, type a user name (typically configured as an e-mail address) and password for the server. Most mail servers require an account login in to send messages, in order to prevent spam. The account credentials will be encrypted in the configuration file. Also, type a From address to appear in the From field of the e-mail.
Click Finish to save the settings and return to the main security settings panel.
If you have not yet installed a Secure Sockets Layer (SSL) certificate for your Web server, you should do so now. By default, the token service that authenticates users in SQL Server requires connections using HTTPS, which is enabled with a SSL certificate. For development purposes, you can disable this requirement. For details and instructions, see Configuring the token service.

On Windows Server 2000, if you get an error when attempting to add users or roles, you may need to add the ASPNET account manually to the SQL Server database. To do this, follow steps 1 through 9 in the following section on Alternate instructions for manual setup.

Using an existing user or role database

If you have an existing SQL Server database with users and roles, you can use that database for users and roles for ArcGIS Server services and applications. The database must comply with the standard ASP.NET Membership and/or Roles schema. For example, the database may have been created with the approach described below under creating the database manually.

To use the existing database, you may need to configure the application name or ID. In a SQL Server Membership or Role database, users and roles are assigned a unique application ID. The application ID is associated with an application name in the aspnet_Applications table in the database. You can have multiple application names in a database. This enables sharing one database among several Web applications, with each Web application authenticating users in the database with a specific application name. This way, the users for one Web application will not be allowed access to Web applications tied to a different database application name.

Manager and ArcGIS Server services and applications by default will use an application name of esriags. This is different from the default for application name defined for ASP.NET in its machine.config file. That default name is /. You may also have defined a different application name for your Web application using the pre-existing database. Users created for these different applications will not be visible to other applications.

For example, you may have created a SQL Server database for users and roles prior to installing ArcGIS Server. If created with the ASP.NET defaults, the application name for your users and roles will be "/". You then install ArcGIS Server and use Manager to configure security to use your existing SQL Server database. After configuring security, you go to the Users panel. None of your existing users will be displayed by default. This is because Manager sets the application name for users to esriags, and will not see users with the default name of "/".

To allow ArcGIS Server services and applications to use existing users and roles, you need to coordinate the application name used by ArcGIS Server with the one in your database. To coordinate the application name, do one of the following:

Set the application name for ArcGIS Server to the one used by your database. This is the recommended option for most users. To use this option:
1. Follow the steps to configure security to use your SQL Server database as described in the section above, Specify SQL Server as the location for users and roles.
2. Obtain the application name used for your users and roles. You can use the SQL Server Management Studio application or other appropriate tool to examine your database. The application names are listed in the aspnet_Applications table. If multiple application names exist, check the Users and Roles tables for the application ID used, which corresponds to an entry in the Applications table.
3. Use a text editor (such as Notepad), XML editor or Visual Studio to open the web.config file in the Security folder of your ArcGIS web applications instance. By default this path is C:\Inetpub\wwwroot\ArcGIS\Security.
4. In the web.config file, find the <membership> section in the <system.web> section. Within the <add> tag that defines the SQL provider, find the applicationName="esriags" attribute. Change the value esriags to the application name you found for your database. Save the web.config file.
5. Repeat the previous two steps for the web.config files in three additional folders inside the ArcGIS web applications folder: Rest, Services and Tokens. Save each web.config file after making the changes.
6. Restart Manager, log in, and go to the Users panel and to the Roles panel. You should see the users and roles previously added to the database. You can then assign permissions to services based on these roles and their users.
Alternatively, you can configure the application name in your database and any existing applications that use its users and roles to the name used by ArcGIS Server (esriags). This option is recommended only if you have already used Manager to add users and roles with its default application name (esriags). To use this option, use SQL Server Management Studio or other tool to edit the database. Open the Applications table and copy the ApplicationId value corresponding to the esriags application name. Then open each table for users (Membership and Users) and roles (Roles). In each table, change the ApplicationId for each user or role to match the ID value you copied from the Applications table for the esriags application name. Then, in any existing Web applications using users in the database, edit the web.config file to change or add the applicationName attribute in the <membership> provider to esriags (i.e., applicationName="esriags").

Alternate instructions for manual setup

If for some reason you cannot use Manager to create the membership database, or you want to create it separately, you can use any other standard means for creating the database and schema for the membership database. The following is an outline of steps for Microsoft SQL Server Express using the .NET Framework tools. For details, consult ASP.NET documentation.

Installing Microsoft SQL Server Express 2005 and creating the database manually

Install Microsoft SQL Server Express 2005 as described above.
Install Microsoft SQL Server Management Studio Express. This can be downloaded from http://msdn.microsoft.com/vstudio/express/sql/download/.
Start SQL Server Management Studio Express from Start > (All)Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express.
On the connection dialog, type "<computer name>\SQLEXPRESS". Click Windows Authentication from the Authentication drop-down list and click Connect.
Add the ASP.NET account to the authorized logins:
1. In the main program window, expand the Security folder.
2. Right-click Logins and choose New Login.
3. On the login dialog, type "<computer name>\ASPNET" as the login name (or use the Search button to find the account). Click OK to add the account to the Logins list.
4. If the SQL Server is on a separate computer from ArcGIS Server, add the ArcGIS Server computer to the logins, using (b) and (c) above. Specify the computer name by appending a "$" character at the end. If the computer is a member of a domain, include the domain name. For example, for a computer named GIS1 in a domain called city, enter "city\GIS1$". Note that you will not be able to browse to view computer names. You must enter the name manually.
Add the new database for membership:
1. In the main program window, right-click Databases and click New Database.
2. On the database dialog, type a name. The standard name for the ASP.NET membership database is "aspnetdb". A different name can be specified to create a database only for ArcGIS Server users.
3. Click OK to create the new database.
Authorize the ASP.NET accounts to access the new database:
1. In the main program window, expand Databases > new database's folder > Security folders.
2. Right-click Users and click New User.
3. On the new-user dialog box, type "aspnet" for the User name.
4. For the Login name, type "<computer name>\ASPNET" (or use the ellipsis (...) button to find the account). Click OK to add the user.
5. In the Database role membership area, check the db_owner item.
6. Click OK to add the user.
7. If SQL Server Express is installed on a separate computer from ArcGIS Server, also add the ArcGIS Server computer as a login. Repeat (b) through (f) above, but in step (d), add the computer name, appending the character "$". If the computer is a member of a domain, include the domain name, for example, "citydomain\gisserver1$".
Exit SQL Server Management Studio Express.
Use the ASP.NET SQL Server Wizard to set up the membership schema in the database:
1. Open a Microsoft .NET SDK command prompt from Start > (All)Programs > Microsoft .NET Framework SDK 2.0 > SDK Command Prompt.
2. At the command prompt, type "aspnet_regsql.exe" and press the <Enter> key. This opens the ASP.NET SQL Server Setup Wizard.
3. On the wizard, click Next twice (keep the defaults).
4. At Select the Server and Database, type the server name and add "sqlexpress" to specify the SQL Express, for example, "MYSERVER\SQLEXPRESS".
5. Leave authentication at Windows authentication, click the Database drop-down list, then click the database you created earlier (for example, aspnetdb).
6. Click Next, then click Next again to create the schema.
7. Click Finish to close the wizard.
8. Close the command prompt by typing "exit" and pressing <Enter>.

Specifying the SQL Server membership provider manually

Normally you specify the SQL Server database as the membership provider using Manager, following the directions above. If necessary, you can set the database provider manually using the instructions in Setting up users and roles in a custom provider. When setting the provider, point the membership provider to the SQL Server database. Use standard ASP.NET configuration syntax for configuring the membership provider. After you configure the provider, be sure to use Manager to choose this provider as described in Setting up users and roles in a custom provider.

Managing the SQL Server membership database manually

When using SQL Server as the location for users and roles, a standard database is used for ASP.NET membership and roles. If desired, external tools for managing this database can be used, although ESRI cannot support these external tools. You should ensure that if the external tool adds users or roles, it uses the correct application name for the user or role. For more on the application name for users and roles, see the section above on Using an existing user or role database.

One tool commonly used to manage users and roles is the Web Site Administration Tool (WSAT). This tool is available from Visual Studio (or Visual Web Developer Express) after opening the Web site by going to Website > ASP.NET Configuration. To manage users and roles with WSAT, open the /ArcGIS/Security Web application in Visual Studio, then open WSAT. The WSAT tools can be used to add, edit and delete users and roles.

WSAT and other tools cannot be used to manage permission rules for GIS services. Those permissions are managed internally by ArcGIS Server. For information on how services store permissions, see "Setting permissions for a service or folder" in Securing Internet connections to services.