WordCustomRefiner2#afd1ca55-403d-404c-b60c-692906bef937
WordCustomRefiner3#cb001ab7-1f19-4da1-8860-3e04d243802c

How to Create an Application Role in SQL Server 2008 for Forecaster 7.0

Last Modified Tuesday, December 24, 2013
Top Solution

Download

By using an application role, users will be unable to log on to a database by using ODBC or another querying tool unless they know the password for the role. This helps prevent unauthorized access to the Forecaster database data. The Microsoft Forecaster 7.0 client will be able to read the application role password and let users log on.
Sign In Popup
Lock Icon

The content of this page is locked.

To access this information log in to CustomerSource.

Unable to log in to CustomerSource?

Visit our CustomerSource Help Page.

Close

Lock Icon

The content of this page is locked.

To access this information log in to CustomerSource.

Unable to log in to CustomerSource?

Visit our CustomerSource Help Page.

Lock Icon

Your current service plan does not allow access to this information.

To learn more about Microsoft Dynamics Service Plans review our
service plan offerings.

Prerequisites:
Microsoft Forecaster must be on SP1 or higher
Make a backup of your Forecaster database prior to implementing the Application Role.
Read the following consideration regarding Microsoft FRx DirectLink

Important Considerations:
Microsoft FRx DirectLink does not utilize an Application Role. This means that non-Administrator Forecaster users will need to be granted additional SQL permissions to be able to use their Forecaster user with FRx DirectLink. These additional SQL permissions and the steps required to add them are discussed at the end of this article. By granting these users the additional SQL permissions they will be able to run SELECT queries on the following tables from a separate program other than the Forecaster client:

M_SEG   (Segment Info)
R_SEG    (Segment Info)
G_SEG   (Segment Info)
Z_BKC    (Bookcodes)
Z_BKT    (Periods)
Z_RAW  (Account balances)
Z_RSP    (User assignment info)
Z_SEG    (Segment Info)
Z_SEC    (Group Security Options)
Z_HLM2    (System Options)
Z_ROU      (Routings)
Z_USR       (User Info)

Setting up the Application Role

Remove previous Forecaster role and Forecaster schema:

  1. Open Microsoft SQL Management Studio and navigate to the Microsoft Forecaster database. 
  2. Expand the database node, expand the Security folder, and expand the Database Roles folder under the Forecaster database.
  3. Select the existing Forecaster role and delete it.  If asked to delete the schema with the same name, click Yes.

Create new Forecaster Application Role and Schema and assign initial permissions:

  1. Expand the Schemas folder under the Security folder.
  2. Right-click on the Schemas folder and select New Schema.
  3. Type “FCSchema” for the Schema name and “dbo” for the Schema owner.
  4. Click OK to save the schema.
  5. Right-click on the Application Roles folder and select New Application Role.
  6. Type “Forecaster” as the Role name and “FCSchema” as the Default schema.
  7. Specify the password for the role.
  8. Under Owned Schemas, check the FCSchema box.
  9. Select Securables on the Select a page area on the left.
  10. Click Search.
  11. Select the Specific objects option and click OK.
  12. Click the Object Types button.
  13. Mark the checkbox next to Databases and click OK.
  14. Type in the name of the Forecaster SQL Database and click OK.
  15. Select the database and then mark the Grant column checkbox for the following permissions:
    • Create table
    • Create procedure
  16. Click OK.
  17. Download the GrantpermissionsFC70.sql script from the following link:
  18. Run the GrantpermissionsFC70.sql script against your Microsoft Forecaster SQL database. This script will associate permissions on the tables to the Forecaster Application Role.


Provide the Application Role password to Forecaster:

  1. Run the following statement to provide Microsoft Forecaster with the password for the Forecaster Application Role:
    • Insert into Z_HLM3 values('A',' thepassword ')
    • Note In the above statement replace “thepassword” with the password you chose in step 10 for the Forecaster Application Role.
  2. Once this query has been completed, a Microsoft Forecaster Administrator user must login to the Forecaster client. 
  3. After the login process completes, the record inserted into Z_HLM3 will be removed and it will be encrypted inside the database.

    Note Whenever the Database Administrator needs to change the Forecaster Application role's password, steps 22 and 23 must be repeated. If they are not, the non-administrator users will not be able to log into Microsoft Forecaster. 
     

Assign additional permissions:

  1. Right-click on the Forecaster Application Role in SQL Management Studio and click Properties.
  2. Select Securables in the Select a page area on the left.
  3. Click Search.
  4. Select the Specific objects option and click OK.
  5. Click the Object Types button.
  6. Mark the checkbox next to Schemas and click OK.
  7. Type in “dbo” and click OK.
  8. Mark the Grant column checkbox for the following permissions:
    DELETE
    INSERT
    SELECT
    UPDATE
  9. Click OK.
  10. Right-click on the FCSchema under the Schemas folder and click Properties.
  11. Select Permissions in the Select a page area on the left.
  12. Click Search.
  13. Type in Forecaster and click OK.
  14. Mark the checkbox in the Grant column for the following permissions:
    ALTER
    DELETE
    INSERT
    SELECT
    UPDATE

Set up non-Administrator Forecaster users:

Run the following script on the Forecaster SQL database for each non-Administrator Forecaster user. This will assign to them the Forecaster Application Role and the FCSchema using the following command:
ALTER USER USERNAME with DEFAULT_SCHEMA=FCSchema

Note Replace “USERNAME” with the Forecaster username.
Note This user must have the public role on the Forecaster database or the above statement will error.

Set up Administrator users:

Assign all Forecaster Administrator users the db_owner role and the dbo schema.

  1. Expand the Security folder under the SQL Server node in SQL Management Studio.
  2. Expand the Logins folder.
  3. Right-click on a user that is an Administrator in Forecaster and click Properties.
  4. Select User Mapping in Select a page on the left.
  5. Mark the checkbox in the Map column for the Microsoft Forecaster SQL database.
  6. With the Microsoft Forecaster SQL database row highlighted, mark the checkbox next to db_owner and leave public marked as well.
  7. Click OK.
  8. Repeat steps 1-7 for each Administrator user in Forecaster.

How to test the Application Role:

  1. Log into Microsoft SQL Management Studio with a non-administrator  Forecaster user and run the following query on the Forecaster SQL database:

    select  *  from D_HR

    The non-Administrator users should not be able to query this table. If the Application Role is working you will see the following error: The SELECT permission was denied on the object 'D_HR'

    Note If this non-Administrator user is able to query to this table then they have too much permissions. Confirm they only have the public role on the Forecaster database.
  2. Log into the Microsoft Forecaster client and confirm you can pull up the list of departments in an Input Set.
    Note If the user does not have access to the list of their assigned departments you may need to run a Validate and Recovery in the Forecaster client. Also confirm that you have granted the Create table and Create procedure permissions to the Forecaster Application Role.

How to create separate SQL role for use with Microsoft FRx DirectLink:

Microsoft FRx DirectLink does not utilize an Application Role to connect to the Forecaster database. Non-Administrator Forecaster users will need to be granted additional SQL permissions through a Database role to be able to use this user for FRx DirectLink.  As noted in the beginning of this article, this Database role will allow users to run SELECT queries outside of the Forecaster client on the tables listed. The following steps will create a separate Database role on the Forecaster database which will contain the permissions needed for FRx DirectLink.

  1. Expand the Security folder under the Forecaster database.
  2. Right-click on the Database Roles folder and click New Database Role.
  3. Enter FCDirectLink for the Role name and dbo for the Owner.
  4. Click OK.
  5. Run the following SQL script against the Forecaster database to grant SELECT permissions to the FCDirectLink database role.

    declare @tab varchar(30)
    declare @strSQL varchar(255)
    declare tables cursor for select o.name from sysobjects o,sysusers u where o.type='U' and o.uid=u.uid and u.name='dbo'
      OPEN tables
      FETCH NEXT FROM tables INTO @tab
      WHILE (@@FETCH_STATUS <> -1)
       BEGIN
    if(substring(@tab,1,5)='M_SEG') or (substring(@tab,1,5)='R_SEG') or (substring(@tab,1,5)='G_SEG') or
    (@tab='Z_BKC') or (@tab='Z_BKT') or (@tab='Z_RAW') or (@tab='Z_RSP') or (@tab='Z_SEG') or
      (@tab='Z_SEC') or (@tab='Z_HLM2') or (@tab='Z_ROU') or (@tab='Z_USR')
        begin
      select @strSQL = 'grant select on '
      select @strSQL =  @strSQL + @tab + '  to FCDirectLink'
      exec (@strSQL)
        end
     FETCH NEXT FROM tables INTO @tab 
      end
    close tables
    deallocate tables
  6. Expand the Security folder under the SQL Server node in SQL Management Studio.
  7. Expand the Logins folder.
  8. Right-click on a non-Administrator Forecaster user and click Properties.
  9. Select User Mapping in Select a page on the left.
  10. Select the Forecaster database in the database list.
  11. Mark the checkbox for the FCDirectLink database role.
  12. Click OK.






https://mbsauthor.partners.extranet.microsoft.com/sites/CSSL/northamerica/Learning/Pages/MSDForecasterAppRoleSQLserver_howto.aspx
No
Microsoft Dynamics SL