We have a data warehouse and users are trying to browse the database directly from excel. We put those users in an AD group and give them read only permission to a couple of databases.

The problem is under such circumstance users are not able to establish connection from excel to SQL Server. "login failed for user ***" will be shown as error message.

However, if we add every single user's login to SQL Server security, they are able to access to databases from excel then.

Does anyone have any idea why this happens?

  • How long are you waiting after updating the AD group before attempting to login? Sometimes it can take a while for the membership changes to propagate. – LowlyDBA - John M Jan 12 '15 at 16:59
  • @John M, yes we have waited for a fair amount of time. Also users have logged off and back on to test it. Still no luck. – Joann.B Jan 12 '15 at 18:17
  • Have you looked at the reason for the failure? The 18456 error will leave a state number in the log, if you have the default audit setting, that will help you understand the failures. See Aaron Bertand's synopsis at: sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/… – RLF Aug 16 '15 at 1:31

Ah group permissions in SQL Server have never worked out for me right off the bat. I would check this question and make sure you are setting it up right on the SQL side https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

Also I agree with John, if you add a member to a group they need to log off and back on again before AD will update with the new group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.