Knowing who has been logging into your SQL Server is one of the key things to know about your SQL Server. It lets you know quite a bit about who’s using your server, and about who’s been trying to break into your SQL Server, and most importantly if they have succeeded.
Obviously you can setup a SQL Profiler trace to capture this information but that requires the overhead of running SQL Profiler, and who wants that.
All version of SQL Server (from 2000 and up at least) provide some level of logging about who has tried to log into the SQL Server. Within Enterprise Manager or SQL Server Management Studio’s Object Explorer right click on the Server and select properties (if using Enterprise Manger select properties not connection properties).
Select the Security Tab and find the Login Auditing section. By default SQL Server only logs the failed logons which is good as it tells you who hasn’t been able to log into the server. However it doesn’t tell you it they have been successful which is why you may want to change this to both failed and successful logins.
Now changing this setting has an upside and a downside. The upside is that you know who has been successfully broken into your database using a brute force attack and when. The downside is that every client that successfully connects to the SQL Server will also log an entry, making it very hard to find the correct entry you are looking for.
Where do these entries get logged to you ask? That’s the other downside. They get logged to the SQL Server ERRORLOG file and the Windows Security log file. Which means that these files will fill up fast. And if you have a large enough client base logging into the database VERY FAST.
In a perfect world, I’d set this screen to both failed and successful logins. In reality failed is probably all I can do.
SQL Server 2000 didn’t provide a whole lot of information about what is happening as it only says that Login n has tried to connect and failed. Not exactly helpful as you don’t know who was trying to login to the SQL Server using the sa account over and over again. SQL Server 2005 and up include a little piece of helpful information, the IP Address of the person who tried to connect to the SQL Server. This will help tell you who is connecting to the SQL Server so that you can smack them around.
Or you could do what I have done: create a stored procedure that runs on SQL startup which creates a server-side trace. In my case, it logs to a file, but you could log to a table as well. In that trace, I filter out noise like service accounts (ever seen how many logins MOSS makes?). It’s not a complete record of logins, but it’s easier to track things if you filter out the noise. Besides, if somebody was using a service account to access the server, their accesses would get lost in the noise and I would say you have a little more to worry about – someone got the password to one of the service accounts! You either have poor security or somebody has some elevated rights they probably shouldn’t have.
I just set up a job and trace for all logins and failed logins, the way cosmictrickster said with a few exceptions. It dumps hourly to disk, then loads summary into a table. I don’t filter anything, I can do that on the way out. I can then catch developers who are using admin accounts based on the host name in the trace. I then set SSRS to email a daily login report for my main production servers each morning.
Hello @davidHay . Do you have the tutorial on how to do this including the script? Thank you.