User created multiple times

[Geert Bellekens]

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

[Geert Bellekens]

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

Code: [Select]

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

[Geert Bellekens]

I found an automatic workaround for the problem.

This EA-Matic script corrects the user logins each time we open the model.

Code: [Select]

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

[Geert Bellekens]

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

[Paolo F Cantoni]

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

[Geert Bellekens]

Wonderful spin!  "it's all your fault, you naughty users!"

Yes, how dare you have an email address longer than 32 characters

Geert

[Paolo F Cantoni]

Wonderful spin!  "it's all your fault, you naughty users!"

Yes, how dare you have an email address longer than 32 characters

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

[Geert Bellekens]

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 the maximum size is 254, so I was right on the spot 

Geert

[Uffe]

According to the accepted answer on Stackoverflow the maximum size is 254, so I was right on the spot 

Off by one. Most common error in programming.

On the serious side, 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

[Geert Bellekens]

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

[Uffe]

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.

[Geert Bellekens]

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

[Uffe]

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

[skiwi]

According to the accepted answer on Stackoverflow 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

Why: Path max length = 256 Octets
and to simply
Path           = "<" Mailbox ">"   
Mailbox        = Local-part "@" Domain

[Uffe]

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. 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