License Server Management

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Installation and Administration > Licensing >

License Server Management

Keys Bound to Database and Server

 

Network Keys are bound to the Network ID for the database storing the keys. If the database server name (or database name) changes, the Network ID will also change. Any existing keys bound to the old Network ID are rendered invalid. Organizations planning to move or rename their database server should plan accordingly.

 

EarthSoft recommends the following:

 

1.Set up the new server.

2.Request that new keys be issued prior to the actual move. This will prevent any loss of productivity during the transition.

 

Manage License Keys

 

All Network Keys should be managed through the registration form in EQuIS Professional. Attempts to manually modify the contents of ST_LICENSE may result in invalid or corrupt keys. A valid key consists of an APP_ID, KEY_TYPE, and KEY_STRING. The field KEY_TITLE is filled in by the licensed product's proper name upon first use. If the field is null, then this key has never been used and the Software Registration window will display "Unused License" for this key. There is no relational constraint between the ST_LICENSE and ST_LICENSE_USE tables. This allows keys to be deleted without losing usage history.

 

To manage network licenses, paste the new key strings into the text area of the Network Licenses tab of the Registration Form and click Save Keys. The Registration Form will validate the keys and apply any necessary changes to ST_LICENSE. Keys are processed in order of appearance in the text area. There should be only one key per line.

 

License Use History

 

EQuIS tracks usage history for all products using Network Licenses. This information is saved in the ST_LICENSE_USE table and includes the fields listed below.

 

APP_ID is the webkey identifier for the product.

WORKSTATION is the workstation used to consume the license. It is a concatenation of the computer name environment variable, the CPU ID, the volume serial number of the first hard drive, and the network MAC address.

USER_NAME is the database login name used when acquiring the license, and reflects the connection type used when first opening EQuIS. For Windows Authentication, the user name is the Windows Login. For SQL Authentication, the name is the Database Management System login name. For application level security, the name is the value of ST_USER.USERNAME from the database the user connected to when starting EQuIS. In the case of a master license server, it could be the credentials used to connect to the license database as stored in another EQuIS Database, ST_LICENSE table.

LICENSE_CHECKED_OUT is the date and time that the record was created. The time is given in Coordinated Universal Time (UTC).

LICENSE_EXPIRES

oFor Professional licenses, this field is populated after five minutes on database query with an expiration date that is approximately 15 minutes in the future. The LICENSE_EXPIRES is set to the current date and time upon successful termination of a session. Values set in the future indicate current leases, while expiration dates in the past represent historical usage data. Since expiration dates are automatically set to 15 minutes in the future, session duration is accurate to within 15 minutes, even in the event of power failure or loss of network.

oFor Enterprise licenses, this field populates with a date expiring 30 years in the future (or until user is disabled or deleted through User Manager Widget).

CHECKSUM is used to determine if the license use record was modified while the license was checked out.

 

License Server Connection String

 

In order for more than one EQuIS Database to share network licenses, create a connection string that points to the database containing the licenses. Read here for more instructions on applying network licenses.

 

A valid database connection string must be created to enter into the ST_LICENSE.LICENSE_SERVER field in the secondary database(s). The value for APP_ID must be 1023 and the value for KEY_TYPE must be four. There should be no other records in ST_LICENSE. This connection string must point to the original database (where the network license keys are stored). The connection string must be valid for any user that will be using the network licenses. For complete documentation on creating a connection string, read here and here.

 

Example connection strings include the following, and are case sensitive.

 

SQL Server with Windows Authentication
Data Source=dbserver;Integrated Security=SSPI;Database=licensedb;

SQL Server with SQL Authentication
Data Source=dbserver;User ID=someusername;Password=somepassword;Database=licensedb;

 

The connection string stored in the ST_LICENSE.LICENSE_SERVER field will automatically be encrypted the first time it is used for security purposes. If EQuIS Professional is forced to use a specific License Server, the connection string placed in .\Program Files\EarthSoft\EQuIS\EQuIS.exe.config will also be encrypted. An example of an encrypted connection string follows.

 

<connectionStrings>

   <add name="databaseConnection" connectionString="RWN3ZSVZdn17bXBJcm+BhHlNaId6iDdhXVeAfY+Sh1txg5aXnJWZjGaPjJ6hlmp0kqaUlpapnHWMqq2lq6Wlqaaup38="

     providerName="EarthSoft.Common.Data.SqlConnection" />

 </connectionStrings>

 

This encrypted value represents the current license server connection information. It is used by some EQuIS Professional Interfaces for licensing. Stand-alone EDP uses this to implement network licensing in the absence of an EQuIS Database connection.

 

Create Standalone License Database

 

By default, EQuIS will use the EQuIS Database originally logged in to as its licensing server. With the license server linking feature, it is possible to designate one or more databases to manage all the keys in an organization. The database managing EarthSoft product keys does not need to be an EQuIS Database. An external license database only needs to contain the ST_LICENSE, ST_LICENSE_USE, ST_VERSION and ST_USER tables, and must be accessible by all workstations that will consume network licenses.

 

A standalone license database contains only the tables required to manage licenses. This is created and updated through SQL Server Management Studio and is not available connecting through EQuIS Professional. A standalone license database can be used with all EQuIS Products, including Standalone EDP. Users should review the release notes on each new release to determine if standalone license databases require a schema update.

 

A separate EQuIS Database can also be used as a license database. Caution should be exercised when using an EQuIS Database as a license database as these databases can be connected to through Professional, and data could be loaded into this license database. The following SQL statement will create the standalone license database and table structure required by EQuIS.

 

The database users used in the license connection strings require the permissions indicated below for each table.

 

/* Copyright (c) EarthSoft, Inc.

 

 External License Database

 

 Manage EQuIS network licenses for multiple EQuIS Databases

 External license databases are not compatable with SPLA Licensing.

 Please refer to EarthSoft documentation on License Server Management for details.

 

 Usage:

 

   (a) to create a new license database:

       1. Create a new, empty database

       2. Execute this script within that database with db_owner permissions

 

   (b) to update an existing license database:

       1. Execute this script within the existing license database with db_owner permissions

 

*/

 

 

IF SCHEMA_ID('equis') IS NULL

 EXEC sp_executesql N'CREATE SCHEMA equis AUTHORIZATION dbo'

go

 

 

-- NOTE: This table is obsolete in EQuIS 7, but still necessary for this script to run properly

IF OBJECT_ID('dbo.st_id') IS NULL

 CREATE TABLE dbo.st_id

(   

  code VARCHAR(20) NOT NULL DEFAULT ('last_id')

 ,[value] INT NOT NULL

 ,CONSTRAINT PK__st_id PRIMARY KEY ( code )

 ,CONSTRAINT CK__st_id__only_one_row CHECK ( code = 'last_id' )

)

GO

-- create the record to store the last_id

IF NOT EXISTS (SELECT * FROM dbo.st_id WHERE code = 'last_id')

 INSERT INTO dbo.st_id (code, value) VALUES ('last_id', 0)

GO

 

-- PERMISSIONS:

--    Adding licenses: SELECT

--    Using licenses: SELECT

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[st_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

exec sp_executesql N'

create table dbo.st_user (

       user_id              int NOT NULL,

       client_code          char(4) NULL,

       user_name            varchar(128) NOT NULL,

       password             varchar(64) NOT NULL,

       email_address        varchar(100) NOT NULL,

       ftp_location         varchar(256) NULL,

       notice_delivery_type varchar(12) NULL,

       status_flag          char(1) CHECK (status_flag IN (''A'',''B'',''C'',''D'',''R'')) DEFAULT ''A'',

       PRIMARY KEY (user_id))';

go

 

 

-- PERMISSIONS:

--    Adding licenses: SELECT/INSERT/UPDATE/DELETE

--    Using licenses: SELECT

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[st_license]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

exec sp_executesql N'      

CREATE TABLE dbo.st_license(

         app_id int NOT NULL,

         key_type int NOT NULL,

         key_string varchar(25) NULL,

         license_server varchar(255) NULL,

         license_title varchar(255) NULL,

         PRIMARY KEY (app_id, key_type))';

go

 

 

-- PERMISSIONS:

--    Adding licenses: SELECT/INSERT/UPDATE/DELETE

--    Using licenses: SELECT/INSERT/UPDATE/DELETE

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[st_license_use]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

exec sp_executesql N'      

CREATE TABLE dbo.st_license_use(

         app_id int NOT NULL,

         workstation varchar(128) NOT NULL,

         user_name varchar(128) NOT NULL,

         license_checked_out datetime NOT NULL,

         license_expires datetime NOT NULL,

         checksum varchar(128) NULL,

         PRIMARY KEY (app_id, workstation, user_name, license_checked_out))';

go

 

 

if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='ST_LICENSE' and COLUMN_NAME='LICENSE_SERVER' )

 alter table dbo.ST_LICENSE alter column LICENSE_SERVER varchar(1024);

go

 

 

if not exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='st_user' and COLUMN_NAME='enterprise_code')    

 alter table dbo.st_user add enterprise_code varchar(40) null;

go

 

 

-- PERMISSIONS:

--    Adding licenses: SELECT/INSERT/UPDATE/DELETE

--    Using licenses: SELECT/INSERT/UPDATE/DELETE

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[st_licensed_database]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

         exec sp_executesql N'

create table st_licensed_database(

         licensed_db_id int not null,               -- EUID of the licensed database

         server_name varchar(128) null,             -- server name/IP

         database_name varchar(128) null,           -- database name

         connection_string varchar(1024),           -- connection string used to connect (encrypted)

         PRIMARY KEY (licensed_db_id))';

go

                 

 

if not exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='st_license_use' and COLUMN_NAME='licensed_db_id')

begin

 alter table dbo.st_license_use add licensed_db_id int null;

 alter table dbo.st_license_use add constraint FK_licensed_db_id foreign key (licensed_db_id) references st_licensed_database(licensed_db_id);

end;

go

 

/**** EQuIS 7 [31-Jul-2018] ****/

-- get the current EUID value

declare @val int = (select max(value) + 1 from st_id);

declare @sql nvarchar(max);

set @sql = N'

-- create sequence for data (i.e. "real" EUIDs)

create sequence dbo.sq_euid 

  as int

  start with ' + cast(@val as nvarchar(30)) + 

  increment by 1

  cache 5000;

 

-- create sequence for system workflow IDs

create sequence dbo.sq_sysid 

  as int

  start with ' + cast(@val as nvarchar(30)) + '

  increment by 1

  cache 5000';

exec sp_executesql @sql;

 

-- FB.147215: modify stored procedure to use sq_euid sequence

if object_id('equis.get_ids','P') is not null drop procedure equis.get_ids;

go 

create procedure equis.get_ids ( @count int, @next int output ) as 

begin

 

 declare @id int;

  declare @v sql_variant;                

 

  if @count = 1

  begin

    set @next = next value for dbo.sq_euid;

    return;

  end;

 

  if @count > 1

  begin

    exec sp_sequence_get_range @sequence_name = N'dbo.sq_euid'

                              ,@range_size = @count

                              ,@range_first_value = @v output;  

    set @next = cast(@v as int);

    return;

  end;

                                                          

end

go

 

grant execute on equis.get_ids to public

go

 

if object_id('dbo.trg_st_id_no_delete') is not null

  drop trigger dbo.trg_st_id_no_delete;

go

create trigger dbo.trg_st_id_readonly on [dbo].[st_id]

  instead of insert, update, delete

  as

  begin 

   raiserror ('Cannot INSERT/UPDATE/DELETE ST_ID - this table is no longer in use.', 16, 10);

   rollback;

  end;

go

 

Install EQuIS Professional - Connect to License Database

 

To install EQuIS Professional and connect to the License Database, complete the following steps.

 

1.Install EQuIS Professional on the local PC.

2.Create a connection to the network server and using the options in Professional, create an EQuIS Database on the server.

3.In the EQuIS Database, add the connection string in ST_LICENSE, as described above.

4.Close EQuIS.

5.Relaunch EQuIS and connect to the EQuIS Database.

6.Register when prompted as no licenses currently exist in the database.

7.Follow the registration screens and click on the Network tab to obtain the serverID. The ServerID that will be shown is related to your hardware. Note that the combination of that ServerID with the registration keys will successfully place licenses in the license database.

 

Troubleshooting

 

Error acquiring license for appID=101 from server (local)\SQLEXPRESS. Exception Message: Warning: Fatal error 9001 occurred at [Date] [time]. Note the error and time, and contact your system administrator.

 

This is followed by a Registration pop-up window showing that all licenses are available before the program terminates. The error may be caused by a corruption in the ldf file or, in some cases, by a virus checker locking the database. To resolve the issue, close all connections to the database, take the database offline, and then restart it.

 

Error acquiring license for appID=40 from server apc-sql. Exception Message: Object referenced not set to an instance of an object.

 

This error is typically a result of old licenses left in ST_LICENSE or non-applicable licenses for a different server ID. To resolve this error, delete all rows in the license database ST_LICENSE, except rows pertaining to Custom EDP or EDGE formats, restart Professional and reapply all current, valid keys.