Server – ARKSD Consultancy Private Limited

Backup, Development Tips, How To, Information, PowerShell, Schedule, Server, SQL, Tip & Tricks

SQL Server Database Backup using PowerShell

July 7, 2016 Ashwini Tripathi

In this post we will see how we can take backup of databases from SQL Server using PowerShell and schedule it as a daily run Plan.

Step-1 : We will create Powershell Script to take backup of Databases in SQL Server.

SCheduleJob-18

Here is the full Script for your ready refrence.

param( $serverName, $backupDirectory )

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

$server = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) $serverName

$dbs = $server.Databases

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })

{

$dbName = $database.Name

$timestamp = Get-Date -format yyyy-MM-dd-HHmmss

$targetPath = $backupDirectory + “\” + $dbName + “_” + $timestamp + “.bak”

$smoBackup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”)

$smoBackup.Action = “Database”

$smoBackup.BackupSetDescription = “Full Backup of “ + $dbName

$smoBackup.BackupSetName = $dbName + ” Backup”

$smoBackup.Database = $dbName

$smoBackup.MediaDescription = “Disk”

$smoBackup.Devices.AddDevice($targetPath, “File”)

$smoBackup.SqlBackup($server)

“backed up $dbName ($serverName) to $targetPath“

}

Save this Script file as ps1 extension.

You can create the script using even notepad.

Step 2: We will Create Batch file to call PowerShell scrip and to be used in Windows scheduler.

SCheduleJob-18

Save as .BAT file. Here is the batch script for ready refrence:

powershell -ExecutionPolicy RemoteSigned

-File “C:\User Data\SQL Backup\Tools\SQLServerBackupAllDatabase.ps1”

-serverName “INDEL-AXT5283NB”

-backupDirectory “C:\User Data\SQL Backup”

>> “C:\User Data\SQL Backup\LOG\\%date%.log”

Step 3 : Create a Windows Scheduler

Open Windows Task Scheduler.

Create New Task as shown below :

SCheduleJob-18

Enter Name & Description on General Tab as shown below:

SCheduleJob-18

On Trigger Tab create New Trigger and enter details as shown below :
SCheduleJob-18

On Action Tab Create Action and enter information as shown below : Here Select the batch file created in Step 2.

SCheduleJob-18

In Settings Tab do the setting as shown Below :

SCheduleJob-18

Click on OK to Save the Task and return to Task Scheduler Window.

Here you can see the newly created Task.

SCheduleJob-18

When Task is executed you will find the backup of databases at defined path in the script.

SCheduleJob-26

You can also find Log file at the path defined in batch.

SCheduleJob-27

Thats all for this post, will come up will more information in my up comming posts.

Azure, Information, Tip & Tricks

Customers with Enterprise Agreement who already have SQL Server Licenses, can use them on Azure Virtual Machines

May 29, 2016 Ashwini Tripathi

Now Microsoft Enterprise Agreement customers can bring existing licenses to run SQL Server on Azure Virtual Machines. Since the launch of Azure Virtual Machines, customers can already run SQL Server on Azure Virtual Machines through several existing SQL Server images available in the Azure Gallery, or bring their own images to Azure.

These images and capabilities will continue to be supported so customers can continue to take advantage of pay per use licensing for SQL Server on Azure Virtual Machines.

Starting this week, customers with Enterprise Agreement who already have SQL Server Licenses, can use them on Azure Virtual Machines with Microsoft-certified (BYOL) gallery images. These images will not charge for SQL Server licensing, just for compute cost.

For more details see the Link.

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

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

October 30, 2015October 30, 2015 Ashwini Tripathi

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.

Syntax & Parameter explaination:

Set-NAVServerConfiguration (cmdlet)

-KeyName <String>

(The configuration key name. Examine the CustomSettings.config file to determine the correct key name.)

[-Element <String> ]

(Specifies the navigation path from the root element to the appSettings section of the configuration document.)

[-ServerInstance] <String>

(Specifies the name of a Microsoft Dynamics NAV Server instance. The default instance name is DynamicsNAV90. You can specify either the full name of an instance such as MicrosoftDynamicsNavServer$myinstance or the short name such as myinstance.)

-DatabaseCredentials <PSCredential>

[-Force] (Forces the command to run without asking for user confirmation.)

[-KeyValue <String> ] (The configuration key value.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

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

Example:

C:\PS>Set-NAVServerConfiguration MyInstance -KeyName DatabaseServer -KeyValue DatabaseServer.Domain.Com

If you are creating a new Microsoft Dynamics NAV Server instance, run the New-NAVServerInstance cmdlet.

Syntax & Parameter explaination:

New-NAVServerInstance

[-ServerInstance] <String>

(Specifies the name of the Microsoft Dynamics NAV Server instance. The default instance name isDynamicsNAV90. You can specify either the full name of an instance, such as MicrosoftDynamicsNavServer$DynamicsNAV90, or the short name, such as DynamicsNAV90. You must use single-quotes around the instance name.)

-ManagementServicesPort <ServicePort>

(Specifies the TCP port that is used to manage the Microsoft Dynamics NAV Server instance. The Management Services port has no exceptions in the firewall, and will only be accessed from the local computer. The port is used by Windows PowerShell for access Microsoft Dynamics NAV Server management data.)

[-ClientServicesCredentialType <String> ]

(The type of client credential used for client authentication.Possible values are: Windows, Username, NavUserPassword and AccessControlService.)

[-ClientServicesPort <ServicePort> ]

(Specifies the listening TCP port for clients such as Microsoft Dynamics NAV Windows client and Microsoft Dynamics NAV Web client.)

[-DatabaseCredentials <PSCredential> ]

(The user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to connect to the Microsoft Dynamics NAV database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database. If the Microsoft Dynamics NAV Server instance is configured for multitenancy, then parameter configure SQL Authentication on the connection to the application database, not the tenant database. The login account must be a member of the db_owner role on the database.)

[-DatabaseInstance <DatabaseInstance> ]

(Specifies the SQL Server instance on which the Microsoft Dynamics NAV database is installed.)

[-DatabaseName <DatabaseName> ]

(Specifies the name of the Microsoft Dynamics NAV database.)

[-DatabaseServer <DatabaseServer> ]

(Specifies the name of the computer on which the SQL Server instance for the Microsoft Dynamics NAV database is installed.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-Multitenant]

(Specifies the Microsoft Dynamics NAV Server instance to be a multitenant instance.)

[-ODataServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV OData web services.)

[-ServiceAccount <ServiceAccount> ]

(Specifies the Windows-based computer account that the Microsoft Dynamics NAV Server instance must use to log on. The default value is NT AUTHORITY\NETWORK SERVICE. Only NetworkService and User values are supported. This parameter accepts values from the enum System.ServiceProcess.ServiceAccount.)

[-ServiceAccountCredential <PSCredential> ]

(Specifies a set of security credentials that you must use when configuring the service account.)

[-ServicesCertificateThumbprint <ClientServicesCertificateThumbprint> ]

(Specifies the certificate thumbprint for the x509 certificate that is going to be used for securing communication with the server. The certificate must be stored in the local machine store and in the personal sub-store in the certificate store. The private key of the certificate must be present and exchangeable. The certificate must be in .pfx format, not .cer format. The certificate can be either self-signed or issued by a trusted certification authority (CA).

When specifying a ServicesCertificateThumbprint, SOAP web services and OData web services become HTTPS.)

[-SOAPServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV SOAP web services.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

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

Examples:

C:\PS>New-NAVServerInstance NewInstance -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

C:\PS>Get-Credential | New-NAVServerInstance NewInstance -ServiceAccount User -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

For Multitenant Environment

Configure SQL Server Authentication with the application database as above.
To configure SQL Authentication with the tenant database, run the Mount-NAVTenant

Syntax & Parameter explaination:

Mount-NAVTenant

[-AlternateId] <System.Collections.ObjectModel.ReadOnlyCollection[string]>

(Specifies the alternative IDs for the tenant, such as host names for the Microsoft Dynamics NAV Web client, SOAP web services, OData web services, or the Microsoft Dynamics NAV Windows client.

If you use alternative IDs for tenant resolution in the Microsoft Dynamics NAV Web client, you must also enable some of the UrlRewrite rules in the Web.Config file for the Microsoft Dynamics NAV Web Server components.)

[-AzureKeyVaultSettings] <Microsoft.Dynamics.Nav.Types.AzureKeyVaultSettings>

(Specifies the Azure key vault settings. This parameter is available only if the EncryptionProvider is set to AzureKeyVault.)

[-ServerInstance] <String>

(Specifies the Microsoft Dynamics NAV Server instance that you want to mount the tenant against, such as DynamicsNAV90. You can specify either the fully qualified name, such as ‘MyServer$DynamicsNAV90’, or the short name, such as ‘DynamicsNAV90’.)

[-DatabaseInstance] <System.String>

(Specifies the name of the SQL Server instance that hosts the database. You can also specify the instance in the DatabaseServer parameter, such as MyServer\MyInstance.)

[-DatabaseName] <System.String>

(Specifies the name of the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance, such as ‘Demo Database NAV (9-0)’.)

[-DatabaseServer] <System.String>

(Specifies the name of the database server that hosts the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance.)

[-DefaultCompany] <System.String>

(Specifies the name of the company that NAS services, OData web services, and SOAP web services use if no other company is specified.)

[-DefaultTimeZone] <System.TimeZoneInfo>

(Specifies the default time zone that is used by the NAS services, OData web services, and SOAP web services for this tenant.

You can set the parameter to UTC, ‘Server Time Zone’, or the ID of a Windows Time Zone.

UTC specifies that all business logic for services on the server instance runs in Coordinated Universal Time (UTC).

‘Server Time Zone’ specifies that services use the time zone of the computer that is running Microsoft Dynamics NAV Server instance.

ID of a Windows Time Zone specifies that services use a Windows time zone as defined in the system registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. For example, Romance Standard Time is a valid Windows time zone value.

If this parameter is not specified, the value is taken from the ServicesDefaultTimeZone setting in the CustomSetting.config file for the Microsoft Dynamics NAV Server instance.)

[-NasServicesEnabled]

(Specifies to enable NAS services on the tenant. The default value is false.)

[-RunNasWithAdminRights]

(Specifies the NAS services to run with administrator rights. This grants the NAS service the same permissions as the SUPER permission set in Microsoft Dynamics NAV without having to add the Microsoft Dynamics NAV Server service account as a user. The default is false.)

[[-ApplicationDatabaseCredentials] <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the application database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the application database.

The login account must be a member of the db_owner role on the database.

This parameter is only relevant when you set with the ApplicationDatabaseServer and ApplicationDatabaseName parameters )

[[-ApplicationDatabaseName] <System.String> ]

(Specifies the name of the application database to use with the tenant database.

This parameter is only relevant if the Microsoft Dynamics NAV Server instance is configured for multitenancy. This parameter, together with the ApplicationDatabaseServer parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect a running Microsoft Dynamics NAV Server instance.)

[[-ApplicationDatabaseServer] <System.String> ]

(Specifies the SQL Server name and instance, such as MyServer\MyInstance, that hosts the application database that you want to use with the tenant database,.

This parameter, together with the ApplicationDatabaseName parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect to a running Microsoft Dynamics NAV Server instance.)

[-AllowAppDatabaseWrite]

(Specifies if the tenant can write to the application database. The default value is false.)

[-DatabaseCredentials <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the tenant database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database.

The login account must be a member of the db_owner role on the database.)

[-EncryptionProvider <Microsoft.Dynamics.Nav.Types.EncryptionProvider> ]

(Specifies the name of the encryption provider.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-OverwriteTenantIdInDatabase]

(Specifies if the Mount-NAVTenant cmdlet must overwrite the tenant ID in the database if the database has been mounted as a tenant earlier. If this is false, and the tenant database has previously been mounted with a different tenant ID, an exception is thrown.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

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

Examples:

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’

PS C:\> Mount-NAVTenant DynamicsNAV90 Test Test_Database

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential)

PS C:\> Get-NAVTenant Server1 | Mount-NAVTenant Server2

PS C:\> Get-NAVTenant Server1 | Dismount-NavTenant Server1 -Force | Mount-NAVTenant Server2

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’-AlternateId @( “test.mydomain.com”, “http://mydomain.sharepoint.com/sites/teamsite” )

PS C:\> Mount-NAVTenant -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential) -ApplicationDatabaseServer ‘MySQLServer\NAV’ -ApplicationDatabaseName ‘MyNavAppDatabase’ -ApplicationDatabaseCredentials (Get-Credential) -KeyFilePath ‘C:\key\nav.key’ -KeyFilePassword (Get-Credential).Password

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

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

October 30, 2015October 30, 2015 Ashwini Tripathi

Recall from my previous post Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1, action is almost similar with few small differences when dealing with Multitenant Deployment.
Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment
How to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV Server instance.

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.

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 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.
Configure SQL Server Authentication with the application database as follows:
- In the Actions pane, choose Database Credentials.
- On the Database Credentials page, choose the Edit button.
- Set the Database Authentication Mode to SQL Server 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 application 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.
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.

Restart the server instance.

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

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

October 30, 2015October 30, 2015 Ashwini Tripathi

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, Instalation & Configuration, Jet Reports, Office Integration, Report

Installing and Publishing the Jet Business Objects on the Microsoft Dynamics Server

September 16, 2015 Ashwini Tripathi

To install the .fob file open the Microsoft Dynamics NAV Development Environment and then connect to the database.

Open the Object Designer (on the Tools menu, choose Object Designer)
Import the .fob file (on the File menu, choose Import)
Locate the import file (Jet Reports Objects.fob), and then choose Open.
The .fob file includes modified objects. When prompted choose Yes to import all objects.
Verify that no objects are to be skipped. Objects are skipped if the version of the object in the database is later than the one in the .fob file.
Choose the OK button to import the .fob file.

The .fob file installs a set of business objects in the range of 14125500-14125504 and Table 14125600.

Jet Express users must have Read, Insert, Modify, Delete (RIMD-) permissions to Table 4125600 “Jet Cancellation”.

Checkout upcoming posts for more information.

Development Tips, How To

How to: Set .NET Framework Types to Run on the Microsoft Dynamics NAV Windows Client or Microsoft Dynamics NAV Server Computers

August 28, 2015August 28, 2015 Ashwini Tripathi

Here is the brief description for how to set .NET Framework objects that are instantiated by DotNet variables to target either the Microsoft Dynamics NAV Windows client or Microsoft Dynamics NAV Server.

By default, a type is set to target Microsoft Dynamics NAV Server. However, you can set the type to target the Microsoft Dynamics NAV Windows client.

Setting the .NET Framework Type to Target the Microsoft Dynamics NAV Windows Client

To set a .NET Framework type instance to target the Microsoft Dynamics NAV Windows client, you must complete the following tasks:

Set the DotNet variable for the .NET Framework type to target the Microsoft Dynamics NAV Windows client.
Copy the .NET Framework assembly to the computer that is running the Microsoft Dynamics NAV Windows client.

You must copy the assembly to each computer that is running the Microsoft Dynamics NAV Windows client. If the assembly is included in the global assembly cache, then you do not have to do this task.

For the Microsoft Dynamics NAV Web client, you cannot implement Microsoft .NET Framework interoperability objects that target the client.

To set the .NET Framework type to target the Microsoft Dynamics NAV Windows client

In Object Designer, open the C/AL code of the Microsoft Dynamics NAV object that uses .NET Framework interoperability.
Do one of the following steps:
- For a global variable, on the View menu, choose C/AL Globals.
- For a local variable, select the trigger that uses the variable, and then on the View menu, choose C/AL Locals.

On the Variables tab, select the DotNet variable, and then on the View menu, choose Properties.
In the Properties window, set the RunOnClient property to Yes

To copy a .NET Framework assembly to a computer that is running the Microsoft Dynamics NAV Windows client

On the computer that is running the Microsoft Dynamics NAV Windows client, copy the.NET Framework assembly to the Add-ins folder of the Microsoft Dynamics NAV Windows client installation.

By default, the path of this folder is C:\Program Files\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins or C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins.

You can also put assemblies in a subfolder of the Add-ins folder. This can be useful when you have multiple versions of the same assemblies and dependencies.

Setting the .NET Framework Type to Target Microsoft Dynamics NAV Server

To set a .NET Framework type instance to target Microsoft Dynamics NAV Server, you must complete the following tasks:

Set the variable for the NET Framework type to target Microsoft Dynamics NAV Server.
Copy the .NET Framework assembly to the computer that is running Microsoft Dynamics NAV Server.

If the assembly is included in the global assembly cache of the computer that is running Microsoft Dynamics NAV Server, then you do not have to do this task.

To set the .NET Framework type to target Microsoft Dynamics NAV Server

In the development environment, in Object Designer, open the object that uses the .NET Framework variable.
Do one of the following steps:
- For a global variable, on the View menu, choose C/AL Globals.
- For a local variable, in the C/AL Editor, select the trigger that uses the DotNet variable, and then on the View menu, choose C/AL Locals.

On the Variables tab, select the NET Framework variable type, and then on the View menu, choose Properties.
In the Properties window, set the RunOnClient property to No

To copy a .NET Framework assembly to the computer that is running Microsoft Dynamics NAV Server

On the computer that is running Microsoft Dynamics NAV Server, copy the.NET Framework assembly to the Add-ins folder of the Microsoft Dynamics NAV Server installation folder.

By default, the path of the Microsoft Dynamics NAV Server installation folder is C:\Program Files\Microsoft Dynamics NAV\80\Service\Add-ins.

You can also put assemblies in a subfolder of the Add-ins folder. This can be useful when you have multiple assemblies and dependencies.

If you are working in the development environment, to compile and test Microsoft Dynamics NAV objects, you must also copy the assembly to the Add-ins folder of the Microsoft Dynamics NAV Windows client installation on the computer that is running the development environment. By default, the path of this folder is C:\Program Files\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins or C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins.

Development Tips, Security, Server

Provisioning the Microsoft Dynamics NAV Server Account

August 21, 2015 Ashwini Tripathi

The Microsoft Dynamics NAV Server account is used by Microsoft Dynamics NAV clients to log on to the Microsoft Dynamics NAV Server instance.

The Microsoft Dynamics NAV Server then uses the service account to log on to the Microsoft Dynamics NAV database.

When you install Microsoft Dynamics NAV Server, you identify an Active Directory account to provide credentials for the server. By default, Setup runs Microsoft Dynamics NAV Server under the Network Service account, a predefined local account used by the service control manager. This account has minimum privileges on the local computer and acts as the computer on the network.

Microsoft recommends that you create a domain user account for running Microsoft Dynamics NAV Server.

The Network Service account is considered less secure because it is a shared account that can be used by other unrelated network services. Any users who have rights to this account have rights to all services that are running on this account.

If you create a domain user account to run Microsoft Dynamics NAV Server, you can use the same account to run SQL Server, whether or not SQL Server is on the same computer.

Because Microsoft Dynamics NAV Setup and the New-NavDatabase cmdlet configure the required permissions for the Microsoft Dynamics NAV Server account, you will typically use the procedures in this topic when you change the Microsoft Dynamics NAV Server account for an existing installation.

To provision a Microsoft Dynamics NAV Server account, complete the following procedures as described in this topic:

Provisioning a Domain User Account
If you are running the Microsoft Dynamics NAV Server under a domain user account, you must:

Enable the account to log in as a service
Enable the account to register an SPN on itself
Give the account necessary database privileges in SQL Server

Enabling the account to log in as a service
Depending on various factors, the account may or may not already have this ability.

For example, if you have already installed SQL Server and configured it to run under the same account, SQL Server will have modified the account to log in as a service.

Add the Log on as a service Right to an Account
Considered for Windows 8.1, for other versions please check accordingly.

You can use this procedure to add the Log on as a service right to an account on your computer.

Membership in the local Administrators group, or equivalent, is the minimum required to complete these procedures.

To add the “Log on as a service” right to an account on your local computer

To open Local Security Policy, click Start, point to Control Panel, point to Administrative Tools, and then double-click Local Security Policy.
In the console tree, double-click Local Policies, and then click User Rights Assignment.
In the details pane, double-click Log on as a service.
Click Add User or Group, and then add the appropriate account to the list of accounts that possess the Log on as a service right.

To add the “Log on as a service” right to an account for a Group Policy object, when you are on a server that is joined to a domain or a domain controller

Click Start, point to Run, type mmc, and then click OK.
On the File menu, click Add/Remove Snap-in.
In Add/Remove Snap-in, click Add, and then, in Add Standalone Snap-in, double-click Group Policy Object Editor.
In Group Policy Object, click Browse, browse to the Group Policy object (GPO) that you want to modify, click OK, and then click Finish.
Click Close, and then click OK.
In the console tree, click User Rights Assignment.
Where?
GroupPolicyObject [ComputerName] Policy
Computer Configuration
Windows Settings
Security Settings
Local Policies
User Rights Assignment
In the details pane, double-click Log on as a service.
If the security setting has not yet been defined, select the Define these policy settings check box.
Click Add User or Group, and then add the appropriate account to the list of accounts that possess the Log on as a service right.

When this permission is lacking, Microsoft Dynamics NAV Server server instances may not be able to start.
Enabling the account to register an SPN on itself
To enable secure mutual authentication between clients and Microsoft Dynamics NAV Server, you must configure the Microsoft Dynamics NAV Server account to self-register Service Principal Names (SPNs).

Mutual authentication is recommended in a production environment but may not be necessary in a testing or staging environment. The following procedure assumes a computer running Windows Server 2008 or Windows Server 2008 R2. On Windows 7 or Windows Vista you would need to install the Remote Server Administration Tools first.
To enable the Microsoft Dynamics NAV Server account to register an SPN on itself

Start the Active Directory Users and Computers snap-in in Microsoft Management Console (MMC):

Choose Run on the Start menu, type mmc on the command line, and the choose OK.
When the console opens, select Add/Remove Snap-In from the File menu, select Active Directory Users and Computers, and choose Add. If you do not see Active Directory Users and Computers in the list of available snap-ins, you may need to use Server Manager to install the Active Directory Domain Services role on your server computer.

In MMC, select Active Directory Users and Computers in the tree view and choose Advanced Features from the View menu.
Expand the domain node in the tree view and choose Users.
Right-click the service account, select Properties, and then choose to display the Security tab.
Choose SELF in the Group or user names list.
Under Permissions for SELF, in the lower part of the panel, scroll down to Write public information and select the Allow column.
Choose OK to exit the Properties panel, and close Active Directory Users and Computers.

Giving the account necessary database privileges in SQL Server
The Microsoft Dynamics NAV Server account must be a member of the db_owner database role on the Microsoft Dynamics NAV database.

When you install the Microsoft Dynamics NAV database by using Microsoft Dynamics NAV Setup or the New-NAVDatabase PowerShell cmdlet, you can specify the Microsoft Dynamics NAV Server account.

In these cases, the server account that you specify should already have the necessary privileges in SQL Server. If you change the Microsoft Dynamics NAV Server account for an existing installation, then you should verify the account has the required privileges in SQL Server.

To verify database privileges after you create your Microsoft Dynamics NAV database, use SQL Server Management Studio and, if necessary, modify database privileges.

If you installed the Demo option in Microsoft Dynamics NAV Setup, then the Network Service account already has the necessary database privileges.

To assign necessary database privileges for the Microsoft Dynamics NAV Server account

Start SQL Server Management Studio and connect to the instance where the Microsoft Dynamics NAV database is installed.
Create a login for the Microsoft Dynamics NAV Server account.

Navigate the tree view: Security, Logins
Right-click Logins and choose New Login.
Choose Search, and use the Select User or Group dialog box to identify the Microsoft Dynamics NAV Server account.
Choose OK to exit the New Login dialog box.

Add the login as a user on the master database.

Navigate the tree view: Databases, System Databases, master, Security, Users.
Right-click Users and choose New User.
Choose the ellipse button at the far right of the second line in the Database User – New dialog box.
In the Select Login dialog box, enter or browse for the login you created for the Microsoft Dynamics NAV Server account.
Enter a name in the User name field (the first line in the Database User – New dialog box).
Choose OK to exit the Database User – New dialog box.

Grant the Microsoft Dynamics NAV Server login permissions on the master database. In the tree view, right-click master and choose Properties. Then do the following in the Database Properties – master dialog box.

Under Select a Page, choose Permissions.
Under Name, choose the login you created for the Microsoft Dynamics NAV Server account name.
Under Permissions for <username>, on the Explicit tab, scroll down to down to the Select line, and select the check box in the Grant column.
Choose OK to exit the Database Properties – master dialog box.
Navigate the tree view: Databases, System Databases, master, Tables, System Tables.
Right-click the dbo.$ndo$srvproperty table and choose Properties.
Under Select a Page, choose Permissions.
Choose Search, and use the Select User or Group dialog box to identify the login for the Microsoft Dynamics NAV Server account.
Under Permissions for <username>, on the Explicit tab, scroll down to down to the Select line, and select the check box in the Grant column.
Choose OK to exit the Table Properties – dbo.$ndo$srvproperty dialog box.

Grant the login the necessary database roles on the Microsoft Dynamics NAV database.

Navigate the tree view: Databases, <your Microsoft Dynamics NAV database>, Security, Users.
Right-click Users and choose New User.
In the Database User – New dialog box, choose the ellipse button at the far right of the second line.
Select the login you created for the Microsoft Dynamics NAV Server account name and choose OK.
Under Database role membership, select the db_owner check boxe.
Choose OK to exit the Database User – New dialog box.
Right-click your Microsoft Dynamics NAV database and choose Properties.
Under Select a Page, choose Permissions.
Choose Search, and use the Select User or Group dialog box to identify login you created for the Microsoft Dynamics NAV Server account.
Under Permissions for <username>, on the Explicit tab, scroll down to down to the View database state line, and select the check box in the Grant column.
Choose OK to exit the Database Properties dialog box for your Microsoft Dynamics NAV database.

It is also possible to script these steps in SQL Server Management Studio:

USE [master]

GO

CREATE LOGIN [domain\accountname] FROM WINDOWS

CREATE USER [domain\accountname] FOR LOGIN [domain\accountname]

GRANT SELECT ON [master].[dbo].[$ndo$srvproperty] TO [domain\accountname]

GO

USE [Microsoft Dynamics NAV Database]

GO

CREATE USER [domain\accountname] FOR LOGIN [domain\accountname]

ALTER ROLE [db_owner] ADD MEMBER [domain\accountname]

GRANT VIEW DATABASE STATE TO [domain\accountname]

Provisioning the Network Service Account
The only circumstance where it is necessary to take any action with regard to the Network Service account is when change the Microsoft Dynamics NAV Server account on an existing installation from a domain account to the Network Service.

In this situation you must verify that the account has the necessary database privileges in SQL Server, as per Giving the account necessary database privileges in SQL Server, above.

Security, Server

Enhancing Microsoft Dynamics NAV Server Security

August 19, 2015August 19, 2015 Ashwini Tripathi

Microsoft Dynamics NAV Server is a .NET-based Windows Service application that works exclusively with SQL Server databases.

Microsoft Dynamics NAV Server provides an additional layer of security between clients and the database. It leverages the authentication features of the Windows Communications Framework to provide another layer of user authentication and uses impersonation to ensure that business logic is executed in a process that has been instantiated by the user who submitted the request. This means that authorization and logging of user requests are performed on a per-user basis.

Login Account

After you install Microsoft Dynamics NAV Server, the default configuration is for the service to log on using the NT Authority\Network Service account. If Microsoft Dynamics NAV Server and SQL Server are on different computers, then MS recommends that you configure Microsoft Dynamics NAV Server to log on using a dedicated Windows domain user account instead. This account should not be an administrator either in the domain or on any local computer. A dedicated domain user account is considered more secure because no other services and therefore no other users have permissions for this account.

Disk Quotas

Client users can send files to be stored on Microsoft Dynamics NAV Server, so MS recommend that administrators set up disk quotas on all computers running Microsoft Dynamics NAV Server.

This can prevent users from uploading too many files, which can make the server unstable. Disk quotas track and control disk space usage for NTFS volumes, which allows administrators to control the amount of data that each user can store on a specific NTFS volume.

Limiting Port Access

The Microsoft Dynamics NAV Setup program opens a port in the firewall on the computer where you install Microsoft Dynamics NAV Server. By default, this is port 7046.

To improve security, you can consider limiting access to this port to a specific subnet. One way is to use netsh, which is a command-line tool for configuring and monitoring Windows-based computers at a command prompt.

The specific version of this command that you would use is netsh firewall set portopening. For example, the following command limits access to port 7046 to the specified addresses and subnets:

netsh firewall set portopening protocol=TCP port=7046 scope=subnet addresses=LocalSubnet

You can learn more on netsh command here.

Development Tips

How to grant access to the certificate’s private key to the service account for Microsoft Dynamics NAV Server

August 7, 2015August 7, 2015 Ashwini Tripathi

To start with this exercise you need to first create certificate and certificate revocation list, if not yet created refer to previous post.

Previous Step Link- How to create a root CA and a private key file by using the makecert.exe utility

Previous Step Link- How to create certificate revocation list for the root certification authority

Previous Step Link- How to create and install a test certificate for the Microsoft Dynamics NAV Server computer

Let’s start with this exercise:

In the left pane of MMC, expand the Certificates (Local Computer) node, expand the Personal node, and then select the Certificates subfolder.
In the right pane, right-click the NavServiceCert certificate, choose All Tasks, and then choose Manage Private Keys.
In the Permissions for NavServiceCert private keys dialog box, choose Add.

In the Select Users, Computers, Service Accounts, or Groups dialog box, enter the name of the service account that is used by Microsoft Dynamics NAV Server By default, the service account is NETWORK SERVICE. Choose OK when done.

Security Note

In a production environment, you run Microsoft Dynamics NAV Server under a dedicated domain user account instead of the less secure NETWORK SERVICE account. Because this is a test implementation, the NETWORK SERVICE account is acceptable.

In the Permissions for NavServiceCert private keys dialog box, select the account, and then select the Allow check box next to Full Control. Choose OK when done.
In the right pane, double-click the NavServiceCert certificate.
In the Certificate dialog box, choose the Details tab, and then select the Thumbprint field.

Copy or note the value of the Thumbprint field.

In next step we will Implement Security Certificates and configure Microsoft Dynamics Server and update configuration file for Windows & Web Clients to support login over a WAN.

Next Step Link- Implementing Security Certificates.

Rate this:

Share this with:

Rate this:

Share this with:

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

For Multitenant Environment

Rate this:

Share this with:

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

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with: