Mathom Solutions Blog-IT

• PASS - The Professional Association for SQL Server > Learning Center > Top 10 Lists | SQLPass
  Late Nite TV does, it so why don't DBA's do it. Well they do. The folks over at PASS have come up with a great list of Top 10 for subjects such as Clustering, Profiler, Execution Plans, Reporting Services, Analysis Services, Integration Services, Security,...you get the picture. It is thorough and will be expanding soon! Check it out and learn a little.
  (tags: sqlserver profiler ssrs ssas ssis security)

• In Recovery... | Finding out who dropped a table using the transaction log | Paul Randal
  Paul Randal (blog | twitter) discusses a way to possibly track who dropped a table in SQL 2008 and older when all you have is the log file. Not perfect but at least you can get a time and if you are using Windows authentication, you should be able to track back to the Windows SID with some accuracy.
  (tags: sqlserver transaction_log forensics)

• Carpe Datum | Buck Woody
  Buck Woody's (blog | twitter) awesome series of blog posts and links to better help you prepare for the 70-432 SQL 2008 Implementation and Maintenance certification.
  (tags: sql2008)
• Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail - SQLServerCentral | Jonathan Kehayias
  Jonathan (blog | twitter) provides the best solution involving a certificate signed assembly to call sp_send_dbmail. This solution prevents one from adding users to msdb and the DatabaseMailUserRole.  It removes the risks associated with having TRUSTWORTHY ON setting enabled.
  (tags: sqlserver certificates security)
• Working with the New SQL Server 2008 Collations and Earlier Versions of the Native Data Provider - Whitepapers | Microsoft
  This Microsoft Whitepaper is on Microsoft SQL Server 2008's new Windows collations (denoted by *_100 in the collation name) that were added that make it in full alignment with the Windows Server 2008 operating systems.  (Authors:  Fernando Caro with technical review by immy Wu, Sanjay Mishra, Qingsong Yao, Juergen Thomas, Burzin Patel, Wanda He, and Tres London)
  (tags: sqlserver Unicode collations sql2008 Whitepaper)
• CSS SQL Server Engineers : How It Works: Are you handling cancels correctly in your SQLCLR code? | CSS SQL Server Engineers | Microsoft
  The CSS SQL Server Engineering team (blog | twitter) discusses that when a query is cancelled and it is currently executing in the SQLCLR that an System.Threading.ThreadAbortException exception is raised. It seems harmless enough to add a catch or finally block to your code to cleanup necessary resources. What they found is that it is not as easy as catching the ThreadAbort call. In some scenarios, the ThreadAbort is sometimes upgraded to a rude abort and the catch/finally block is not executed.
  (tags: sqlserver CLR Error_Trapping)
• Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries | Microsoft
  In this Microsoft white paper, there is a good discussions about two new features in Microsoft SQL Server 2008: Star Join and Few-Outer-Row optimizations. Microsoft tested the performance of SQL Server 2008 to highlight the effect of these two features and how they got a significant performance gain (about 75% gain and a reduction in the number of rows processed) over SQL Server 2005 without these two features.
  (tags: sqlserver performance datawarehouse star schema Whitepaper)

As always, I am on the lookout for good free resources.  The good folks at Red-Gate with John Magnabosco have put out a very good book on Protecting SQL Server Data.  This free e-book covers the following topics:

1. Understanding Sensitive Data
2. Data Classification and Roles
3. Schema Architecture Strategies
4. Encryption Basics for SQL Server
5. Cell Level Encryption
6. Transparent Data Encryption (TDE)
7. One-Way Encryption
8. Obfuscation
9. Honeycombing a Database
10. Layering Solutions

The focus of this book is protecting sensitive data that is "at rest" within your SQL Server database (primarily Microsoft SQL Server 2005 and 2008). Security methods and appliances/devices that protect data externally to the DB, such as firewalls, secured network connections, and secure web design are all very important and recommended, but the main success of your efforts to protect sensitive data will depend upon how well you guard your data in the database. 

As you know, I am a big fan of Free...here are some other goodies:

Engineering Software for Accessibility by Microsoft Press. In an effort to enable software developers to create accessible Windows applications, we wanted to share our process with the community. We have captured this engineering process in a new book, Engineering Software for Accessibility. The book addresses three basic questions:

1. How do you plan for accessibility?
2. How do you design your software for accessibility?
3. How can you implement and test to your software to confirm it meets the accessible design?

You will learn that properly implemented accessibility enables access to Windows applications for users with a variety of capabilities.  

Get it before it is gone!

- - - - - - - - -  

SQL Server Tacklebox by Rodney Landrum. Essential tools and scripts for the day-to-day DBA that helps with Scripts, tools and techniques to tackle SQL Server issues, Troubleshoot performance issues, Receive notifications of impending issues, Fight off data corruption, Document and report on your servers, Automate and standardize SQL Server installation, Migrate data and manage data growth, and Secure access to your servers.    

This is sponsored by Red-Gate Software.   

• Layer 8 -- All is fair in security and war
  Not much to say, just read it and laugh!
  (tags: Security Sun Tzu)
• How to Test SSRS MDX Queries in SQL Server Management Studio ... | Prologika | Teo Lachev
  SSMS doesn't support parameterized MDX queries so you have to resort to the following technique to make it work.
  (tags: MDX Analysis Services)

It's been awhile but wanted to post something that I found very useful.  

I encountered a situation where I need to parse through tens of thousands of log files for SQL Server and IIS and needed to do an analysis of what was contained within for a possible security incident and to create a standard auditing process for some compliance requirements for a new contract.  I thought about pulling the logs to a secure location, creating a SQL Server database and importing the files and using full text search but then I came across Splunk.   They have a nice little free version that allows you to index up to 500MB per day.  

With Splunk, I was able to pull down the logs to a directory, point Splunk at it and within minutes, it was all indexed and it automatically did the rest as new logs were added.   From there I was able to do research for XSS, SQL Injection, Login/Logout and failed/succesful information.  

Very nice and fast!  Check Splunk out as it has tons of possiblities besides log management! 

As I am sure you are all aware, database hacking is happening--afterall it's where the crown jewel--you data--are located.  I came across this article today and it was very helpful in terms of how a hacker could get through a system with as little footprint as possible and how to think about protecting your systems. 

Cesar Cerrudo is a Security Consultant and Lead Research for Application Security Inc and he presented recently at Black Hat 2009.  His paper was on SQL Server Anti-Forensics: Techniques and Countermeasures.  The paper talks about anti-forensics (which is about how a hacker covers/removes traces of their activity) and the counter measures you can take to help reduce or mitigate the chances of them reducing their tracks when they have invaded your system. 

This is a must read and make sure to check out his other articles!