MS SQL Repository Log-in Credentials

[sheldon bateman]

Two questions I can't seem to find the answers to:

1. Setting up a SQL repository, is it satisfactory to leverage the enterprise AD single sign-on for any user simply to connect to the repository? Our corp standard is to not enable a dedicated SQL log-in construct.

2. Strictly for connectivity and use of the repository, what are the minimum permissions any given user needs (https://msdn.microsoft.com/en-us/library/ms191291.aspx)?

This has nothing to do with user/role based security, I plan to implement Sparx Role Based Security for that. Rather, these 2 Q's are only for configuring the SQL repository.

[qwerty]

1. You can import AD users in EA and enable login for them.

2. R/W for all tables.

q.

[sheldon bateman]

Thanks qwerty! #2 confirms my assumption but wrt #1, I understand that importing AD User ID's applies to sparx user security on the model (as per the user guide), http://www.sparxsystems.com/enterprise_architect_user_guide/10/projects_and_teams/import_user_ids_from_active_di.html

My question is specific to connecting to the SQL server; do AD User ID's also apply to connecting to the repository?
Data Link Properties dialog >> Connection tab >> 2. Enter information to log on to the server

[qwerty]

It's a while ago I last needed to do that, so I can't recall what exactly had to be done. There was probably something in the server to tell that AD login has to be used. And the import of the AD users is only needed for the EA security. I guess it's all written in Sparx white paper about setting up configuration management with EA (it's hosted somewhere on their resource site).

q.

[smendonc]

We usually have AD security groups set up to provide the correct permissions on the SQL Server.  Users are then added to or removed from the AD security group.  From an EA perspective this ends up being transparent.  A user has access if they have access through the security group to the database or they don't.

On some of the repositories we had two security groups, once with r/w permissions and the other with r/o.  I can't definitely remember now but I believe that worked as well to help segregate updaters vs. read only.

After setting up the base you can additionally layer the Sparx security within the repository itself with no issues.

Stan.

[sheldon bateman]

Thanks Stan, that is a handy piece of information indeed.  I appreciate the responses!!

Sheldon

[Geert Bellekens]

Hi Sheldon,

When defining the connection tot the database you can define whether or not Windows Authentication should be used to log on to the database server.

This is completely independent of the security setup in EA.

I think it's a best practice to define AD groups for read-only/read-write access and setup the security on the database as well as in EA.

SQL Server can actually use the AD groups for its security, so in order to allow a new user access to the database you only have to assign him to the correct AD group.

For the internal EA security you will still need to import each and every individual user. Changes to AD groups will not automatically be reflected in EA's user security setup.

Geert

[skiwi]

I think it's a best practice to

• define AD groups for read-only/read-write access and
• setup the security on the database as well as in EA.

Geert

This is how we do it, with each user being defined within EA to a group (role), e.g. read only, BA, Architect, and as requiring Windows Authentication (Security is on)
There does not appear to be a way of linking AD roles with EA groups

[Geert Bellekens]

I think it's a best practice to

• define AD groups for read-only/read-write access and
• setup the security on the database as well as in EA.

Geert

This is how we do it, with each user being defined within EA to a group (role), e.g. read only, BA, Architect, and as requiring Windows Authentication (Security is on)
There does not appear to be a way of linking AD roles with EA groups

No indeed. That would be valuable improvement if we could just define define the AD groups in EA and not have to worry about adding individual users.

Geert

[sheldon bateman]

I created a concept model of what I'm proposing and the image file is here (too large to imbed): http://wp.me/azG6c-eu

I believe this will get my team to where they need to be.

[qwerty]

If one could view that at reasonable rendering it would be possible to comment.

q.