Share via

How to: Configure SQL Server Authentication in Microsoft Dynamics NAV

  • Article

This topic describes how to configure SQL Server Authentication between the Microsoft Dynamics NAV Server instance and a Microsoft Dynamics NAV database. You perform the configuration in two places: on the databases in SQL Server and on the Microsoft Dynamics NAV Server instance. The procedure is different when the Microsoft Dynamics NAV Server instance is configured as a multitenant server instance than when it is not a multitenant server instance. To configure SQL Server authentication, complete the following procedures:

  1. Set Up an Encryption Key

  2. Configure SQL Authentication on the Database

  3. Configure SQL Server Authentication on Microsoft NAV Server Instance (Non-Multitenant) or Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment

Set Up an Encryption Key

When using SQL Server authentication, Microsoft Dynamics NAV requires an encryption key to encrypt the credentials (user name and password) that the Microsoft Dynamics NAV Server instance uses to connect to the Microsoft Dynamics NAV database in SQL Server. The encryption key must be installed on the computer where the Microsoft Dynamics NAV 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 Microsoft Dynamics NAV 2016 Administration Shell cmdlets, as described in the following procedure.

  • If you are configuring SQL Server authentication on a Microsoft Dynamics NAV Server instance for the first time, you can use the Microsoft Dynamics NAV Server Administration tool which can automatically create and install a system encryption key. If you decide to use this method, no action is required.

To create and import encryption key

  1. In the Microsoft Dynamics NAV 2016 Administration Shell, run the New-NAVEncryptionkey cmdlet.

    This 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 Microsoft Dynamics NAV Server instance and database.

For more information, see Managing Encryption Keys for SQL Server Authentication.

Configure SQL Authentication on the Database

This section describes how to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV 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 Microsoft Dynamics NAV Server instance is configured as a multitenant server instance, you must complete the following procedure on the application database and tenant database.

To configure SQL Server Authentication on the database in SQL Server

  1. Configure the SQL Server instance (Database Engine) that hosts the Microsoft Dynamics NAV 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 Microsoft Dynamics NAV database, and add the user to the db_owner role of the Microsoft Dynamics NAV database.

    For more information, see Create a Database User.

Configure SQL Server Authentication on Microsoft NAV Server Instance (Non-Multitenant)

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

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  1. Open the Microsoft Dynamics NAV Server Administration tool.

    Choose Start, and in the Search programs and files box, enter Microsoft Dynamics NAV Administration, and then choose the related link.

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

  3. In the Actions pane, choose Database Credentials.

  4. On 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV 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. For more information, see How to: Start, Stop, Restart, or Remove a Microsoft Dynamics NAV Server Instance.

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV 2016 Administration Shell

  • If you are modifying an existing Microsoft Dynamics NAV 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.

  • If you are creating a new Microsoft Dynamics NAV 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.

Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment

This section describes how to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV Server Administration tool or Microsoft Dynamics NAV 2016 Administration Shell.

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  1. Open the Microsoft Dynamics NAV Server Administration tool.

    Choose Start, and in the Search programs and files box, enter Microsoft Dynamics NAV Administration, and then choose the related link.

  2. In the console tree, which is the left pane, expand the node for the computer that contains the Microsoft Dynamics NAV Server instance, and then select the Microsoft Dynamics NAV 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV 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 Microsoft Dynamics NAV Server instance and specify the login credentials (user name and password) for the database user that you want to use to access the Microsoft Dynamics NAV tenant database in SQL Server.

    If the tenant is already mounted to the Microsoft Dynamics NAV Server instance, you must dismount the tenant, and mount it again.

    For more information see How to: Mount or Dismount a Tenant on a Microsoft Dynamics Server Instance.

  5. Restart the server instance. For more information, see How to: Start, Stop, Restart, or Remove a Microsoft Dynamics NAV Server Instance.

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV 2016 Administration Shell

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

    • If you are modifying an existing Microsoft Dynamics NAV 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.

    • If you are creating a new Microsoft Dynamics NAV 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.

  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.

See Also

Concepts

Working with PowerShell
Microsoft Dynamics NAV Windows PowerShell Cmdlets
Configuring Authentication Between the Microsoft Dynamics NAV Server and Database