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.