How tos

How to use multiple licenses simultaneously in service tier and development (and classic) environmen

Author

Alain Krikilion (alias kriki)

Website

http://mibuso.com/blogs/kriki

Date

04/06/2013

First release

28/05/2013

Size

26,64 KB

Downloads

1731

Rating

Popularity

Downloaded 7 times in the last two weeks

What problem do we want to solve?

A partner has multiple databases (at least 1 per customer) and wants to use, at the same moment, the developer license for development and also the customer license for testing. At the moment, this is quite difficult to do (except creating multiple databases).
The same problem is at the customer site. The customer works with his license, but sometimes the partner needs to do some work on the database using a development license. This customer problem is a subset of previous problem because instead of having multiple customer licenses, it has only the customers' license and the partners' development license.

How can we solve this?

The solution would be to take the license depending on the needs of the moment: this can be based on database, username that accesses the database and the version of the database (licenses between NAV2013 and pre-NAV2013 are not compatible).
And it should support both the development environment as the servicetier environment.

What is the idea to solve the problem?

Instead of 1 license in the table master.dbo.[$ndo$srvproperty], it should contain multiple records with the different licenses to use in different situations.
It must be possible to define a license for a certain database, a certain NAV Version, a certain user. And also a combination of these fields.

Is that possible?
Using a trick, YES!
But only for databases with "License Per Database" = No

How do we implement that?

In this paragraph I explain the big picture of the changes without giving any code. The details (and code) are explained in the next paragraph.

First we need to rename the original table master.dbo.[$ndo$srvproperty] to master.dbo.[$ndo$srvproperty_OLD] because we want a view with the name [$ndo$srvproperty] and SQL does not like 2 objects with the same name,even if they are of different types (a table and a view in this case).

We need another table (a NAV-maintained table is ideal for ease of use) with some fields in it. The view (that we will create) master.dbo.[$ndo$srvproperty] will point to this table. This table can be in a dedicated database (if you have multiple NAV databases [like the partners] it is best put in a dedicated database ; for a customer, this table can be in the customer database).

We need the following fields:
-"License Database": blank means this license is valid for all databases. "Some Value": this license is only valid for the database with this name.
-"License Version": 0:it doesn’t matter (because the license is bound to a certain database) ; 6 if it is a PRE NAV2013 license ; 7: if it is a NAV2013 license
-"License User Name": name of the user (user of the servicetier for NAV2013 or userlogin for all the others (RTC and classic))
-"The License": a BLOB with the license
-"License Comment": some comment on the record that can be useful
-"License Import DateTime": datetime when the license was last imported in current record. Will be filled up automatically.

After this, a view must be created with the name master.dbo.[$ndo$srvproperty]. This view will be used by NAV to read the license. And some permissions must be granted on it.

IMPORTANT: It will NOT be possible anymore to upload a license using the way we did that for years. Uploading a license will now happen using the form/page on the table (the reason the table is best a NAV table AND that it is licensed in the customers database).

I already wrote it before, and I will repeat it because it is important: To be able to use this system for a database, the NAV database properties must have flag "License per Database"=false. If this flag is true, the license in the database will be used. But with this system it is not necessary anymore.

Step-by-step

Before you start doing this, tried it first out on a test-environment. It is also best you know how to handle SQL Server and TSQL.
Users should stop working (not really mandatory but best to avoid they get errors when logging in or when restarting the service tier).
IMPORTANT: Import the license-table into a NAV2013 database: do NOT stop the Dynamics NAV service. It must use your current license from table master.dbo.[$ndo$srvproperty] to be able to run the page to import the first license. Remember you CANNOT use "Change License" and run the form you want!
Import the objects (NAV2013.fob or pre-NAV2013.fob) into your database (dedicated or customer DB). I only tested importing and running the objects in a NAV2009R2 and NAV2013 database. But I think it should also work in older databases (but you probably need to do that exporting the objects as text, removing some NAV2009R2 thingies and than import it in your older database). I can confirm that once installed, you can also use older version databases (I tried working with 4.0,4.0SP1,4.0SP2,4.0SP3,5.0,5.0SP1,2009,2009SP1,2009R2,2013 and they all took the correct license).
- It is possible to put the table/form/page on other ID's without problems. The important thing is that you do NOT change the name of the table (or if you do, you also need to change the view)
- In NAV2013, it is best to license the table and the page in the customers' database. This way you can run it using the customers license. If you don't you can't upload your partner license when needed.
Run the form/page and create at least one of these records (the screenshot is typical for a customer site with NAV2013):

The first record contains the license that everyone should use (the order of the records is NOT important!).
The second record contains the partner license. To be able to use it, you need to create an extra service for the database and using the "DOMAIN\PARTNERSERVICEUSER" user.

IMPORTANT: Because you should NEVER leave your partner license at a customer site, when finish your work, you should stop your partner-service and remove the license (there is an action/function to remove the license in the current record. You can leave the record there without a license and put a license in it when needed). When you need to do something, load the license in the record and start the partner-service.

The next screenshot (with NAV2009R2 classic client) is typical for a partner that has dozens of customer databases with all kinds of versions.

The first record contains the license for all versions of NAV. The second record contains the license for NAV2013.
But if you use NAV2013, which license will it take then? The view doing the SELECT contains a "TOP 1" and an "ORDER BY [License Database] DESC,[License User Name] DESC". So, the more less selection fields in your record, the less priority it has.
- How to load a license in the record? Because it needs to work in all kinds of circumstances, and I didn't want to change the code between them, I use this trick: in the record where you want to load a license, you need to give the full path of the license file in the "File Name" field. Exiting the field asks if you want to load the license or not.
- Show the license in the form/Page: There is an action/function "Show License" to do just that.
- Remove the license of the record but keeping the record: There is an action/function "Remove License" to do just that.
IMPORTANT FOR NAV2009R2 RTC!
The NAV2009R2 service tier does NOT use the user of the service to connect to the DB. Not even to read the license. The license is read the first time someone connects to the service tier. In that moment, the service tier will read the master.dbo.[$ndo$srvproperty] view using the user-login that connected to it. So, to make this system work, you can still create 2 service tiers. 1 for the customer and 1 to be used by the partner. The partner should always connect to that 2nd service tier using the same login (the login should be put in the "License User Name")!

Now the basic (and mandatory) setup is ready, we can start "hacking" NAV.
Start SQL Server Management Studio and connect to your server.
Rename the table master.dbo.[$ndo$srvproperty] to master.dbo.[$ndo$srvproperty_OLD]

Create the view master.dbo.[$ndo$srvproperty] with the TSQL statement under here (remember to change [YOUR DATABASE] to the database in which you imported the fob.)

USE master;
GO
 
IF object_id(N'dbo.[$ndo$srvproperty]', 'V') IS NOT NULL
DROP VIEW dbo.[$ndo$srvproperty];
GO
 
CREATE VIEW dbo.[$ndo$srvproperty] AS
  SELECT TOP 1
    srvp.[The License] AS [license]
   ,srvp.[License Database]
   ,srvp.[License Version]
   ,srvp.[License User Name]
   ,srvp.[License Comment]
  FROM master..sysprocesses AS sysp
    INNER JOIN [YOUR DATABASE].dbo.[License Master DB] srvp
  ON (1 = (CASE
                 WHEN (    (srvp.[License Version] = 7) 
                       AND (   (sysp.program_name LIKE '%NAV Service%')
                            OR (sysp.program_name LIKE '%NAV Development%'))
                      )
                 THEN 1
                 WHEN (    (srvp.[License Version] = 6) 
                       AND (   (sysp.program_name LIKE '%NAV Classic%')
                            OR (sysp.program_name LIKE '%NAV WEB%')
                            OR (sysp.program_name LIKE '%NAV RTC%'))
                      )
                 THEN 1
 WHEN (srvp.[License Version] = 0) 
 THEN 1
                 ELSE 0
               END
              )
         )
      AND ((srvp.[License Database] = DB_NAME(DBID)) OR 
          ((srvp.[License Database] = '')))
      AND ((srvp.[License User Name] = SYSTEM_USER COLLATE Latin1_General_100_CI_AS) 
          OR ((srvp.[License User Name] = '')))
  WHERE (spid = @@SPID)
  ORDER BY srvp.[License Database] DESC,srvp.[License User Name] DESC
 
GO

Now the view has been created, but everyone should be able to do a SELECT on it. This can be done with the following TSQL command:
```
use master
GRANT SELECT ON OBJECT::dbo.[$ndo$srvproperty] TO Public;
```
If the table is in the customer DB, everything should work fine, but if the table is in a dedicated database to which not everyone has access, you need to give everyone access to the database and the table. The command is like the previous one. This can be done with the following TSQL command:
```
USE [YOUR DATABASE]
CREATE USER [YOUR USER] FOR LOGIN [YOUR USER]  
-- If you have a group in which all users reside, 
-- you can just add this group, otherwise you have to 
-- run this line for each user.
GRANT SELECT ON OBJECT::dbo.[License Master DB] TO Public;
```
Everything is ready now, you can test it by starting a NAV2009R2 (or lower) classic client or the NAV2013 development environment. If everything is ok, it should start without problems. If you have the following error, you probably have not given the permissions to the NAV-table or the view.
Now you can (re)restart the servicetier(s) and start working.

Uninstall the system

Remove the view master.dbo.[$ndo$srvproperty]
Rename table master.dbo.[$ndo$srvproperty_OLD] back to master.dbo.[$ndo$srvproperty]

Run the TSQL query (probably not necessary):

use master
GRANT SELECT ON OBJECT::dbo.[$ndo$srvproperty] TO Public;

An extra

Sometimes it can be useful to know which license a certain user/service is using at the moment (or will be using if you change something in the "License Master DB"-table before restarting the services/clients). I only created a TSQL command for it to be run directly in SSMS.
The following fields are defined in the view

[Session User ID]: this is the spid of the session in SQL Server
[Session User Name]: the loginname of the session in SQL Server
[Session Database]: the database of the session in SQL Server
[Session Program Name]: the program name of the session in SQL Server
[License Comment],[License Database],[License Version],[License User Name],[License Import DateTime],[The License]: these are the fields of the record that is used / or will be used by the session of this record

And the SELECT(s):

USE [YOUR DATABASE]
 
-- show all licenses in the master-license-table
select * from dbo.[License Master DB]
 
-- show all NAV sessions and which license they would use
-- PS: if a "Session User ID" is shown multiple times, 
-- the view will take the license shown in the first record.
SELECT
 sysp.spid AS 'Session User ID'
 ,sysp.loginame AS 'Session User Name'
 ,DB_Name(sysp.dbid) AS 'Session Database'
 ,sysp.program_name AS 'Session Program Name'
 ,srvp.[License Comment] AS 'License Comment'
 ,srvp.[License Database] AS 'License Database'
 ,srvp.[License Version] AS 'License Version'
 ,srvp.[License User Name] AS 'License User Name'
 ,srvp.[License Import DateTime] AS 'License Import DateTime'
 ,srvp.[The License] AS 'The License'
  FROM master..sysprocesses AS sysp
    LEFT JOIN dbo.[License Master DB] srvp
  ON (1 = (CASE
                 WHEN (    (srvp.[License Version] = 7) 
                       AND (   (sysp.program_name LIKE '%NAV Service%')
                            OR (sysp.program_name LIKE '%NAV Development%'))
                      )
                 THEN 1
                 WHEN (    (srvp.[License Version] = 6) 
                       AND (   (sysp.program_name LIKE '%NAV Classic%')
                            OR (sysp.program_name LIKE '%NAV WEB%')
                            OR (sysp.program_name LIKE '%NAV RTC%'))
                      )
                 THEN 1
 WHEN (srvp.[License Version] = 0) 
 THEN 1
                 ELSE 0
               END
              )
         )
      AND ((srvp.[License Database] = DB_NAME(DBID)) OR ((srvp.[License Database] = '')))
      AND ((srvp.[License User Name] = sysp.loginame COLLATE Latin1_General_100_CI_AS) OR 
          ((srvp.[License User Name] = '')))
WHERE (sysp.program_name like '%NAV%')
   OR (sysp.program_name like '%Navision%')
ORDER BY DB_Name(sysp.dbid),sysp.spid, -- group by "dbname used by session" and spid 
                                       -- to easy show the info
         srvp.[License Database] DESC,srvp.[License User Name] DESC -- group by to 
                                       -- take the highest priority license

Download code

Screenshots

View screenshot

Favourite this entry

person people favourited this entry, including:

Be the first to favourite this entry
Mail to a friend
Report a broken link
Go to forum discussion