0

Besides the sa user in the SQL server, I created a new user (called test) which only has read permissions. The user was created via MSSMS, with the following properties:

  • Sql server authentication
  • Enforce password policy
  • Enforce password expiration
  • Server Roles: public
  • User Mapping: db_datareader and public for master and test_db databases. test_db is the database that I need to access
  • Status: Permission to connect to database engine -> Grant. Login -> Enabled. Sql server authentication -> Login is locked out is unchecked and greyed out

The user exists in the Logins list, and also in the syslogins table. I made an exception for my pc ip in the server firewall.

I am able to connect with sql server using this user in MSSMS. However, if I try to connect remotely to sql server with this user (ex. from my pc, using software such as navicat), I get the following error:

28000 - [SQL Server] Login failed for user test

Looking at the logs in the event viewer, the actual error is:

Login failed for user 'test' reason: could not find a login matching the name provided.

Also, I am able to connect remotely using software such as navicat with the sa user.

I tried creating another user with the same properties and I get the same error. Also, since this is a live server, restarting it is out of question.

  • 1
    State 5 really indicates the login doesn't exist. Are you connecting to the right server (or instance) and have you double checked for typo's? – Tom V May 2 '18 at 12:47
  • Check the properties for the database user. Towards the top, does it say SQL User with Login or without Login. If it's without login, it's either because the login doesn't exist or possibly because the SID's are out of sync. – Shooter McGavin May 3 '18 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.