Desktop Help 10.0 - 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 or the SQL Server Express edition. If you have a version of SQL Server installed, you can use it to store your users and roles. If not, SQL Server Express, is included with the Workgroup level of ArcGIS Server, as described below. Once installed, you can allow ArcGIS Server 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 ArcGIS Server Manager.

License:

Microsoft SQL Server Express is not included with the Enterprise level of ArcGIS Server for the Microsoft .NET Framework.

Install Microsoft SQL Server Express

Although SQL Server Express is included with ArcGIS Server for the Microsoft .NET Framework (Workgroup level only), it is not installed with the software. If you installed the ArcSDE for SQL Server Express package, 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.

Steps:

Verify that Microsoft SQL Server Express is not installed:
1. Open Windows Control Panel > Add or Remove Programs.
2. Scroll down to find an entry for Microsoft SQL Server. If it is 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 (Workgroup level only), open the ArcSDE_Workgroup folder.
Within the ArcSDE_Workgroup directory, you will see a 32-bit folder and a 64-bit folder. Open the folder that corresponds to your operating system environment and run SQLEXPR.exe. For example, if you are using a 64-bit operating system, such as Windows Server 2008 R2, open the 64-bit folder and run SQLEXPR.exe. You can also download and install SQL Server Express directly from Microsoft, but those steps are not listed here.
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, click Run, type services.msc, then press ENTER) and scroll down to find SQL Server Browser. Start the service if it is not running. If Startup Type is not set to Automatic, right-click the service and click Properties. In the Properties window, use the General tab to set 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 ArcGIS Server 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

To use the settings wizard to specify SQL Server for your users and roles, the user must have permissions for 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 ArcGIS Server Manager must also be added as a login for SQL Server and be granted sysadmin or dbcreator status in Server Roles for SQL Server. If you cannot log in to ArcGIS Server Manager with such an account, you can use the alternative instructions below for creating the database and specifying the membership provider manually.

Steps:

Ensure that the user account for logging in to 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 ArcGIS Server 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 ArcGIS Server Manager to SQL Server uses Windows authentication rather than a SQL database login.
Start ArcGIS Server Manager, expand the Security tab, then 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. Check that the account used to log in to ArcGIS Server 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 ArcGIS Server 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 ArcGIS Server Manager is used for database and table schema creation.
- If Use Trusted Connection is checked, applications 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 is used to connect to the database. In most cases, this is the account that runs the ASP.NET process. On Windows 2000 and XP, this is normally the ASPNET account. On Windows Server 2003, Windows Vista, and Windows Server 2008, this is usually the Network Service account, and on Windows 7 and Windows Server 2008 R2 this is an application pool identity named ArcGISServicesAppPool (which is the same name as the application pool). ArcGIS Server Manager adds 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, 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 SQL Server is on a different computer, trusted connections may not work. This is because the ASPNET (Windows XP), Network Service (Windows Server 2003 and 2008) and Application Pool Identity Virtual Accounts (Windows Server 2008 R2 and Windows 7) accounts are local accounts 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, or run your ArcGISServicesAppPool Application Pool using a fixed, managed, or domain account that has access to the remote SQL Server database.
- If Use Trusted Connection is unchecked, enter a SQL Server login that will be used to connect to the database. 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; after ArcGIS Server Manager creates the database, remove the sysadmin role and 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, 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 choose it from the drop-down list. If the database was not created by the ArcGIS Server 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 transfer 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 to send messages, in order to prevent spam. The account credentials will be encrypted in the configuration file. Also, in From address, type an 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 an 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, 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 Installing Microsoft SQL Server Express 2005 and 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, which 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.

By default, ArcGIS Server Manager and ArcGIS Server services and applications use an application name of esriags. This is different from the default application name defined for ASP.NET in its machine.config file. That default name is a forward slash (/). You may also have defined a different application name for your Web application using the preexisting database. Users created for these different applications are not 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 is /. You then install ArcGIS Server and use ArcGIS Server 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 are displayed by default. This is because ArcGIS Server Manager sets the application name for users to esriags and does 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:
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 ArcGIS Server Manager, log in, and go to the Users panel and to the Roles panel. The users and roles previously added to the database are present. 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 ArcGIS Server Manager to add users and roles with its default application name (esriags). To use this option, use SQL Server Management Studio or other tools 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 utilizing users in the database, edit the web.config file to change or add the applicationName attribute in the <membership> provider to esriags (in other words, applicationName="esriags").

Alternate instructions for manual setup

If for some reason you cannot use ArcGIS Server 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 2008 and creating the database manually

Steps:

Install Microsoft SQL Server Express 2008 as described above.
Install Microsoft SQL Server Management Studio Express. This can be downloaded from http://www.microsoft.com/express/Database/.
Start SQL Server Management Studio Express from Start > (All) Programs > Microsoft SQL Server 2008 > SQL Server Management Studio Express.
On the connection dialog box, type the name of your instance of SQL Server, for example <computer name>\SQLEXPRESS. Click Windows Authentication from the Authentication drop-down list and click Connect.
Add the ASP.NET account (or the IIS APPPOOL\ArcGISServicesAppPool and IIS APPPOOL\ArcGISApplicationsAppPool accounts) to the authorized logins:
1. In the main program window, expand the Security folder.
2. Right-click Logins and choose New Login.
3. For IIS 6 or 7, on the login dialog box, 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. For IIS 7.5, add IIS APPPOOL\ArcGISServicesAppPool and IIS APPPOOL\ArcGISApplicationsAppPool instead.
4. If 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 dollar sign ($) 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, type city\GIS1$. Note that you are not 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 box, 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:
If you're using IIS 6 or 7, follow these substeps. (If you're using IIS 7.5, skip to the next set of substeps.)
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 click the ellipsis (...) button to find the account.
5. In the Database role membership area, check the db_owner item.
6. Click OK to add the user.
7. Repeat steps (b) through (f) above, but in step (d), add the <computer name>\Network Service account.
8. 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$.
If you're using IIS 7.5, follow these substeps:
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 arcgisservicesapppool for the User name.
4. For the Login name, type IIS APPPOOL\ArcGISServicesAppPool or click the ellipsis (...) button to find the account.
5. In the Database role membership area, check the db_owner item.
6. Click OK to add the user.
7. Repeat steps (b) through (f) above, but in step (c), name your account arcgisapplicationsapppool, and in step (d), add the IIS APPPOOL\ArcGISApplicationsAppPool account.
8. 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 SQL Express, for example, MYSERVER\SQLEXPRESS.
5. Leave authentication at Windows authentication, click the Database list, then click the database you created earlier (for example, aspnetdb).
6. Click Next and 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 ArcGIS Server 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 ArcGIS Server 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, Using an existing user or role database.

One tool commonly used to manage users and roles is 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.

8/22/2012