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	[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'
		,@environment	varchar(20)	= NULL
		,@server	varchar(200)	= 'localhost'
		,@database	varchar(200)	= 'FoobarNovusQA'

	INSERT	[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	[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	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.

Return to Novus