Just very short article: do you know that the database triggers “OnDatabaseXXX” in CU1 are working for object table too if you return correct flags in “GetDatabaseTableTriggerSetup”?
It is some time I have noticed one small change which is not described or I never see it in some official documentation (excluding help files). You know the drill when you need to pass temporary table:
You pass it as parameter by reference, or you copy the records one by one.
Sometime you will use the first, sometime you will use the second. Now, you have new possibility:
You are thinking: “Oh, wait, it is not copying the data inside, only filters etc.!”
It is not true anymore. If you look into the Symbol menu to this function over some record variable, you will notice that there is new second parameter with name “ShareTable”. And this is it. If you set this parameter to “True” and you will call this COPY on some temporary table, the record variable to which you are copying will share the temporary table with the first variable! It means you see same data, but you have another variable to iterate and modify them!
TempVar1.Code := ‘TEST’;
TempVar1 and TempVar2 are temporary records e.g. over table 3. EASY! If you forgot to set the temporary on one of the variables, you will get this error in run time:
The COPY function can only be used with the shareTable argument set to true if both records are temporary.
Quote from the on-line help:
Specifies whether the function creates a copy of the record or creates a reference to a temporary record.
If FromRecord and Record are both temporary and ShareTable is true, then the COPY function does not create a new copy of the record. Instead, the COPY function causes Record to reference the same table as FromRecord.
The default value is false. If you specify false, all records are copied to Record from FromRecord.
As you can see, there is one mistake in the description: if the parameter is false, regarding the online help it should copy the records. But THIS IS NOT TRUE! In this case, the same example with false parameter will give you same result (only the table will be another copy, not the same one), but the real result is error, because the table in TempVar2 is empty!
I think that many of you didn’t notice this change, which could help us to create much better code. Sometime even the standard objects are using arrays of record variables to solve this. Now, you can use different variables and set them to same instance of the temporary table by just using COPY on them.
Those little things makes me happy each time I find something like that.
To all you interested in newest things around NAV and everyone interested in face to face meeting with people around Mibuso.com and Microsoft Dynamics NAV product group: do not forget to register to NAV TechDays 2011. It is time to get your air tickets, hotel reservations and travel plans. Do not miss this possibility to be part of the NAV World and be in contact with all the people around. Do not hesitate and use this event to pass your ideas, questions and other things to the correct people and learn something new from the NAV top X presenters.
See you there!
Everyone, who once tried to install NAV 2009 Server to separate computer than MS SQL Server, needed to solve one “mystery”: how to set the SPNs correctly. Today I will try to uncover the fog around the SPNs and how to set them correctly. At first, I am not expert in this area too. I will try to describe everything as “I understand it”. It means, it could not be fully true, but it works for me. I know that there are already different articles about NAV and SPNs but I have found them as a not fully understandable or I missed some parts in them. Thus I will try to describe all what you need to know…
What is SPN?
SPN = Service Principal Name
As you can see from the name, SPN somehow identifies some service. Name is created from service/instance name and the host name in most cases. It means:
identifies service with name MSSQLSvc (name for default instance of SQL server) running on server.contoso.local using port 1433. It means same service on another server have different SPN. Another service on same server have different SPN too. BUT! The server name depends on the way, how you are calling the service. It means, if you use servername “server” to connect to the SQL, this SPN will not work! You will need different SPN:
It means that parameter “DatabaseServer” in CustomSettings.config for NAV server must use the naming you have used when creating the SPN. If you use different name, you need to create the SPN for it. Same applies when calling NAV webservices – if you use http://server/xxx or http://server.contoso.local/xxx it needs different SPNs to be set. Else it will not work.
To be able to use the service with Kerberos, each SPN must be registered with one account object of the domain. It means that specific service instance is connected with one specific domain account, under which the service is running. It means that if the service (e.g. MS SQL) is running under account “contoso\SQLServer”, the SPN must be registered with the account “contoso\SQLServer”. This is done through command prompt by running this command:
setspn –A MSSQLSvc/server.contoso.local:1433 contoso\SQLServer
If you change account under which the service is running, you need to re-register the SPN with new account. There cannot be one service registered with two accounts, else Kerberos will not work for this service! If you want to list all services registered for specific account, you can use this command:
If you want to see all SPNs for specific server name, you can use this command:
setspn –q */server.contoso.local
By using these commands you can check your settings and correct it if you need.
Using service account
in previous text I was describing how the SPN works and that they are registered with the domain account under which the service is running. But what if you are using system account like “Network service” or “Local service”? It is not domain account…
In this case, you need to use the server domain account. It means that you will use “contoso\server$” as a account name. Each computer connected into the domain have account in format “<domain>\<computer_name>$”. If you don’t forget this, it is much easier to set everything to work correctly.
Chain of trust
If you need to be able to pass Kerberos authentication between services, you need to create the chain correctly. Every service in the chain must have correct SPN registered with correct account. Than you need to set some additional properties of the domain account which will pass the authentication to other services. In case of NAV it means that you need to allow the account, under which the NAV server is running, to pass the authentication to the SQL server. But I think that this is the easiest step. You need to open properties of the account through mmc snap-in console “Active Directory Users and Computers”. You can find the new tab “Delegation” on which you need to set to which services could this specific account delegate the authentication. You can set it to “all services” or only for specific one. But because this is easy to set (you just select specific server and pick the correct service from list of all available services registered on specific server) I will not go deeper into this. But do not skip this step, else it will not work…
Tools to simplify the process
If you have still problems to find correct commands to set all what you need, I recommend to use DelegConfig v2 (beta). You just add this application to some IIS (if you use different server than where you are running NAV server, set the application pool for this application on the IIS to some account which have enough permissions to read data from Active directory) and open the site. There is wizard, which will take all info from you (Front-end is NAV Service – service name is the instance name you are using in NAV server – DynamicsNAV by default, back-end is the SQL) and the tool will shows you the report with all information you need. If there are missing SPNs, it will give you the commands you need to create them. If there are duplicities or strange records, it will give you commands to delete them. If this tool tells you that everything is OK, trust me, it will work. If there is some error (not the one that the tool have not enough permissions to read all necessary info, this means that the used account have not enough permissions), the delegation will not work and you need to set everything the tool tells you.
Dynamics NAV server instance name TESTNAV on server NAVSERVER.contoso.local under “Network service” (incl. webservices)
SQL Server running on SQLSERVER.contoso.local under “contoso\SQLServer” account.
We are using both naming conventions to connect to the servers, FQDN and NetBios names.
Needed SPNs for NAV Server:
setspn –A TESTNAV/NAVSERVER.contoso.local:7046 contoso\navserver$
setspn –A TESTNAV/NAVSERVER:7046 contoso\navserver$
SPNs for NAV WebService:
setspn –A HTTP/NAVSERVER.contoso.local contoso\navserver$
setspn –A HTTP/NAVSERVER contoso\navserver$
SPN for SQL:
setspn –A MSSQLSvc/SQLSERVER.contoso.local:1433 contoso\SQLServer
setspn –A MSSQLSvc/SQLSERVER:1433 contoso\SQLServer
On “contoso\navserver$” account must have enabled “Trust this computer for delegation to any service (Kerberos only)” or “Trust this computer for delegation to specified services only.” and the MSSQLSvc service on SQLServer must be selected as trusted service.
If it doesn’t work, check that you are using the correct name and port for which you have registered the SPN, that the service is running under account for which you have registered the SPN, that there are not duplicities (setspn –X) and that the account is trusted for delegation to correct services. If you do not still know where is the problem, try to use the DelegConfig tool to check all for you.