Authentication Issue for Windows Groups

Question

Authentication Issue for Windows Groups

Balaji-485243

SSChasing Mays

Points: 613
More actions
March 4, 2009 at 6:50 am

#128775

Hi All,
We are getting the following error when we try to connect with SQL Server 2005 SP2.
"Cannot open user default database. Login failed.
Login failed for user ' '. (Microsoft SQL Server, Error: 4064)"
We are getting this issue only when we try to access SQL Server through "Windows User Groups". If we add the individual login accounts under Security tab then we are able to connect with SQL Server without any issues.
We changed the default database name to "Master" for all the Windows User Groups, but still we are getting the same issue.
Please provide your suggestions to fix this issue...
Thanks,
Balaji.

Viewing 9 posts - 1 through 8 (of 8 total)

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

did you reboot the user's PC or log off and back on after adding them to the group? when you are log on you get a logon token or ticket, forget which one. it has all your group permissions. if you add a user to a group you have to wait for AD replication to replicate the data to the local DC and then have the user log off and back on for the new group membership to take effect. we just tell people to reboot because half the users think we mean to close and reopen Outlook or SSMS

Balaji-485243 SSChasing Mays Points: 613 More actions · Answer 2

Hi,

Thanks for the response. Actually the users or user group was not newly added. It was already exisitng and it was working fine till 2 days back. We got into this issue recently and we dont have any clue why it is happening. Am sure that there is Windows Updates or SQL updtes are run on the Server.

Also, before connecting to SQL Server through SSMS we try to change the "Connect to Database" option to master and then we were able to connect to the server successfully. However after opening a New Query Window, the list of databases are not listed in the drop down box. Instead we are getting the message "Server is not available".

Please help us to get rid of this issue.

Thanks,

Balaji.

rathinakumarc Newbie Points: 6 More actions · Answer 3

Hey Balaji,

Did you checked the windows account is enabled in the SQL 2005? I guess that should be the issue.

Regards

Rathina

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

do the logins have public permissions to all the databases? we always leave the public default and add the extra r/w permissions as needed

Balaji-485243 SSChasing Mays Points: 613 More actions · Answer 5

Rathina,

I have checked the authentication mode, both Windows and SQL Server Authentication mode is enabled. Also we are able to connect with the server if we add the domain accounts individually. This problem occurs only when we try to connect using domain groups.

Also am sure that all the domain groups has public and dbo permissions. But still we are fighting with this authentication issue.

Thanks,

Balaji.

rathinakumarc Newbie Points: 6 More actions · Answer 6

Balaji,

What i asked is that when the group was added to SQL there should be some entry for the group in the SQL. Just check whether that entry is enabled or disabled

Regards

Rathina

Balaji-485243 SSChasing Mays Points: 613 More actions · Answer 7

Rathina,

Can you please be bit elaborate with your point. Am not able to understand about the SQL entry which you are saying...

Thanks,

Balaji.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 8

Right click the login name nt and see if it not set to disabled.........

Also if that GROUP has many levels i.e

DOMAIN\SALESGROUP

DOMAIN\SALES perhaps SALES id disabled.

Also the database has to be selected