New Customer Checklist

Checklist for Logging into New Customer Novus/Shell Databases

1. Check [Identity].dbo.domain has a domain record. Run the following query:

    SELECT    *
    FROM      [Identity].dbo.domain d
    WHERE     d.name = 'foobar.com';

If it doesn’t exist, run the following INSERT:

    INSERT    INTO [Identity].dbo.domain
              (name
              ,release_number)
    VALUES    ('foobar.com', -- name
              ,'2022.5.2.0'); -- release_number

2. Check [Identity].dbo.domain_database has two records with IDs: SHELL and NOVUS:

    SELECT    db.*
    FROM     [Identity].dbo.domain d
    INNER JOIN [Identity].dbo.domain_database db ON d.domain_key = db.domain_key
    WHERE    d.name = 'foobar.com';

If they do not exist, then they have to be inserted. We need to generate the encrypted user ID and password for the SQL login for the customer’s SHELL and NOVUS databases. Open the Visual Studio project, in the Novus.IdentityServer project, in the Startup class, temporarily modify the Configure() method to the below, and set the SQL login user ID or password at textToEncrypt to get the encrypted string of the user ID or password:

    public void Configure(IApplicationBuilder app)
    {
    // ...

    /* run any StartupOneOffMethods temporarily */

    var oneOffMethods = new StartupOneOffMethods(app);
    Debug.WriteLine(oneOffMethods.AesEncrypt("textToEncrypt").encryptedString);
    }

For example, if the text is “Password1”, then the output will be “VxXhYh/bH2IHo/ZGZEHl5w==”.

Execute the below UPDATE two times: (1) for the Shell database by setting @id = 'SHELL' and @database to the Shell database e.g. 'FoobarShellQA'; and (2) for the Novus database by setting @id = 'NOVUS' and @database to the Novus database e.g. 'FoobarNovusQA'. Leave the @environment as NULL for production or set it to a specific environment to connect to a different database e.g. 'QA'. Set @server to the hostname or IP address of SQL Server Set @userId and @password to the encrypted SQL login user credentials generated previously.

    DECLARE @domain varchar(253) = 'foobar.com'
            ,@id          varchar(20)  = ''
            ,@environment varchar(20)  = NULL
            ,@server      varchar(200) = 'localhost'
            ,@database    varchar(200) = ''
            ,@userId      varchar(200) = 'zpfimVI/pFs10wUYAqU3ig=='
            ,@password    varchar(200) = 'eWRsvEZpFxJt6KmvMk0f/A=='
            ,@cssFiles    varchar(50)  = NULL; -- values: NULL, 'qa', 'uat'

    INSERT    [Identity].dbo.domain_database
              (domain_key
              ,id
              ,environment,server
              ,[database]
              ,trusted_connection
              ,user_id
              ,password
              ,css_files)
    VALUES    ((SELECT    domain_key
              FROM        [Identity].dbo.domain
              WHERE       name = @domain)  -- domain_key
              ,@id -- id
              ,@environment -- environment
              ,@server -- server
              ,@database, -- [database]
              ,(CASE
                  WHEN @userId IS NOT NULL AND @userId <> '' THEN 0 
                  ELSE 1
              END) -- trusted_connection
              ,@userId                    -- user_id
              ,@password                  -- password
              ,@cssFiles)                 -- css_files 

2a) Check if [Identity].dbo.domain_ssrs record exists by running the following query: SELECT ssrs.* FROM [Identity].dbo.domain_ssrs ssrs

 INNER JOIN [Identity].dbo.domain d ON ssrs.domain_key = d.domain_key

WHERE d.name = 'foobar.com'; If it doesn’t exist, run the following INSERT: DECLARE @domain varchar(253) = 'foobar.com'; DECLARE @environment varchar(20) = NULL; DECLARE @server varchar(200) = 'localhost'; DECLARE @database varchar(200) = 'FoobarNovusQA';

INSERT INTO [Identity].dbo.domain_ssrs (domain_key,id,environment,server,[database]) VALUES (

 (SELECT domain_key FROM [Identity].dbo.domain WHERE name = @domain), -- domain_key,
 'NOVUS', -- id,
 @environment, -- environment,
 @server, -- server,
 @database -- [database]

); 3) Check if Identity user record exists by running the following query: SELECT * FROM [Identity].dbo.[user] u WHERE u.user_id = 'admin@foobar.com'; If it doesn’t exist, run the following INSERT: INSERT INTO [Identity].dbo.[user] (user_id,email,is_active,normalized_username,normalized_email) VALUES (

 'admin@foobar.com', -- user_id,
 'visfuture@foobar.com', -- email,
 1, -- is_active,
 UPPER('admin@foobar.com'), -- normalized_username,
 UPPER('visfuture@foobar.com') -- normalized_email

); 4) If you cannot log in, nullify the password by running the following UPDATE: UPDATE [Identity].dbo.[user] SET password_hash = NULL WHERE user_id = 'admin@foobar.com'; Then log in with password admin. 5) Check if SHELL user record exists by running the following query: SELECT u.* FROM Shell.dbo.sec_user u WHERE u.user_id = 'admin'; If it doesn’t exist, run the following INSERT: INSERT INTO Shell.dbo.sec_user (user_id,first_name,last_name,email,default_company,default_branch,default_warehouse,is_active,is_administrator,created_on,created_by,version) VALUES (

   'admin', -- user_id,
   'Admin', -- first_name,
   , -- last_name,
   'visfuture@foobar.com', -- email,
   '1', -- default_company,
   '1', -- default_branch,
   '1', -- default_warehouse,
   1, -- is_active,
   1, -- is_administrator,
   GETUTCDATE(), -- created_on,
   SYSTEM_USER, -- created_by,
   0 -- version

); 6) Should be good now. If you can log in but the list of applications is not showing up, if could due to the following: • If a database in domain_database was misspelled and then corrected, run the following cURL (for UAT) command to reset the tenant cache (see section for more details): curl -v -X POST -H 'Content-Length:0' -H 'IdentityServerApiSecret:<cF9[3kYxXSGwr2&' https://novusuatapi.kiwanatechnologies.com/api/tenant/clear • The novususer SQL login doesn’t have access permission to the “Shell” or “Novus” database. You can quickly check if this is the case by opening SSMS, expand the database, and see if you can query a database table inside.