Unlock Advanced SQL Server Options for Dynamics GP User Setup: A Deep Dive

Table of Contents

Microsoft Dynamics GP, a robust enterprise resource planning (ERP) system, offers extensive customization and security features to manage business operations effectively. Among these features are advanced SQL Server options within the User Setup window, designed to enhance security and streamline user management by integrating with Active Directory domain password policies. Understanding and correctly implementing these options is crucial for maintaining a secure and compliant Dynamics GP environment. This article provides a detailed exploration of these advanced SQL Server options, answering key questions and providing insights into their functionality and prerequisites.

Understanding the Purpose of Advanced SQL Server Options¶

Q1: What is the purpose of the advanced SQL Server options in the User Setup window?¶

The primary purpose of the advanced SQL Server options in the Dynamics GP User Setup window is to bridge the gap between Microsoft Dynamics GP security and the overarching Active Directory domain password policies. These options are specifically engineered to allow Dynamics GP to leverage the established password policies configured within your organization’s Active Directory environment. By enabling these settings, you ensure that Dynamics GP users are subject to the same stringent password requirements and security protocols as other users within the domain. This integration strengthens overall security posture and simplifies password management across the organization by centralizing policy enforcement through Active Directory. In essence, these options extend the reach of your domain’s security policies to Dynamics GP, creating a unified and more secure user authentication system.

Prerequisites for Implementing Advanced Options¶

Q2: What are the prerequisites for these options?¶

Before you can effectively utilize the advanced SQL Server options in Dynamics GP, it’s imperative to ensure that your system environment meets specific prerequisites. These prerequisites encompass both software and operating system requirements, ensuring compatibility and seamless integration. Firstly, you must be running Microsoft Dynamics GP 9.0 or a later version. This ensures that the necessary features and functionalities related to advanced SQL Server options are available within your Dynamics GP installation. Secondly, your database environment needs to be powered by SQL Server 2005 or a later version. This is critical because these options leverage SQL Server’s capabilities for integration and policy enforcement.

Furthermore, your server infrastructure needs to be running on a compatible operating system. This includes Windows Server 2003 or Windows Server 2008, or later versions, which are necessary to host both Active Directory and SQL Server effectively. The domain itself must also be a Windows Server 2003 or Windows Server 2008 domain, ensuring that Active Directory services are properly configured and accessible. Lastly, each client workstation accessing Dynamics GP must have either SQL Server Native Client or SQL Server Native Client 10.0 installed. This component is essential for establishing a robust and reliable connection between the Dynamics GP client and the SQL Server database, particularly when utilizing advanced security features. Ensuring all these prerequisites are in place is fundamental for the successful implementation and operation of the advanced SQL Server options in Dynamics GP.

Q3: Where can I obtain SQL Native Client?¶

SQL Native Client is a crucial component for establishing connectivity between Dynamics GP clients and the SQL Server database, especially when leveraging advanced features. Obtaining and installing it is a straightforward process, typically accomplished using the installation media for SQL Server.

Method 1: Using SQL Server 2005 CD:

If you are using SQL Server 2005, the SQL Native Client installer can be found directly on the SQL Server 2005 installation CD. To access it, insert the SQL Server 2005 CD into your CD-ROM drive. Navigate to the Servers\Setup folder located on the CD. Within this folder, you will find a file named Sqlncli.msi. Double-click this Sqlncli.msi file to initiate the installation process of SQL Native Client on the client workstation. Follow the on-screen instructions to complete the installation.

Method 2: Using SQL Server 2008 CD:

For environments utilizing SQL Server 2008, the SQL Native Client installer is also available on the SQL Server 2008 installation CD. Insert the SQL Server 2008 CD into your CD-ROM drive. Navigate to the x86\Setup\x86 folder on the CD. Inside this folder, locate the Sqlncli.msi file. Double-click the Sqlncli.msi file to launch the SQL Native Client installation. Proceed with the installation by following the prompts and instructions provided by the installer.

Both methods provide a direct and reliable way to install SQL Native Client, ensuring that your Dynamics GP client workstations are properly configured to communicate with the SQL Server database and utilize the advanced SQL Server options effectively.

Domain Password Policies and Dynamics GP Integration¶

Q4: What domain password policies are in effect when the advanced SQL Server options are enabled?¶

When you enable the advanced SQL Server options in Dynamics GP, specifically by selecting the “Enforce Password Policy” checkbox in the User Setup window, you are instructing Dynamics GP to actively enforce several domain password policies that are configured within your Active Directory. This integration ensures that user passwords within Dynamics GP adhere to the same security standards as other domain accounts.

The domain password policies enforced when “Enforce Password Policy” is selected include:

• Enforce Password History: This policy prevents users from reusing recently used passwords. If enabled in Active Directory, Dynamics GP will also enforce it, requiring users to choose new passwords that are not part of their password history.

• Minimum Password Length: This policy dictates the minimum number of characters required for a password. If set in Active Directory, Dynamics GP will ensure that all new or changed passwords meet this minimum length requirement.

• Password Must Meet Complexity Requirements: This policy enforces password complexity rules, such as requiring a mix of uppercase and lowercase letters, numbers, and symbols. When enabled in Active Directory and enforced in Dynamics GP, users must create passwords that adhere to these complexity criteria.

• Account Lockout Duration: This policy specifies how long an account will be locked out after a certain number of failed login attempts. If configured in Active Directory, Dynamics GP will respect this lockout duration, temporarily disabling user accounts after too many incorrect password entries.

• Account Lockout Threshold: This policy defines the number of invalid password attempts that will trigger an account lockout. Dynamics GP, when enforcing password policies, will adhere to this threshold set in Active Directory, locking accounts after the specified number of failed login attempts.

In addition to “Enforce Password Policy”, the “Enforce Password Expiration” checkbox also integrates with Active Directory policies. When selected, it enables the following domain password policies within Dynamics GP, provided they are also enabled in Active Directory:

• Minimum Password Age: This policy specifies the minimum time a user must use a password before it can be changed. If set in Active Directory, Dynamics GP will prevent password changes before this minimum age is reached.

• Maximum Password Age: This policy dictates the maximum period a password can be used before it expires and must be changed. Dynamics GP will enforce this policy, prompting users to change their passwords when they reach the maximum password age defined in Active Directory.

Finally, the “Change Password at Next Login” checkbox provides a straightforward mechanism to force a user to change their password upon their next login to Dynamics GP. It’s important to note that no direct domain policy is related to this specific option. This is a Dynamics GP specific setting to immediately enforce a password change for a user.

By understanding which domain password policies are linked to each of these advanced SQL Server options, administrators can effectively manage user security and ensure compliance with organizational password standards within Microsoft Dynamics GP.

Q5: How do the Account Lockup Threshold and the Account Lockup Duration domain policies relate to Microsoft Dynamics GP?¶

The Account Lockout Threshold and Account Lockout Duration domain policies are critical security measures that directly impact user access and security within Microsoft Dynamics GP when advanced SQL Server options are enabled.

The Account Lockout Threshold policy is designed to prevent brute-force password attacks by specifying the number of failed login attempts that will result in locking a user account. When this threshold is reached, the user is locked out of the system, preventing further unauthorized access attempts.

The Account Lockout Duration policy complements the threshold policy by defining the period for which a locked-out account remains disabled. After this duration expires, the account is automatically unlocked, or it may require administrative intervention to unlock, depending on the specific Active Directory configuration.

The interaction of these policies with Dynamics GP behavior varies based on the Dynamics GP version and service pack level, particularly concerning how password attempts are handled during login:

• Microsoft Dynamics GP 10.0 and Service Pack 1 - 4 for Microsoft Dynamics GP 10.0: In these versions, Dynamics GP sends the user’s password to SQL Server four times during each logon attempt. This is a crucial detail because it means that a single user login attempt can consume four password attempts against the Account Lockout Threshold policy. Therefore, to achieve a lockout after, for example, three actual user logon attempts, the Account Lockout Threshold policy in Active Directory needs to be set to 12 (3 attempts * 4 password submissions per attempt).

• Microsoft Dynamics GP 10.0 Service Pack 2 and Later Service Packs, and Microsoft Dynamics GP 2010: Starting with Service Pack 2 for Dynamics GP 10.0 and in Dynamics GP 2010, the behavior changed. These versions send the user’s password to SQL Server only one time during each logon attempt. This more efficient approach aligns the password attempts more directly with user actions. In these versions, to lock out a user after three logon attempts, the Account Lockout Threshold policy should be set to 3.

Given these nuances, it is generally recommended to set the Account Lockout Threshold to a higher value, such as 50, to accommodate potential variations in password attempt handling across different Dynamics GP versions and service pack levels, and to minimize the risk of accidental account lockouts due to multiple password submissions during a single login attempt, particularly in older versions of Dynamics GP. This higher threshold provides a more forgiving margin for error while still maintaining a reasonable level of security against brute-force attacks.

It is essential for administrators to understand these version-specific behaviors and carefully configure the Account Lockout Threshold and Duration policies in Active Directory to strike a balance between robust security and user accessibility within their Dynamics GP environment. Proper configuration prevents both unauthorized access and unnecessary disruptions to legitimate user access due to account lockouts.

Password Change Scenarios and Domain Policies¶

Q6: Does the Minimum Password Age domain policy affect a user who wants to change his or her password?¶

Yes, the Minimum Password Age domain policy directly impacts a user’s ability to change their password in Dynamics GP. This policy, when enabled and configured in Active Directory, enforces a waiting period after a password is set or changed before it can be altered again.

Once a password is set for a user account, the Minimum Password Age policy dictates that the user must wait for a specified duration before they are permitted to change it. This policy is designed to enhance security by preventing users from repeatedly changing passwords in quick succession, which could potentially weaken password history controls and complexity requirements.

For example, if the Minimum Password Age policy is set to one day, a user who has just created a new password or changed their existing password will not be able to change it again until at least one day has passed from the time of the last password change. If the user attempts to change their password before this waiting period expires, the system will prevent the change and typically display an error message indicating that the minimum password age requirement has not been met.

This policy is particularly relevant in Dynamics GP when users attempt to change their passwords through the User Preferences window or when prompted to change an expired password. If the Minimum Password Age policy is in effect, Dynamics GP will respect this policy and prevent password changes until the specified time has elapsed.

Therefore, administrators need to be aware of the Minimum Password Age policy setting in Active Directory and its impact on users’ ability to manage their passwords within Dynamics GP. If users report being unable to change their passwords, even though they believe they are following all other password rules, the Minimum Password Age policy is a likely cause to investigate. Adjusting this policy in Active Directory, if necessary, can resolve such issues, but it should be done with careful consideration of the overall security implications.

Compatibility with Older Dynamics GP Versions¶

Q7: Do the advanced SQL Server options work in Microsoft Business Solutions - Great Plains 8.0?¶

No, the advanced SQL Server options are not functional and are unavailable in Microsoft Business Solutions - Great Plains 8.0. In this older version of the software, these options will appear dimmed or greyed out within the User Setup window, indicating that they cannot be selected or configured.

These advanced features, designed to integrate with Active Directory domain password policies, were introduced in later versions of Dynamics GP, specifically starting from Microsoft Dynamics GP 9.0. Microsoft Business Solutions - Great Plains 8.0, being an earlier iteration of the product, does not include the necessary underlying architecture and functionalities to support these advanced SQL Server options.

Therefore, if you are using Microsoft Business Solutions - Great Plains 8.0, you will not be able to leverage the benefits of integrating Dynamics GP user password management with Active Directory domain policies through these advanced settings. To utilize these features, upgrading to a more recent version of Microsoft Dynamics GP, such as Dynamics GP 9.0 or later, is required. Modern versions of Dynamics GP offer enhanced security features, improved integration capabilities, and other advancements that were not present in Great Plains 8.0.

Troubleshooting Password Change Errors¶

Q8: If I enable the advanced SQL Server options, why do users receive the following error message when they try to change their passwords in the User Preferences window?¶

The password change failed for an unknown reason. Enter a different password or contact your system administrator.

This error message, “The password change failed for an unknown reason. Enter a different password or contact your system administrator,” encountered when users attempt to change their passwords in the Dynamics GP User Preferences window after enabling advanced SQL Server options, is often directly related to the Minimum Password Age domain policy.

This problem specifically arises when the Minimum Password Age domain policy is set to a value greater than 0 in Active Directory. As explained previously, this policy enforces a waiting period before a password can be changed again.

When the Minimum Password Age is set to a number of days (e.g., 1, 7, 30), a password change attempt will be rejected if it occurs before the specified number of days has passed since the last password change. Dynamics GP, when integrated with Active Directory password policies, respects this rule. Therefore, if a user tries to change their password too soon after a previous change, Dynamics GP will receive a rejection from the domain controller due to the Minimum Password Age policy, resulting in the “unknown reason” error message.

The error message itself is generic and does not explicitly state the Minimum Password Age policy violation, which can make troubleshooting challenging for users and administrators. However, this policy is a primary suspect when encountering this error after enabling advanced SQL Server options.

To resolve this issue and allow users to change their passwords in the User Preferences window at any time, one potential solution is to set the Minimum Password Age domain policy to 0. Setting it to 0 effectively disables the minimum password age restriction, allowing users to change their passwords whenever they meet other password policy requirements (such as complexity and length).

However, disabling the Minimum Password Age policy should be considered carefully from a security perspective. While it resolves the immediate password change error, it may weaken password history controls and could potentially reduce overall password security. Organizations should weigh the usability benefits of allowing frequent password changes against the potential security implications of disabling this policy. A balanced approach may involve setting a very short Minimum Password Age, such as 1 day, to provide some level of control without overly restricting password changes and causing user frustration.

Troubleshooting Password Creation Errors: Password Length¶

Q9: Why do I receive the following error message when I click Save after I create a new user?¶

The password is too short, enter a different password.

This error message, “The password is too short, enter a different password,” encountered when saving a new user in Dynamics GP after enabling advanced SQL Server options, is a clear indication that the password being set for the new user does not meet the requirement of the Minimum Password Length domain policy.

This issue specifically arises when the “Enforce Password Policy” checkbox is selected in the User Setup window. When this option is enabled, Dynamics GP actively validates new passwords against the password policies defined in Active Directory, including the Minimum Password Length policy.

If the password entered for the new user is shorter than the minimum length specified in the Minimum Password Length policy in Active Directory, Dynamics GP will reject the password and display this error message. The system is enforcing the domain policy, ensuring that all user passwords meet the minimum security standards defined by the organization.

To resolve this error, you need to ensure that the password you are setting for the new user meets or exceeds the minimum password length defined in the Active Directory domain policy. You should consult your organization’s password policy documentation or your system administrator to determine the specific minimum password length requirement. Once you have this information, create a new password for the user that satisfies this length requirement and try saving the user again. The error should then be resolved, and the new user account should be created successfully.

Troubleshooting Password Creation Errors: Complexity Requirements¶

Q10: Why do I receive the following error message when I click Save after I create a new user?¶

This password does not meet the minimum criteria defined by the system administrator. Enter a different password.

This error message, “This password does not meet the minimum criteria defined by the system administrator. Enter a different password,” appearing when saving a new user in Dynamics GP with advanced SQL Server options enabled, indicates that the password being set fails to meet the requirements of the Password Must Meet Complexity Requirements domain policy.

Similar to the previous error, this issue occurs when the “Enforce Password Policy” checkbox is selected in the User Setup window. With this option enabled, Dynamics GP validates new passwords against all enforced Active Directory password policies, including password complexity.

The Password Must Meet Complexity Requirements policy typically dictates that passwords must include a combination of different character types, such as:

• Uppercase letters (A-Z)
• Lowercase letters (a-z)
• Numbers (0-9)
• Symbols (e.g., !, @, #, $, %, ^, &, *)

The specific complexity rules can vary depending on how the policy is configured in Active Directory. If the password you are attempting to set for the new user does not include the required mix of character types as defined by this policy, Dynamics GP will reject the password and display this error message. The system is enforcing the domain’s password complexity standards to enhance password strength and security.

To resolve this error, you need to create a new password for the user that adheres to the Password Must Meet Complexity Requirements domain policy. This typically involves including a combination of uppercase and lowercase letters, numbers, and symbols in the password. Refer to your organization’s password policy documentation or consult your system administrator for the precise complexity rules in place. Once you have crafted a password that meets these complexity criteria, try saving the new user again. The error should be resolved, and the user account should be created successfully.

Troubleshooting Logon Errors: Password Expiration¶

Q11: Why do I receive the following error message when I log on to Microsoft Dynamics GP?¶

Your password has expired. Do you want to change your password now?

This message, “Your password has expired. Do you want to change your password now?”, encountered upon logging into Microsoft Dynamics GP, is a direct result of the Maximum Password Age domain policy being enforced.

This situation arises because the user’s password has reached the maximum allowed age as defined by the Maximum Password Age policy in Active Directory. When a password reaches this age limit, it is considered expired, and the user is required to change it for security reasons.

Dynamics GP, when integrated with Active Directory password policies through the advanced SQL Server options, actively checks for password expiration upon user logon. If the system detects that the user’s password has exceeded the Maximum Password Age, it will present this message, prompting the user to change their password immediately.

This prompt is a security best practice, encouraging users to regularly update their passwords to mitigate the risk of compromised credentials. Clicking “Yes” in response to this message will typically guide the user through the password change process within Dynamics GP. Clicking “No” may prevent the user from logging into Dynamics GP until the password is changed, depending on the specific configuration and policy enforcement.

To resolve this, the user must change their password when prompted. The new password must, of course, comply with all other enforced password policies, such as minimum length and complexity requirements. Regular password changes enforced by the Maximum Password Age policy are a fundamental aspect of maintaining a secure IT environment.

Troubleshooting Logon Messages: Forced Password Change at Next Login¶

Q12: Why do I receive the following message when I log on to Microsoft Dynamics GP?¶

You must change your password before access Microsoft Dynamics GP. Do you want to change your password now?

This message, “You must change your password before access Microsoft Dynamics GP. Do you want to change your password now?”, encountered at Dynamics GP logon, is triggered by the “Change Password at Next Login” option being enabled for the user in the User Setup window.

Unlike the previous password expiration message, this message is not directly related to a domain password policy. Instead, it is a specific Dynamics GP feature that administrators can use to force a user to change their password upon their next login. This is often used when initially setting up a new user account or when requiring a user to update their password for security reasons, such as after a potential security incident or as part of a periodic password reset policy.

When the “Change Password at Next Login” checkbox is selected for a user in Dynamics GP, the system flags their account to require a password change on the next successful login attempt. Upon the user attempting to log in, Dynamics GP will detect this flag and display this message, forcing the user to change their password before they can proceed to use the application.

Clicking “Yes” will initiate the password change process within Dynamics GP. The user will be prompted to enter their current password (if applicable) and then a new password that conforms to any enforced password policies. Clicking “No” may prevent the user from accessing Dynamics GP until they complete the password change, depending on the system’s configuration.

To resolve this, the user must change their password as prompted. This is a mandatory password change enforced by the Dynamics GP administrator. After successfully changing their password, the user will be able to log into Dynamics GP and use the application normally.

Troubleshooting Logon Errors: Password History Enforcement¶

Q13: Why do I receive the following error message when I log on to Microsoft Dynamics GP?¶

This password cannot be used at this time. Enter a different password.

This error message, “This password cannot be used at this time. Enter a different password,” encountered during Dynamics GP logon, indicates that the password the user is attempting to use violates the Enforce Password History domain policy.

This issue occurs when the “Enforce Password Policy” checkbox is selected in the User Setup window, and the Enforce Password History policy is active in Active Directory. The Enforce Password History policy prevents users from reusing passwords that they have used in the past. Active Directory maintains a history of previously used passwords, and if a user attempts to set or reuse a password that is in this history, the password change or logon attempt will be rejected.

When a user encounters this error during logon, it means that the password they are trying to use is in their password history and is therefore not permitted to be reused. This policy is designed to enhance security by preventing users from cycling through a small set of passwords repeatedly, which could make it easier for attackers to compromise accounts.

To resolve this error, the user must choose a new password that is not in their password history. This means selecting a password that is sufficiently different from their previously used passwords. The number of previous passwords kept in history is configurable within the Enforce Password History policy in Active Directory (e.g., the policy might prevent reuse of the last 5, 10, or 24 passwords).

When prompted with this error, the user should try a different password, ensuring it meets all other password policy requirements (length, complexity, etc.) and is not one of their recently used passwords. By choosing a genuinely new password, the user should be able to successfully log into Dynamics GP.

---

Do you have any experiences with implementing these advanced SQL Server options in Dynamics GP? Share your insights or questions in the comments below!