SQL

Setup Options > System Setup > SQL

This validates operator access according to the SQL Server standard user login and password for all operators logging into SYSPRO by default.

You can override this at operator level (Operator Maintenance) which will allow you to trace SQL usage by SQL login.

Select this if the operator login and password required is the network login id that was authenticated by Windows NT.

This indicates the SQL Server name that will be used. This is typically the name of the computer on which SQL is running.

This indicates the SQL driver used in the connection string:

  • SQL Server (the default selection)
  • ODBC Driver 13 for SQL Server
  • ODBC Driver 17 for SQL Server

Only drivers that you have installed are displayed and available for selection.

Select this to ensure that the connection between SYSPRO and Microsoft SQL Server is encrypted.

If you enabled the Encrypted connection string option then you can select this to indicate that the encryption must use a self-signed server certificate.

This option is not available when the SQL Driver to use is defined as SQL Server.

Self-signed certificates don't guarantee security, as the encrypted handshake is based on NT LAN Manager (NTLM). We highly recommend that you provision a verifiable certificate on SQL Server for secure connectivity. Transport Security Layer (TLS) can only be made secure with certificate validation.

This indicates the database of SYSPRO's configuration tables.

Select this to use the SQL Server standard user login and password for all operators logging into SYSPRO.

Select this to use the Login name and Password defined against the operator when logging into SYSPRO.

Select this if you want SYSPRO to generate SQL login details for operators. This becomes the default SQL Login preference for all operators created in future.

If you enabled the Use generated SQL user details option then you can enter characters that you want to prefix the operator code with and use as the SQL login name. Leave blank if no prefix is required.

Up to 20 characters can be used. These characters cannot contain backslash or embedded spaces or start with $ or @.

Enter the minimum number of characters for the password. This can be a value of between 0 and 99, however a warning is displayed if less than 8 characters.

Enter the maximum number of characters for the password. This can be a value of between 0 and 128 but cannot be less than the minimum password length.

The combined length of the following cannot exceed the Maximum password length:

  • Minimum digits required
  • Minimum letters required
  • Minimum special characters required

Enter the minimum number of digits (0 – 9) that must be included in the password. This can be a value between 0 and 99.

Enter the minimum number of alphanumeric characters (A – Z, a-z) that must be included in the password. This can be a value between 0 and 99.

Enter the minimum number of special characters (excluding [ ] { }( ) , ? * ! @) that must be included in the password.

This can be a value between 0 and 99.

This indicates the administrator login with the authority to perform advanced SQL functions.

This must be an account login that is a member of the sysadmin fixed server role.

This indicates the password assigned to the administrator login.

Indicates the standard user login.

The user entered here must have the following permissions enabled:

  • datareader
  • datawriter

This indicates the password assigned to the SQL Server standard user login.

This indicates the system-wide code page used by the BULK INSERT statement when special characters exist that SQL Server needs to recognize.

You can configure a code page at company level, which takes precedence over the code page defined here.

Indicates the temporary folder for storing intermediate bulk copy format files (*.bcp).

In a 3-tier environment, ensure that the temporary folder is available to both SQL server and the application tier.

Typically, use a Universal Naming Convention (UNC) address (e.g. \\server-name\path-to-shared-resource).

This lets you use a SQL Managed Instance created in Azure (i.e. this makes programs use logic written specially for SQL Managed Instance).

This is the address that is used to store objects in Azure.

This indicates the credentials that will be used when accessing resources in the Azure Blob storage container.

This is the Azure external data source name (used by SYSPRO for Bulk Inserts).

This relies on SQL authentication for access to the database.

This passes the Windows NT credentials to SQL for authentication.

This indicates the name of the SQL Server hosting the database.

Specify the SQL authentication login here.

Specify the SQL authentication password here.

Select an authentication mode to use when processing transactions on the Harmony database.

Indicate the name of the SQL server that will be used for the Harmony database.

Provide a valid SQL login name for the Harmony database.

Indicate the corresponding password for the SQL login.