JIWA 6.5.13 to 7.2.1  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 4:56 pm

Separate instance....still okay to run the command:

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Mike.Sheen » Wed Mar 18, 2020 4:59 pm

Luehman wrote:Separate instance....still okay to run the command:

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO



Yep, but beforehand you might want to drop the users and re-create them.

This script will do all you need for those credentials you provided:

Code: Select all
DROP USER JiwaLogin
DROP USER JiwaReports
DROP USER JiwaUser
GO

CREATE USER JiwaLogin FOR LOGIN JiwaLogin
CREATE USER JiwaReports FOR LOGIN JiwaReports
CREATE USER JiwaUser FOR LOGIN JiwaUser
GO

UPDATE HR_Staff
   SET SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
   SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
   ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
   ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
   SQLLoginLastSavedDateTime = '2014-05-28 11:20:48.000',
   ReportsLoginLastSavedDateTime = '2014-05-28 11:20:48.000'
WHERE UserName = 'Admin'
GO

-- Set Jiwa Password
UPDATE HR_Staff SET Password = '60A889064BAD474508723B9200184D09',
       PasswordLastChangedDateTime = '2014-05-28 11:20:48.000',
       LastSavedDateTime = '2014-05-28 11:20:48.000',
       SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
       SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
       ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
       ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C'
WHERE UserName = 'Admin'
GO

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1640
Joined: Tue Feb 12, 2008 11:12 am
Location: North Sydney
Topics Solved: 522

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:02 pm

I run the Query, run out with the following output (didn't paste the whole lot) however still getting the Login Error:

Granting permissions...
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_History] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Main] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Provider] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_ProviderFilters] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_ProviderHeaders] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Schedules] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_TaskFilters] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_TaskHistory] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Tasks] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProduction] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionCustomFields] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionCustomValues] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionDocuments] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionNotes] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionWorkOrder] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_CustomSetting] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_CustomSettingValues] TO JiwaUser
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:04 pm

Ran the latest script you posted and now get this when running it:

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
Msg 15138, Level 16, State 1, Line 3
The database principal owns a schema in the database, and cannot be dropped.
Msg 15023, Level 16, State 1, Line 2
User, group, or role 'JiwaLogin' already exists in the current database.
Msg 15023, Level 16, State 1, Line 3
User, group, or role 'JiwaReports' already exists in the current database.
Msg 15023, Level 16, State 1, Line 4
User, group, or role 'JiwaUser' already exists in the current database.

Also, still cannot log in...
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Scott.Pearce » Wed Mar 18, 2020 5:06 pm

Use Jiwa 7.2.1 to create a demo database on the same server/instance as the upgraded database. Call this new database "JiwaDemo".

Then run this command on the upgraded database:

Code: Select all
update hr_staff set SQLLogin = (SELECT TOP 1 SQLLogin FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set SQLPassword  = (SELECT TOP 1 SQLPassword FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportSQLLogin  = (SELECT TOP 1 ReportSQLLogin FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportSQLPassword = (SELECT TOP 1 ReportSQLPassword FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set SQLLoginLastSavedDateTime   = (SELECT TOP 1 SQLLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportsLoginLastSavedDateTime   = (SELECT TOP 1 ReportsLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff)


A pain, I know, but we are obliged to take security pretty seriously, especially when company data is involved.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Jiwa Senpai
Jiwa Senpai
 
Posts: 565
Joined: Tue Feb 12, 2008 11:27 am
Location: North Sydney, New South Wales
Topics Solved: 169

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:08 pm

Thanks, understood re security....

Unfortunately still no dice....still getting the same error message when trying to log in....
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:09 pm

Sorry, just re-read your post...okay...I''ll create a demo dB etc.
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:11 pm

What should I put in these fields?

demo.png
demo.png (23.35 KiB) Viewed 29 times


I assume my SA for the SQL Login field however I do not know the Report SQL one....
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby Luehman » Wed Mar 18, 2020 5:37 pm

I ended up using (for JiwaDemo dB creation):

JiwaLogin / JiwaApplicationLogin123
JiwaReports / JiwaFinancials123

I can log in to the JiwaDemo dB no issues (ran the previous SQL Query against the upgraded dB)....however I still cannot login to in the upgraded dB....see error below:

Login_error.png
Luehman
Occasional Contributor
Occasional Contributor
 
Posts: 30
Joined: Tue Mar 17, 2020 9:48 am

Re: JIWA 6.5.13 to 7.2.1

Postby SBarnes » Wed Mar 18, 2020 9:31 pm

Was the upgrade run on a different server than the one the database now resides on?

If so try

Code: Select all
EXEC sp_change_users_login 'Auto_Fix', 'JiwaUser'
EXEC sp_change_users_login 'Auto_Fix', 'JiwaLogin'
EXEC sp_change_users_login 'Auto_Fix', 'JiwaReports'

Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 816
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 71

PreviousNext

Return to Technical / Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron