Deploying Commerce Server Using Windows Authentication

  • Article

It is strongly recommended that you install Microsoft Commerce Server 2002 using Microsoft Windows Integrated Security, and that you secure your databases to use Windows Authentication.

You must perform the following steps to install Commerce Server using Windows Integrated Security, and to secure your services and databases to use Windows Authentication. The instructions provided in this topic are not specific to a particular deployment scenario. Each of these steps is explained in detail in this section.

  • Step 1: Run dcpromo and Install Active Directory Services
  • Step 2: Create Required Accounts
  • Step 3: Install Commerce Server Using Windows Integrated Security
  • Step 4: Unpack Your Site and Configure Database Connection Strings for Windows Authentication
  • Step 5: Set up a Trusted Connection for Business Desk users
  • Step 6: Map the Anonymous Account of the Web Site to the Anonymous Domain Account
  • Step 7: Create SQL Login Accounts for Each Required Account
  • Step 8: Run the Commerce Server Database Security Scripts
  • Step 9: Add the Required Accounts to the Database Roles
  • Step 10: Set up the OLAP Database
  • Step 11: Add the Service Accounts to the Config COM+ Application Role
  • Step 12: Add Individual User Accounts to the Group Domain Accounts
  • Step 13: Grant Users Business Desk Permissions
  • Step 14: Enable Integrated Windows Authentication in Internet Explorer
  • Step 15: Map the User Login Account to Database Owner (dbo) User
  • Step 16: Enable Users to Run the Model Builder DTS Task

For additional information about using Windows authentication in a single computer or distributed deployment configuration, see Using Windows Authentication in a Single Computer Deployment and Using Windows Authentication in a Distributed Deployment.

Step 1: Run dcpromo and Install Active Directory Services

This step assumes you do not have Active Directory Services installed on any computer, you do not have Domain Name Service (DNS) installed, and you do not have a domain controller. If Active Directory Services is already installed, go to Step 2: Create Required Accounts.

For detailed instructions for running dcpromo and installing Active Directory Services, see Configure Active Directory and DNS on Computer 1.

To run dcpromo and install Active Directory Services

  1. Click Start, and then click Run.

  2. In the Run dialog box, in the Open box, type dcpromo, and then click OK.

    The Active Directory Installation Wizard is started.

  3. Follow the online instructions for the wizard, but note the following:

    • In the Directory Services Restore Mode Administrator Password dialog box, you are prompted for the password you want to assign to the Administrator account for the DNS server. This account is different from the Administrator account for the computer.

Step 2: Create Required Accounts

Create the required user and group accounts on the computer on which you installed Active Directory Services.

To create the required accounts

  1. Log on to the domain controller as a user that has permissions to create users in the domain. By default, domain administrators have this privilege.

  2. Click Start, point to Programs, point to Administrative Tools, and then click Active Directory Users and Computers.

  3. In Active Directory Users and Computers, right-click User, and then click New User.

  4. In the New User dialog box, type the user account name, for example, CSDM, a password, and then click Create.

  5. Repeat these steps until you have created each of following accounts.

    Account user Sample account name Description
    Run-time user Anonymous A user account for the anonymous users visiting your Web site.
    Direct Mailer service CSDM A user account for the Direct Mailer service.
    List Manager service CSLM A user account for the List Manager service.
    Predictor service CSPred A user account for the Predictor service.
    Config COM+ application CSCOMPlus A user account for the Config COM+ application.
    Business Desk group BDGroup A group account for all Business Desk users, including users who run reports. Also known as design-time users.
    Advanced Report group ReportAdvanced A group account for Business Desk users who must save dynamic reports, modify reports, and delete reports.
    Segment Viewer group SegmentViewer A group account for Business Desk users who work with the Segment Viewer module.
    DTS Task Import group DTSImport A group account for system administrators who run the DTS tasks to import and extend the Data Warehouse.

    To create the Business Desk group account, right-click Group instead of User, and then click New Group.

Step 3: Install Commerce Server Using Windows Integrated Security

Before you install Direct Mailer, verify that SQL Server Agent is started. If it is not started, Direct Mailer will not install.

To install Commerce Server using Windows Integrated Security

  1. Log on to the Commerce Server as a user who is a member of the Administrator group on the Commerce Server server. Do not log on as the domain administrator.

  2. Insert the Commerce Server 2002 CD into the appropriate drive. In the root of the Commerce Server 2002 CD, double-click Setup.exe. The Commerce Server 2002 Setup program starts.

  3. In the Administration Database Configuration screen, select Use Windows Integrated Security, and then click Next.

  4. In the Direct Mailer Database Configuration screen, select Use Windows Integrated Security, and then click Next.

  5. In the Services Accounts screen, click Direct Mailer service, and then specify the Windows 2000 account for the Direct Mailer, for example, CSDM.

  6. Repeat step 5 for the following services:

    Service Sample account name
    List Manager CSLM
    Predictor CSPred
    Config COM+ application CSCOMPlus

    Commerce Server Setup automatically grants the logon as a service right to the account(s) you specify.

    Event Logging must run under a local Administrator account.

  7. Click Install to begin the installation process.

  8. Click Finish when the installation process is finished.

Step 4: Unpack Your Site and Configure Database Connection Strings for Windows Authentication

When you unpack your site, the connection strings to your databases are automatically configured to use Windows Authentication.

  1. Login as a domain user that has administrative rights to the Web server computer(s) and the database computer(s).

  2. Click Start, point to Programs, point to Microsoft Commerce Server 2002, and then click Commerce Site Packager.

    Commerce Server Site Packager is started.

  3. Follow the online instructions. By default, all the database connection strings are configured to use Windows Authentication.

    It is assumed that you choose the Custom option, and you create the following databases:

    • Business Desk Permissions
    • Catalog
    • Profiles
    • Campaigns
    • TransactionConfig
    • Transactions

    Modify the following steps accordingly if your databases are different, or you installed all the resources in single database.

Step 5: Set up a Trusted Connection for Business Desk Users

Perform this step on the computer on which you installed Active Directory Services in Step 1: Run dcpromo and Install Active Directory Services.

To set up a trusted connection for Business Desk users

  1. Click Start, point to Programs, point to Administrative Tools, and then click Active Directory Users and Computers.

  2. In the Active Directory Users and Computers screen, in the Tree pane, expand <domainName>, and then click Computers.

  3. In the Computers pane, right-click the computer name that you want to trust for delegation, and then click Properties.

    Ee810478.note(en-US,CS.20).gifNote

    • When using Windows Authentication, you must perform this step for the Commerce Server and the Web server on which the Business Desk application is running.

  4. In the <computer name> Properties dialog box, on the General tab, select the Trust computer for delegation check box, and then click OK.

    The Active Directory message box appears.

  5. To close the Active Directory message box, click OK.

  6. To close the <computer name> Properties dialog box, click OK.

Step 6: Map the Anonymous Account of the Web Site to the Anonymous Domain Account

In this step you map the anonymous user account of your Web site (by default, in Internet Information Services this is the IUSR_<computername>) to the anonymous domain account you created in Step 2: Create Required Accounts.

These steps appy even if you are using an ASPNET anonymous account.

To change the anonymous account of the site to a domain account

  1. Click Start, point to Programs, point to Administrative Tools, and then click Internet Services Manager.

  2. In Internet Services Manager, navigate to your application (for example, Retail_Site).

  3. Right-click your application (for example, Retail_Site), and then click Properties.

  4. In the Properties dialog box, on the Directory Security tab, in the Anonymous access and authentication control section, click Edit.

  5. In the Authentication Methods dialog box, in the Anonymous access section, click Edit.

  6. In the Anonymous User Account dialog box, in the Username box, type the name of the anonymous domain account (for example, Anonymous).

  7. Click OK several times to close the dialog boxes.

  8. Click Start, and then click Run.

  9. In the Run dialog box, in the Open box, type iisreset, and then click OK.

    A command box appears while IIS is being reset. IIS is reset when the command box closes.

Step 7: Create SQL Login Accounts for Each Required Account

In this step, you create SQL login accounts on the SQL Server. Create one account for each domain account, except the Config COM+ application account.

To create a SQL login account

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, expand the nodes to the following path: Microsoft SQL Servers/SQL Server Group/<computer name>/Security.
  3. Right-click Login, and then click New Login.
  4. In the SQL Server Login Properties - New Login screen, on the General tab, do the following:
    Use this To do this
    Name Type the name of the domain user account (for example, Anonymous, a sample user account for your run-time users).
    Windows Authentication Verify this option is selected.
    Domain Select the domain of the account from the drop-down list.
    Grant access Verify this option is selected for security access.
    Database Select master as the database for this login from the drop-down list.
    Language Select the default language for this login from the drop-down list.
  5. Click OK.
  6. Repeat these steps until you have created a SQL Server login account for each of the following domain accounts:
    Account Sample account name
    Run-time users Anonymous
    Direct Mailer service CSDM
    List Manager service CSLM
    Predictor service CSPred
    Business Desk group BDGroup
    Advanced Report group ReportAdvanced
    Segment Viewer group SegmentViewer
    DTS Task Import group DTSImport
    Config COM+ application CSCOMPlus

Step 8: Run the Commerce Server Database Security Scripts

Run the Commerce Server security scripts to automatically create security roles for the required accounts, and assign the appropriate database permissions. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder.

To run the scripts and create the security roles

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click SQL Query Analyzer.

  2. In the Connect to SQL Server dialog box, specify the appropriate SQL server.

  3. In Query Analyzer, in the database drop-down box, select the database you want to run the script against.

  4. Click File, and then click Open.

  5. Navigate to the scripts located in the Program Files\Microsoft Commerce Server\Support folder, and select the script you want to run.

    The script opens and the code appears in the Query Analyzer window.

  6. On the toolbar, click Run to run the script against the selected database.

  7. Repeat these steps to run all the scripts against the databases listed in the following table.

Ee810478.note(en-US,CS.20).gifNote

  • You must run the ReportViewer.sql script before you run the ReportAdvanced.sql script.

Security Scripts for the Commerce Server Run-Time Databases

Script name Run against this database
BDReaderRole and BDWriterRole Business Desk Permissions
CampaignReaderRole and CampaignWriterRole Campaigns
CatalogSecurityRoles Product Catalog
ProfileReaderRole and ProfileWriterRole Profiles
TransactionConfigReaderRole and TransactionConfigWriterRole TransactionConfig
TransactionReaderRole and TransactionWriterRole Transactions

Security Scripts for Analysis and Data Warehouse

Script Run against this database
ReportViewer.sql Data Warehouse
ReportAdvanced.sql Data Warehouse
SegmentViewer Data Warehouse
Dts_AdminDB_SecurityRole.sql Administration (MSCS_Admin)
Dts_CommerceDB_Campaign_SecurityRole.sql Campaigns
Dts_CommerceDB_Catalog_SecurityRole.sql Product Catalog
Dts_CommerceDB_Transaction_SecurityRole.sql Transactions
Dts_CommerceDB_UPM_SecurityRole.sql Profiles

Ee810478.note(en-US,CS.20).gifNotes

  • Rerun the CatalogSecurityRoles script and the Dts_CommerceDB_Catalog_SecurityRole script after you do any of the following tasks:
    • Create new catalogs
    • Add languages to a catalog
    • Rebuild virtual catalogs
  • Rerun the Dts_CommerceDB_Campaign_SecurityRole script after you create new campaigns.
  • When you extend the Profiles database (create new profile definitions), manually assign permissions to the DTSImport account.

The Commerce Server run-time database tables can reside in one database, or each resource can have tables in its own database.

These scripts are designed to work with SQL server, for data stored in the Commerce Server run-time databases and the Administration database. They are not designed for use with Active Directory or an Oracle database.

Step 9: Add the Required Accounts to the Database Roles

After you run all of the scripts, add the required accounts to newly created roles and secure the MSCS_Admin tables.

Add the required accounts to a database role

Secure the MSCS_Admin tables

To add a required account to a database role

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In SQL Server Enterprise Manager, expand Security, and then click Logins.

  3. Right-click a SQL login account, and then click Properties.

  4. In the SQL Server Login Properties dialog box, click the Database Access tab.

  5. In the top box, select a database, and then in the bottom box, specify the role for the account.

  6. Repeat these steps until all the required accounts are assigned membership to the database roles they need, and then click OK.

    The following table lists all the accounts, the databases, and the roles the accounts must be assigned to.

Account

(sample account name)

 For this database Assign to this database role
Run-time users

(Anonymous)

 Administration (MSCS_Admin) For information, see Securing the MSCS_Admin Tables.
  MSCS_CatalogScratch db_owner
  Catalog ctlg_CatalogReaderRole
  Business Desk Permissions BDReaderRole
  Profiles ProfileReaderRole
  Campaigns CampaignReaderRole
  Transaction Configuration TransactionConfigReaderRole
  Transactions TransactionReaderRole
Business Desk group

(BDGroup)

 Administration (MSCS_Admin) db_datareader
  MSCS_CatalogScratch db_owner
  Catalog db_owner
  Business Desk Permissions BDWriterRole
  Profiles ProfileWriterRole
  Campaigns CampaignWriterRole
db_datareader
  Transaction Configuration TransactionConfigWriterRole
  Transactions TransactionWriterRole
  Data Warehouse ReportViewerRole
Advanced Report group

(ReportAdvanced)

 Data Warehouse ReportAdvancedRole
Segment Viewer group

(SegmentViewer)

 Data Warehouse SegmentViewer
Direct Mailer service
(CSDM)		 Administration (MSCS_Admin) db_datareader
  Direct Mailer db_owner
List Manager service account

(CSLM)

 Administration (MSCS_Admin) db_datareader
  Direct Mailer db_owner
  Campaigns db_owner
Predictor service account

(CSPred)

 Administration (MSCS_Admin) db_datareader
  Data Warehouse SQL Server db_datareader
db_datawriter
db_ddladmin
DTS Task Import group

(DTSImport)

 Administration (MSCS_Admin) DTS_ImportRole
  Catalog DTS_ImportRole
  Business Desk Permissions DTS_ImportRole
  Profiles DTS_ImportRole
  Campaigns DTS_ImportRole
  Transaction Configuration DTS_ImportRole
  Transactions DTS_ImportRole
  Data Warehouse SQL Server Run sp_addrolemember to assign the dbo for the Data Warehouse (see Step 15: Map the User Login Account to Database Owner (dbo) User)
  Data Warehouse OLAP database **Add to the OLAP Administrators group
  Computer from which you run the DTS tasks Power Users group
  Config COM+ Application Administrators (see Step 11: Add the Service Accounts to the Config COM+ Application)
Config COM+ application Data Warehouse SQL Server db_owner

**The DTSImport account must be a part of the OLAP Administrators group for both the OLAP database computer, and the computer on which you are going to run the Report preparation DTS task. This is required to run the Report preparation DTS task and the Report Caching DTS task.

To enable Business Desk users to edit catalogs (for example, edit the catalog definition, rebuild the catalog, refresh the full-text index and publish the catalog), you must assign them to the db_owner role.

It is recommended that you create a group account specifically for Business Desk users who will edit catalogs.

Carefully consider the trustworthiness of the Business Desk user that you assign to the db_owner role. When assigning a Business Desk user to this role, you must consider the following security risk:

Ee810478.important(en-US,CS.20).gifImportant

  • A Business Desk user assigned to the db_owner role could potentially delete a catalog database. To mitigate this risk, you must create a firewall to disallow direct connection from the Business Desk client to the SQL Server database that contains the catalogs. This is the recommended secure configuration. For detailed instructions, see Deploying a Secure Site.

Securing the MSCS_Admin Tables

The following table lists the table names used to grant or deny access in the MSCS_Admin tables for the <domain>\Anonymous account, where <domain> is the name of your Active Directory domain. To set these permissions, use SQL Server Enterprise Manager on the server on which your MSCS_Admin tables reside. These tables are created by the Commerce Server 2002 setup application.

Table name IUSR or ASPNET
ExtendedProps No access
pupdbscripts No access
ResourceProps Select
Resources Select
SiteResources Select
Sites Select

This procedure is performed on the SQL Server server on which your MSCS_Admin tables reside.

To secure the MSCS_Admin tables

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In the SQL Server Enterprise Manager screen, in the Tree pane, expand Microsoft SQL Servers, expand SQL Server Group, expand <ServerName>(Windows NT), expand Databases, expand MSCS_Admin, right-click Roles and then click New Database Role.

  3. In the Database Role Properties - New Role dialog box, in the Name text box, type Anonymous_IIS_Role.

  4. In the Database role type section, select Standard role, and then click OK.

    The standard role you created and named Anonymous_IIS_Role appears in the Roles pane on the SQL Server Enterprise Manager screen.

  5. In the Roles pane, right-click Anonymous_IIS_Role, and then click Properties.

  6. In the Database Role Properties - Anonymous_IIS_Role dialog box, click Permissions.

  7. In the Database Role Properties - MSCS_Admin dialog box, in the SELECT column, select the ResourceProps, Resources, SiteResources, and Sites check boxes, and then click OK.

  8. In the SQL Server Enterprise Manager screen, in the Tree pane, expand Microsoft SQL Servers, expand SQL Server Group, expand <ServerName>(Windows NT), expand Security, and then click Logins.

  9. In the SQL Server Enterprise Manager screen, in the Logins pane, right-click <DomainName>\Anonymous, and then click Properties.

    Ee810478.note(en-US,CS.20).gifNote

    • You created the user account named <DomainName>\Anonymous, where <DomainName> is the name of your active directory domain, in Step 2: Create Required Accounts.

  10. In the SQL Server Login Properties - <DomainName>\Anonymous dialog box, select MSCS_Admin.

  11. In the Database roles for MSCS_Admin section, select the Anonymous_IIS_Role check box, and then click OK.

For information, see Securing the Administration Database.

Step 10: Set up the OLAP Database

In this step, you migrate the OLAP repository so it resides on the SQL Server. You do this to improve the security of your OLAP repository. Then you assign the appropriate permissions to the DTSImport user account to access the repository.

To migrate the OLAP repository to SQL Server

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, create a new SQL database.
  3. In Analysis Services Manager, right-click the server name, and then click Migrate Repository.
  4. Choose the recommended Analysis Services native format.
  5. Select the SQL Server name where the OLAP repository database will reside.
  6. Use an Administrator level, SQL Authentication account for tracking this connection.

To assign the DTS_Import user account appropriate permissions

  1. Assign the DTSImport account to the OLAP Administrator group for both OLAP server and the SQL Server. To do this, perform the following steps:

    • Click Start, point to Programs, point to Administrative Tools, and then click Computer Management.
    • Expand Users and Groups, and then expand Groups.
    • Right-click the OLAP Administrator group, and then click Properties.
    • Add the DTSImport group as a member of the OLAP Administrators group.

  2. On the OLAP server, provide "Full Control" to the bin directory under <olap_server_root>\bin to the DTS_Import user. To do this, perform the following steps:

    • Navigate to the <olap_server_root>\bin directory, for example, \Program Files\Microsoft Analysis Services\Bin.
    • Right-click Bin, and then click Select.
    • Click Permissions, and then add the DTSImport account to this directory.
    • Provide Full Control access.
    • Click OK.

  3. On the computer on which you will run the DTS tasks, assign the DTSImport account to the "Power Users" group.

    In this security context, the debug messages generated by this task will not be shown. This is documented in Knowledge Base article Q274559. The workaround is to have the user run under the local Administrator account.

  4. If the OLAP cubes and the Data Warehouse are on different computers, you must set up the account for the MSSQLServerOLAPService service so it has db_owner role privileges on the Data Warehouse.

Step 11: Add the Service Accounts to the Config COM+ Application Role

The Config COM+ application has an Administrators role. Accounts in this role will use objects for writing to the Administration database. By default, the user logged on during Commerce Server setup is added to this role, and the local Administrators group is added.

You must add the service accounts to the Administrators role. (This step assumes you have already added the DTSImport account to the Administrators role. If you have not, do so now.)

To add the service accounts to the Administrators role

  1. Click Start, point to Programs, point to Administrative Tools, and then click ComponentServices.
  2. Expand Component Services, expand Computers, expand My Computer, expand COM+ Applications, expand Commerce Server Config, expand Roles, and then expand Administrators.
  3. Right-click Users, select New, and then select User.
  4. In the Select Users or Groups dialog box, in the Look in box, select the Direct Mailer service account, for example, CSDM.
  5. Click OK.
  6. Repeat this procedure to add the List Manager service account, and Predictor service account.

Step 12: Add Individual User Accounts to the Group Domain Accounts

For each person who is going to use Business Desk, create a Windows users account. Next, assign those Windows accounts to the following group accounts as appropriated:

  • BDGroup account. Assign all Business Desk users.
  • ReportAdvanced account. Assign users who are going to save, modify, and delete dynamic reports.
  • SegmentViewer account. Assign users who are going to use the Segment Viewer module.

Ee810478.note(en-US,CS.20).gifNote

  • Members of the ReportAdvanced group and the SegmentViewer group also have the privileges granted to users in the BDGroup.

To assign individual Business Desk users accounts to the group accounts

  1. Click Start, point to Programs, point to Administrative Tools, and then click Active Directory Users and Computers.
  2. In Active Directory Users and Computers, right-click the group account, and then click Properties.
  3. On the Members tab, click Add.
  4. Type the Windows accounts of the appropriate users, and then click OK.
  5. Repeat these steps to add the user accounts to the group accounts as needed.

Step 13: Grant Users Business Desk Permissions

Using the Business Desk Permissions module, you must add the Business Desk domain group account to the Permissions module, and then grant members of the domain permissions to use Business Desk.

Ee810478.note(en-US,CS.20).gif Notes

  • If a Windows account is not in the Permissions module account list, or does not have any "allow" option assigned to it, then users assigned to that account cannot access Business Desk.
  • If the Permissions module account list contains a Windows account that no longer exists, users assigned to that account are denied access to all of the Business Desk categories and modules.

To add Windows accounts to the Permissions module account list

  1. In Security, click Permissions.

  2. In the Security Permissions screen, in the Windows Account section, click Add to List.

  3. In the Add Account dialog box, type the Windows account for which you want to add permissions (for example, BDGroup), and then click Add.

  4. To return to the Business Desk Welcome screen, click Back on the toolbar.

    The Windows account has been added to the Permissions module account list.

  5. Repeat these steps to add the ReportAdvanced group account and the SegmentViewer group account.

To set permissions for a Windows account

  1. In Security, click Permissions.

  2. In the Security Permissions screen, in the Windows Account drop-down box, select the Windows account in the Permissions module account list for which you want to add permissions.

  3. In the Security Permissions screen, in the Securable Entities section, grant the following accounts these permissions:

    • BDGroup – Allow All
    • ReportAdvanced – Allow All
    • SegmentViewer – Allow All

    For more information, see Setting Business Desk Permissions for Windows Accounts.

  4. To save changes and return to the Business Desk Welcome screen, click Save and go back on the toolbar.

Ee810478.important(en-US,CS.20).gifImportant

  • After permissions are changed for a Business Desk user, that user must close the Business Desk client, and then restart it for the new settings to take effect.

    If the Windows identity (groups membership, rights, ACLs, and so on) has changed for that user, the user must log off and then log on for new settings to take effect on the server. (Otherwise, the client will work correctly, but the server will not.)

Step 14: Enable Integrated Windows Authentication in Internet Explorer

Perform this step on each computer that has the Business Desk client installed. In addition, on the client computer, assign the Business Desk account to the Administrators group.

To enable Integrated Windows Authentication

  1. On the desktop, right-click Internet Explorer, and then click Properties.
  2. In the Internet Properties dialog box, click the Advanced tab.
  3. Scroll to the Security section, and then select Enable Integrated Windows Authentication.
  4. Click OK, and then restart the client computer.

To assign the Business Desk user to the Power Users group

  1. On the Business Desk client computer, click Start, point to Programs, point to Administrative Tools, and then click Computer Management.
  2. Expand Users and Groups, and then click Groups.
  3. Right-click Administrators, and then click Properties.
  4. Add the Business Desk group account, and then click OK.

Step 15: Map the User Login Account to Database Owner (dbo) User

Perform this step for users who will run the Commerce Server DTS tasks, and perform this step for the List Manager service account:

  • For DTS, you must perform this step so if any objects are created by the DTS task, the objects will be owned by the dbo.
  • For List Manager, you must perform this step because the List Manager export component looks for a table called [dbo].[tablename] as the output.

For example, if you or the List Manager service logged on as "joeuser," and you were not aliased to dbo, when you run the DTS tasks, the extended Data Warehouse table names would be owned by "joeuser" and would be qualified incorrectly as "joeuser.<table>." In the case of List Manager, it would look for the wrong table, and the export would fail.

By performing this procedure, you ensure that the table names will be correctly qualified as "dbo.<table>."

For information about database owner (dbo), and about using sp_addrolemember, see sp_addrolemember in SQL Server Books Online.

Ee810478.note(en-US,CS.20).gifNote

  • The sp_addrolemember stored procedure will fail if the user has already been added to the Data Warehouse, for example, by creating a new user via the Security tab in SQL Enterprise Manager. Do not add the account by using any method other than sp_addrolemember. If you have already added the account, you will need to remove it and then run the sp_addrolemember command afterwards.

To map the user login account to dbo user

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click SQL Query Analyzer.

  2. In the Connect to SQL Server dialog box, specify the appropriate SQL server.

  3. In Query Analyzer, in the database drop-down box, select the Data Warehouse database.

  4. In the Query Analyzer window, type exec sp_addrolemember 'dbo', 'domain\login_name'.

    For example, you might type the following:

        Exec sp_addrolemember 'dbo', '<domain>\joeuser'

  5. On the toolbar, click Run.

    The user account you specified will now be aliased to the database owner (dbo) user.

  6. Repeat this step to assign the dbo alias to the List Manager user to Data Warehouse.

    For example, you might type the following:

        Exec sp_addrolemember '<domain>\CSLM','dbo'

Step 16: Enable Users to Run the Model Builder DTS Task

Before you can run the Model Builder DTS task, you must first add your account to the security context of the Predictor service component, that is, to a list of user accounts that are allowed to call the Predictor service.

If you are setting up the Model Builder task to run on a scheduled basis, then you must also add the account for SQL Agent to list of allowed users to call the Predictor service.

To add your account to the Predictor security context

  1. Click Start, click Run, type Dcomcnfg, and then press Enter.
  2. On the Applications tab, select Microsoft Commerce Server Predictor Service, and then click Properties.
  3. On the Security tab, select Use custom access premissions, and then click Edit.
  4. On the Registry Value Permissions dialog box, click Add.
  5. Add the user account you want to run the Model Builder task, and in the Type of Access box, select Allow Access.
  6. The Administrators group must have the Log on as a service right.
  7. Click OK to save your the changes, click OK to exit the Properties dialog box, and then click OK to exit Dcomcnfg.
  8. Restart the Predictor service.

Copyright © 2005 Microsoft Corporation.
All rights reserved.