SQL Server Login access deny

Hi ,

I have created a sql login , but when i try to login using the id into the server i get the error "Login failed for user domain\username" error id is 18456 When i queried the Syslogin table i found the #access is 0 for the particular user and i am not able to manually change it. Can some one help me why this is occurring ?

Thanks in advance. Nk

more ▼

asked Jul 01, 2011 at 11:01 PM in Default

avatar image

2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It appears from the message that you are trying to login with a Windows user. When you created the user in SQL did you create a Windows login account domain\userid or did you specify SQL Account? It might be as simple as you are trying to login specifying Windows authentication but using the credentials of a SQL Account. As @thomasrushton mentioned, to use both types of authentication your server has to be in mixed mode. More details about your situation would be very valuable in helping us fully assist you.

more ▼

answered Jul 02, 2011 at 05:26 AM

avatar image

40.9k 39 95 168

Agreed, not sure why a domain user name is being attempted if SQL auth is intended.

Jul 02, 2011 at 08:29 AM AaronBertrand
(comments are locked)
10|1200 characters needed characters left

What is the state accompanying error 18456? The text of the message the end user receives is often intentionally obscure for security reasons. But the state mentioned in the SQL Server error log will help shed more light. See here for a list of potential causes, depending on the state you see:


more ▼

answered Jul 02, 2011 at 07:39 AM

avatar image

905 3 6

The OP reports it's SQL Server 2000 - ISTR that that only uses one value for State.

Jul 02, 2011 at 10:25 AM ThomasRushton ♦♦

Missed the tag. The OP then needs to validate a few things:

(1) that if they set up a SQL auth login, they're attempting to log in using SQL auth (not Windows auth as implied by "domainusername").

(2) that they are specifying a valid database in the connection string, or at least that the user's specified default database is valid.

(3) that they have entered the password correctly, and that if SQL Server is set to follow Windows password rules, that the password follows those rules, hasn't expired etc.

They should also try connecting via Management Studio and through code to see if any variance in error messages lend clue.

And finally, they should consider moving off of an 11-year-old platform that is no longer under any kind of support. :-)

Jul 03, 2011 at 08:39 AM AaronBertrand
(comments are locked)
10|1200 characters needed characters left

What sort of user ID is it, a sql user or a domain account? If it's a sql user, is sql server set up to allow "mixed authentication"?

What's the full error message? There's a stack of different causes for error 18456 - the full text may provide more information.

more ▼

answered Jul 02, 2011 at 02:03 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 01, 2011 at 11:01 PM

Seen: 4654 times

Last Updated: Jul 01, 2011 at 11:28 PM

Copyright 2018 Redgate Software. Privacy Policy