Error 18456: Login failed for user 'DOMAIN\user'

[Christoffer]

Hi,

I've just installed SQL Server 2005 Beta 2 and added Active Directory
account to our SQL Server (under Database/Security/Logins). However, when I
try to login using the Windows Authentication, I get this error message:

Login failed for user 'DOMAIN\user'. (Microsoft SQL Server, Error: 18456)

where DOMAIN\user is really my domain-account, which I rather not publish.
When I try to login using 'sa' and SQL Server Authentication there is no
problem.

I've tried to find out what error 18456 is but no luck. Could anyone help me
please?

Cheers,
Chris

[Christoffer]

I take it that either no one has ever heard of this error or that is so
common that its in every FAQ. In the latter case, could someone please point
me to a FAQ with a solution...

Cheers,
Chris

"Christoffer" <chris...@nospam.com> wrote in message
news:%23alyI3z...@TK2MSFTNGP11.phx.gbl...

[Sue Hoegemeier]

Error 18456 is "login failed for user xxx"...the error you
are getting. You have something incorrect with adding the
windows account to the logins or the permissions you have
set for the login. Try deleting the login and then try
adding it again. Make sure the login has access to whatever
database you have set up as the default database.

-Sue

[Christoffer]

Hello

I deleted and re-added the account and I also gave it access to every
database with db_owner on every database. I also made the account a member
of the sysadmin role. Still no luck :(

The account is in a Active Directory, could this be the problem?

Cheers,
Chris

"Sue Hoegemeier" <Su...@nomail.please> wrote in message
news:9rlpj0982j03evgpt...@4ax.com...

[Sue Hoegemeier]

Are you a member of a group that has been denied access to
the server? Did you try another Windows account and test
with that?

-Sue

[Christoffer]

Hello,

I think I have found the problem. When I add my login (DOMAIN\USER) using
the Microsoft SQL Server Management Studio (2005 Beta 2), the property
"Server Access" is set to "Via Group Membership". If I change this to
"Permit" I can login!

My question is: What is "Via Group Membership" and is this the best practice
when adding Windows logins?

Thanks,
Chris

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:90psj0p1dethn063m...@4ax.com...

[Sue Hoegemeier]

Via group membership means the login was has access to SQL
Server through their membership in a windows group.
Managing logins through windows group can definitely make
the management of logins much easier.

-Sue

[Christoffer]

I see, the thing is, there is no added Windows groups login in the SQL
Server. I tried adding DOMAIN\Administrators but I got the errore message:

"The login 'DOMAIN\Administrators' does not exist. (Microsoft SQL Server,
Error: 15007)"

But I'm sure it exists since I browsed to it using the Active Directory. Is
there some limitation to adding the DOMAIN\Administrators group? Should I
try to set up yet another group for database access?

Thanks for your help!
/Chris

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:8pttj05a5jihmdoqe...@4ax.com...

[Sue Hoegemeier]

Chris,
I'm not real clear on when you are receiving this error. It doesn't
look like something you would get when you actually try to add the
login. Try add the account using Query Analyzer instead and executing
sp_grantlogin. The help file has more information on sp_grantlogin.
Whether you set up another group or not depends upon how you are going
to design your security model. Using Windows groups certainly can be
easier but whether that works for you or not depends on how your
windows groups are set up, how well they reflect the access, security
needs of the applications that will use the databases on the server,
etc.

-Sue

[Christoffer]

Hi,

When I use the Microsoft SQL Server Management Studio, which is in SQL
Server 2005 and is equivalent to Enterprise Manager for SQL Server 2000, to
add a Windows login, the login's Server access property is set to "Via group
membership".

If I use the stored procedures sp_grantlogin on SQL Server 2005, the login's
Server access property is set to "Permit".
If I use the old Enterprise Manager for SQL Server 2000, the login's Server
access property is set to "Permit".

Why does the SQL Server Management Studio (2005) set the Server access
property to "Via group membership" instead of "Permit". I know this is a
beta and an answer might not be possible to give...

Thanks for helping me out, I no longer experience the 18456 error, this is
just out of curiousity :)
/Chris

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:iq7uj0tv8g78vlco9...@4ax.com...

[Sue Hoegemeier]

I'm not sure Chris. I've run 2005 on a stand alone box and I never
noticed anything like that. You could try the beta newsgroup - you can
get more information on it at:
http://www.aspfaq.com/sql2005/show.asp?id=1

-Sue

On Thu, 9 Sep 2004 11:49:20 +0200, "Christoffer"

[===steve pdx===]

I'm a new DBA for my company. after I took over the existing servers and
started auditing all accouts. i noticed that some nt accouts has server
access "Via group membership". i don't know how they were created that way
because i have never been able to duplicate the result using EM to add a NT
account. (it's always 'permit'). Does anyone know how to duplicate that "Via
group membership".
thru sql2k EM? We use sql2k on nt5 box.

thank you.

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:9vv3k0hfuj5r7uapp...@4ax.com...

[Sue Hoegemeier]

Via Group Membership for a login has to do with how that
login was added. One scenario is that a windows account can
be initially added directly to a role (vs. using
sp_grantlogin then sp_addsrvrolemember).
To duplicate the via group membership:
1. Create a windows account
2. In Query Analyzer, add that account to one of the server
roles such as processadmin using sp_addsrvrolemember - e.g.
sp_addsrvrolemember 'Domain\WindowsAccount', 'processadmin'

-Sue

[Christoffer]

Thanks for all your help Sue!

/Chris

"Sue Hoegemeier" <Su...@nomail.please> wrote in message

news:9vv3k0hfuj5r7uapp...@4ax.com...