Issue

Installation or conversion to Sage Pro ERP with a Microsoft SQL Server (SQL) database completed without error. However, the following message appears when a user tries to login to Sage Pro:

Invalid User ID or Password or Could not connect to the database.

Solution or Workaround

This issue occurs because no SQL logins were created for Sage Pro users. This occurs if the Sage Certified Consultant installing or converting Sage Pro did not have the appropriate privileges in SQL to create new logins.

Important
When installing or converting to SQL, adding users to a Sage Pro SQL installation, or importing and exporting companies involving a Sage Pro SQL installation, the program will prompt for the SQL Administrator login; this is not the Sage Pro Administrator login. Issues such as those described in this article may occur if a non-Administrator SQL login is used.

It is highly recommended that only a SQL Administrator login be used in these situations. However, if it not possible to use a SQL Administrator login, refer to the list below for privileges that must be assigned to avoid these issues:

• Security Administrators
  This privilege is required to add users to SQL. Otherwise, the installation may complete but no users will be added to SQL and Sage Pro will not be accessible.
• Disk Administrators
  This allows the ability to manage SQL disk files.
• Database Creators
  This privilege is required when the installation attempts to create databases for Sage Pro, otherwise, the following error may appear:
  Allowed DO nesting level exceeded
• Database Owner (DBO)
  This must be assigned for all existing Sage Pro databases in the installation. It is required for existing SQL installations and is especially important when adding new users and importing and exporting companies. If the login is not a DBO for all Sage Pro databases, the following error may appear:
  Error 1526: Server user [AI-Pro User ID] is not a valid user in Database [name of database].

To ascertain whether these privileges exist for a particular login, follow the steps below in the SQL Enterprise Manager:

1. Open SQL Enterprise Manager.
2. Expand the Registered SQL Server tab.
3. Click Security.
4. Click Logins; all SQL logins should appear in the right-hand frame.
5. Right-click the appropriate login and click Properties.
6. Click Server Roles.
7. Review the privileges and ensure that Security Administrators, Disk Administrators and Database Creators are selected.
8. To ensure that the login is a DBO for all Sage Pro databases, click Database Access.
9. In this tab, select Permit for each Sage Pro database. This includes all Sage Pro system tables such as ProSys and ProTemp as well as all company databases such as ProSampleData. The option for db_owner should also be selected in the lower scroll pane for each of these databases.

   Important
   If System Administrators is selected for this login, Security Administrators, Disk Administrators and Database Creators do not need to be assigned as they are included with the System Administrators setting.

If this issue occurs for only some users and the option to create non-existing users was selected, this may be corrected by logging into Sage Pro as an administrator and resetting the password for the new users. This has been documented under PTR 82653.

If this issue is encountered, it is best to restore to the original installation and redo the conversion with the proper privileges. Whether this is a conversion or a new installation, follow the steps below to correct this issue:

Important
All database components must be removed. It is best to install new databases under a new name, in case there are any old remaining components.

• Completely remove all Sage Pro directories used or created during installation.
• After these directories have been removed, restore to the backup. Restoring over top of these directories will only replace files, not remove new ones. These new files may create errors when attempting to convert again.
• The SQL Database Administrator should remove any Sage Pro databases that were created during this installation. This includes the system, temporary, sample data and any other company databases.

In Sage Pro 7.4, SQL 2005 and SQL 2005 Express databases are supported. To use these databases, the TCP/IP protocol in the SQL Server Configuration Manager must be enabled. Follow the steps below:

1. Open the SQL Server Configuration Manager.
   1. Click Protocols for XXXX<sup>*
      
      *</sup>XXXX denotes the SQL Server name
   2. Click TCP/IP.
   3. Right-click TCP/IP and click Enable.
   4. The following message appears:

      Any changes made will be saved; however, they will not take effect until the service is stopped and restarted.

   5. Click OK.
   6. Under the SQL Server 2005 Services, stop and restart the SQL Server FullText Search, SQL Server, SQL Server Agent and SQL Server Browser.

The TCP/IP protocol for both SQL 2005 or SQL 2005 Express has now been enabled. Both databases can now be installed and used with Sage Pro. For more information on the SQL Server Configuration Manager, please refer to the SQL Server 2005 manual or a Microsoft SQL Server Database Administrator.