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
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.
Agreed, not sure why a domain user name is being attempted if SQL auth is intended.
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.
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
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. :-)
The OP reports it's SQL Server 2000 - ISTR that that only uses one value for State.
