question

aRookieBIdev avatar image
aRookieBIdev asked

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
sqlsql-server-2000administrationloginuser
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand avatar image AaronBertrand commented ·
Agreed, not sure why a domain user name is being attempted if SQL auth is intended.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand avatar image
AaronBertrand answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand avatar image AaronBertrand commented ·
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. :-)
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
The OP reports it's SQL Server 2000 - ISTR that that only uses one value for State.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.