Enabling SQL login auditing will help you detect insider and outsider threats in time to protect your SQL databases against data breaches. These capabilities are included in SQL Server audit services, but this is not a very convenient solution because truly suspicious events get buried in the huge amount of noise data. Netwrix Auditor for SQL Server provides complete visibility into and control over changes and access events in SQL databases, including auditing of successful and failed logon attempts from Active Directory or locally.
It provides all the critical who-what-when-where details you need to streamline auditing of attempts to log on to the database or SQL Server Management Studio so you can minimize the risk of a security breach.
Improve this question. Add a comment. Active Oldest Votes. Improve this answer. And I see you have to restart the service as well. After further experimentation, it seems that this only gives me the Usernames of those who has connected, not the actual workstations.
CodeSlave - For that you'll need a profiler trace sorry for the confusion! Tony Sepia Tony Sepia 6 6 bronze badges. Jason You are correct, and I understand why you are saying this - it is exactly what the documentation suggests. This satisfies my need to view connection history, and I hope it helped the OP!
Sign up or log in Sign up using Google. Sign up using Facebook. This is how we login to SQL Server:. Improve this question. Andriy M Cute Bear Cute Bear 1 1 gold badge 1 1 silver badge 5 5 bronze badges. Add a comment. Active Oldest Votes. Improve this answer. If you want to setup a system that will track this going forward you can try DDL triggers, SQL Traces and more… The best way to do this would be to use extended events to capture logins so that you know precisely who is connecting to what.
Also, if luck favours you, you can try below steps: Loginto the instance. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. Therefore, for this setting to take effect, you'll need to restart the SQL Server service. Because it is a registry setting, the only easy way to set how to audit is through the GUI.
There isn't a way using T-SQL without using one of the extended stored procedures that hits the registry directly. A word about the various settings.
At the very least, you should be auditing failed logins on production systems. There may be cases where it is necessary to audit successful logins, but realize that auditing successful logins will likely generate a lot of events to sift through.
On extremely sensitive systems it is important to have this level of control. However, on less critical systems, you may be better served to only audit failed logins so you don't get overwhelmed with the number of events. If you have a log management solution, by all means audit both. Once you've turned on auditing, the next step is to be able to find the results of said auditing. The first place where the audit information is recorded is the SQL Server log.
For instance, an audit success by the "sa" account and an audit failure are shown in the following excerpt:. You can use it to dump the results of the error log to a recordset by:. To read further back, you can give this extended stored procedure an integer parameter which corresponds to the order of the SQL Server log.
So to see the contents of the 3rd log including the current one you would pass a parameter of 2 counting up from 0 - 0, 1, 2 would be the third , you would execute:. To search the current error log and only return failed logins you can use the following command. If you have log management software, another good place to look is the Application event log for the operating system.
You can view this by using the Computer Management tool. For instance, the following shows the audit success and failure events we looked at before in the SQL Server log:. SQL Server uses the same event ID for both, making it impossible to determine of the event signifies a success or failure without looking at the event details.
0コメント