Configure SQL Server for the Retail databases

Article
11/29/2021

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack

This topic provides guidance about how to set up Microsoft SQL Server in a Retail implementation.

Supported SQL Server editions

SQL Server Express editions are supported for channel databases, message databases, and offline databases. However, SQL Server change tracking is required for channel databases and message databases. Change tracking is available only in SQL Server Express with Advanced Services. It is not available in other versions of SQL Server Express Edition.

Settings that are required for PCI compliance

To comply with the PCI Data Security Standard, you must enter specific settings during SQL Server setup. The following table describes these settings.

Wizard page	Required settings
Server Configuration page, Service Accounts tab	Specify dedicated logon accounts that are not default accounts.
Database Engine Configuration page, Account Provisioning tab	Select Windows authentication. Specify SQL Server administrators, but do not use any default accounts.

For more information, see the Implementation Guide for PCI Compliance.

Note

If you want to use an existing instance of SQL Server anywhere in the Microsoft Dynamics AX 2012 deployment, you must first verify that the settings for the instance are PCI-compliant. Whenever you can, set up a new instance for Retail that uses the recommended settings. For more information about how to set up a new instance, see SQL Server Books Online.

SQL Server collation settings for Retail databases

The collation for each database is based on the Windows locale. To avoid collation issues, verify that the Windows locale of each database computer is set to one of the supported locales for Retail.

SQL Server logons and database permissions

SQL Server logons are created automatically when you install the following services.

Service	SQL Server logon	Logon permissions
Commerce Data Exchange: Async Server	Same name as the application pool identity for Async Server	Provides read (db_datareader) and write (db_datawriter) access to the Async Server message database
Commerce Data Exchange: Async Client	Same name as the service account for Async Client	Provides read (db_datareader) and write (db_datawriter) access to the Async Client message database
Commerce Data Exchange: Synch Service	Same name as the service account for the head office instance of Synch Service	Provides read (db_datareader) and write (db_datawriter) access to the Synch Service message database at headquarters

If you change the user account for one of these services, on the server where the component is installed, create a SQL Server logon that has the same name as the user account. The logon must have read and write permissions on the corresponding message database.

When you configure a channel database or an offline database, a local user group for point of sale (POS) users and a corresponding SQL Server logon are created. We recommend that you assign the Windows user accounts of cashiers to this local user group. In AX 2012 R2, we also recommend that you assign the account that is used by Synch Service to this group. In AX 2012 R3, we recommend that you assign the account that is used by Async Client to the RetailDataSyncUsers group.

Database	SQL Server logon	Logon permissions
Message database for Async Server	RetailHQMessageDBUsers	Provides read (db_datareader), write (db_datawriter), and execute stored procedure (db_executor) access to the database.
Message database for Async Client	RetailSyncClientDBUser	Provides read (db_datareader), write (db_datawriter), and execute stored procedure (db_executor) access to the database.
Channel database	RetailDataSyncUsers	Assigned to the DataSyncUser role. Gives appropriate read and write permissions at the table level. For example, members of this group have only read permission on transaction tables.
Channel database and offline database	POSUsers	Corresponds to the local user group for POS users. Provides read (db_datareader), write (db_datawriter), and execute stored procedure (db_executor) access to the channel database and offline database.

On the computer where the channel database is installed, use SQL Server Management Studio to assign the local RetailOfflineSyncUsers group read and write permissions to the channel database.

Enable remote connections and TCP/IP

Any instance of SQL Server that must be accessed from a remote computer must be set up to accept remote connections. For example, any store database server and any computer on which a Synch Service message database is installed without an instance of Synch Service must accept remote connections. Make sure that the TCP/IP protocol is also enabled for SQL Server instances that are accessed remotely.