Difference between AD group login and AD user login

Question

We have some users having issues whilst running a stored procedure, and passing a date as one of the parameters. Basically wrong format. Can't be parsed.

This is a hand full of users and so far I've narrowed the issue down to a specific AD group which has been given access. The issue is the login in SQL looks no different to a login (another AD group) which works fine (if I add one of the bad users to this new AD group it works). They look identical!

Since I couldn't get it working I thought why not remove the login from SQL and re-add it so I did just that. Removed from both database level and server! Add the group back with the dbowner role as it had before on the database and same result error parsing the date.

The bizzare thing is I do the same thing adding one of the users directly as a login (not an AD group) with the same roles, permissions, and default language and then it works.

Why is using the AD group erroring and the direct user not? Could there be some remnants left from the last time this AD group was used, corrupt somehow?

Not sure what to look at or attempt next? btw. the collation on the database in question is SQL_Latin1_General_CP1_CI_AS (historic thing). But why would one login work and the other not. Please help :)

Apologies if this is the wrong forum.

Answer 1

Ultimately all date-parsing problem are due to bad application design.

When parsing dates, SQL Server looks at the dateformat setting, which is a session-level setting, for which the default is taken from the user's default language.

Review the column default_language in sys.server_principals.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Agree with the bad design etc but this is inherited and we are where we are unfortunately.

The sys.server_principals show a working AD group and the non working AD group are the same, default_language_name of us_english. I can't see why one works over the other based on what I'm seeing in the sys or UI.

Answer 3

Maybe you could be a bit more specfic then. What errors do you get? How does
the statements look like? Have you verified that the error actually is on
the SQL Server side? Last time I had someone ask about date-parsing problems
it turned out to be a configuration issue in IIS.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Windows forms app. 

I've done more research and it's looking possibly like that whilst the SQL login (AD group) is used for authentication to the desired database the default language could be possibly being picked up by other AD groups that the user has access to at the SQL Server level. See below link. I'm now attempting to prove this but this makes sense since adding the user directly as a login works which the follows blog states is one of the solutions.

http://blogs.msdn.com/b/ialonso/archive/2012/07/25/which-database-and-language-the-domain-user-goes-by-default-when-is-a-member-of-multiple-windows-groups-and-these-groups-have-logins-created-in-sql-with-different-default-databases-and-languages.aspx

I'll post back findings but I find the above really interesting since in our example there are other databases on the server with AD groups added which this user will have access to and language for these is set to British.

Fingers crossed and thanks for your time.

Answer 5

I am pretty sure that the Default Language is the default language of the individual login.  It is not selected from one of the group(s) that the login might be a part of.  (If many groups, which one would win?)

A login can have a specific language specified.  I am unsure if the client machine's locale chooses the language setting in SQL Server, but I have seen different language problems on the forums before that boiled down to the language setting used by the connection. 

The process's language setting in turn controls how it will interpret dates.

So, check what language the person is using for connections that work and connections that do not work. 

You can run the following to find out which language is in use.

SELECT @@language, @@langid

RLF

Answer 6

Russell.

That is the issue though. We use AD groups for security, they don't have a user specific login. As stated, if I did add a user specific login then all would be fixed. That blog link talks about how groups are used to pick up a default language. However, it can't be guaranteed as to which one would win!

I've now confirmed this by copying the user. Slowly removing other AD groups and this indeed was to blame.

That select was also used, and regardless reported the same for me (a working user) and a non working user so again everything looked fine in terms of config. The only difference was the different groups they also had. Basically removing and cleaning the security of the groups got it working! (Server contains 20+ databases and even more group security).

I'm happy as it now makes sense to me and it's working for all users. Thank you for all replies, time and effort as usual.

Answer 7

Thanks.  I had missed your last post when I replied. (Got distracted before hitting submit.)  But I have learned something in the process.

RLF