Jiwa on Azure

[Riyaz]

Hi There

We have installed JIWA on azure and database via a bacpac File.

Now we are trying to map Jiwa users with proper mapping and permissions, but since azure database limits us using management tools, we used sql scripts like below but it comes with you don’t have permission error. Pls let me know if there are any special instructions to setup Jiwa with azure database. Currently running a VM with Windows server 2019.

ALTER ROLE dbmanager ADD MEMBER [JiwaLogin];
ALTER ROLE loginmanager ADD MEMBER [JiwaLogin];

[Mike.Sheen]

You shouldn't be adding such roles to the JiwaLogin user.

JiwaLogin is a special login which should just have SELECT permission on HR_Staff. We only use that login to read the encrypted SQL credentials from HR_Staff, then we discard that connection and create a new one after decrypting the credentials.

If you've restored a database from BACPAC, just run Jiwa and upgrade it (even though it probably does not need upgrading). We'll set permissions for you. As part of that process you'll be prompted to set the SQL credentials for each user - make sure you enter in there the SQL credentials you want each Jiwa staff member to be using.

[Riyaz]

Thanks Mike

[Riyaz]

Hi Mike

Am trying another database on Azure, did a jiwa upgrade as advised in this post, except when I try to login it comes with the attached error, can you pls guide

[SBarnes]

Based upon what you have provided take a look at the link below, I would say you either don't have user on the server at all or its not added to the database, you will possibly have to do the same for JiwaReports, you will need a user with admin privileges to do this under azure that user is usually support

You will also need to run the grant permissions script like below.


https://sqlserverplanet.com/security/ad ... ase-script

Code: Select all

EXEC    [dbo].[usp_Jiwa_Grant_Reporting_Permissions]
      @SQLLogin = N'JiwaUser'


EXEC    [dbo].[usp_Jiwa_Grant_Reporting_Permissions]
      @SQLLogin = N'JiwaReports'


EXEC    [dbo].[usp_Jiwa_Grant_Application_Permissions]
      @SQLLogin = N'JiwaUser'


EXEC    [dbo].[usp_Jiwa_Grant_Application_Permissions]
      @SQLLogin = N'JiwaReports'



[Riyaz]

Thanks Stuart

The users exist already, and have just rerun those queries again to grant them permissions, but still no luck, getting the same error.

[SBarnes]

Then the only other issue could be that the password in HR_Staff for the Jiwa user is different to what the sql server is expecting it to be.

Try logging in as the Jiwauser through sql manager to confrim what you think the password should be, also confirm that the JiwaUser has access to the database in question, you can do that in sql manager alsoby simply trying to expand the database and its list of tables.

[Mike.Sheen]

Thanks Stuart

The users exist already, and have just rerun those queries again to grant them permissions, but still no luck, getting the same error.

Use SSMS to connect as the user 'JiwaUser' - do you get the same error? If not, then the password stored against the staff record(s) for the JiwaUser SQL Login is incorrect - the easiest way to rectify that is re-upgrade the database - it'll know it's already the same version so will just re-create views and stored procedures - but you will get the opportunity to tick the box to reset the SQL credentials for all users - so do that and carefully set the password.

[Mike.Sheen]

Then the only other issue could be that the password in HR_Staff for the Jiwa user is different to what the sql server is expecting it to be.

Try logging in as the Jiwauser through sql manager to confrim what you think the password should be, also confirm that the JiwaUser has access to the database in question, you can do that in sql manager alsoby simply trying to expand the database and its list of tables.

Snap!

[SBarnes]