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.