Share via

Configure SQL Membership Provider forms authentication for Project Server 2007

  • Article

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2015-03-09

In this article:

  • SQL Membership Provider overview

  • Configuring the infrastructure

  • Updating the Web.config file to add the SQL Membership Provider

  • Setting Up Form Authenticated User Accounts

This article describes how to configure SQL Membership Provider forms authentication in order for users to access Microsoft Office Project Server 2007.

SQL Membership Provider overview

The SQL Membership Provider is an identity management system that uses forms authentication to manage Office Project Server 2007 user account information.

Forms authentication is very similar to Project Server authentication, which is the authentication mechanism provided in Microsoft Office Project Server 2003, in that a user enters a user name and password for access. The main difference is that in forms authentication, the lists of users and their passwords are stored in membership stores rather than in the Project Server database. Examples of these stores include Active Directory, an LDAP store, and an SQL Membership Provider store.

For more information on Office Project Server 2007 authentication, see Plan Project Server 2007 authentication method.

Using the SQL Membership Provider store with Office Project Server 2007 involves the following:

  • Configure the infrastructure   This step has two parts: creating the SQL Membership Provider database in which you plan to store your user account information, and creating the IIS site through which your forms-authenticated users can access Project Server.

  • Update the new site's configuration file in IIS   Add a string to the site's Web.config file that specifies the SQL Membership Provider.

  • Set up user accounts   Create an XML list of all forms-authenticated users that you want to add and import them into the SQL Membership Provider store.

    Note

    To configure the SQL Membership Provider for Office Project Server 2007, you must install Office Project Server 2007 in a server farm configuration (versus a Basic installation) and must have a working Project Web Access site.

Configuring the infrastructure

Configuring the infrastructure in which you can add users to the SQL Membership Provider database involves the following steps:

  • Create the SQL Membership Provider database

  • Extend the Web application to create a site for your forms-authenticated users to access Project Server.

  • Configure the new site for the SQL Membership Provider.

Creating the SQL Membership Provider database on your computer running SQL Server

The following procedure creates the SQL Membership Provider database, referred to as the SQL Membership store. All forms-authenticated accounts using the SQL Membership Provider to access Office Project Server 2007 will need to have their account information imported into this database.

The Windows account with which you are logged on to SQL Server when creating the SQL Membership store will be the Forms Administrator account needed to add users to the database later.

Create the MembershipProvider database on your SQL Server

  1. Open a Command Prompt window.

  2. From any directory, enter: <Windows Directory>\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -A m -E

    This creates a database named aspnetdb, authenticating with your existing Windows credentials (the -E option).

    The following table describes other important parameters that you can use with the aspnet_regsql.exe command. For a complete listing of all options for the command, use the -? option.

    Parameter Description

    -d

    Specifies a database name other than the default (aspnetdb). Enter the database name after the parameter. For example: -d aspnetdb_testdbs

    -S

    Creates the database on a remote SQL Server. Enter the SQL Server instance name on which you are creating the database after the parameter. For example: -s SQLServerInstance

    -E

    Authenticates with current Windows credentials

    -U

    Specifies the SQL Server user name to authenticate with if you are using SQL authentication. Enter the user name after the parameter. This parameter requires the -P parameter.

    -P

    Specifies the SQL Server password to authenticate with if you are using SQL authentication. Enter the password after the parameter. This parameter requires the -U parameter.

Extending the Web application

By extending the Web application that contains your Project Web Access sites, you are creating a new IIS Web site through which forms-authenticated users can access Project Web Access. This Web site, and any other IIS Web sites using different authentication mechanisms that are created within the same Web application, will have the same content because they share the same content database. For example, your Web application may expose its content to your employees through an IIS Web site that uses Windows authentication, but you can also extend the Web application to have a separate IIS Web site that uses forms authentication for vendors to access the same content. Because there are two separate IIS Web sites, the users need to access them on different port numbers. For example:

  • http://contoso/pwa:80   (for Windows authentication)

  • http://contoso/pwa:81   (for forms authentication (SQL Server))

When your forms-authenticated users attempt to log in to Project Web Access, verify that they are using the URL to the new forms-authenticated site.

Extend the Web application

  1. Open the SharePoint Central Administration Web site by clicking Start, pointing to Administrative Tools, and then clicking SharePoint 3.0 Central Administration.

  2. On the Application Management tab, select Create or extend Web application.

  3. In the next page, select Extend an existing Web Application.

  4. If a Select Web Application dialog box appears, select the Web site you want to extend (for example, Default Web Site), and then click OK.

  5. Select Create a new IIS web site.

  6. Change the port number to 81 (or to another unused port). Verify that the Description and Path numbers change correctly.

  7. Note the URL located in the Load Balanced URL box. This is the URL for the new forms-authenticated site. You can use the default URL provided in the box or enter a different URL.

  8. Click OK.

Configure the new site for the SQL Membership Provider

The following procedure allows you to set the new site for forms authentication and the SQL Membership Provider.

Configure the new site for the SQL Membership Provider

  1. From the Application Management page, click Authentication providers.

  2. For Intranet Zone, click the Windows link.

  3. For Authentication Type, select Forms.

  4. In the Membership provider name box, enter:

    AspNetSqlMembershipProvider

  5. Click Save.

Updating the Web.config file to add the SQL Membership Provider

You need to add the SQL Membership Provider to the Web.config file for the new IIS site that you have created. You also need to make the same changes to the Web.config file for the Windows authenticated site of the Web Application. You can do this through IIS Manager and by adding an entry to that file that points to the SQL Membership Store database (aspnetdb).

Note

This needs to be done on all front-end Web servers in the farm.

Add the SQL Membership Provider to the Web.config file

  1. Open the Internet Information Services Manager on the front-end Web server. You can do this by clicking Start, choosing the Run command, typing Inetmgr in the Open box, and then clicking OK.

  2. In IIS Manager, expand the computer name and then click the Web Sites folder.

  3. Under the Web Sites folder, you will see the Web site you had created previously. Right-click this site and choose Explore.

  4. Right-click web.config, choose Open, and then open the file using an editing tool such as Notepad.

  5. Add the following entry to Web.config (the best place to add it is immediately after the </ConfigSections> section):

    <connectionStrings>

    <remove name="LocalSqlServer" />

    <add name="LocalSqlServer" connectionString="data source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=aspnetdb" />

    </connectionStrings>

    The example above uses a value of 127.0.0.1 for the data source parameter. This is correct if SQL Server is installed on the local computer. If SQL Server is installed on a different computer (for example, a computer named "Computer1" and a SQL Server instance named "Project"), the <add> element should be:

    <add name="LocalSqlServer" connectionString="data source=Computer1\Project;Integrated Security=SSPI;Initial Catalog=aspnetdb" />

  6. Make sure the connection string points to the SQL Membership Provider database in which you will be storing user accounts (for example, Intial Catalog=aspnetdb).

    Note

    Make the same changes to the Web.config file for the Windows site of the Web application. This change also needs to occur on all front end Web servers in the farm.

Setting up forms-authenticated user accounts

When setting up forms-authenticated user accounts through the SQL Membership Provider, you need to do the following:

  • Create user account XML data

  • Import the user data into the membership store

  • Add the new user accounts to a Project Server security group

Creating XML data for user accounts

The PjFormsAuthUpgrade.exe tool is installed with Office Project Server 2007; it enables you to add your forms-authenticated users to the SQL membership store. In this step, it is used to generate an XML file that contains your account data for users you are adding to the SQL membership store.

Note

In a later step, the PjFormsAuthUpgrade.exe tool will also be used to import the user data into the SQL Membership Provider database.

Note

Running the PjFormsAuthUpgrade.exe tool on a basic- or digest-authenticated site is not supported.

The PjFormsAuthUpgrade.exe tool can be used to generate a file named Users.xml. The tool can be used to add two types of users:

  • Project Server–authenticated users migrated from Project Server 2003

  • New Office Project Server 2007 forms-authenticated users

Creating the Users.xml file for Project Server–authenticated users being migrated from Project Server 2003

Office Project Server 2007 does not support the Project Server authentication method that is used in Project Server 2003. However, if you are using Office Project Server 2007, you can migrate Project Server–authenticated users from Project Server 2003 and then add them to the SQL membership store, allowing them to access Office Project Server 2007 through forms authentication.

Note

For more information on migrating to Office Project Server 2007, see Introduction: Project Server 2007 migration guide.

The first step in this process is using the PJFormsAuthUpgrade.exe tool to generate the Users.xml file. This file will contain all users that need to be migrated to the SQL membership store.

The PJFormsAuthUpgrade.exe tool does the following:

  • Searches for all Project Server–authenticated users migrated from Project Server 2003, based on your Office Project Server 2007 URL.

  • Generates an XML file including each user's account information. And it auto-generates other information required by the store, such as GUID, logon names, and passwords.

The following procedure describes how to generate the Users.xml file.

Note

These steps are only needed if you have migrated Project Server–authenticated users from Project Server 2003 to Office Project Server 2007. If you are only adding new users to the SQL Membership Provider database, go to the next section titled "Configuring the Users.xml file for new users."

Create a Users.xml file for migrated users

  1. In a Command Prompt window, navigate to the directory containing PJFormsAuthUpgrade.exe. (The default location is Program Files\Microsoft Office Servers\12.0\Bin.) Type the following text at the command prompt and customize it for your environment:

    PjFormsAuthUpgrade.exe -createusersfile -log forms.log -passwordlength 8 -url https://localhost/pwa -usersfile users.xml

    Option Description

    -createusersfile

    Creates the Users.xml file with Project Server– authenticated accounts migrated from Project Server 2003.

    -log

    Generates a log file for troubleshooting issues that occur when running the PjFormsAuthUpgrade.exe tool. You can specify the path to the log file. If you do not specify a path, the file will be created in the same directory that contains the PjFormsAuthUpgrade.exe tool.

    -passwordlength

    Specifies the length of the random password that will be generated for each account. You can edit this password in the file or allow the user to reset this by using a password question option through the SQL membership store.

    Note

    Project Server–authenticated passwords are not migrated from Project Server 2003 to Office Project Server 2007.

    -URL

    Specifies your Office Project Server 2007 URL, which is needed in order to search the database for the migrated accounts.

    -usersfile

    Creates the Users.xml file. You can specify the path to the file. If you do not specify a path, the file will be created in the same directory that contains the PjFormsAuthUpgrade.exe tool.

    For example:

    PjFormsAuthUpgrade.exe -createusersfile -log c:\mydir\forms.log -passwordlength 8 -url http://contoso/pwa -usersfile users.xml

    Run the command with the options customized for your environment. If the command is successful, the Users.xml file will be generated in the same directory that contains the PjFormsAuthUpgrade.exe tool (because no path was specified).

    Note

    If no users are returned in your Users.xml file, then the tool has not found any migrated Project Server–authenticated users that need to be migrated to the SQL membership store.

  2. Open the Users.xml file in Notepad. The file should be similar to the following:

    <?xml version="1.0"?>

    <Users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="https://schemas.microsoft.com/Project/Users.xsd">

    <UpdateUser>

    <DisplayName>Bob Sutton</DisplayName>

    <EmailAddr>BobSutton@contoso.com</EmailAddr>

    <LogonName>Bob_Sutton</LogonName>

    <Password>?rBt8Rv(</Password>

    <Guid>c4572c86-7452-4d29-9d28-1a8a49ad5f89</Guid>

    <PasswordQuestion>Please add a password question?</PasswordQuestion>

    <PasswordAnswer> Please add a password question?</PasswordAnswer>

    </UpdateUser>

    <UpdateUser>

    <DisplayName>Peter Krebbs</DisplayName>

    <EmailAddr>PeterKrebbs@contoso.com</EmailAddr>

    <LogonName>Peter_Krebbs</LogonName>

    <Password>!rHtNv)</Password>

    <Guid>d2372c86-7452-4d29-8d67-1a8a49ad5f34</Guid>

    <PasswordQuestion>Please add a password question?</PasswordQuestion>

    <PasswordAnswer> Please add a password question?</PasswordAnswer>

    </UpdateUser>

    </Users>

  3. Add data to the <PasswordQuestion> and <PasswordAnswer> elements. These elements may be required by the SQL Membership Provider database in order for users to reset their password if they have forgotten it.

  4. If you do not want your users to use the auto-generated passwords, update the file to change the password.

  5. Save the file.

    Note

    If you do not plan on adding new users to the SQL membership store, skip the next section and go to the section titled "Import the user data into the Membership Store."

Configuring the Users.xml file for new users

If you are not adding any users migrated from Project Server 2003, use the following steps to add a new user to the SQL Membership Provider database. The following processes will take new users that do not exist in Project Web Access and add them to both Project Server and the SQL membership store.

Create a Users.xml file for new users

  1. In a Command Prompt window, navigate to the directory containing PJFormsAuthUpgrade.exe. (The default location is Program Files\Microsoft Office Servers\12.0\Bin). Type the following text at the command prompt and customize it for your environment:

    PjFormsAuthUpgrade.exe -createemptyusersfile -log forms.log -url https://localhost/pwa -usersfile users.xml

    Option Description

    -createemptyusersfile

    Creates the Users.xml file. This file can be used as a template to add new users.

    -log

    Generates a log file for troubleshooting issues that occur when running the PjFormsAuthUpgrade tool. You can specify the path to the log file. If you do not specify a path, the file will be created in the same directory that contains the PjFormsAuthUpgrade.exe tool.

    -URL

    Specifies your Project Server URL.

    -usersfile

    Creates the Users.xml file. You can specify the path to which the file will be created. If you do not specify a path, the file will be created in the same directory that contains the PjFormsAuthUpgrade.exe tool.

    Run the command. If it is successful, then the Users.xml file will be generated in the same directory that contains the PjFormsAuthUpgrade.exe tool (because a path was not specified).

  2. Open the Users.xml file in Notepad.

    The file should be similar to the following:

    <?xml version="1.0"?>

    <Users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="https://schemas.microsoft.com/Project/Users.xsd">

    <CreateUser>

    <DisplayName>Display Name</DisplayName>

    <EmailAddr>newuser@project.com</EmailAddr>

    <LogonName>LogonName</LogonName>

    <Password>Password</Password>

    <Guid>ab3ad5fe-6b56-41e8-a9d8-44af71b91dc4</Guid>

    <PasswordQuestion>Password Question</PasswordQuestion>

    <PasswordAnswer>Password Answer</PasswordAnswer>

    </CreateUser>

    <UpdateUser>

    <DisplayName>Display Name</DisplayName>

    <EmailAddr>upgradeduser@project.com</EmailAddr>

    <LogonName>LogonName</LogonName>

    <Password>Password</Password>

    <Guid>2e8c4617-1f38-42d2-8496-9c85eb761b5f</Guid>

    <PasswordQuestion>Password Question</PasswordQuestion>

    <PasswordAnswer>Password Answer</PasswordAnswer>

    </UpdateUser>

    </Users>

  3. Modify the Users.xml file with information about the new user. It should look similar to the following example:

    <?xml version="1.0"?>

    <Users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="https://schemas.microsoft.com/Project/Users.xsd">

    <CreateUser>

    <DisplayName>Jeff Smith</DisplayName>

    <EmailAddr>JSmith@test.com</EmailAddr>

    <LogonName>JeffSmith</LogonName>

    <Password>p@ssword1</Password>

    <Guid>ab3ad5fe-6b56-41e8-a9d8-44af71b91dc4</Guid>

    <PasswordQuestion>Password Question</PasswordQuestion>

    <PasswordAnswer>Password Answer</PasswordAnswer>

    </CreateUser>

    </Users>

    If you want to add additional users, add additional <CreateUser> sections and edit them to include the new users' information. Make sure to increment the GUIDs you are using so that the same GUID is not used for two users. (Incrementing the GUID in the XML file can also be done programmatically.)

    The data you want to import into Project Server is now contained within the Users.xml file. Because this is an XML file, it can be modified either manually or programmatically. If you want to change the password, display name, e-mail address, or GUID that the tool automatically generates, you can modify it with whatever mechanism you choose.

Import the user data into the membership store

After the Users.xml file has been created for new or migrated users, you can use the PJFormsAuthUpgrade.exe tool to import the users into the SQL Membership Provider store.

During this process, the tool also updates Office Project Server 2007 with any new user account information. New users are not able to access Project Server until they are also added to a Project Server security group.

Migrated Project Server–authenticated user accounts will also be updated on Office Project Server 2007 with their new account information. Migrated users should still be members of their existing security groups.

Note

The database owner of the SQL membership database is allowed to add users to the SQL membership store. Make sure you are logged in with this account when adding forms-authenticated users.

Run the PjFormsAuthUpgrade.exe tool to import users into the membership store

  • In a Command Prompt window, navigate to the directory containing PjFormsAuthUpgrade.exe, and use the following command:

    PjFormsAuthUpgrade.exe -log forms.log -url https://localhost/pwa -usersfile users.xml

    For example:

    PjFormsAuthUpgrade.exe -log c:\mydir\forms.log -url http://contoso/pwa -usersfile users.xml

    Migrated users will be added to the SQL membership store and their account information in Project Server will be updated.

    New users will be added to the SQL membership store and to Project Server.

Adding users to a Project Server security group

At this point, both new and migrated Project Server 2003 users are in both the SQL membership store and Project Server. However, new users must be added to a Project Server security group in order to log in to Project Server through Project Web Access. As noted earlier, migrated Project Server 2003 users will still belong to their existing security groups.

Add a new user to a Project Server security group

  1. In Project Web Access, go to the Server Settings page.

  2. In the Server Setting page, go to the Security section, and then click Manage Users.

  3. In the Manage Users page, in the User Name list, click the name of the new forms-authenticated user account you had just added.

  4. In the Edit User page for the user, go to the Security Groups section. From the Available Groups list, select the group or groups you want to add the user to, and then click Add to move the groups to the Groups that contain this user list.

  5. Make any other changes you want to make to the user account, and then click Save.

You can also add a user to a security group from a command prompt by using the Stsadm.exe tool. When using the tool, you should run it as a user who has administrator rights in Project Web Access.

Add a new user to a Project Server security group through the command prompt

  1. In a Command Prompt window, go to the following directory: Program Files\Common Files\Microsoft Shared\Web server extensions\12\BIN.

  2. Run the following command:

    stsadm -o projmodifyuseringroup -url http://<servername>/pwa -groupname <group to which you want to add user> -username <forms user> -addorremove add

    For example:

    staadm -o projmodifyuseringroup -url http://contoso/pwa -groupname administrators -username Brad Sutton -addremove add

Note

When you are adding a user to a Project Server security group by using the command-line option, you can only add one user to one security group with each running of the executable file. However, an administrator can create scripts to automate the process.

After adding the user to a security group, the users can access Project Server by using the Project Web Access forms-authenticated site.

Logging on to the site with forms authentication

When users are logging on to Project Web Access, they must use the URL to the forms-authenticated site, which was created when you extended the Web application earlier.

Log on to Project Web Access

  1. In a Web browser, go to the URL for the forms-authenticated site: http://<computername>:<port number>/PWA

  2. Log on as the newly added user by using your user name and password.

See Also

Concepts

Plan Project Server 2007 authentication method

Other Resources

Plan authentication methods (Windows SharePoint Services)
Plan authentication settings for Web applications (Windows SharePoint Services)
Authentication samples (Windows SharePoint Services)