Error 15401 during CREATE LOGIN on case sensitive database

Question

Error 15401 during CREATE LOGIN on case sensitive database

Jason-407717

Old Hand

Points: 318
More actions
November 7, 2007 at 4:17 pm

#119374

We're attempting to automate the creation of logins in one of our applications. When we do, we get the following error:
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'MYDOMAIN\ALLUPPER' not found. Check the name again.[/color]
Before you jump to conclusions about matching the way the name was entered exactly in Active Directory, let me re-create the scenario for you:
1. Create a login in Active Directory using all upper case letters (e.g. MYDOMAIN\ALLUPPER)
2. Start SQL Server Management Studio and open a new query window on a case sensitive database.
3. Issue the command: CREATE LOGIN [MYDOMAIN\allupper] FROM WINDOWS
Note: This command should succeed, despite not matching the case in AD exactly.
4. Issue the command: DROP LOGIN [MYDOMAIN\allupper]
5. Issue the command: CREATE LOGIN [MYDOMAIN\ALLUPPER] FROM WINDOWS
Step 5 fails with the error:
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'ORBITCITY\ALLUPPER' not found. Check the name again.
... in fact, any other combination of case fails except the original one ("[MYDOMAIN\allupper]" in my example above). It appears that SQL Server is caching the credentials, including the case, somewhere. I've checked a bunch of the "sys" tables, I've checked to see if Kerberos is caching it... I'm not sure where else to check.
This may seem like a rare case, but if the client ever adds the user themselves by manually typing in the name, then subsequently removes them, they will need to find the exact same case they used the first time around to ever enter this user again.
Can anyone tell me how to get around this? My application prompts the user for a user name, and if it doesn't exist in the database, we would like to create it for them... however, until I can get this issue resolved, we may be left with asking the user to create it themselves.
Thanks in advance...
Jason

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I don't have a setup like your to test against, but - is your server or just database case sensitive?

I believe you'd be able to see the correct case for an AD login by using xp_logininfo. Tha ctual server login would I figure be determined by the master database whereas the database login would be handles by the database - thus you could have different login names to user names - which might not be a good thing. Other than that I can't offer much more advice, sorry.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jason-407717 Old Hand Points: 318 More actions · Answer 2

Only the database is case sensitive.. FYI:

xp_logininfo 'MYDOMAIN\ALLUPPER' returns the following:

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'MYDOMAIN\ALLUPPER', error code 0xffff0002.

xp_logininfo 'MYDOMAIN\allupper' returns successfully, despite the user being added to AD in upper case... it still seems to be cached somewhere from the original create command.

Thanks for the response... I'll keep digging.

Jason

HLogic SSC-Addicted Points: 435 More actions · Answer 3

Take a look here: http://tp.its.yale.edu/pipermail/cas/2005-July/001335.html

From the CREATE LOGIN statements it appears that neither your AD nor master db is case-sensitive. If you are not creating an associated user in the case-sensitive user db, there should be no issue but the above URL points to another interesting artifact of AD/Kerberos - caching credentials until password change.

You will need to be very careful about case when the server (master, tempdb) collation is insensitive and the user db is case sensitive. You may want to consider building the entire SQL server (instance) as the collation required by the user db.

HTH,

Art

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

I don't really know how to test this properly - are you adding the login in the context of master database - which should be case insensitive rather than the user database which I guess would be.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

lorimerp Valued Member Points: 53 More actions · Answer 5

Check the length of your AD name. I've seen a problem where the AD name was shortened and it looks like SQL uses the login name not the actual AD name. Example DOMAIN\ReallyReallyReallyLongName would be DOMAIN\ReallyReallyReallyLo for the CREATE Login.