Error Creating Shared Password Lists IF user policy E2=X

[Haagen IT Partner]

Hi,

 

After updating Passwordstate we are having problem creating Password Lists and a error sign is shown:

When querying the Passwordstate DB it shows the following:
Error Code = The statement has been terminated.  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LinkedPasswordLists_PasswordListTemplates". The conflict occurred in database "passwordstate", table "dbo.PasswordListTemplates", column 'PasswordListTemplateID'., StackTrace =    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)     at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()     at (Object )     at Passwordstate.Passwordstate.PasswordList.LinkPasswordListToTemplate(String PasswordListTemplateID, String PasswordListID, String PasswordList, Boolean AddLinkedRecord, Boolean IsPrivate)

The Password List is displayed after hitting F5 in browser and is created in the root of the tree instead of the folder in the tree I created in. When creating in root path the issue is the same.

This error occurs when i have a User Policy set in E2:
 
 

The users in E2 all exists and are active and have not been a problem before the upgrade.
If I disable the policy or default E2 value and relogg the policy is applied the users can create their Password Lists.

.

I saw in the release of 9100 there was a fix in this area:

• Fixed an issue with setting permissions when creating Password Lists under folders with Advanced Permissions model, where settings and permissions were based off a Template via a User Account Policy

I don't know if i have missed something in the upgrade proccess or if the upgrade didn't apply the fix during the process?
I had to manually install DOT NET 4.7.1 on the core servers via cli before upgrade if that have something to with it.

Thanks

[support]

Hello,

 

This issue relates to SQL Server not being able to insert a record into the DB. We've only seen this before when customers are using SQL Server Transactional Replication, and the High Availabilit instance of Passwordstate was not set to be passive i.e. read only.

 

Is this your setup? If so, if you go to the screen Administration -> Authorised Web Servers, are the roles for your servers set correctly?

 

If so, please contact us via the following page, as we will need to check your database schema to ensure it is correct - https://www.clickstudios.com.au/support.aspx
 

You can provide us a copy of your schema by following this article - https://www.clickstudios.com.au/documentation/generate-database-schema.aspx 

 

Version 9 also required .NET Framework 4.7.2 or higher, but we do not believe this is your issue.

Thanks

Click Studios

[Haagen IT Partner]

Hi Support,

 

Okey, yeah i guess something was not applied or converted during the upgrade., the webserver is listed as authorized to read/write in the database.
I have sent you a copy of the database schema.

 

Thanks

[support]

Hello,

 

Thanks for the script, and we will take a look at get back to you.

 

Can you confirm if you are using more than one Passwordstate web server? If so, can you also send screenshots of the Authorised Web Servers screen in Passwordstate, and also let us know what SQL Server Replication you are using?

Thanks

Click Studios

[Haagen IT Partner]

Hi,

Sorry for the delay.
Im using a primary website that is listed as Authorised in the Authorized Web Servers section.
This instance uses a simple MSSQL Server 2016 Core without replication.

I can confirm after upgrading from 8995 to 9xxx and when using the E2 policy that applies permissions to the created Shared Password List is enabled and applied this error occors only. (On two instances)



If we take a look at the result of the applied policy when creating a Shared Password List permissions are copied from the Password List Template: PermissionX as set.

But there is a small detail here:
Take a look at this checkbox that is applied as a default when E2 is enabled. During the process of applying permissions to a not selected Password List or Template and that's why i recieve an SQL error when the DB try to link the attribute to a NULL variable (none existing)


Wordkaround 1:
If i chose a Shared Password List or Template when this checkbox is applied i recieve no error and the Shared Password List is created since the DB have an attribute to apply to.


Wordkaround 2:
If i uncheck the box below to not execute a linking the Shared Password List is created without an error as well.

How can i disable this prefered Linking for Shared Password Lists while using E2?
I have recreated Password List Templates and policies, changed all attributes in Settings and relogged everytime without success.

I also have this setting in: Settings\Password List Settings: which not seames to work since it's prechecked when E2 is used.

Can you assist me on this part? Since i experience it's a prechecked setting and have to be deselected each time a password list is created if E2 is enabled. I have two separate instances that experiance this after upgradeing from 8550 to 9xxx.

Thanks in advance

[support]

Hi Tony,

 

Thanks for the information, and we'll use your screenshots to and do some testing to see if we can reproduce this. In theory, if you do not have a Password List selected for copying settings in the User Account Policy, then we should ignore that System Settings for linking the Password List to the Template.

 

It does sound like a bug, and we'll need to work on a fix for the next release.

Thanks again.

Regards

Click Studios

[Haagen IT Partner]

Hi Support,

 

I agree on that point and think that if E2 policy or some of these system settings > password list options i sent to you over mail is activated while upgrading from 8995 to 9x version this bug occurs. Shout if you need an example of settings since i have a saved backup of them that always (7/7 migrations) ended up with this error after the upgrade process.

Thanks

[Haagen IT Partner]

Hi Support,

Tested and validated after upgrading version 9300 i can confirm the error is now resolved. I can now create a Shared password list and no error occurs while E2 policy is active and permissions are applied correct.

However: when E2 policy is in use the GUI act as settings are copied and applied to the new Shared Password List.
1. The Password List Settings are grayed as if it's inherited when they are not since only permissions are in use.
2. This is probably triggered by "Link this Password List to the selected Template" is still checked as default and therefor also graylists the checkboxes since it thinks it's inheritit setting from a template while it's not.


When E2 Policy is disabled:
3. After disabling E2 policy and relogging the same Template permissions are choosen as default and the Password List Settings are not grayed, when createing a SPL: the permissions of the Template are applied . When setting the value "- Copy Permissions from Template" (no inherance) the permissions applied is the logged on user.

I think when applying permissions via the E2 policy "Link this Password List to the selected Template" get's triggered even if there is only a permission applied and not Template Settings (should be threated differently: settings vs permissions). And when disabling the E2 the settings of "Copy Permissions from Template " are not defaulted correctly since it still inherits values as preselected.

Same experience in several different Passwordstate environments, not a huge problem at all  but i don't think it works as intended

Thanks