Login to database failed, but login to database was granted

See previous post with WCF connection working some times for additional info.

I'm checking the server log and my attempt to log into one database works. When I try to login to the second database I get this logged:

Login failed for user 'xxx' (Client {my ip address} )

Error: 18456, Severity: 14, State: 16

When I go to the database server and pull up the user, both the original and second database are checked in the User Mappings at the Server Users level. If I set master to the intial database, the first database, or the second database as the initial catalog....I can access the first database but not the second.

No I have not found one word written for SQL Server 2005 that I could find that states THIS IS HOW YOU ENABLE LOGIN FOR A DATABASE, however since I've not found anything different between the DB that works and the one that doesn't then I must assume I've done things right.

The only thing that is odd on the server is that there are two different logins created with different names but for the same user. I've made sure that database 2 is checked for both user records. I tried to purge the duplicate but I get a DROP failure and don't have the time to try and interpret SQL Servers' obscure error messages.

I need to get access before 3PM EST and would love any help on this brick wall ASAP. Thanks.

I recommend reading Laurentiu’s article on SQL Server security concepts (http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx) and consult BOL for CREATE LOGIN and CREATE USER.

Please let us know if you have further questions, we will be glad to help.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Okay, I read the article and except for the need to differentiate between a login and user, the discussion was at such a generic, global level that it still did not answer my original question.

First I am using the management console. I added my Windows Security ID to the server. It exists.

Under the LoginProperties / Users Mappings tab I have all my databases listed and both Guide and Talker are checked.

Under Guide database / Users a user exists with the Windows Security ID.

Under Talker database / Users a user exists with the Windows Security ID.

First: I assume that by creating the Windows login at the server level I'm creating a Login. Correct?

Second: By checking the databases under the LoginProperties / Users Mappings tab I'm defining the databases that the user can login to. Correct?

Third: If your answer is YES to both questions, then why is my login for Talker failing getting reported as no login established for the database?!!!!!

|||

Okay. I finally found my answer.

I changed my connection strings to have my service access the database on my local machine. The behaviour was exactly the same. We began talking through the connections that had to occur from service, to server, to SQL, to the database. And we could eliminate everything but the connection string.

Looking at the connection string, I found that the database name was misspelled. So I was NOT trying to login to a database that I was not given access for....I was trying to login to a database that does not exist.

Now at the core process within SQL Server it seems to me that this is a state that could be easily recognized and logged......instead of making it sound like I didn't setup security correctly. In this case the real database name that I called Talker had three m's instead of 2. These kinds of errors the mind does not see...it automatically corrects for the extra 'm' making it difficult to differentiate and locate. The error log would really help point out that I've specified a database that does not exist and would immediately point me to the connection string.