Sql server authentication fails using SSMS but works when using sqlcmd

Question

I have my SQLServer 2005 Standard instance configured for mixed mode fine. When using SSMS, none of the sql server logins seem to work. Just "Login failed for user '..'" message. But when I use SQLCMD, it works fine. The behavior is not just for one specific login account. All sql server logins including 'sa' fail when tried via SSMS. Windows logins work fine.

Any ideas?

EDIT1: When connecting via SQLCMD, I do not specify the -S option. Just the -U and -P Whereas in SSMS, Server name defaults to the machine name. In SSMS the server seems to be registered as say xxxxN. When I connect using windows authentication, my @@servername returns xxxx_N. When I try to use the xxxx_N in SSMS it fails to connect altogether.

Edit2: When trying via SQLCMD if I give the -S option as xxxxN, it works fine too.

Edit3:

My SQLCMD:

C:...>SQLCMD -U sa -P Password (works)

C:...>SQLCMD -S xxxxN -U sa -P Password (works)

C:...>SQLCMD -U test -P Password (works)

C:...>SQLCMD -S xxxxN -U test -P Password (works)

in SSMS:

ServerType: Database Engine

ServerName: xxxxN

AUthentication: Windows Authentication (works)

ServerType: Database Engine

ServerName: xxxxN

AUthentication: Sql Server Authentication

Login: sa

Password: Password (fail)

ServerType: Database Engine

ServerName: xxxxN

AUthentication: Sql Server Authentication

Login: test

Password: Password (fail)

EDIT 4(Answer)

ODBC connections using sql accounts seem to work fine. SSMS connections from remote machines work fine also. This HAS TO BE an issue with local SSMS install. I believe a reinstall would fix this..

Can you show your sqlcmd parameters? (Substituting sensitive info of course, but a complete command-line call) — granadaCoder
– granadaCoder, Commented Dec 4, 2013 at 21:56
Does the SQL Server Error Log contains any more information relating to the failed logins? — JodyT
– JodyT, Commented Dec 4, 2013 at 22:04
Connect to the instance using sqlcmd and the sa login then execute select * from sys.dm_exec_connections, what protocol does it say you're using? — JodyT
– JodyT, Commented Dec 4, 2013 at 22:36

granadaCoder · Accepted Answer · 2013-12-05 14:09:44Z

1

Your server/instance name is not consistent between the two.

MyMachineName\Express

is the "most common" instance name.

But yours may vary.

Since sqlcmd "works".

Run sqlcmd, and pass it this command

select  [MyServerAndMaybeInstanceName] = @@servername

Then you will know what to put into SSMS ! (ta da!)

EDIT:

You say in sqlcmd, you give it

-U and -P

thus you're using Sql Authentication. (and not windows authentication)

Are you providing Sql Authentication credentials when trying through SSMS ?

I see you said "All sql server logins including 'sa' " thus you are trying Sql Authentication credentials. Can you just verify you've tried some Sql Authentication creditials, maybe something besides sa.

EDIT:

Here is a guess, but I hit it one time, a long time ago.

In the SSMS connection box, click "Connection Properties"
Find "Network".
And pick TCP/IP as the network protocol.

especially if the machine is a remote machine.

EDIT:

Here is another poor man's test.

Go to Control Panel /Admin Tools / Data Source (ODBC) / System DSN. And try to add a connection there.

You can also play with the protocols there (named-pipes vs tcp/ip, etc) with the "Client Configuration" button in the Wizard.

edited Dec 5, 2013 at 14:09

answered Dec 4, 2013 at 22:03

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brian Over a year ago

That's what I tried to explain in my edit.. when I put in the exact string that @@servername gives me, SSMS attempt fails at right at connection stage itself. In teh list of registered servers in SSMS, it shows up as xxxxN where as @@servername returns xxxx_N

Brian Over a year ago

Ofcourse I am using Sql Authentication Credentials. Even my 'sa' account doesn't work. I created some new sql accounts, none of them work.

Brian Over a year ago

I did try other accounts as well. I created a new sql account and tried to use it via SSMS (failed) but when I use it using SQLCMD it worked.

Brian Over a year ago

using sql server authentication, I tried all available network protocol options (default,shared memory, tcp/ip, named pipes). None of them worked

Brian Over a year ago

I tried the odbc route, it worked fine. I think something is the issue with my SSMS. SSMS from a different machine works fine!!!! Definitely it is SSMS issue on the server

|

Collectives™ on Stack Overflow

Sql server authentication fails using SSMS but works when using sqlcmd

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related