domain\user cannot log on SQL server 2005

Hi All,

I have an issue to connect to SQL 2005. My user is also declared in SQL 2005 as sysadmin.

C:\>ECHO %USERDOMAIN%\%USERNAME%
CMVAL\damien

C:\>sqlcmd -S CM-DB
1> quit

C:\>sqlcmd -S CM-DB -U "CMVAL\damien" -P "ThePassword"
Msg 18456, Level 14, State 1, Server CM-DB, Line 1
Login failed for user 'CMVAL\damien'.

C:\>

Seems that the system reject the domain I've provided. Some of my service failed to log to SQL Server due to this issue.

Thanks for your help.

Damien

By default, sqlcmd uses Windows Authentication. If you are using windows system, then you automatically get authenticated through windows authentication mode by not using -U options.

While if you do use -U, then it means you will be authenticated using Sql login mode, you need to use the sql server logins, not windows logins.

You may refer to http://msdn2.microsoft.com/en-us/library/ms165702.aspx for more information.

|||

Thanks LY.J for your answer.

In fact, my original issue is that I have a lot of this error in the event viewer :

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 13/12/2006
Time: 09:42:29
User: CMVAL\damien
Computer: CM-DB
Description:
Login failed for user 'CMVAL\damien'. [CLIENT: 10.194.94.8]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 06 00 00 00 43 00 4d 00 ....C.M.
0010: 2d 00 44 00 42 00 00 00 -.D.B...
0018: 07 00 00 00 6d 00 61 00 ....m.a.
0020: 73 00 74 00 65 00 72 00 s.t.e.r.
0028: 00 00 ..

Then I suppose that if I can't connect to the DB with sqlcmd providing -U "CMVAL\damien", my service could not either connect.
But according to what you said, it seems that the issue is elsewhere.

Do you have an idea on how I can investigate on it?

Thanks
Damien

|||

Has the account CMVAL\damien expired?

This happens when you create an account specifically for a service and forget to set the 'does not expire' attribute.

If so, change the password, and logoff/logon

Also I had a case where a user could not be authenticated to SQL because he used a German keyboard which typed 'y' in some applications and 'z' in other applications... :)

|||

Try running a command prompt under the damien credentials, then connect using -E option. Also, have a look at the SQL Server ERRORLOG file and let us know what is the state of the error printed there (it will be different from the one printed by the client tool).

Thanks
Laurentiu