mandag den 28. maj 2012

Microsoft Dynamics NAV 6.0 is making my head hurt

So, several things have been “bugging” me while working with this.
First of all, if you are running a 3 tier setup (and you probably are) then NAV server will need to monitor changes in the database. It can do this in one of two ways. Pooling or using notifications, though the service broker. If using pooling it will do a series of SQL queries against the database every few seconds to see if they have been any changes, this is even if you only have one NAV Server service running and all chances are bound to happen though that service anyway. The other way is is though the service broker, where each NAV service can “subscribe” to changes in certain parts of the database and get notified by the SQL server when ever changes occurs. This will ofc lower the “stress” on the SQL, especially if you are running several NAV services for one or more databases.

Pooling works out of the box, no problem. But using Service broker seems more appealing to me. Service broker works out of the box if your running the NAV Server as Local System/Network Service. When running as a domain account it needs a bit more “tweaking”, explained when following this guide.
http://msdn.microsoft.com/en-us/library/dd568739.aspx

As always, Kerberos was giving me a hard time and while troubleshooting one of the errors I noticed I was getting a ton of these errors on the SQL server.
(The activated proc '[$ndo$navlistener].[SqlQueryNotificationStoredProcedure-cf5de25d-f6a3-457e-928f-cfd42a83f241]' running on queue 'NAV6_NAVI0101.$ndo$navlistener.SqlQueryNotificationService-cf5de25d-f6a3-457e-928f-cfd42a83f241' output the following:  'Could not obtain information about Windows NT group/user 'int\NAVI01_NAV601_NAVI01', error code 0x534.')

image

If you search Google for this error you wont get a lot of hits, most tell you to do some stuff that, to me, didn’t make a lot of sense. From what I can understand basically this error comes when the SQL server cannot resolve the user. This can happen if the account your running the SQL Server Agent as, doesn't have read access to the account mentioned in the error, or the account doesn’t exists.
I got the error while backing up and restoring various NAV databases and hadn't cleaned up the database between the restore.  If you backed up a database and restored it, and get this error just, stop the Navision server, delete the Queue and associated service, then start the Navision service again.

image

When testing Navision from the Role Based client, its worth mentioning that the Navision Client will save all user configuration in the LOCAL profile and not the roaming directory. ( this is mentioned on page 31 in Microsoft Dynamics NAV - Hosting Guide too. You get around this by placing the config file somewhere else, and pointing to that file by adding settings:{path} … the guide suggest copying the file to %APPDATA%\ClientUserSettings.config but it could be anywhere that makes sense in your environment.

If you get this error from the client, when tested on a “remote” machine, but everything is working when called locally on the Navision Server
(The login failed when connecting to SQL Server [FQDN] )
image

you probably got the delegation on the Navision account wrong (or if running as local system, delegation on the computer account for the Navision server ) The account need access to pass on the user’s credentials to the SQL Server. IF the SQL server is running as local system, it needs delegation permissions to the SQL computer account, else to the account that SQL Server is running as.

image

Either of the 3 options here will work. Trusting user for delegation to any service (Kerberos only) is not considered safe or secure and should never be used (but will work for Navision too)
Trusting for delegation to the SQL account/server for the Service type MSSQLSvc for Kerberos only is the recommended and most secure way. If you want to support NTLM authentication too, you will need to set this to “any authentication protocol” but this is considered less secure.

One way to check if Kerberos is configured correctly would be to set above to “any authentication protocol” and then in ClientUserSettings.config set AllowNtlm=True. If this works, then change ServicePrincipalNameRequired=True and AllowNtlm=False. If you get the below error

The remote server did not satisfy the mutual authentication requirement
image

Something in the Kerberos authentication went wrong. Looking though the Security Event log will be extremely helpful. Don’t just give up and set AllowNtlm to True. That is for losers

And remember. If something is running as Local System or Network Service, it is running as the computer account. If you mess with SPN or delegation on that computer account, you need to restart the server for the changes to take effect. If you are running as a domain account and make chances to that, you need to restart all services running as that account for the chances to take effect.

lørdag den 26. maj 2012

Dynamics NAV - Multiple Service Tiers

updated 28-05: Small issued fixed
updated 12-06: I was dead wrong regarding SPN’s. Read more here

So you want to host several Navision Server Services on a single host.

Easy, first you read http://msdn.microsoft.com/en-us/library/dd301254.aspx
Seriously, I normally think of my self as one who got the whole delegation stuff figured out, but this on had me spinning for a while.

If a ran the service as Local System or Network Service, (and gave the NAV server computer account permission to delegate for MSSQLSvc to the SQL Server) everything worked but as soon as a ran the service as a Domain account (and gave that user account permission to delegate for MSSQLSvc to the SQL Server) everything worked locally on the NAV server, but when called from a client machine you would get an error saying it couldn’t authenticate to the SQL Server.

Oh my, turns out I was running my SQL server as a domain account, but some how, some where (and it can’t have been me, I swear) the MSSQLSvc was registered to the SQL Servers computer account, and/but not to the Domain Account the SQL Server was running as. so after deleting the SPN from the SQL server and adding it to the SQL Account, everything was working perfectly.

Well, almost.

you will probably come across this post http://blogs.msdn.com/b/nav/archive/2009/10/20/creating-a-web-service-manually-the-importance-of-what-name-you-give-it-and-a-few-small-things-to-remember.aspx and this post http://blogs.msdn.com/freddyk/archive/2009/08/05/multiple-service-tiers-sp1.aspx
Awesome reading by the way, but it doesn’t take into account you might not want to run everything as local system, and all the fun that comes with that.

Next problem, do you want to assign a new port for each NAV Service or share them ?
Managing several ports are fun, managing many many more ports must be so much more fun. not
So you fire up the NetTcpPortSharing service and suddenly nothing works for NAV, since the domain account doesn’t have permission to do Port sharing. Several post if you Google this, will tell you to uninstall .NET 4.0 *pffhhh*, nah, just open C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMSvcHost.exe.config and add

<system.serviceModel.activation>
<net.tcp>
<allowAccounts>
<add securityIdentifier="S-1-5-11" />
</allowAccounts>
</net.tcp>
</system.serviceModel.activation>

Well, maybe not THAT specifically, this will allow any authenticated user to use port sharing, you might want to add each service account by SID. ( And restart the server every time you update it =| )


Next NAV will start complaining it cannot start a listener on net.tcp://+:7046/[something]/Service … This should fix that


netsh http add urlacl "url=http://+:7046/" user="NT AUTHORITY\Authenticated Users"
(replace NT AUTHORITY\Authenticated Users with the real account your running the service as)


Next the Web Service Service will fail with a similar error ( http://+:7047/ ) ..
Ahh well,
netsh http add urlacl "url=http://+:7047/" user="NT AUTHORITY\Authenticated Users"
will shut it up …. (replace NT AUTHORITY\Authenticated Users with the real account your running the service as)


So now everything is working, you can start the services, and can probably also connect using a locally installed client on the NAV server, but once you go to a client computer it will complain it cannot talk with the SQL Server.
Here is a dilemma. You cannot have a SPN registered twice in AD. (ok, you can but it will not work) lets imagine you have 2 NAV services one on
net.tcp://nav01:7046/navservice1/Service running with domain account “navservice1acct”
and one on
net.tcp://nav01:7046/navservice2/Service running with domain account “navservice2acct”


We know we need to register a DynamicsNAV/FQDN and NAVSERV_DynamicsNAV/FQDN for the account’s but if FQDN is the same, we have a duplicate and it wont work.
So create a CNAME for each service


$rec = Get-WmiObject -Namespace root\MicrosoftDNS -ComputerName $dnsserver -Class MicrosoftDNS_CNAMEType -Filter "ContainerName='$fzone' and OwnerName='$cname'"
if(!$rec){
Write
-Host "$cname not registrered in DNS, creating it now"
$text = "$cname IN CNAME $NAVServer"
#$rec = [WmiClass]('\\$dnsserver\root\MicrosoftDNS:MicrosoftDNS_CNAMEType')
$rec = [WmiClass]"\\$($dnsserver)\root\MicrosoftDNS:MicrosoftDNS_ResourceRecord"
$rec.CreateInstanceFromTextRepresentation($dnsserver, $fzone, $text) | Out-Null
}


And register the SPN’s for those. in my tests, the role based client will be using host SPN and not DynamicsNAV, not sure if that is related to me using cnames and/or using domain accounts for AX, but just be safe add all 3.
Turns out it was not even doing that. See part 2. Use this



$username = $NAVAdmin.Username
Start
-Process -FilePath 'C:\Windows\System32\setspn.exe' -ArgumentList ("-A $instancename/" + $NAVServer + ":7046 -U $username") -NoNewWindow -Wait
Start
-Process -FilePath 'C:\Windows\System32\setspn.exe' -ArgumentList ("-A $instancename/" + $cname + ":7046 -U $username") -NoNewWindow -Wait
Start
-Process -FilePath 'C:\Windows\System32\setspn.exe' -ArgumentList ("-A HOST/" + $cname + " -U $username") -NoNewWindow -Wait
Start
-Process -FilePath 'C:\Windows\System32\setspn.exe' -ArgumentList ("-A HTTP/" + $cname + " -U $username") -NoNewWindow -Wait


We want InstanceName\fqdn since that, is what Navision is using. Using CNames is still a good idear to handle the WebService, but for purely Navision, we could just stick with InstanceName\fqnd of nav server


Next you want to the Service account to allow delegation to the SQL server. If you want to use constrained delegation and restrict it to Kerberos, all you have to do is add the SPN you want to delegate too


$ADNAVAdminUser = Get-ADUser $NAVAdmin.Username
$SQLServerDelegate = @( ("MSSQLSvc/"+$NAVDBServer+":1433") )
$ADNAVAdminUser | Set-ADObject -Replace @{"msDS-AllowedToDelegateTo"=$SQLServerDelegate}

If, how ever you also want to support NTLM, you need to allow constrained delegation to any authentication protocol, and you need to add TRUSTED_TO_AUTH_FOR_DELEGATION to userAccountControl on the accounts


$NORMAL_ACCOUNT = 512
$DONT_EXPIRE_PASSWORD = 65536
$TRUSTED_TO_AUTH_FOR_DELEGATION = 16777216
$userAccountControl = ($NORMAL_ACCOUNT + $DONT_EXPIRE_PASSWORD + $TRUSTED_TO_AUTH_FOR_DELEGATION)

$ADNAVAdminUser = Get-ADUser $NAVAdmin.Username
$SQLServerDelegate = @( ("MSSQLSvc/"+$NAVDBServer+":1433") )
$ADNAVAdminUser | Set-ADObject -Replace @{"msDS-AllowedToDelegateTo"=$SQLServerDelegate; "userAccountControl"=$userAccountControl}

And to make it all easier, you might want to script updating a client config file too
Remember, the NAV client doesn’t support roaming profiles, so you need to override the config file anyway with "C:\Program Files (x86)\Microsoft Dynamics NAV\60\RoleTailored Client\Microsoft.Dynamics.Nav.Client.exe" -settings:"X:\NAV6\ClientUserSettings.config"
So why not place it somewhere everyone have access to, and keep it simple.
( I had a few issues getting Kerberos to work against the cname, but after reading http://blogs.msdn.com/b/nav_developer/archive/2009/08/17/troubleshooting-multi-machine-installations-of-nav-2009.aspx I tried adding DelegationInfo to the client config and setting it to DomainUser and that did the trick, after also adding a HOST SPN to the Service account running Navision )
( if you are using my cname trick, you probably also need to set ServicePrincipalNameRequired to False. This will also make the logon faster so might be a good idea no matter what ????. Since we have no way of telling the Navision Service what hostname to listen to and register it self as, it will always register it self with the hostname of the server. And since the client is connecting on, and expecting a reply for, the cname they cant do a full mutual authentication, based on hostname and you will get below error. )
( The remote server did not satisfy the mutual authentication requirement. )
image


[xml]$CustomSettings = Get-Content ($clientconfig)
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'Server'}).value = $cname
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'ServerInstance'}).value = $InstanceName
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'ServerPort'}).value = "7046"
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'AllowNtlm'}).value = "False"
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'ServicePrincipalNameRequired'}).value = "False"
(
$CustomSettings.configuration.appSettings.add | ?{$_.key -eq 'ClientCredentialType'}).value = "Windows"
$CustomSettings.save($clientconfig)

LogOnAsService and other fun rights, and creating a service through PowerShell

So once in a while you run into the issue of having the need to grant some account a special right, like LogOnAsService.
Lets say, you want to make a script, that can create a windows service, and needs to set the user it runs under too.
Lets imaging it’s a Navision 6.0 service
Lets just imaging

ok, installing a service.

That’s easy, we can use SC ( http://support.microsoft.com/kb/251192 )
or we can spend 2 hours goggling ways to download windows 2000 resource kit, and follow this guide http://support.microsoft.com/kb/137890
But that’s not PowerShell and we looooove PowerShell, so lets use WMI

$computer = "." # this computer
$class = "Win32_Service"
$method = "Create"
$mc = [wmiclass]"\\$computer\ROOT\CIMV2:$class"
$inparams = $mc.PSBase.GetMethodParameters($method)
$inparams.DesktopInteract = $false
$inparams.DisplayName = "Microsoft Dynamics NAV Server ($InstanceName)"
$inparams.ErrorControl = 0
$inparams.LoadOrderGroup = $null
$inparams.LoadOrderGroupDependencies = $null
$inparams.Name = $InstanceName
$inparams.PathName = "$servicepath\Microsoft.Dynamics.Nav.Server.exe $InstanceName"
$inparams.ServiceDependencies = 'NetTcpPortSharing'
$inparams.ServiceType = 16
$inparams.StartMode = "Automatic"
#$inparams.StartName = $null # will start as localsystem builtin if null
#$inparams.StartPassword = $null
$inparams.StartName = $NAVAdminUPN
$inparams.StartPassword = $NAVAdminPassword

$result = $mc.PSBase.InvokeMethod($method,$inparams,$null)
#$result | Format-List
if($result.ReturnValue -ne 0){
throw [System.Exception]("Failed installing Microsoft Dynamics NAV 6.0 NAS as a service")
}

But this wont work, unless you cheated and granted the user account LogOnAsService ( through local Policy snapin, or by just trying to set a service to run as this account, the Services MMC snapin will do it for you )


You could Google it, and would probably at some point hit this site
http://support.microsoft.com/?kbid=279664 , great more resource kit stuff

and this one
http://www.powershellcommunity.org/Forums/tabid/54/aft/5949/Default.aspx ok, thumbs up for being creative but I still think it’s a bit messy


http://www.leeholmes.com/blog/2010/09/24/adjusting-token-privileges-in-powershell/ I love this one, but its not easy using in several scripts and many machines


http://www.roelvanlisdonk.nl/?p=1151 Now were getting somewhere but it’s C# and no download link for the DLL, and I refuse to compile c# on any of my machines.


So I created a PowerShell module that will do it all ( you could make one without using a DLL using the 3rd link, but … this was easier for me )



So here it is. LSAWrapper.zip contains just the PowerShell module and a .bat file to install it. LSAWrapper.srv.zip contains the source code.


The module have 2 PowerShell commands. Set-UserRightsAssignment just give it a username and domain and press – and <tab> to see the list of rights
Set-Autologin will give you a SLIGHTLY more secure way of storing a username and password on a machine you want to automat logon after reboot, besides just adding it all to the registry ( see http://support.microsoft.com/kb/310584 ) this command will add the 2 keys, but save the password in the LSA store. not pretty but slight more secure that having it in plaintext in registry.

SetSPN slow

This one was kind of obvious, but took me a while to see though.

Ever had issues with setspn being very slow when listing or adding a new SPN ?
Did you notice its only when working with user accounts, but not machine accounts ?

Look at the help text

image

So SetSPN will assume your working with a computer account unless you specify other wise. IT will work yes, but be slow, so if you are working with a User Account remember to add -U