SQL Server Authentication

[TheCosmicTrickster]

Found what appears to be a problem with authenticating EA users to SQL Server (using Windows Authentication based on their domain login).

We have several users who need access to a SQL Server-based repository.  These users are in a single group in Active Directory, and that group is granted login rights to the SQL Server & necessary rights to the EA database.  However, when a user tries to connect to the database through EA, they cannot see the database (when creating an ODBC connection).  It's not until I add their individual Active Directory login to SQL Server and grant rights to the EA database that they can see it.  They are in other groups that have rights to other databases on the same SQL Server and have no issues.

I'd rather not add individual SQL Server logins for every user that needs to access to EA repository databases (especially if we have to create a database per EA model - but that's the subject of another thread).

Has anybody else encountered this issue?

Note:  I'm the DBA and know little about the EA front end.

[mrf]

Taking a complete stab in the dark...

I think the property is set on the SQL server isn't it? I've got SQL Server Express on my machine with SQL Server Management Studio Express installed.

From SSMSE I right click on the database in the tree, and select Properties. The Navigation page contains an option Server Authentication which allows you to choose from either

- Windows Authentication mode
- SQL Server and Windows Authentication mode

I've set mine to Windows Authentication mode and it accepts my domain user/password as authentication.

I hope this is somehow relevant to what you're trying to achieve, I'm unfortunately not an expert in DBMS admin  

[smendonc]

I use SQL server and grant access to users through active directory security groups quite a bit.  I suspect the issue is the access privileges that have been granted to the security group for the repository database.  I have mine set to read & write.  Windows authentication is also turned on as Michael notes.

I did experience a similar problem once with a handful of users.  The issue ended up being security group updates not propagating to domain controllers, but if you have this working for other databases on the same SQL server I don't think this is the issue.

Stan.

[Geert Bellekens]

Same here. We have the exact same setup (an active directory group is granted access to our SQL Server database) but we don't have any issues with regards to connecting to the database.

Geert

[TheCosmicTrickster]

Taking a complete stab in the dark...

I think the property is set on the SQL server isn't it? I've got SQL Server Express on my machine with SQL Server Management Studio Express installed.

From SSMSE I right click on the database in the tree, and select Properties. The Navigation page contains an option Server Authentication which allows you to choose from either

- Windows Authentication mode
- SQL Server and Windows Authentication mode

I've set mine to Windows Authentication mode and it accepts my domain user/password as authentication.

I hope this is somehow relevant to what you're trying to achieve, I'm unfortunately not an expert in DBMS admin  

Thanks for the assistance.  I AM an expert in DBMS admin, so I know this isn't the issue - we're running Mixed Mode on this particular server and have a combination of SQL & Windows logins being utilised by various apps.

CT

[TheCosmicTrickster]

I use SQL server and grant access to users through active directory security groups quite a bit.  I suspect the issue is the access privileges that have been granted to the security group for the repository database.  I have mine set to read & write.  Windows authentication is also turned on as Michael notes.

I did experience a similar problem once with a handful of users.  The issue ended up being security group updates not propagating to domain controllers, but if you have this working for other databases on the same SQL server I don't think this is the issue.

Stan.

It could be this - we've had funny issues with our domain controllers in the past.  However, I was using an existing group (i.e. one that's been in AD for months, if not years), so domain replication shouldn't have come into it.  I might recreate the group login in SQL Server & leave it for a few days before getting the user to try again, see if that does any better.

CT