December 9, 2013 at 8:21 am
Hi there,
I have an AD group which I would like to give access to a specific database. So far I have created a new SQL login mapped to the group, created a new user in the database mapped to that login and assigned it db_datareader role membership. However, when I try and connect as a member of the group I just get a 'login failed for user' error. Is there something else I need to do?
Thanks,
Matt
December 9, 2013 at 8:40 am
Sounds like you have the "Default" database set to a database that the login does not have access. Check that, and make certain you change the database name to the database that you have the login set too.
Andrew SQLDBA
December 9, 2013 at 8:59 am
AndrewSQLDBA (12/9/2013)
Sounds like you have the "Default" database set to a database that the login does not have access. Check that, and make certain you change the database name to the database that you have the login set too.Andrew SQLDBA
Hi Andrew, The default database for the login is set to the correct database they require access to. For some reason if I even try and connect to the SQL instance using SSMS it still says login failed for user, so it's not allowing any members of the group to access SQL at all (which I assume means any database permissions are irrelevant anyway?!).
December 9, 2013 at 9:06 am
Further to this I've just found the following in the SQL error logs:
"Login failed for user 'DOMAIN\User'. Reason: Token-based server access validation failed with an infrastructure error."
December 9, 2013 at 9:10 am
Sounds correct. I would check the SQL error logs for a failed login attempt to confirm you have reached SQL Server. There should be an entry stating there was a failed login attempt.
If you are using a connection string to access sql, the default db doesn't matter if you provide a "catalog" as well.
Is the server a named instance? Are you providing it? Is the SQL browser service on?
Is this development or SQL Express edition? Have you enabled TCP/IP?
It think that covers most standard eventualities 🙂 but if you have any further information please let me know.
December 9, 2013 at 9:39 am
Sussed it! The AD group is on a different domain and they had set it up as a 'Domain local' group rather that a 'Global' group. It still let me add it as a SQL login but only started working once I changed the group to global.
Thanks for your help all 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply