JIWA 6.5.13 to 7.2.1

[Mike.Sheen]

What should I put in these fields?

demo.png

I assume my SA for the SQL Login field however I do not know the Report SQL one....

Ummm... no, not the sa user.

That should have been be JiwaUser and JiwaFinancials123.

Those messages you got:

Code: Select all

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Is what is I think is preventing you from progressing further. You've now got some SQL Users (JiwaUser, JiwaLogin, JiwaReports) with bad SID's AKA orphaned users.

The easiest way to move forward from where you are is by using SQL Server Management Studio (SSMS) to drop the users, and then running the script I last provided (It may present errors that it can't drop users that don't exist, but the rest will run ok - those errors can be ignored and would be expected after using SSMS to drop them).

Also, when you created the instance of SQL Server, did you choose mixed mode authentication? Because that's what we require. You can change that also via SSMS - but do that before anything else if you don't currently have mixed mode authentication.

Again - you can always engage us to take your V6 database and we can hand you back an upgraded one for V7. There is a limit as to how much free support we can provide in these forums. These forums are the Technical / Programming forums aimed at assisting, educating and empowering developers, and not really aimed at assisting customers to self-upgrade.

[Scott.Pearce]

1. Delete JiwaDemo database

2. Delete SQL Logins JiwaUser, JIwaReports, and JiwaLogin using SQL Management Studio. Delete them from the "Security\Logins" node at the SERVER level, and from the "Security\Users" node at each DATABASE level.

3. Create the demo database again, call it JiwaDemo. Use these values for SQL Logins and passwords:

JiwaUser / JIwaFinancials123
JiwaReports / JIwaFinancials123

Make sure you can log in to the demo database OK after it is created

4. Run the following script against your upgraded database:

Drop and create Jiwa users and grant all.sql

(2.65 KiB) Downloaded 68 times

Now try to log in to the upgraded database again.

[Luehman]

Apologies guys, we eventually got there in the end... was 8pm when we got it working.

Ran a command against each user as none of them had the relevant login against them?

USE DatabaseName ALTER USER Username WITH LOGIN = LoginName

Did this for each of the JiwaLogin, JiwaUser & JiwaReports and away we went.

Finally live with JIWA7 after several hiccups that we did not face only months earlier when we updated my test version.

Thank you for assisting our IT guy yesterday as when we encountered the errors I suggested the forum as you had helped with the plugins etc the day before.

Although we are live there are a few small issues i will post topics for to see if there is work arounds or plugins to solve them.

[Scott.Pearce]

i will post topics for to see if there is work arounds or plugins to solve them.

No worries.

[Mike.Sheen]

Finally live with JIWA7 after several hiccups that we did not face only months earlier when we updated my test version.

I find that hard to believe... the nature of the problems you encountered are not things which would have changed. For instance, the problem with your triggers - that had to have been a problem with your previous attempts to upgrade?

[Luehman]

Yeah im not sure, we ended up doing it from scratch again at about 6pm but i think he used an old script that fixed a few of the things that he originally asked you about like the triggers then he ran one of your scripts about passwords and our final issue was just the logins and we got around that with a Google search.

[Mike.Sheen]

It's great you managed to work through the problems and got the database upgraded.

Your very next step is to set your SQL Logins and passwords to be something other than our default values.

I cannot stress this enough - you should not be using the JiwaUser or JiwaReports SQL logins or SQL Users, you should create your own with their own unique passwords.

The JiwaLogin SQL Login however, should remain and should still have the default password. That's only used to connect to the database and read the encrypted SQL login and password from the Jiwa staff record (the HR_Staff table), which the Jiwa application then uses to establish a connection with.

The steps to make your own credentials would be as follows:

1. Login to Jiwa
2. Open the Staff Maintenance form
3. Now whilst Jiwa is still open, create your SQL Logins using SSMS (one for the Jiwa application - that's what we default as JiwaUser - choose something else, one for reports - we default as JiwaReports - choose something else) with privately known passwords - or you can create them with a script

Code: Select all

CREATE LOGIN Your_Replacement_for_JiwaUser WITH PASSWORD = 'MyPassword123!!!'
CREATE LOGIN Your_Replacement_for_JiwaReports WITH PASSWORD = 'AnotherPassword123!!!'

4. Create SQL Users for those SQL Logins in your database created in step 3. You can use SSMS or a script (make sure you run this within your database):

Code: Select all

CREATE USER Your_Replacement_for_JiwaUser FOR LOGIN Your_Replacement_for_JiwaUser
CREATE USER Your_Replacement_for_JiwaReports FOR LOGIN Your_Replacement_for_JiwaReports

5. Run the following scripts (as sa or equivalent):

Code: Select all

usp_Jiwa_Grant_Application_Permissions 'Your_Replacement_for_JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'Your_Replacement_for_JiwaReports'
GO

6. Back in Staff Maintenance in Jiwa, locate the staff member you are currently logged into Jiwa as - then on the Database Login tab, set the credentials to that you set in step 3. and then save
7. Keep that instance of Jiwa open, open another Jiwa instance and verify you can login - if you cannot, then you got the login or password wrong in step 6 - go back to the Jiwa instance you have open in step 6. and get it right.
8. Now that you have verified your own private SQL login and password are valid, for each staff member in the staff maintenance form, set their credentials on the Database Login tab
9. DROP the SQL Users JiwaUser and JiwaReports using SSMS from the database - DON'T drop JiwaLogin!
10. DROP the SQL Logins JiwaUser and JiwaReports using SSMS - DON'T drop JiwaLogin!

And then you'll have your own SQL credentials nobody else knows.

[SBarnes]

Hi Guys

Had a similar problem in getting logins to work on a client database where I needed to reset the the underlying user passwords ran the first script below, thinking this would fix my problem but the got an error about login failed for gibberish user name. Then tried the second script which is from the script Scott posted on this topic and that fixed my issue. I had unorphaned the users as well.

Has something changed in the encryption sequence since the first script was put together as that would explain why gibberish was being given for the user name?

Code: Select all

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'
GO


Code: Select all

update hr_staff set SQLLogin = (SELECT TOP 1 SQLLogin FROM JiwaDemo7.dbo.HR_Staff)
update hr_staff set SQLPassword  = (SELECT TOP 1 SQLPassword FROM JiwaDemo7.dbo.HR_Staff)
update hr_staff set ReportSQLLogin  = (SELECT TOP 1 ReportSQLLogin FROM JiwaDemo7.dbo.HR_Staff)
update hr_staff set ReportSQLPassword = (SELECT TOP 1 ReportSQLPassword FROM JiwaDemo7.dbo.HR_Staff)
update hr_staff set SQLLoginLastSavedDateTime   = (SELECT TOP 1 SQLLoginLastSavedDateTime FROM JiwaDemo7.dbo.HR_Staff)
update hr_staff set ReportsLoginLastSavedDateTime   = (SELECT TOP 1 ReportsLoginLastSavedDateTime FROM JiwaDemo7.dbo.HR_Staff)
GO


[Mike.Sheen]

Has something changed in the encryption sequence since the first script was put together as that would explain why gibberish was being given for the user name?

Code: Select all

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'
GO


Are you saying the above doesn't work for you? Because it works for me. I've just copy-pasted that script and run it against a 7.2.1 database and I could login fine.

To answer the question - no, we've not changed anything with regards to the encryption.

[SBarnes]

Correct, it came back with an error about login for gibberish as name failed, then tried Scott's script where JiwaDemo7 is a database on the same SQL server that worked and the problem was fixed.

I can send you the hightail links for the database on email if you want to have a look, its to do with the client who's name starts with "F" who you have done some work on recently??