Security users - windows authentication

[heba]

Hi all,

I'm trying to setup a repository with security enabled. In the "Security Users" dialog I can successfully import AD users.

But it seems that selecting the "Accept Windows Authentication" checkbox does not have the desired effect.  I cannot login with my Windows/AD credentials.  Setting a normal/specific EA user password works.

Help says, when importing "Enterprise Architect generates random passwords for Windows user IDs; however, if necessary you can assign a new password to an imported user ID".  

That sounds wrong, somehow?  Because I want users to be able to just use their regular Windows password and not having individual passwords.

Do I miss something, or do I make something wrong?  

Thanks!
Heiko

[smendonc]

Just a thought, does the AD group also have R/W permission on the database/repository?

I've used this functionality for years with SQL server and the only issues I've run into have been the above or a variant such as the SQL server being on a different domain then the user AD group or configured to not allow authentication via AD.  Not sure if there is any specific configuration for other database servers.

Stan.

[Geert Bellekens]

Heiko,

Are you absolutely sure that you have correctly imported the users?
They should all have the format of DOMAIN\UserName

Are you trying this on a .eap file or on a DBMS repository? I've seen issues with .eap files and windows authentication in the past.

Geert

[heba]

Access to the DB is working. I'm using a MSSQL server 2014. Using SQL server management studio I can select "Windows authentication". It automatically selects my account and login + access works fine.

In EA, I tried both formats to login. Using the domain in front of the username, DOMAIN\username, works after entering a dedicated password.

I'm not that expert with SQL server but I'll try to figure out if EA connects to the server and what credentials it is using.  

I was expecting that the windows authentication used by the EA security feature is only used internally by EA since the account to connect to the DBMS is a different one.  Now when thinking about that... maybe that's the mistake :-?   I'll try to check this as soon as possible.

[Uffe]

Hello,


When using Windows authentication, EA checks whether the logged-on Windows user has an account in the project (t_secuser, the user name includes the domain).

I don't know whether or not EA actually requests authentication from the Windows domain controller, but the authentication for EA project access is in any case completely separate from the underlying database access.

You can, if you want, set it up so that everyone connects to the database using the same credentials (although this is obviously poor security), but that will not affect the EA project authentication.

Other problems you may run into:

• The database user does not have proper permissions in the database (all database users need write permission, even if their EA project user group does not have write access).
• If you use Windows authentication in a file-based repository and move/copy the file, the users are locked out. The solution is to disable and re-enable Windows authentication. (Not relevant here, included for future searches.)

HTH,


/Uffe

[smendonc]

I'm not that expert with SQL server but I'll try to figure out if EA connects to the server and what credentials it is using.  

I was expecting that the windows authentication used by the EA security feature is only used internally by EA since the account to connect to the DBMS is a different one.  Now when thinking about that... maybe that's the mistake Huh   I'll try to check this as soon as possible.

SQL server will not allow you to impersonate a user i.e. have one user authenticated via a windows (AD) login and another actually connect to the database.  This is how it appears you are set up.  The reason for this is that it can introduce all kinds of security issues.  

To get around all of this and allow the underlying Window/AD infrastructure to take care of the details I usually create an AD security group for EA users.  Then assign that security group read/write permission on the database repository and synchronize the same SG with EA.  This way any member of the security group will have access to EA and the relevant rights on the database.  Behind the scenes the windows authentication token created when a user logs into Windows is used by EA for it's internal security procedure and also sent to SQL server.  SQL server validates that the same user who logged into the windows account has rights on the database before allowing access.

Rather wordy but hope this helps.

Stan.

[heba]

After some more tests I found that I might have misunderstood that feature.

What I did (and missed to mention, sorry) was to use the "Login as another user..." entry in the Security submenu.  This does not seem to work at all with Windows authentication.

If I close the project and reopen it selecting Windows authentication is opens the project with the permissions I gave to the current Windows user (me) so I expect I'm logged in with my Windows account.

By the way, is it possible to see to currently used DB connection and the current EA user account?

-Heiko

[Geert Bellekens]

The used EA login shortly shows on the bottom left status bar.
The name of the model shows on the top status bar.

You could of course write a little script to show that, something like

Code: [Select]

option explicit

sub main
      Msgbox "The current connection string: " & Repository.ConnectionString & vbnewline & _
               "The current user: " & Repository.GetCurrentLoginUser(false)
end sub

main
Geert

[Uffe]

Hi again Heiko,

What I did (and missed to mention, sorry) was to use the "Login as another user..." entry in the Security submenu.  This does not seem to work at all with Windows authentication.

No, it doesn't and that's by design.
If you're using Windows authentication, EA uses the current Windows user to authenticate access to the project without a password check. As long as you're still logged in in Windows as the same user, you can't change to a different Windows-synched user in the EA project.

However, you can still use "Login as Another User" to switch to non-Windows-synched users, eg admin. But not back again. (Which is annoying, and would make a good feature request.)

The name of the model shows on the top status bar.

This is a common misconception. An EA project has no name.

• If you're using a file-based repository, the top bar shows the file name.
• If you're using a DBMS connection, the bar shows whatever name you have specified -- which might be completely different from what your team mates have specified in their connections.
  (Some people think the name of the database -- "Initial Catalog" in the connection string -- is shown, but this is incorrect. It's good practice to specify that as the name, but it's not automatic.)
  
• If you're using an EA shortcut (an .EAP file containing a connection string), its file name is shown, even if the connection string contains a different name.

In a deployment where multiple EA projects are used, and especially if the same users accesses several of them, I always set up a shared directory with .EAP shortcut files and tell people to use those instead of rolling their own DBMS connections, simply because this way everyone sees the same name in the top bar.

This directory sits neatly alongside the shared directory for custom MDG Technologies.


/Uffe

[steen.jensen]

Hi Uffe.


How do you make a EA shortcut for a DBMS-project? with correct connection params.

[Nizam]

Connect to your DBMS using 'Connect to Server' and use File -> Save As to save as a EAP file. (this will store the connection string in a file extension .EAP, which EA will recognize)

[Paolo F Cantoni]

Connect to your DBMS using 'Connect to Server' and use File -> Save As to save as an EAP file. (this will store the connection string in a file extension .EAP, which EA will recognize)

FWIW, we name ALL shortcut files with a leading "@" to separate them from REAL .eap files.

Otherwise, you can get quite confused... by EAUI.

Paolo

[Uffe]

Tjena!

How do you make a EA shortcut for a DBMS-project? with correct connection params.

As has been suggested, you can just save one out of EA. But it's actually quite simple to create a shortcut .EAP file manually, and in a deployment where you work with multiple projects and add new ones over time this often ends up being simpler.

Here's a template for a SQL Server-based repository which uses Windows credentials (which is unrelated to EA's user authentication, see earlier posts in this thread).

Code: [Select]

EAConnectString:#Window title# --- DBType=1;Connect=Provider=SQLOLEDB.1;
Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=#Database name#;Data Source=#DB server and port#;LazyLoad=1;
Note that the split into three lines is just for clarity here. When EA creates a shortcut .EAP, it puts everything onto a single line and no whitespace after the semicolons. The hashes are there just to signify parameters you need to replace. In a real file, there are no hashes, no quotation marks or anything like that.

Most of what's in the file is plain ODBC, so things like the correct DBType value for a particular database server are defined by Microsoft. Only the very first and very last bits (window title and LazyLoad) are EA-specific.

#DB server and port#: Something like MyServer,1433 for an SQL Server server.
#Database name#: The name of the database on the server. If you set up a DB connection manually, this is in the "Connection -- 3. Select the database on the server" dropdown.
#Window title#: This is what's shown in the window title. It's a good idea to make sure this is the same as the name of the file, otherwise it's confusing.

I usually impose a naming scheme where the #Window title# is set the same as the file name, and the file name is set to Meaningful project name (database name). The "Meaningful project name" is something that makes sense to the modellers, and the databases are named EA001, EA002, etc which makes it easier for the DBAs. If the client wants one or more reusable asset repositories they're RAS01, RAS02, ...

FWIW, we name ALL shortcut files with a leading "@" to separate them from REAL .eap files.

That's a very good little practice there, hadn't thought of that. I'll steal that for next time.

Finally, set up the shared folder so that only the EA admins can write to it, or at least make the files read-only. Just for drulleförsäkring.

HTH,


/Uffe