x

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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 51 56 61

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

3 answers: sort oldest
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

Tim gravatar image

Tim
36.4k 38 41 139

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 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

ThomasRushton gravatar image

ThomasRushton ♦
34k 18 20 44

(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:

[http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx][1]

[1]: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
more ▼

answered Jul 02, 2011 at 07:39 AM

AaronBertrand gravatar image

AaronBertrand
905 1 3

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
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x729
x476
x126
x85
x31

asked: Jul 01, 2011 at 11:01 PM

Seen: 3140 times

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