Recently on a popular SQL Server forum as well as commonly during my trainings people ask about how to get additional security information about logins. Most often the error they see is similar to the one below. Where a connection keeps failing for any number of reasons and the DBA is trying to identify from where the connection is originating.
Login failed for user ‘reader’. Reason: Password did not match that for the login provided. [CLIENT: XXXX.XXX.XX.XX]
As you can see from the above message you get to know the Login name as well as the IP address from which the connection originated. In most cases at the data tier level we use static IPs so it should be easy to identify the host machine from which the connection is being established.
A common mistake made by L1 DBAs is to assume the next logical step is to create an Audit. The problem with that is its overkill for what they are trying to achieve. An Audit has the ability to shut down the server is not configured properly and once created it requires maintenance. If the DBA is looking to troubleshoot just a single issue then the better approach would be to use profiler and filter for the Login Name in question.
As shown below
If the DBA is looking to perform a more robust alerting mechanism or suspects a Security breach then it would require the capabilities of a Server level Audit. In which case you get roughly the same level for information but also the ability to perform corrective action where foul play is suspected.
Knowing when to use the right feature is important since they have impact on all other aspects of sql server too.
Please Consider Subscribing
