A Login exception is a failed attempt to log in to the database instance during a process of connection to the database. When a login attempt fails, an error message arises.
This alert means that a user tried to connect to SQL and received a response indicating the user does not have access via name/ password authentication or that an application tried to connect to an SQL database and the credentials are invalid. The user’s access is validated by the Windows log-on process. A server’s credentials are contained in a record containing the authentication information needed to connect to a resource outside of SQL Server (for example- network shares, SMTP, and more). A single credential can be mapped to multiple SQL Server logins. However, an SQL Server login can be mapped to only one credential.
In the case of individual users with needs for wider access, credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. This is primarily used to execute code in assemblies with a special permission set. Credentials can also be used when a user needs access to a domain resource, such as a file location, to store a backup.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Access was denied to the SQL server.
Impact: Medium
Frequent login exceptions can lead to poor user experience and longer response times. In addition, it can cause resource overload.
In the case of multiple access attempts failing, this could be a sign of an attack or unauthorized access attempts.
Expected behavior :
This alert indicates an abnormal access attempt and should be investigated. Login exceptions should not happen.
Possible causes:
1- Invalid Credentials. Priority: High
When the username or password provided for authentication is incorrect, the database instance will reject the login attempt.
This might also happen when the user name/password has changed, and the user is not aware of the correct new credentials.
Problem identification:
Identify error messages related to incorrect user credentials.
- Look for error messages related to login failures in the SQL Server error logs or event logs. The error message will typically include details such as the login name, reason for failure, and error code. You should focus on reviewing repeated failed login attempts from the same user or application. It might be hard to track.
- Identify the login exceptions using tracking tools such as SQL Server Profiler. Set up a trace to capture login-related events and examine it for patterns of failed logins. This task should be done by a DBA who knows how to utilize these results correctly. However, it might take time, and you won’t be provided with historical data.
- You should test the login credentials (username and password) manually using SQL Server Management Studio.
Recommended action :
Check the credentials of the application trying to connect to SQL and review the application’s code that handles database connections.
Check if the password associated with the login has expired. Otherwise, check if that user has specific credentials.
Ensure that the user’s Windows account is valid and active, or otherwise, check if the user has access to Windows but not to SQL.
2- The account has been locked. Priority: Low
The user’s account might be locked when the user has tried doing many login attempts without success. Once a user attempts to log in with incorrect credentials and reaches the lockout threshold, if exists, the account is locked. This security measure is designed to prevent possible attacks or unauthorized access attempts.
Problem identification:
Identify error messages of login exceptions containing details such as the account status (locked) and an error code.
- Look for error messages related to login failures in the SQL Server error logs or event logs. The error message will typically include details such as the login name, reason for failure, and error code. You should focus on reviewing failures related to locked users. It might be hard to track.
- Identify the login exceptions using tracking tools such as SQL Server Profiler. Set up a trace to capture login-related events and examine it for locked users’ accounts. This task should be done by an experienced DBA able to analyze these results correctly. It might be a lengthy process, and you won’t be provided with historical data.
- Test this issue manually and read the error message.
Recommended action :
Once the lockout period is over, the account is automatically unlocked, and the user can attempt to log in again. Otherwise, an administrator user can manually unlock the user’s account.
Configure proper lockout thresholds and duration that match your business needs and balance between security and usability.
3- Server Unreachable. Priority: Low
If the server hosting the database instance is down or facing network issues, remote login attempts to the database instance will probably fail.
This situation prevents clients from establishing a connection to the database server.
There might be a variety of reasons for an unreachable server or database instance, including shutdown, updates, network issues, and more.
Problem identification:
Identify if only remote logins are failing and have error messages mentioning the server’s unavailability.
- Review the server logs, for example, the event viewer, and verify that the server and instance are up and running. Look whether there was an event of unplanned shutdown.
- Review network errors to see if they exist.
- Look for error messages related to login failures in the SQL Server error logs or event logs. The error message will typically include details such as the login name and failure’s reason. You should focus on reviewing failures related to inaccessible servers or instances – this might be hard to track.
Recommended action :
This issue can disrupt application functionality and user experience. Make sure to schedule maintenance time and planned restarts on times of low user demand.
4- Permission Issues. Priority: High
There are cases when the user doesn’t have enough privileges to access a specific database or perform certain actions, and a login exception might occur.
Database administrators should plan users’ necessary permissions to objects or operations on specific databases based on business plans and needs.
There are several possible cases of insufficient access privileges: database, object, schema, or instance level.
Problem identification:
Identify error messages related to permission issues- to a database, schema, object, or instance levels, concurrent to login exception events.
- This permission issue would pass unknown unless reported by the user affected. An error message indicating that the user lacks the required permissions will pop up to the user.
- Identify the login exceptions using tracking tools such as SQL Server Profiler. Set up a trace to capture login-related events and examine permission violation events. This task should be done by an experienced DBA familiar with the correct analyses of the results correctly. However, it might take time, and you won’t receive historical data. In addition, you will have to follow it manually without knowing when to stop tracking.
Recommended action :
Examine the specific permissions required for the user or application to perform the desired actions and check if it can be approved according to your business rules. Grant the necessary permissions to the user – if it needs to be to a specific object, then the privilege should be granted to both basic login level and the specific object actions required.
After granting permissions, test the login by attempting to perform the actions that were previously causing the exception, also you should make sure that the user doesn’t have wider privileges than planned to be granted.
