Backstory: We have an app that connects to a SQL Server database using a clients app on a user's PC. We got the app working through normal channels (whitelisting machine IP, creating a login for the user, and granting necessary permissions to the user to the databases they needed access to). No issues so far.
Everything functionally was working app side but we then ran to an issue DB side where the machine itself was getting failed login attempts on SQL Server error logs (multiple errors a second):
Error: 18456, Severity: 14, State: 5. Login failed for user 'domain\computer name'. Reason: Could not find a login matching the name provided. [CLIENT: IP address].
The vendor suggested we match permissions of the user and the computer to the relevant databases, we did this and the errors went away.
Concern: Our concern now is that since we gave the computer itself permissions to the database, now anyone who sits at that computer will be able to access the database (computers are in a semi-public location is the reason for the concern). We have thought of a couple possible solutions here:
- Creating a 1-to-1 user/computer relationship where only a certain user can connect to the DB from that computer. This would be preferred but can't find information on if this is even possible.
- Or just taking the computer's permission away completely from the database and telling SQL Server to ignore the specific error we were getting hammered with from those PCs. This would also be an acceptable but not sure how to implement.
- The app functions fine with just the user having the permission to the database. We just need to do away with the computer's failing login attempts in our SQL Server error log. We are open to other ideas we may have not thought of.
We have tried finding a way to get a 1-to-1 user/computer relationship to a DB with no luck. Need to do more exploring on having SQL Server just ignore the errors. Our goal is to just have SQL Server not give us the following machine specific login error at all:
Error: 18456, Severity: 14, State: 5. Login failed for user 'domain\computer name'. Reason: Could not find a login matching the name provided. [CLIENT: IP address].
We would still need to see failed user login attempts. Since machines are on a separate subdomain from the users maybe that can be utilized to ignore errors coming from the machine sub-domain ex. users are on main domain PCs where we need the errors to go away are on sub-domain.main domain. Is there a way to just have SQL Server ignore errors coming from the sub-domain?
UPDATE: We actually might be good here. After probing the app team a little, They have access control on their end where only users they set up can access the app. If that is the case we don't have to worry about unauthorized access. Will update on Monday after they verify there is no other way around this (guest account or something) with the vendor.