January 8, 2008 at 9:39 am
I get "Login failed for user" (and no other warnings or errors) when a user who is part of a windows access group tries to connect to a database server. If I drop and readd the windows group to the database server security the problem goes away. If I add the individual user name to the database server security the problem goes away.
Has anyone else seen this and resolve what is going on?
This has now happened on 2 of my 2005 sp2 servers - both 32 and 64 bit versions.
😎
July 14, 2009 at 2:09 pm
Recently we had the same thing happen in our SQL 2005 Cluster. If you attempt to connect to this instance via SSMS with windows auth, you get this error:
Failed to connect to server xxx. (Microsoft.SqlServer.ConnectionInfo)
Additional infomation:
Cannot open user default database. Login Failed.
Login failed for user 'domain\user'. (Micorsoft SQL Server, Error: 4064)
Here is the error using SQLCMD
C:\Users\user>sqlcmd -S xxx
Msg 4064, Level 11, State 1, Server xxx, Line 1
Cannot open user default database. Login failed.
Msg 18456, Level 14, State 1, Server xxx, Line 1
Login failed for user 'domain\user'.
Keep in mind this is happening to all SQL logins that are based on a windows group, new or existing. Even if you change the default DB this still occurs
If you create a SQL login based on a specific windows user using the same permissions as the group it works just fine.
Anyone have any thoughts?
July 15, 2009 at 1:59 pm
Was it working before? If so, did anything like a domain switch or anything of that sort occur?
K. Brian Kelley
@kbriankelley
July 15, 2009 at 4:14 pm
Yes everything was working fine until a couple days ago when an upgrade was applied to Symantec BackupExec, which has its database (BEDB) on this SQL instance.
I am pretty sure the update has something to do with this, but who knows, and no we have not contacted Symantec just yet. Once we have an idea of what is going on we will proceed as necessary.
July 15, 2009 at 5:31 pm
Found the problem!!
Somehow the "Builtin\Users" login had its default database set to "BE_DLO" which was the old BackupExec DB...don't ask.
When we performed the BackupExec upgrade it created a new DB called "BEDB", migrated the data and dropped "BE_DLO".
Who knows why this only effected windows user groups and not the specific users, but changing the default database on "Builtin\Users" fixed everything.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply