Configure SQL Server Authentication in Microsoft Dynamics NAV 2016

Hi All,

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.


Use below MSDN link for detailed explanation
https://msdn.microsoft.com/en-us/library/dn951464(v=nav.90).aspx

To configure SQL Server authentication, complete the following procedures:

1) Setup 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. 

 


2) Configure SQL Authentication on the Database :

Your SQL Server Instance should be in Mixed Mode Authentication.

Now create a new SQL Server Authentication User.

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




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

Go to Microsoft Dynamics NAV Administration--> Select the NAV Server Instance and add the Database Credential as shown below

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



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 


Restart the NAV Server Instance





Thanks & Regards,
Nandesh Gowda