License Server Features and Management

<< Click to Display Table of Contents >>

EQuIS 7  >>  Enterprise > Installation and Configuration > EQuIS Licenses >

License Server Features and Management

License Server Overview

 

EQuIS Product Licenses are Managed within the EQuIS Database

 

Network Licenses are saved in the ST_LICENSE table. This table stores the product code, encrypted key string, product name, and supports links to External License Database. EarthSoft recommends that the user save keys to the ST_LICENSE table using the registration form of the application (register.exe).

 

The Network ID string is longer than the Computer ID, and the Network ID is a number generated from the server name and database name as reported by the database server. As such, keys are tied to a particular database. If that database is moved to another server, then the Network ID will change, and new registration keys are required. Please plan accordingly when moving or renaming database servers.

 

When a user checks out a network license, they are said to acquire a license lease. Leases are issued when the number of held leases is less than the maximum number of concurrent use licenses available. Those values are displayed on the registration form. Each lease is issued for a 15 minute period. EQuIS Professional products will automatically renew their lease before that expiration period is reached. If for any reason the lease is not renewed, the license becomes available for other users.

 

When EQuIS renews its product leases, it verifies their authenticity. If the authentication fails, EQuIS releases the lease and attempts to acquire a new lease. If no licenses are available at that time, the Evaluation form is displayed with the message "Failed to acquire license". If EQuIS cannot renew the lease because of trouble connecting to the database server, a warning message is displayed. The message informs the user that EQuIS will automatically exit if it cannot renew the lease within the subsequent 15 minutes.

 

License Server Linking

 

Organizations managing several EQuIS databases may wish to designate one as the Network License database. Optionally, the Database Administrator may setup a separate Network License database to manage concurrent licenses for an organization. See the Creating an External License Database section below for additional information. If License Server linking is enabled with an EQuIS database, no other keys are used within that database. EQuIS will follow up to five (5) links before it stops searching for a license server. EarthSoft recommends minimizing the number of links to a license server.

 

To setup License Server Linking, the Systems Administrator will need to:

1.Create a license database OR choose an EQuIS Database where licenses currently reside.

2.Create a link from an unlicensed database, to point to the license database of Step 1.

3.Register the application (if the license database OR licenses do not already exist.)

 

This article covers Step 1 and Step 2 of the process, with additional information regarding license servers.

 

Creating an External License Database

 

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

 

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. End users should review the release notes on each new release to determine if standalone license databases requires a Schema update.

 

A separate EQuIS database can also be used as a license database. Caution should be used 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 statements will create the standalone license database and table structure required by EQuIS. After the database is created, an SQL login should be created with read/write access 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

 

 

Linking an EQuIS Database to the License Database

 

For more than one EQuIS database to share network licenses, a connection string must be created that points to the database containing the licenses.

 

To create a link from the unlicensed EQuIS database to the license database:

1.Create a record in the unlicensed database's ST_LICENSE table with the following information:

APP_ID=1023

KEY_TYPE=4

2.In the LICENSE_SERVER field, enter a plain text connection string for the server and database acting as a license server. Upon successful login, EQuIS will encrypt the connection string and save it back to ST_LICENSE.

3.If Network Licensing is available on the target database, the Server name, Database name, and a new Network ID will be displayed on the registration form.

4.The ST_LICENSE.LICENSE_TITLE can be set to provide information about the link. For example, "Link to database 'Training' on server 'Primary', SQL Login as 'Student'".

 

Note: If license server linking is used, the database should only contain the one record defined above, while the actual licenses reside on the license server.

 

Connection String Examples (for reference)

 

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;

 

 

Information on Changing License Servers

 

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:

1.Setting up the new server.

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

 

 

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 following fields:

APP_ID – The webkey identifier for the product.

WORKSTATION – 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 – 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 – The date and time when the record was created. The time is given in Coordinated Universal Time (UTC).

LICENSE_EXPIRES

For Professional licenses, this field is populated every 5 minutes with an expiration date that is 15 minutes in the future. The LICENSE_EXPIRES is set to the current date and time upon successful termination of a session. Thus, 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.

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

CHECKSUM – Determines if the license-use record was modified while the license was checked out.