Configure Business Central Database Authentication

For a Business Central Server instance to connect to and access a database in SQL Server, the server instance must be authenticated by the database. As in SQL Server, Business Central supports two authentication modes for database communication: Windows authentication and SQL Server authentication. When you set up Business Central, you must ensure that database authentication is configured correctly on both the server instance and database, otherwise the server instance won't be able to connect to the database. By default, Windows authentication is configured on the server instance and database. With Windows authentication, the Business Central Setup does the work for you.

This article describes how to configure SQL Server authentication. You perform the configuration in two places: on the databases in SQL Server and on the Business Central Server instance. The procedure is different when the Business Central Server instance is configured as a multitenant server instance than when it isn't a multitenant server instance.

Set up an encryption key

When using SQL Server authentication, Business Central requires an encryption key to encrypt the credentials (user name and password) that the Business Central Server instance uses to connect to the Business Central database in SQL Server. The encryption key must be installed on the computer where the Business Central Server is installed and also in the database in SQL Server. In a multitenant deployment, the encryption key must be installed in the application database.

To set up an encryption key, you can use one of the following methods:

• You can create and import your own encryption key by using Business Central Administration Shell cmdlets, as described in the following procedure.

• (Version 20 and earlier only) If you're configuring SQL Server authentication on a Business Central Server instance for the first time, you can use the Business Central Server Administration tool which can automatically create and install a system encryption key. If you decide to use this method, no action is required.

Create and import encryption key

1. In the Business Central Administration Shell, run the New-NAVEncryptionkey cmdlet.

   This task creates a file that contains an encryption key. If you already have an encryption key file, you can skip this step.

2. Run the Import-NAVEncryptionkey cmdlet to install the encryption key on the Business Central Server instance and database.

On the computer running the Business Central Server instance, the encryption key file has the name BC210.key and is stored in the %systemroot%\ProgramData\Microsoft\Microsoft Dynamics NAV\[version]\Server\Keys. In the database, the encryption key is registered in the dbo.$ndo$publicencryptionkey table. In a multitenant deployment, the encryption key is registered in the application database.

Configure SQL authentication on the database

This section describes how to configure a Business Central database to use SQL Server authentication with a Business Central Server instance. You can complete the steps in this procedure by using SQL Server Management Studio or Transact-SQL.

Important

In a deployment where the Business Central Server instance is configured as a multitenant server instance, you must complete the following procedure on the application database and tenant database.

Configure SQL Server authentication on the database in SQL Server

1. Configure the SQL Server instance (Database Engine) that hosts the Business Central database to use SQL Server authentication.

   To use SQL Server authentication, you configure the database instance to mixed authentication mode (SQL Server and Windows Authentication). For more information, see Change Server Authentication Mode.

2. In the SQL Server instance, create a login that uses SQL Server authentication.

   For more information, see Create a Login.

3. Map the login to a user in the Business Central database, and give the user the relevant privileges in the Business Central database.

   For more information, see Create a Database User in the SQL Server docs and the Giving the service account database privileges in SQL Server.

Configure SQL Server authentication on the Business Central instance (non-multitenant)

You configure the Business Central Server instance with the login credentials (user name and password) of the user account in the Business Central database in SQL Server that you want to use for authentication. You can do this using the Business Central Server Administration tool or Business Central Administration Shell.

Configure SQL authentication on a server instance using Business Central Administration Shell

• If you're modifying an existing Business Central Server instance, run the Set-NAVServerConfiguration cmdlet.

  Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

  Set-NAVServerConfiguration -ServerInstance BC -DatabaseCredentials (Get-Credential)
  

• If you're creating a new Business Central Server instance, run the New-NAVServerInstance cmdlet.

  Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

  New-NAVServerInstance -ServerInstance BC -DatabaseCredentials (Get-Credential)
  

Configure SQL authentication on a server instance using Business Central Server Administration tool

Important

Starting in Business Central 2022 release wave 2 (v21), the Business Central Server Administration tool is no longer available. Use the Business Central Administration Shell instead.

1. Open the Business Central Server Administration tool.

2. In the console tree, which is the left pane, expand the node for the computer that contains the Business Central Server instance, and then select the Business Central Server instance.

3. In the Actions pane, choose Database Credentials.

4. In the Database Credentials page, choose the Edit button.

5. Set the Database Authentication Type to SQL Authentication.

6. In the Database User Name field, type the login name for the database user that you want to use to access the Business Central database in SQL Server.

7. In the Password field, type the login password for the database user that you want to use to access the Business Central database in SQL Server.

8. Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

   Encryption keys are used to help secure the login credentials over the connection between the Business Central Server instance and the Business Central database in SQL Server.

9. On the Information dialog box about encryption, choose the OK button.

   This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.

10. If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.

11. Restart the server instance.

Configure SQL Server authentication on Business Central instance in a multitenant deployment

This section describes how to configure a Business Central database to use SQL Server authentication with a Business Central Server instance. You can complete the steps in this procedure by using SQL Server Management Studio or Transact-SQL.

To configure a SQL Server authentication on a Business Central Server instance, you set up the server instance with the login credentials (user name and password) for the user accounts for the application and tenant databases in SQL Server. You can do this using the Business Central Server Administration tool or Business Central Administration Shell.

Configure SQL authentication using Business Central Administration Shell

1. Configure SQL Server authentication with the application database as follows:

   • If you're modifying an existing Business Central Server instance, run the Set-NAVServerConfiguration cmdlet.

     Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

     Set-NAVServerConfiguration -ServerInstance BC -DatabaseCredentials (Get-Credential)
     

   • If you're creating a new Business Central Server instance, run the New-NAVServerInstance cmdlet.

     Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

     New-NAVServerInstance -ServerInstance BC -DatabaseCredentials (Get-Credential)
     

2. To configure SQL authentication with the tenant database, run the Mount-NAVTenant cmdlet.

   Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the tenant database.

   Mount-NAVTenant -ServerInstance BC -Id 'MyTenant1' -DatabaseName 'MyDatabase' -DatabaseCredentials (Get-Credential)
   

Configure SQL authentication using Business Central Server Administration tool

Important

Starting in Business Central 2022 release wave 2 (v21), the Business Central Server Administration tool is no longer available. Use the Business Central Administration Shell instead.

1. Open the Business Central Server Administration tool.

2. In the console tree, which is the left pane, expand the node for the computer that contains the Business Central Server instance, and then select the Business Central Server instance.

3. Configure SQL Server authentication with the application database as follows:

   1. In the Actions pane, choose Database Credentials.

   2. On the Database Credentials page, choose the Edit button.

   3. Set the Database Authentication Mode to SQL Server Authentication.

   4. In the Database User Name field, type the login name for the database user that you want to use to access the Business Central application database in SQL Server.

   5. In the Password field, type the login password for the database user that you want to use to access the Business Central database in SQL Server.

   6. Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

      Encryption keys are used to help secure the login credentials over the connection between the Business Central Server instance and the Business Central database in SQL Server.

   7. On the Information dialog box about encryption, choose the OK button.

      This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.

   8. If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.

4. To configure SQL Server authentication with the tenant database, mount the tenant to the Business Central Server instance. Specify the login credentials (user name and password) for the database user that you want to use to access the Business Central tenant database in SQL Server.

   If the tenant is already mounted to the Business Central Server instance, you must dismount the tenant, and mount it again.

   For more information, see Mount or Dismount a Tenant.

5. Restart the server instance.

Integrating directly on SQL Server objects

Warning

During operations such as upgrade and app/extension synchronization, the Business Central Server synchronizes the metadata model defined in AL to physical tables on the SQL Server database.

While it's possible to integrate directly with SQL Server objects on the database (bypassing the Business Central Server), it's not recommended or even supported.

Altering SQL objects created by the Business Central Server directly on the database can break operations such as upgrade and app/extension synchronization.

Adding additional SQL objects such as triggers or stored procedures directly on the database can break operations like upgrade and app/extension synchronization, but will also break system integrations that depend on such additional objects if the table schema created by the Business Central Server synchronization changes.

See Also

Installation Considerations for Microsoft SQL Server
Deployment
Installing Business Central Using Setup