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.
I am mrdenny, and I am the killer of Smurfs.
A lesson that I learned while setting up our new data center for work, is to be careful when setting up your clustered servers.
Most of our servers are VMs, including a Windows 2008 Cluster (yeah I know not supported, I’m a rebel damn it and I want a Windows 2008 cluster installed under VMware ESX). Continued »
Something that most people don’t release needs a little TLC in SQL Server is the backup history.
Every time a database is backed up records are written about it. Over time this can add up to a lot of useless data floating around the SQL Server in the msdb database.
If you like to use the UI to restore you databases, this can also lead to the UI stalling when the restore database window comes up.
Fortunately Microsoft has provided a system stored procedure which you can use to clean up this old data. This procedure is the sp_delete_backuphistory system stored procedure. The usage of this procedure is very simple. It takes a single parameter @oldest_date which is simply the oldest date of data you want to keep. As an exmple:
EXEC sp_delete_backuphistory ‘1/1/2009’ would delete backup data older than Janunary 1, 2009.
The biggest difference between encrypted data and hashed data is that encrypted data can be decrypted later. Hash algorithms such as MD5 are one way hashing algorithms which means that the value that is returned can’t be decrypted back to the original value.
It is important to know the difference between the two when designing your database encryption schema. If you don’t need to retrieve the encrypted value then only store the hash. This way you don’t have the actual data for anyone to steal.
This week ServerFault.com has transissioned from Beta to fully live. This means that you no longer need to have the beta access code to access the site.
Hopefully you find the site as useful as I have while it was in beta.
With the recent release of the ability for the Lock Pages in Memory setting to be used on SQL Server 2005 and 2008 Standard Edition I see more and more people shooting them selves in the foot with this setting when running under VMware. I see this as becoming more of an issue now that this switch is available for Standard edition as I would assume that most virtualized SQL Server installations are done using SQL Server Standard Editions.
As of Monday it’s official, that I’m now one of the two moderators over at ServerFault.com. From what I understand this means I basically go through the list of posts that people have tagged as not being relevant to he site and remove them, as well as making sure that the user base is following the rules.
I think that its a great site, and I’m thrilled that Jeff trusts enough in me to put me into this position on the site.
The site is still in public beta. If you’d like to join the beta you’ll need to use the password “alt.sysadmin.recovery” to get into the site.
Today was day 2 of EMC World 2009. There were some great sessions today. I’m focused on two tracks this year, VMware and the CLARiiON product as we have just deployed both of these in our data center migration project. Continued »
Thanks to my wife Kris reminding me that the SD card in my camera will also fit in my blackberry I’ve gotten the photo’s uploaded to flickr.