Using NetTcpPortSharing for NAV Servers

I just came back from three weeks vacation yesterday.  During my vacation I had made a decision to implement Tcp Port Sharing for the Instance Administration tool used in Advania Azure.

Early last year I published a function that uses the sc.exe to modify a NAV Service startup type.  When a NAV Service is installed and configured in setup, the startup type is Automatic (Delayed Start).  However, create a new service with Powershell New-NavServerIntance and the statup type is Automatic without the (Delayed Start).

To enable Tcp Port Sharing that same sc.exe function is needed.  Interestingly, after I had finished the task and was reading NAV blogs I saw that Waldo just published a powershell function on his blog to do just this.

The script lines I used and added to my Instance Administration powershell scripts are based on my fist sc.exe function but not using the function it self.  Now when a new NAV service is created by the tool the startup type is modified and if so selected by the deployment settings, the Tcp Port Sharing is also activated.

By default, the Tcp Port Sharing service is disabled.
servicedisabled

The startup type should be changed to Manual.  This can be done manually or by an administrative powershell script.

[code lang=”powershell”]#Set Startup Mode for NetTcpPortSharing to Manual
$command = ‘sc.exe \\$Computer config "NetTcpPortSharing" start= demand’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set NetTcpPortSharing to manual start. More details: $Output"
}
[/code]

Similar script is used to update the existing NAV Services to both delayed start and Tcp Port Sharing dependency.

[code lang=”powershell”]

#Stop NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Stop
#Update Startup Type and Dependency on NAV Server Instances
Get-NAVServerInstance | foreach {
$Service = $_.ServerInstance
Write-Host "Working on service $Service"
$Computer = ‘LOCALHOST’
$command = ‘sc.exe \\$Computer config "$Service" start= delayed-auto’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service to delayed start. More details: $Output"
}
$command = ‘sc.exe \\$Computer config "$Service" depend= NetTcpPortSharing/HTTP’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service TcpPortSharing. More details: $Output" -foregroundcolor red
}

}
#Start NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Start
[/code]

It should be obvious that the above script can also use the Set-ServiceStartupMode from my blog and the Enable-NAVServerInstancePortSharing function on Waldo’s blog. That would be a cleaner code and more in line with what we would like to see.

Again quoting Waldo from his previous blog, “When you’re using a dedicated service account, things might become a slight more difficult”.  That is exactly my case, I am using a dedicated service account.

After enabling Tcp Port Sharing and updating the services they would not start.  Event Viewer revealed the reason.

Server instance: CRONUS
The service MicrosoftDynamicsNavServer$CRONUS failed to start. This could be caused by a configuration error. Detailed error information:System.ServiceModel.CommunicationException: The service endpoint failed to listen on the URI ‘net.tcp://mynavserver.dynamics.is:7046/CRONUS/Service’ because access was denied. Verify that the current user is granted access in the appropriate allowAccounts section of SMSvcHost.exe.config. —> System.ComponentModel.Win32Exception: Access is denied

So I started to ask Bing what I could do.  Microsoft MSDN states:

When a net.tcp binding enables port sharing (by setting portSharingEnabled =true on the transport binding element), it implicitly allows an external process (namely the SMSvcHost.exe, which hosts the Net.TCP Port Sharing Service) to manage the TCP socket on its behalf.

Hence, I need to add the Sid of my NAV Service Account to the SMSvcHost.exe.config file.  I could do this manually, but I am a programmer!

Another powershell script was born.  This one could also be converted to a function.  Before executing the script make sure to update the user and domain in the top of the script.  Be smart and execute this function before updating the NAV Services with the script above.

[code lang=”powershell”]
#Modify User and Domain to fit your environment
$UserToAdd = ‘srvNAV’
$UserDomainToAdd = ‘DYNAMICS’

#Initial Values
$UserSidFound = ‘false’
$ConfigurationSet = ‘false’

#Net.Tcp Port Sharing Service Name
$ServiceName = ‘NetTcpPortSharing’

#Get SID for the Service User
$UserSid = ([wmi] "win32_userAccount.Domain=’$UserDomainToAdd’,Name=’$UserToAdd’").SID

#Get Path for SMSvcHost.exe.config file
$SMSvcHostPath = (Get-WmiObject win32_service | ?{$_.Name -like $ServiceName} ).PathName
$SMSvcHostPathConfig = $SMSvcHostPath + ‘.config’

Write-Host "Reading XML from $SMSvcHostPathConfig"
#Read Config file
$xmlDoc = [xml] (Get-Content $SMSvcHostPathConfig)

Write-Host "Looking for access permission for $UserSid"
#Loop through allowed accounts and search for the service user Sid
$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’
Write-Host "Found SID $ConfiguredSid"
}

#Act if Access Configuration is not enabled
if ($ConfigurationSet -eq ‘false’) {Write-Host "Access permission not configured"
$config = [xml] ‘<system.serviceModel.activation>
<net.tcp listenBacklog="10" maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10" teredoEnabled="false">
<allowAccounts>
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
</allowAccounts>
</net.tcp>
<net.pipe maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10">
<allowAccounts>
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
</allowAccounts>
</net.pipe>
<diagnostics performanceCountersEnabled="true" />
</system.serviceModel.activation>’

$configurationNode = $xmlDoc.DocumentElement
$newConfig = $xmlDoc.ImportNode($config.DocumentElement, $true)
$configurationNode.AppendChild($newConfig)

$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
Write-Host "Found SID $ConfiguredSid"
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’
}

}

#Add Service User Sid if needed
if ($UserSidFound -ne ‘true’) {
$nettcp = $xmlDoc.SelectSingleNode("configuration/system.serviceModel.activation/net.tcp/allowAccounts")
$addNode = $xmlDoc.CreateElement(‘add’)
$secIden = $xmlDoc.CreateAttribute(‘securityIdentifier’)
$secIden.Value = $UserSid
$addNode.Attributes.Append($secIden)

$nettcp.AppendChild($addNode)
$xmlDoc.Save($SMSvcHostPathConfig)
Write-Host "Configuration Updated"
#Restart Service if running
if ((Get-Service NetTcpPortSharing).Status -eq "Running") {Restart-Service NetTcpPortSharing -Force}
}

[/code]

This script will search for the SMSvcHost.exe.config file, load it and check to see if the NAV Service User is already allowed access.  If not then the config file is updated and saved.  This script must be executed with administrative privileges.

Perhaps this should be what I started with, but the question; why do we need this, should be answered.

First, modifying the startup mode to delayed start is done to make sure that all the required networking and database processes have been started before the NAV Service starts.  This is very important if the SQL Server is running on the same server.  On a dedicated NAV Service server this is not as important but still recommended.

Secondly, accessing a NAV Service in most cases requires changes to a firewall.  Either to open a specific port or setting up a NAT from a public interface.  To minimize the number of ports used also minimizes the networking setup and maintenance.  If different network permissions or network access is required I recommend using separate ports for the NAV Services.

Waiting for a server warmup – no more

One of the problems NAV users face is that the client is to slow after the NAV service starts.

The reason for this is that NAV uses just-in-time compilation of the source code.  The source code is compiled on the server when the server needs to use it.

If you want the server to be warm when the demoing NAV you need to make sure that the server compiles all needed code before showing off.

On the Microsoft Dynamics NAV 2015 Azure Demo Template you can see that Microsoft has added a WarmUp script to take care of this problem.  This solution includes a solution that will open a list of pages with the web client to make sure that the server compiles the basic functionality for the demo.

If you want to play with this yourself on your own installation just download the zipped warmup folder (WarmupNAV) and start investigating.

NAV 2013 R2 Unable to verify table changes

In my setup I am running a Developement Environment on one computer, the Server on another and the database is on the third computer – the SQL Server.

Running Server and Database on separate machines requires a domain user account for database authentication.  What I have found is that a service running on a domain user account is unable to verify table changes.

ServerCannotBeReached

The server is running.  I can see the server instance.

AvailableServerInstances

There are two methods to get around this error.

  • Start a Server Instance on your Developement machine using domain user account
  • Start a Server Instance on your SQL server using Network Service

Using OpenXML to create an Excel Document

In one of my projects I needed to create a complex Excel document from Dynamics NAV.  I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370.  The problem is that this is dead slow.  It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document.  Great blog and a big help.  The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”.  I was inserting an invalid character into the XML.  I did a quick search and found a solution, I needed to filter the data through a white list.  Here is my AddCell function:

LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
VAR
XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
CellValueAsText@1200050007 : Text[1024];
CommentLine@1200050003 : Text[1024];
CommentPosition@1200050004 : Integer;
CommentLength@1200050005 : Integer;
BEGIN
IF CurrentRow = 0 THEN
GlobalRowNo := GlobalRowNo + 1
ELSE
GlobalRowNo := CurrentRow;
IF CurrentCol = 0 THEN
GlobalColumnNo := GlobalColumnNo + 1
ELSE
GlobalColumnNo := CurrentCol;

//Comments are not yet supported by ClosedXML
//CommentLength := CommentText.LENGTH;
//IF CommentLength > 0 THEN BEGIN
// CommentPosition := 1;
// WHILE CommentPosition < CommentLength DO BEGIN
// CommentPosition := CommentPosition + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
// XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
// END;
//END;

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
IF IsUnderline THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

IF Attention THEN BEGIN
XlColor := XlColor.FromName('Red');
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
END;

IF IsFormula THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
ELSE
CASE TRUE OF
Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
BEGIN
CellValueAsText := FORMAT(Value);
CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
END;
ELSE IF FORMAT(Value) <> '0' THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
END;

CASE Alignment OF
Alignment::General:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
Alignment::Center:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
Alignment::Right:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
Alignment::Left:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
END;
END;

 

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

 

is used to clean the string that is passed to the XML.

The GetEnumValue function is

LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
VAR
Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
BEGIN
ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
END;

 

When constructing the OpenXML dotnet object I also construct the white characther check object.

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

 

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in

 

 

VB.NET NAV Application Server

Most of my clients require a running NAV Application Server.  The NAS that is included in NAV 2009 R2 requires a license that is included in most licenses today.  However, there are cases where more than one NAS is needed.  That requires additional NAS licenses.  Where the customer is running NAV 2009 R2 middle tier service this changes.  By running a VB.NET NAV Application Server it is possible to setup multiple services on a single CAL license.  The CAL license is not as expensive as the NAS license.  Here is the solution that I offer.

First, I create a codeunit in NAV

[code htmlscript=”false”]ExecuteCodeunit(CodeunitID : Integer;Log : Boolean) Success : Boolean

IF Log THEN LogEntryNo := InsertLogEntry(5,CodeunitID);
Success := CODEUNIT.RUN(CodeunitID);
IF Log THEN
UpdateLogEntry(LogEntryNo,Success)
ELSE IF NOT Success THEN BEGIN
LogEntryNo := InsertLogEntry(5,CodeunitID);
UpdateLogEntry(LogEntryNo,Success)
END;

InsertLogEntry(ObjectType : ‘,,,Report,,Codeunit’;ObjectNo : Integer) : Integer
WITH JobQueueLogEntry DO BEGIN
INIT;
ID := CREATEGUID;
“User ID” := USERID;
“Start Date/Time” := CURRENTDATETIME;
“Object Type to Run” := ObjectType;
“Object ID to Run” := ObjectNo;
INSERT(TRUE);
COMMIT;
EXIT(“Entry No.”);
END;

UpdateLogEntry(LogEntryNo : Integer;WasSuccess : Boolean)
WITH JobQueueLogEntry DO BEGIN
GET(LogEntryNo);
“End Date/Time” := CURRENTDATETIME;
IF WasSuccess THEN
Status := Status::Success
ELSE BEGIN
Status := Status::Error;
SetErrorMessage(COPYSTR(GETLASTERRORTEXT,1,1000));
END;
MODIFY;
COMMIT;
END;[/code]

This codeunit uses the Job Queue Log to log the execution.  Next step is to publish this codeunit as a web service in table no. 2000000076.  Default web service name is NAVAppServer.

On the server you install the following files (in my case to C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server)


Next step is to edit the VB.NET NAV Application Server.exe.config file and customize the values.

 <applicationSettings>
        <NAV_Application_Server.My.MySettings>
            <setting name="NAVAppServer" serializeAs="String">
                <value>http://<Middle Tiere Host Name>:7047/DynamicsNAV/WS/<CompanyName>/Codeunit/NAVAppServer</value>
            </setting>
            <setting name="CodeunitID" serializeAs="String">
                <value>81004</value>
            </setting>
            <setting name="LogMode" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="TimerInterval" serializeAs="String">
                <value>90000</value>
            </setting>
            <setting name="LogFrequency" serializeAs="String">
                <value>15:00:00</value>
            </setting>
            <setting name="SMTPHost" serializeAs="String">
                <value>mail.dynamics.is</value>
            </setting>
            <setting name="FromAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="ToAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="RetryTimerInterval" serializeAs="String">
                <value>45000</value>
            </setting>
        </NAV_Application_Server.My.MySettings>
    </applicationSettings>

To install as a service start command prompt in elevated mode and execute installutil.exe command.  The install will prompt for the user to start the service.

C:\Windows\Microsoft.NET\Framework\v2.0.50727>InstallUtil.exe "C:\Program Files(x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe"

Microsoft (R) .NET Framework Installation utility Version 2.0.50727.5420
Copyright (c) Microsoft Corporation.  All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Installing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog
Installing service VB.NET-NAVAppServer$DEFAULT...
Service VB.NET-NAVAppServer$DEFAULT has been successfully installed.
Creating EventLog source VB.NET-NAVAppServer$DEFAULT in log Application...

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Committing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

The last step is to change the service startup and start the service.

The service will create entries in the Application Log.

NAV Web Service Codeunit

The following ZIP files are encrypted.

VB.NET NAV Application Server DEFAULT Executables

NAV App Server Visual Studio 2008 Project

When the Database Name has non-english character

At a client I was trying to start the NAV 2009 R2 Service Tier.  The client has a non-english character in the database name and the service tier complained and would not start.  This is an issue that Microsoft fixed recently and after I installed a newer version of the service tier everything works fine.

I also had to make sure that the CustomSettings.config file was saved with UTF-8 encoding.  This can be done by running Notepad in administrative mode, open the file and select the UFT-8 encoding in save-as dialog.