How do I setup CCH Fixed Assets Manager databases?

Fixed Assets Manger is designed to use Microsoft SQL Server as a data engine for its multi-user, network installations. If you use the Network version of Fixed Assets Manager, your database administrator must manually set up the system and user databases before you can use the program.

Database Recommendations:

  • We recommend that a database maintenance routine be created. For more information refer the 'Database Maintenance wizard' in the Microsoft SQL Server Books Online. We recommend that a qualified SQL DBA perform these tasks when in a production environment.
  • Creating New Databases for Network (Multi-user) Installations

 

Attaching Empty Database Files:

  • To create the databases, attach the system database and the user database to your SQL Server instance by using SQL Server Management Studio.
    • Note: The detached database files are located in the DATABASES folder on the Fixed Assets Manager installation image. If the database server is not already registered, register the server in Management Studio. For more information, refer to 'How to: Create a New Registered Server (SQL Server Management Studio)' in the Microsoft SQL Server Books Online.

  • System Database - To attach a Fixed Assets Manager system database (FASYS):
    1. The FASYS.MDF database file is located in the DATABASES folder on the Fixed Assets Manger installation image. Copy FASYS.MDF to the location where the SQL Server instance is located. Verify that FASYS.MDF does not contain the Read-Only attribute.
    2. Open SQL Server Management Studio.
    3. In SQL Management Studio, expand the database server where the database will reside.
    4. Select the Databases folder on the database server and right-click.
    5. From the list, select Attach.
    6. Select Add and browse to the location where your system data file (FASYS.MDF) is located.
      • Note: The 'Attach as' field displays the default database name. Do not change the system data filename.
    7. In the "FASYS" database details' section highlight fasys_log.ldf and click Remove.
    8. Select OK.
    9. When the database is successfully attached, you will see 'fasys' listed under the Databases folder.

  • User Database - To attach the Fixed Assets Manager user database (the default name is FADATA):
    1. The FADATA_data.MDF database file is located in the Databases folder on the Fixed ASsets Manager image.
    2. Copy FADATA_data.MDF from the image folder to the location where the SQL Server instance is located.
    3. Verify that FADATA_data.MDF does not contain the Read-only attribute.
    4. Open SQL Server Management Studio
    5. In SQL Server Management Studio, expand the database server where the database will reside.
    6. Select the Databases folder on the database server and right-click.
    7. From the list, select Attach.
    8. Select the Add button and browse to the location where you user data file (FADATA_data.MDF) is located.
      • Note: The 'Attach as' field displays the default database name. Don not change the user data filename.
    9. In the "fadata" database details' section, highlight data_log.ldf and click Remove.
    10. Select OK.
    11. When the database is successfully attached, you will see 'fadata' listed under the Databases folder.

 

Creating Users and Granting Rights:

  • For Fixed Assets to run correctly, the application needs access to the SQL Server engine. You can access this either through a 'trusted connection' using Windows authentication or a 'non-trusted connection' using SQL Server authentication. Check your internal IT policies to determine if either method of authentication is a preference or requirement. This connection must be created for each Fixed Assets Manager user. Certain rights and permissions must be assigned to the connection.
    • Note: We recommend creating a domain Users Group for users of the Fixed Assets Manager application. Database access can then be granted to the group. Using group allows you to manager many users with a single step.

  • Option A - Creating a Trusted Connection (Recommended)
    • A trusted connection means that a user will connect to the database using Windows Authentication.

    • To create users who will access Fixed Assets Manager through a trusted connection\Windows authentication:
      1. Open SQL Server Management Studio
      2. Expand the REDMOON database server, select Security > Logins.
      3. Right-click on Logins and select New Login. Login - New opens.
      4. Select the Search button next to the Login Name field.
      5. Select the Advanced button.
      6. Select the Object Types... button. Verify that Users and Groups is selected.
      7. Verify the name of your company's network domain appears under the 'From this Location' heading. If not, select the Locations... button to select the appropriate domain account network.
      8. Select the Find Now button. A list of your domain user and group accounts should appear.
      9. Select the User or Group (recommended) and then select OK.
      10. Select OK again on the Select User or Group dialog screen.
      11. Select User Mapping under Select a page area of the Login - New dialog.
      12. Under the Users mapped to this login: area, select the fadata database.
      13. Select the following roles for the fadata database:
        • db_datareader
        • db_datawriter
        • db_ddladmin
        • public
          • Note: In order for a user to create Accounting Information Fields within Fixed Assets Manager, they must have either db_ddladmin or db_owner access to the FADATA database. Without these rights, users will not be able to create or delete Accounting Information Fields and may have problems backing up entities, and restoring entities. Users with db_owner access can change entity access within the program.
      14. Select the following roles for the fasys database:
        • db_datareader
        • public
      15. Select OK. The user will now have a trusted connection.
      16. If you did not grant rights to a Users Group, you must repeat steps 2 through 14 for each individual Fixed Assets Manager user.

  • Option B - Creating a Non-Trusted Connection
      • A non-trusted connection means that a user will connect to the database using SQL Server authentication.

    • To create users who will access Fixed Assets Manager through a non-trusted connection\SQL Server authentication:
      1. Open SQL Server Management Studio.
      2. Expand the REDMOON database server, select Security > Logins.
      3. Right-click on Logins and select New Login. Login - New opens.
      4. Select SQL Server Authentication, and then enter a user name in the Login Name field and a password in the Password field.
      5. Follow Steps 10 through 14 in the Creating a Trusted Connection section above and select OK. The user will now have a non-trusted connection.
      6. Repeat for each Fixed Assets Manager user. Each user will need to be notified of their new Login Name and Password.

 

Granting Execute Permissions on the User Database(s):

    • For Fixed Assets Manager to work correctly, each login or connection needs execute rights to the stored procedures in the user database. To grant an existing user or SQL group the execute permission to the stored procedures in FADATA ( or your alternatively named user database):

  1. Open SQL Server Management Studio.
  2. Expand the REDMOON database server.
  3. Select File > Open and navigate to the \FAPROG folder where the program is installed and open the FamGrantExecute.sql file. 
  4. Verify that the database name specified on the USE line is correct (FADATA by default).
    • Note: The FamGrantExecute.sql script grants the 'execute' privilege on stored procedures in the FADATA database to all users in the 'Public' database role. Your company policy may not permit granting this privilege to 'Public'. Step 5 is only necessary if you wish to grant execute privileges to someone else other than 'Publc'. If you do not need to do this, skip Step 5 and proceed to Step 6.
  5. By default FamGrantExecute.sql grants permissions to all users in the 'Public database role. If you need to grant permission solely to a SQL group or individual, replace the work 'Public' with the new username or group:
    • From the first Grant line, select Public.
      • Note: The 'Find' field should already display the word 'Public'. If not, type in the word Public in the Find field.
    • Select Edit > Replace.
    • In the Replace with field, enter a valid username and select Replace All.
    • When the replace is complete, close Replace.
  6. To execute the query, press F5 or select Execute.
  7. Repeat Steps 5 and 6, as necessary, to grant another user execute permission.

 

Setting Up the FADBNAMES.fad file:

  • The FADBNames.fad file is a text file, which is utilized only in Network (Multi-user) installations and includes all databases that the Fixed Assets Manager product can access. This file includes the name of the database, the location of the database, and a connection flag. This file is ignored in Local (Single Workstation) deployments of the software.
  • To set up the Fixed Assets Manager FADBNames.fad file:
    1. In Windows Explorer, navigate to the \FAPROG folder where the program is installed.
    2. Open FADBNames.fad with a text editor such as WordPad.
    3. After the file is open, you can add, delete, or modify the database entries. The format for an entry is as follows:
      • DatabaseName|Location|Connection Flag

        where

      • DatabaseName equals the actual name of the database;
        • Location equals Server Name or IP Address
        • Connection Flag equals 0 for Non-Trusted Connection or 1 for Trusted Connection.
      • Examples:
        • FADATA|Your SQLServerDBName|1;
        • FADATA|123.123.123.123|1;
      • Note: The entry in the FADBNames.FAD file must be terminated with a semi-colon. Verify there are not spaces in the connection string.
      • If you are using a named instance of SQL Server, and not the default instance, the entry should include the instance name.
        • Example: FADATA|YourSQLServerDBName\YourInstanceName|1;
    4. After the modifications to the FADBNames.fad file are complete, save and close the file. 
  Solution Tools
  Attachments
 Solution Id 000037264/sw46323
 Direct Link
To provide feedback on this solution, please login.

Your feedback about this article will help us make it better. Thank you!