Could not register the Service Principal Name (SPN)

Recently I checked my SQL Server Error Logs. Quite some interesting information in my opinion, however I also found this message:

Date  25-7-2017 18:26:41
Log  SQL Server (Archive #3 – 25-7-2017 18:34:00)
Source  Server

Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL01.contoso.lan:NAV ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

What’s this message about?
It cleary indicates thats SQL Server couldn’t register SPN’s. I’m running SQL Server under a ‘Virtual’ account so that should be the cause of the ‘error’.

From security perspective it is recommend to run SQL Server under the least privileged account: a virtual of MSA. For more information please go to Microsoft Docs.

In order to use Kerberos authentication with SQL Server there are some conditions to be met:

– The client and server computers should be in the same domain or trusted (2 way)  domains.
– SPN’s must be registered for SQL Server

In theory I can’t connect to my SQL Server using Kerberos authentication so why I’m still able to connect to my SQL Server? What kind of authentication is being used? Even other services from other machines are still able to connect (like Microsoft Dynamics NAV). In order to get an answer you could query SQL Server. With this query you’re able to view what kind of authentication scheme is being used:

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections

It turns out that Microsoft Dynamics NAV for example is falling back to Ntlm, intereseting… So let’s fix the SPN, restart SQL Server and look what’s happening? Now Microsoft Dynamics NAV 2017 is also connected to SQL Server but instead of Ntlm it is using Kerberos now.

In order to fix the SPN problem I manually registered the SPN in Active Directory (on the SQL Computeraccount). The errorlog states two SPN’s couldn’t be registered:

MSSQLSvc/SQL01.contoso.lan:NAV
MSSQLSvc/SQL01.contoso.lan:49753

For your information: I’m running SQL Server in a named instance called ‘NAV’ using Dynamic Ports. If you’re running SQL Server in the default instance on TCP Port 1433 the SPN’s are a little bit different. Please keep this in mind!

ServerInstance Administration with PowerShell

In Microsoft Dynamics NAV 2017 there a  couple of Cmdlets to administer server instances:

Get-NAVServerInstance
New-NAVServerInstance
Remove-NAVServerInstance
Set-NAVServerInstance

In order to run these Cmdlets we need to start ‘PowerShell ISE’ as an Administrator. Now import the NAV Administration module in order to use the NAV PowerShell Cmdlets.

I will show you how to perform the following tasks:

  • Show current instances
  • Create a new instance
  • Configure your newly created instance
  • Remove your created instance

For these tasks to accomplish you must use the following Cmdlets in your PowerShell ISE like this:

Show all created server instances:

get-navserverinstance

Create a new server instance like this:

New-NAVServerInstance -ServerInstance 'NST2017-Demo' -ManagementServicesPort 7045 -ClientServicesPort 7046 -SOAPServicesPort 7047 -ODataServicesPort 7048

Please note: if you omit a non-mandatory parameter for example ‘SOAPServicesPort’ then SOAP services will be disabled on this Server Instance. The server instance will run under the NETWORK SERVICE account. There are some other parameters to specify more information like:

  • MultiTenant
  • DatabaseServer
  • DatabaseInstance
  • DatabaseName
  • ServiceAccount

There are more parameters but they are used lesser in my opinion. Detailed information about this Cmdlet can be found on MSDN – Developer and IT Pro Help for Microsoft Dynamics NAV.

So one more example. In order to create a server instance that runs under a service account you could use the following Cmdlets:

$ServiceAccountCredential = Get-Credential
New-NAVServerInstance -ServerInstance 'NST2017-Demo' -ManagementServicesPort 7045 -ClientServicesPort 7046 -SOAPServicesPort 7047 -ODataServicesPort 7048 -ServiceAccount User -ServiceAccountCredential $ServiceAccountCredential

This will show the Windows credentials screen where you can enter a username and password. In some cases this is very handy right? In same cases not. So what if you want to hardcode the username and password? This way you don’t have to type in the credentials if you need to create a couple of Server Instances. In order to accomplish we need to create a PSCredential object (New-Object Cmdlet). An example:

$SecurePassword = ConvertTo-SecureString 'YourPassword' -AsPlainText -Force
$ServiceAccountCredential = New-Object System.Management.Automation.PSCredential ("ServiceAccountUsername”, $SecurePassword)
New-NAVServerInstance -ServerInstance 'NST2017-Demo' -ManagementServicesPort 7045 -ClientServicesPort 7046 -SOAPServicesPort 7047 -ODataServicesPort 7048 -ServiceAccount User -ServiceAccountCredential $ServiceAccountCredential


To remove a server instance just type:

$ServerInstance = 'NST2017-Demo'
Remove-NAVServerInstance -ServerInstance $ServerInstance

I like to add the ‘Verbose’ parameter to my Cmdlets. This will output verbose messages and gives more feedback:

verbose-output-in-nav-cmdlets

 

 

Cumulative Update 3 for Microsoft Dynamics NAV 2017 has been released

Microsoft just released Cumulative Update 3. This update includes application and platform hotfixes that have been released for Microsoft Dynamics NAV 2017. This Microsoft Support page shows what has been fixed.

You can download the update from KB 4011763 – Cumulative Update 3 for Microsoft Dynamics NAV 2017 (Build 15140) directly from Microsoft. Now you don’t need an account anymore in order to download the CU. Just select your country in the list for a direct download from the Microsoft Download Center.

Download Microsoft Dynamics NAV 2017

Before you install a cumulative update in a production environment, take the following precautions:

  • First deploy the cumulative update in a non-production environment.
  • Always make a SQL backup in order to have a rollback scenario.

For information about how to install the cumulative update, see How to How to install a Microsoft Dynamics NAV 2017 Cumulative Update

Company Administration in Powershell

In Microsoft Dynamics NAV 2017 there a currently five Cmdlets to administer companies:

Copy-NAVCompany
Get-NAVCompany
New-NAVCompany
Remove-NAVCompany
Rename-NAVCompany

In order to run these Cmdlets we need to start ‘PowerShell ISE’ as an Administrator. Now import the NAV Administration module in order to use the NAV PowerShell Cmdlets.

I will show you how to perform the following tasks:

  • Show all companies
  • Copy a company
  • Rename a company
  • Create a new company
  • Delete the new company

For these tasks to accomplish you must use the following Cmdlets:

Import-Module 'C:\Program Files\Microsoft Dynamics NAV\100\Service\NavAdminTool.ps1'
$ServerInstance = 'NST2017RTM' # Modify to the name of your server instance
Get-NAVCompany -ServerInstance $ServerInstance # Show all companies
Copy-NAVCompany -ServerInstance $ServerInstance -SourceCompanyName 'CRONUS Nederland BV' -DestinationCompanyName 'CRONUS International' # Copy an existing company to a new company
Rename-NAVCompany -ServerInstance $ServerInstance -CompanyName 'CRONUS International' -NewCompanyName 'CRONUS Worldwide Enterprises' # Rename the copied company
New-NAVCompany -ServerInstance $ServerInstance -CompanyName 'CRONUS Europe' # Create a new company
Remove-NAVCompany -ServerInstance $ServerInstance -CompanyName 'CRONUS Europe' # Delete a company

If you are operating NAV in a Multi-Tenant setup then you must also specify the Tenant parameter. The ServerInstance parameter is mandatory for all Company Cmdlets.

Of course it’s also possible to accomplish this in the NAV Client:

company-administration

 

Configuring Database Deadlock Detection and Monitoring

On NAV Techdays 2016 in Antwerpen (Belgium) Microsoft demonstrated a beautiful new feature. Microsoft Dynamics NAV 2017 now offers ‘Database Deadlock Detection and Monitoring’. If NAV 2017 detects a deadlock it will rollback one of the transactions.

If you run the NST under a serviceaccount you need to apply some extra configuration to make this work:

  • Alter any event session
  • Grand View Server State

Go to the properties of your serviceaccount in SSMS and go to the ‘Securables’ tab:

alter-any-event-session

view-server-state

nav-deadlock-monitoring-configuration-sql-server

It’s also possible by executing some T-SQL:

use [master]
GRANT ALTER ANY EVENT SESSION TO [CONTOSO\sa_nav]
use [master]
GRANT VIEW SERVER STATE TO [CONTOSO\sa_nav]

If you don’t do this you will notice errors like this in your application Log:

Server instance: NST2017-RTM
Category: Sql
ClientSessionId: d7ea6dfa-622c-48e1-a6ed-9d5d8402b17c
ClientActivityId: a419d67f-0f56-4394-93cc-b5a1e753344b
ServerSessionUniqueId: 857d43f9-be50-4f2e-abcf-533364da2935
ServerActivityId: 2364cada-9b04-49e4-9268-25260ecdd20f
EventTime: 12/05/2016 16:16:04
Message <ii>Deadlock monitoring feature is enabled but current SQL server user does not have ‘VIEW SERVER STATE’ or ‘ALTER ANY EVENT SESSION’ permissions for the database: NAV2017RTM. Please grant both of those permisions in order to use this feature.</ii>
ProcessId: 4052
Tag: 00000DE
ThreadId: 9
CounterInformation:

By Default DeadLock Monitoring is already enabled on the NAV Service Tier (NST):

enable-deadlock-monitoring

Show OData webservice response directly in IE

As a Technical consultant I need to regularly install new NAV software for our customers. After installing I always try to check if web services are also working. In NAV 2017 I check at least three things:

1. Call the SOAP URL

http://nav01:7047/NST2017-RTM/WS/CRONUS%20Nederland%20BV/Page/SalesOrder
http://nav01.contoso.lan:7047/NST2017-RTM/WS/CRONUS%20Nederland%20BV/Page/SalesOrder

First link contains the hostname. Second link contains the FQDN. Always test with both to ensure it’s working flawless!

2. Call the ODataV3 URL

http://nav01:7048/NST2017-RTM/OData/Company('CRONUS%20Nederland%20BV')/SalesOrder
http://nav01.contoso.lan:7048/NST2017-RTM/OData/Company('CRONUS%20Nederland%20BV')/SalesOrder

3. Call the ODataV4 URL

http://nav01:7048/NST2017-RTM/ODataV4/Company('CRONUS%20Nederland%20BV')/SalesOrder
http://nav01.contoso.lan:7048/NST2017-RTM/ODataV4/Company('CRONUS%20Nederland%20BV')/SalesOrder

Please note: the links of course only work in my Lab environment!

The SOAP URL shows the WSDL so that’s okay:

nav2017-soap-response

Now the ODataV3 URL shows the following response:

nav2017-odatav3-response

This isn’t the response I want to see, however… If I show the source of the page (F5) I see the OData output! So this works also! Normally this is a good thing: Internet Explorer is processing the output as an RSS feed. In this case I don’t want that, so the first thing we should do is to disable this:

Go to Internet options > Content > Settings and disable ‘Turn on feed reading view’. Close the browser and retry.

Now we see the OData response immediately! It took me some time to figure this out so I hope I can save you some time with this handy info! To test the ODataV4 URL there is also a problem with the browser. I will investigate this and share my findings soon!

Object Metadata Table (C/AL translated into C#)

Before you can run or execute a Microsoft Dynamics NAV object (Codeunit for example), you must compile the object. By Compiling the object your C/AL code will be transformed into C# Code. NAV only runs compiled C/AL code which is transformed into C# Code. This code is stored in table ‘2000000071 – Object Metadata’

To compile an object, do the following:

  • Start the Development Environment.
  • Open your NAV database. Select an object.
  • In the Object Designer, Go to Tools and then choose Compile.

After your object has been successfully compiled the field [User Code] in the Object Metadata contains the C# Code.

Note: if you create an object without any C/AL code then the [User AL Code] field will be empty. The [User Code] field will also be empty, even after Compiling.