Configure SQL Server for the Retail databases [AX 2012]

Updated: May 7, 2014

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.

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.

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.

NoteNote

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.

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 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.

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.


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Community Additions

ADD
Show: