Sparx Systems Forum
Enterprise Architect => Bugs and Issues => Topic started by: Geert Bellekens on December 16, 2020, 10:54:23 pm
-
Today one of our users noticed something strange.
After re-opening the model he was working on, he noticed that the diagram he was working on was locked by another user. After trying to lock to apply a user lock he got the message that the diagram was locked by himself.
Trying to diagnose the issue I found that the user was created multiple times, but this time with his email address instead of the Domain\User like the other users.
We use the link between the EA user group and an AD group, so the users are created automatically by EA.
We recently upgraded to version 15.2.1555, so I guess it's a pretty recent change.
Reported
Geert
-
I did a few tests, and if I enlarge the UserLogin column from nvarchar(32) to nvarchar(255), and then correct the userLogin in the database, the user gets recognised
ALTER TABLE t_secuser ALTER COLUMN UserLogin nvarchar (255)
update t_secuser set UserLogin = '[email protected]' where userID = '{0A44E541-F59A-4b71-A658-D189D6B91B53}'
Unfortunately, EA only creates a userLogin of 32 characters, even if the column is larger.
So for now, each time we have a new user with a long name, I will have to fix the problem in the database directly; not really a sustainable solution.
I hope Sparx provides a real solution soon.
Geert
-
I found an automatic workaround for the problem.
This EA-Matic script corrects the user logins each time we open the model.
'[path=\Projects\EA-Matic Scripts]
'[group=EA-Matic]
option explicit
!INC Local Scripts.EAConstants-VBScript
'
' Script Name: FixUserLogins
' Author: Geert Bellekens
' Purpose: Since v15.2.1555 EA stores the email address insead of the Domain\UserID in the t_secuser.Login column.
' The problem however is that this column is nvarchar(32) and there are lots of emails addresses that ar larger.
' When a user with a large email address gets automatically created by EA, the login is truncated at 32 characters.
' This script will fix that with an SQL update.
' We previously updated the database so that column size is now nvarchar(255): ALTER TABLE t_secuser ALTER COLUMN UserLogin nvarchar (255)
' Date: 2020-12-16
'
function EA_FileOpen()
dim updateSQL
updateSQL = "update u set u.UserLogin = SUBSTRING ( u.userLogin ,1 , charindex( '@', u.UserLogin)) + 'extern.company.com' " & vbNewLine & _
" from t_secuser u " & vbNewLine & _
" where u.UserLogin like '%@%' " & vbNewLine & _
" and not u.UserLogin like '%extern.company.com' "
Repository.Execute updateSQL
end function
Geert
-
I see that a fix has been released in version 15.2.1556 (release date today: 2020-12-16)
release notes say:
Security User login names will now truncate to the database field length rather than a fixed value, to accommodate users extending the field in the Database
I guess we are supposed to enlarge the field ourselves (as I did already), and EA won't truncate it anymore to 32 characters.
I haven't been able to test it yet.
Geert
-
I see that a fix has been released in version 15.2.1556 (release date today: 2020-12-16)
release notes say:
Security User login names will now truncate to the database field length rather than a fixed value, to accommodate users extending the field in the Database
I guess we are supposed to enlarge the field ourselves (as I did already), and EA won't truncate it anymore to 32 characters.
I haven't been able to test it yet.
Geert
(my emphasis)
Wonderful spin! "it's all your fault, you naughty users!"
There's an Aussie political aphorism (paraphrased), adopted by the (manly) political right, "Never back down, never apologise, never explain" [1]. This looks like a prime example.
If they really believe what they announced, then that explains EAUI.
[1] It may have originated from the album "Never Apologise Never Explain" by Thereapy
-
Wonderful spin! "it's all your fault, you naughty users!"
Yes, how dare you have an email address longer than 32 characters :o
Geert
-
Wonderful spin! "it's all your fault, you naughty users!"
Yes, how dare you have an email address longer than 32 characters :o
Geert
Geert,
is there a defined limit to an email address (set by some standard) or should we just go out to nvarchar(255) to be safe?
Paolo
-
is there a defined limit to an email address (set by some standard) or should we just go out to nvarchar(255) to be safe?
I didn't bother to check and set it to nvarchar(255). The reason for this size is to stay consisting with other similar fields (such as names)
According to the accepted answer on Stackoverflow (https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address#:~:text=%22There%20is%20a%20length%20limit,total%20length%20of%20320%20characters.) the maximum size is 254, so I was right on the spot ;D
Geert
-
According to the accepted answer on Stackoverflow (https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address#:~:text=%22There%20is%20a%20length%20limit,total%20length%20of%20320%20characters.) the maximum size is 254, so I was right on the spot ;D
Off by one. Most common error in programming. :)
On the serious side (https://www.youtube.com/watch?v=IXKxrDFKEx0), I think this is a decidedly odd thing for a supplier to do. This is a bug which has been shown to break the tool in the wild, and which has a simple solution. Instead of rolling out that solution, they encourage users to go hacking the database schema by themselves.
Those users who find the solution, that is -- a one-liner in the release note for one particular version isn't much use on its own, so users would have to find this thread to understand that this is something they can, and should, do. No help on how to do it either, for any of the DBMSes EA supports, and no patcher script to download and run.
The release note doesn't say whether the client, or the supplied base project, has been updated so that projects created as of 15.2.1556 will have the correct length for this field. Most likely they won't.
So are Sparx perhaps putting schema updates on the shelf for now to be included in 16.0?
/Uffe
-
So are Sparx perhaps putting schema updates on the shelf for now to be included in 16.0?
/Uffe
That would be my guess.
I think they wanted to already provide a quick-fix, awaiting a database schema updatelater on.
Would be good if a Sparxian could confirm that.
Geert
-
The release note doesn't say whether the client, or the supplied base project, has been updated so that projects created as of 15.2.1556 will have the correct length for this field. Most likely they won't.
I've just checked, and new projects created with 1556 will have a 32-char UserLogin, both in Access/JET4 and Firebird.
-
The release note doesn't say whether the client, or the supplied base project, has been updated so that projects created as of 15.2.1556 will have the correct length for this field. Most likely they won't.
I've just checked, and new projects created with 1556 will have a 32-char UserLogin, both in Access/JET4 and Firebird.
Hmm I might need to check if the project transfer still works in that case... ???
That might start throwing errors if the EABase still contains the 32 character UserLogin and I have users with longer logins.
Geert
-
I've just checked, and new projects created with 1556 will have a 32-char UserLogin, both in Access/JET4 and Firebird.
Hmm I might need to check if the project transfer still works in that case... ???
That might start throwing errors if the EABase still contains the 32 character UserLogin and I have users with longer logins.
Sounds like a very good idea.
But I think your 255 characters is too short.
An FQDN caps out at 255. Then you need one for the separator ( @ or \ ), and then the user logon name on top of that.
Max length for a logon name that I've seen on a Microsoft page is 104 (more than 64 "not practical") and although that's for Server 2000 they aren't likely to have reduced it in the intervening 20 years.
So to be safe, I'd make it 360 characters. Possibly more, indeed why not take it up to 512?
It is quite possible that the actual limit is lower in any specific installation, but remember, the EA schema is not what should dictate any IT policies in your organization: it needs to be no more restrictive than the other components in your IT environment. 255 sounds needlessly optimistic to me in the general case. You don't want to have to deal with this problem more than once.
/Uffe
-
According to the accepted answer on Stackoverflow (https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address#:~:text=%22There%20is%20a%20length%20limit,total%20length%20of%20320%20characters.) the maximum size is 254, so I was right on the spot
Correct,
The maximum 256 octet length of a forward or reverse path restricts the entire email address to be no more than 254 octets long, see RFC 5321 section 4.5.3.1 (https://tools.ietf.org/html/rfc5321#section-4.5.3.1)
Why: Path max length = 256 Octets
and to simply
Path = "<" Mailbox ">"
Mailbox = Local-part "@" Domain
-
Hi again,
You're barking up the wrong tree here, lads. The max length of an e-mail address isn't the issue.
We are not talking about e-mail addresses, but about Windows accounts (https://docs.microsoft.com/en-us/windows/win32/secauthn/user-name-formats). These come in two forms, the older Down-Level Logon Names (DomainController\UserName) and User Principal Names (UserName@Domain).
UPN account names are on the same form as an e-mail address, but they are not the same thing and do not obey the rules in RFC 5321.
/Uffe
-
There a new version (15.2.1557) available with some more fixes for this issue:
User Login length is now detected correctly during Project Transfers
Updated the default EA Schema to support longer Login Names
Geert
-
According to the schema release notes, they've increased the size to 255. I still say that's probably insufficient, but it should be enough for most users so should make the problem occur less frequently. Near enough, good enough.
The client history doesn't mention the fact that there is a new version of the DBMS creation scripts, which you must use to set up a DBMS repository. The base projects have been updated, so if you update the client but not the DBMS creation scripts (which are, of course, a separate download), and then try to transfer a newly created file-based project to a DBMS project initialized with the old scripts, well who knows.
/Uffe
-
According to the schema release notes, they've increased the size to 255. I still say that's probably insufficient, but it should be enough for most users so should make the problem occur less frequently. Near enough, good enough.
The client history doesn't mention the fact that there is a new version of the DBMS creation scripts, which you must use to set up a DBMS repository. The base projects have been updated, so if you update the client but not the DBMS creation scripts (which are, of course, a separate download), and then try to transfer a newly created file-based project to a DBMS project initialized with the old scripts, well who knows.
/Uffe
I'm guessing they need a bit more time to update the database creation scripts. If that is the case we should see updates to the DBMS creation scripts, and a new update script soon.
The other option is that they reverse the change and go back to the old Domain\userName
Although that already had the same problem to a lesser extend:
Max domain name = 15 character
Max userName = 20 characters
So Domain + "\" + UserName = 15 + 1 + 20 = 36 whereas the current field stores a maximum of 32 characters. :o
Geert
Geert
-
No, the creation scripts have been updated. It's just not mentioned in the client release notes.
Didn't see any patch scripts, but I might have missed them.
The other option is that they reverse the change and go back to the old Domain\userName
I don't see how that's an option. EA can't decide what the domain controller returns.
/U
-
No, the creation scripts have been updated. It's just not mentioned in the client release notes.
Didn't see any patch scripts, but I might have missed them.
Ah, I see. Good to know, thanks.
The other option is that they reverse the change and go back to the old Domain\userName
I don't see how that's an option. EA can't decide what the domain controller returns.
It's EA that chooses to either use the Domain\userName or the "emailaddress" style username.
If EA doesn't have your email address registered, but still the old Domain\userName, then you will be logged in anyway.
This is then mentioned in the system output like this:
Login: Windows user 'Geert Bellekens ([email protected])' is not a member of the model.
Login: Logged in as Windows user 'Geert Bellekens (DOMAIN\Geert.bellekens)'.
Geert
-
The other option is that they reverse the change and go back to the old Domain\userName
I don't see how that's an option. EA can't decide what the domain controller returns.
It's EA that chooses to either use the Domain\userName or the "emailaddress" style username.
I don't think so.
If EA doesn't have your email address registered, but still the old Domain\userName, then you will be logged in anyway.
This is then mentioned in the system output like this:
Login: Windows user 'Geert Bellekens ([email protected])' is not a member of the model.
Login: Logged in as Windows user 'Geert Bellekens (DOMAIN\Geert.bellekens)'.
That would appear to be EA checking if the account in the Windows session is stored in t_secuser. If not, and if it is a UPN, it constructs a DLLN from the simple account name and tries that. This may have security implications, and once again it would be interesting to know exactly what the algorithm is here. (Keeping your authentication algorithm obscure is a frustration for IT staff and a godsend to hackers.)
But what I meant was that EA doesn't decide what the domain controller returns when you import the user in the first place. In this case, it appears that what's been imported is a DLLN, but the Windows session returns a UPN.
/Uffe
-
Uffe,
The behavior changed when we upgraded from 15.1 to 15.2.
We didn't change anything else except that, and suddenly we see this new behavior with the email addresses.
I'm pretty sure if I go back to 15.1, EA uses the DOMAIN\Geert.bellekens again. (I can do a test next week to be 100% sure)
Geert
-
Hello again,
The behavior changed when we upgraded from 15.1 to 15.2.
We didn't change anything else except that, and suddenly we see this new behavior with the email addresses.
It was in 15.2.1555. And they're User Principal Names, not e-mail addresses.
Other
- Active Directory integration improved:
- Importing Active Directory users now uses 'User Principal Name'
- Auto login will now attempt to match both 'User Principal Name' and legacy NetBIOS\SAMUsername formats
- New option added. 'Allow non-domain users (insecure)'. This is off by default when enabling security but existing models it will be on
So pretty much as I outlined. But since you're presumably logging into a pre-existing project, it's got DLLNs stored and so you get that message.
However, this also makes clear that EA does in fact choose whether to retrieve DLLNs or UPNs when importing users, and it has changed from DLLN to UPN. This is a good thing, but was clearly not executed with due care and consideration, which is why they had to rush the 1557 fix.
It would have been even better if they'd included an option to re-import users so they'd retain the old GUIDs but change t_secuser.UserLogin from DLLN to UPN. You can automate it, but if you want to do it right (which, you know, we're talking about security and accessibility here, so probably just as well to make the effort) it's a fair amount of work so some support would have been nice.
/Uffe
-
Can you explain why the change is a good thing?
Is that because UPN can stay the same even if you change domain?
In my case, since I have the groups in EA linked to AD groups, I could simply release all user locks, and then delete all users.
They will be automatically recreated (with their UPN) the next time they open the model.
I'm just not sure if I should. After all, its working now as well.
Geert
-
Hello,
Can you explain why the change is a good thing?
Is that because UPN can stay the same even if you change domain?
No, the UPN is linked to a domain. Different domain, different UPN.
But UPNs are more readable, and can be longer. They seem to be the preferred option when authenticating in Windows, although it is hard to tell for sure.
In my case, since I have the groups in EA linked to AD groups, I could simply release all user locks, and then delete all users.
They will be automatically recreated (with their UPN) the next time they open the model.
I'm just not sure if I should. After all, its working now as well.
I wouldn't. If you want to get rid of the message, I'd modify t_secusers. You never know what might be tied to the user GUID somewhere deep down in the schema.
/U
-
We did our weekly project transfer this morning an got error:
Database target field for t_secuser.UserLogin is too small. target length 32, required 33
This is using version 15.2.1557. I was under the impression the main point of release 1557 was to resolve this problem.???
At first sight it seems the database field in the EABase.eap(x) hasn't been enlarged after all.
Geert
-
Hi again,
Not presuming to speak for Sparx or anything, but surely the schema change only applies to new projects?
I'd be surprised if they'd implemented an auto-update in the client. For one thing, the account that runs the client might not have permission to make those changes in the database (in a file project yes, but not in a DBMS).
So you'll have to make that change yourself if you need it.
In summary, 1555 changed what gets stored in t_secuser.UserLogin when importing users from AD. Notably, the newer format (UPN) requires longer strings, but the column length was left at 32. (This was too short before as well, but usually worked due to sheer luck.)
1556 added a fix so that when storing accounts in t_secuser, the actual length of .UserLogin is used in case you've increased the length of the column yourself.
1557 updated the schema so that projects created from then on would have the column extended to 255 characters.
But if you're working with a project created with any version of the client prior to 1557, you need to extend that column yourself.
Sparx have not published any scripts to help you do that, you simply have to know a) that you need to do it in the first place, and b) how to do it in your chosen DBMS.
/Uffe
-
Hi Uffe,
I changed the UserLogin size in my database to nvarchar(255)
Since then we have a user with a UserLogin of 33 characters.
When we now try to do a project transfer from DBMS to .eap or .eapx I get this error.
When you do a project transfer EA creates a new .eap file based on the EABase.eap file in the program files folder, and I thought it was exactly this one that should have been updated.
I'm going to investigate a bit further on Wednesday (working for another client now)
Geert
-
It's the weirdest thing.
I checked the EABase.eap, and the field t_secuser.UserLogin is varchar(255) alright.
So I guess there's somewhere another code that checks the size of the contents before transferring.
It appears that this check is based on a hardcoded set of contraints, and not on the size of the actual database fields.
And apparently, they forgot to adjust that check as well.
I'm not sure what the testing process for this change was, but it seems to not have covered the most basic scenario: transfer a project with a user that has a login larger than 32 characters.
My current workaround is to manually update the t_secuser and set back to the old DOMAIN\userID format
Geert
-
Hi again,
I'm wondering about the project transfer.
When you do a project transfer EA creates a new .eap file based on the EABase.eap file in the program files folder, and I thought it was exactly this one that should have been updated.
Are you sure about this?
You do need a project, not just an empty database, to transfer into, but why should it first copy EABase to the target project and then copy the source project? Remember, the target is already a project (ie has all the EA tables) and will be clobbered with the data from the source. Copying the base project first is redundant.
Presumably also the transfer process doesn't copy the schema from the source to the target but simply clears all the tables in the target and then copies the data from the source.
If this is all correct, what you need to do is update the schema in both the source and the target before you transfer.
It's only if you've created the target project with 1557 that you don't need to update it before the transfer.
/Uffe
-
Uffe,
It depends. If you do a manual project transfer, you need to provide a .eap file yourself.
If you execute the transfer using the API, the .eap file is created by Sparx.
I did the manual project transfer after creating a new .eap file (and verifying that the field is actually varchar(255)
In both cases the project transfer failed if I had a userlogin larger than 23 characters.
Geert