Configure SQL Server for the Retail databases
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 |
|---|---|
Specify dedicated logon accounts that are not default accounts. |
|
Database Engine Configuration page, Account Provisioning tab |
|
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.