CODEWITHSUNDEEP
sql-serversql-server-2005
Brian
Brian

Reputation: 1387

Sql server authentication fails using SSMS but works when using sqlcmd

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..

Upvotes: 0

Views: 5536

Answers (1)

granadaCoder
granadaCoder

Reputation: 27884

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.

Upvotes: 1

Related Questions