Part-1 – ARKSD Consultancy Private Limited

Microsoft Dynamics NAV 2016 supports SQL Server authentication between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server. Previously only Windows authentication was supported.

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

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

Create an encryption key and stores it in a file in a specified path on the computer or network.

Syntax

New-NAVEncryptionKey [-KeyPath] <String> [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

The New-NAVEncryptionKey cmdlet enables you to specify a destination file for the key and specify a password to protect the file.

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

Example: New-NAVEncryptionKey -KeyPath “C:\UserData\SQLKey\MySQLKey” -Password (Get-Credential).Password

Run the Import-NAVEncryptionkey cmdlet to install the encryption key on the Microsoft Dynamics NAV Server instance and database.

Imports an encryption key from a file to a Microsoft Dynamics NAV Server instance and database in SQL Server.

Syntax

Import-NAVEncryptionKey [[-ServerInstance] <String> ] [-KeyPath] <String> -ApplicationDatabaseName <String> -ApplicationDatabaseServer <String> [-ApplicationDatabaseCredentials <PSCredential> ] [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

Example: Import-NAVEncryptionKey -ServerInstance ‘DynamicsNAV90’ -KeyPath “C:\UserData\SQLKey\MySQLKey.key” -ApplicationDatabaseServer ‘INDEL-AXT5283VM’ -ApplicationDatabaseName ‘Demo Database NAV (9-0)’ -Password (Get-Credential).Password

You cannot import an encryption key on the Microsoft Dynamics NAV Server instance if an encryption key file already exists. You must first delete the encryption key from the computer where Microsoft Dynamics NAV Server is installed.

By default, encryption keys are stored in the C:\ProgramData\Microsoft\Microsoft Dynamics NAV\90\Server\Keys folder.

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

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

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

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.

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

Open the Microsoft Dynamics NAV Server Administration tool.
In the Actions pane, choose Database Credentials.
Set the Database Authentication Type to SQL Authentication.
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.

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.

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.

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.

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

Checkout my upcoming posts for more details on this.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Using NL( Lookup ) in Jet Reports Part-1

September 25, 2015September 25, 2015 Ashwini Tripathi

The NL(“Lookup”) function can be used to provide Lookup to the Users so that he can select from a specified list of values when setting their report filters. This will allow them to see the list of options available to them in regards to a particular report filter.

The most common (and basic) use of the NL(“Lookup”) function is to simply pull a list of values from the database.

For example, if a list of customer numbers (“No.”) from the “Customer” table is desired then the function would look something like this:

=NL(“Lookup”,”Customer”,”No.”)

The resulting lookup that the user sees would be:

It is also possible to allow the user to see more than one value from a particular table. This can help the user to make a choice more easily by displaying additional details about the values returned.

Multiple fields can be displayed by placing them in an array. This is accomplished by placing the list of fields to be displayed, separated by commas, in the Field parameter and surrounding it with curly braces.

If the fields to be shown are the “Name” and the “Country/Region Code” associated with each customer “No.”, the resulting function would look something like this:

=NL(“Lookup”,”Customer”,{“No.”,”Name”,”Country/Region Code”})

The resulting lookup would be:

NOTE: The first field in the array is the field that will be returned by the Lookup form.

In addition to selecting multiple fields to be displayed in the list, it is also possible to customize the headers, that appear at the top of the Lookup window. This can be done by placing “Headers=” in one of the FilterField parameters of the NL(“Lookup”) function.

For example, instead of the field names appearing as “No.”, “Name”, and “Country/Region Code”, it has been decided that they should be displayed as “Cust. No.”,”Cust. Name”, and “Cust. State” to the user.

To do this, “Headers=” will be added in one of the FilterField parameters, and the desired names will be placed in the associated Filter parameter. The function should look like this:

=NL(“Lookup”,”Customer”,{“No.”,”Name”,”Country/Region Code”},”Headers=”,{“Cust. No.”,”Cust. Name”,”Cust. State”})

The resulting Lookup window would now appear as:

In addition to returning a list of values from the database, it is also possible to manually specify the values that will be returned.

This allows you to present the user with a list of values that are not stored in your database. The syntax that will be used is slightly different. Since data is no longer being returned directly from the database, the Table parameter is no longer used to specify the table that the information will pull from. Instead, an array containing the values to be displayed is placed in the Table parameter of the Lookup function. The Field parameter will then contain the list of column headers of the Lookup window.

To create an NL(“Lookup”) function that will display “East”, “West”, “North”, and “South” for Directions, the function will be:

=NL(“Lookup”,{“East”,”West”,”North”,”South”},”Direction”)

The resulting lookup window would now be:

NOTE: It is important to place the field name that will be displayed at the top of the Lookup window (in this example “Direction”) in the Field parameter. Omitting this will not display any values in the Lookup Window.

In addition to placing the values in the NL(“Lookup”) function itself, a cell reference can also be used. The following example will display the exact same result as the previous example, but the values are specified by a cell reference instead of text.

It is also possible to display multiple columns in the Lookup Window by utilizing cell references. To achieve this, another column of values will need to be inserted next to the values that we are already displaying. Once this is done, the cell reference in the NL(“Lookup”) function will also need to be expanded to encompass both columns of data. Since multiple columns are now being specified, names for these columns will also need to be defined in the NL(“Lookup”) function as well. This is done by using the array syntax that was described above in order to specify the field names in the Field parameter, in this case “Direction” and “Description”.

=NL(“Lookup”,F5:G8,{“Direction”,”Description”})

Below is an example of what this would look like:

Will come up with more details in my next post on this.

Stay tuned for more details in my upcoming post.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Creating Simple List Report in Excel Using Jet Reports Part-1

September 25, 2015September 25, 2015 Ashwini Tripathi

Dear friends I will discuss today simple report creating in Jet Reports.

I will take a simple example for creating Customer List showing the Credit Limit & Balances.

We will be using two basic Functions of Jet Reports NL & NF.

Then we will add few more features in my next post on this report.

Let’s Start Step wise Step:

Step 1: Add NL Function to retrieve Records from Customer Table.

I have Add NL Function in Cell E5 as shown above, if want to add using Jfx – NL place a Cursor in E5 and press NL from Jfx Group of functions and fill as below:

Step 2: Add Fields which you wish to include in your Report using NF Jfx Function.

I am adding No. field from Customer Table.

Here we have already created the connection to Customer Table in previous Step. This will retrieve all Fields and Records from the Customer Table.

Using NF function I am selecting the Fields of which we want to show/include value in our Report.

I have add the Function in Excel as shown below:

I have Add NF Function in Cell F5 as shown above, if want to add using Jfx – NF place a Cursor in F5 and press NF from Jfx Group of functions and fill as below:

Step 3: Following Step 2 add all other Fields.

The Excel should look as below:

I have Added fields No., Name, Credit Limit (LCY), Balance (LCY).

I have also added Heading for these fields, and applied general Excel Formatting.

The Column E with NL function, I have Hide from the Report Output as the information is having no relevance showing to User.

Now we are god to see the output of our Report Created above. Output format of this report will be as below:

Stay tuned to have more updates in my upcoming posts.

Tag: Part-1

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1

Using NL( Lookup ) in Jet Reports Part-1

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this:

Rate this:

Share this with:

Like this: