August 16, 2017 at 2:05 am
Hello. I can not connect from SSMS using sql users from a computer with windows 10 (let's say computer X) to a server with SQL Server 2014. I already have tried (using the same sql server instance):
- connect from computer X with windows login - OK;
- verify SQL authentication - it's set SQL Server and Windows Authentication - OK;
- connect from other computers with windows users - OK;
- connect from other computers with sql users - OK;
- verify sql user password - OK;
- try with other sql user from computer X - login failed;
- verify server error log - Alert '18456 - Login failed for user' has occurred (error logs) and Login failed for user 'rrr'. Reason: Password did not match that for the login provided. - Error: 18456, Severity: 14, State: 8.
- install SSMS 2014 and SSMS 2012 - same error with sql user;
- telnet from computer X to SQL Server on 1433 - OK;
- try with sql server name - same results;
- try with sql server IP - same results;
- connect from computer X with sql user using sqlcmd (from cmd) - OK
I can't understand why is working from sqlcmd and from SSMS sends error (login failed). SQLCMD uses something else (port, protocol, DNS, etc...)?
Thank you!
August 16, 2017 at 2:34 am
What are the error messages in the SQL Server's logs? When using sqlcmd and SSMS are you using the same credentials? Same User? Both Integrated Security/SQL Login? It might be worth setting your logs (even tempoaryily) to log successful and unsuccessful logins, then you can double check the successful and failed logins are connecting under the same login.
When using SSMS/SQLCMD are you defining a default database in either connection? Does that login have a mapped user to that database?
EDIT: Didn't see this: "verify server error log - Alert '18456 - Login failed for user' has occurred (error logs) and Login failed for user 'rrr'. Reason: Password did not match that for the login provided."
This tells you the answer, the password you're supplying for the SQL Login is incorrect. If you are connecting successfully via SQLCMD, then you are supplying a different password to the one you are using in SSMS.
Obfuscate the password, but what is the command you are using the connect via SQLCMD?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 8:37 am
- the user default database is master;
- SSMS and sqlcmd used with the same credentials, same user (sql user);
- connection without default database (SSMS and sqlcmd);
- sqlcmd command: sqlcmd -S server_name -U user -P "pass";
Any ideas?
August 16, 2017 at 8:53 am
l.ambrozie - Wednesday, August 16, 2017 8:37 AM- the user default database is master;
- SSMS and sqlcmd used with the same credentials, same user (sql user);
- connection without default database (SSMS and sqlcmd);
- sqlcmd command: sqlcmd -S server_name -U user -P "pass";Any ideas?
Please share what the logs suggest .
August 16, 2017 at 11:35 am
l.ambrozie - Wednesday, August 16, 2017 8:37 AM- the user default database is master;
- SSMS and sqlcmd used with the same credentials, same user (sql user);
- connection without default database (SSMS and sqlcmd);
- sqlcmd command: sqlcmd -S server_name -U user -P "pass";Any ideas?
Did you actually use quotes on the password?
August 18, 2017 at 6:43 am
Errors:
Alert '18456 - Login failed for user' has occurred (error logs)
and
Login failed for user 'rrr'. Reason: Password did not match that for the login provided. - Error: 18456, Severity: 14, State: 8.
Yes, I used quotes on the password when I used sqlcmd and it worked. With SSMS I did not use.
August 18, 2017 at 6:53 am
You know, the more I read this the more I think that it is a wrong login or wrong password issue. I ran into this personally the other day when I kept trying to type a lower case L in my password for a website where it was actually supposed to be a 1 (as in the number one). I have a random password generator and for some reason, I just couldn't read the number correctly and kept mistaking it for l.
Try changing the user password on that particular SQL server to the password you're using. See if the login works then.
August 18, 2017 at 7:10 am
Brandie Tarvin - Friday, August 18, 2017 6:53 AMYou know, the more I read this the more I think that it is a wrong login or wrong password issue. I ran into this personally the other day when I kept trying to type a lower case L in my password for a website where it was actually supposed to be a 1 (as in the number one). I have a random password generator and for some reason, I just couldn't read the number correctly and kept mistaking it for l.Try changing the user password on that particular SQL server to the password you're using. See if the login works then.
Agreed.
Something is inherently different with the way you are trying to connect via SSMS and sqlcmd. Although you're saying that you are connecting to the same server, using the same username and the same password, and connection settings, the fact of the matter is they are different. The problem for us is that isn't necessarily easy for us to work out for you. Something is inherently wrong in the connection you are making attempting from SSMS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply