Backup is important, Protecting the Backup is equally important

It is a sad day when hacker(s) deliberately attached WebHostingTalk and this is just another incident recently that relates to database backups (after Carbonite, Ma.gnolia, JournalSpace)

As a DBA, it is hard to not imagine the worst for the databases we manage, and I will consult with my colleagues to ensure that our tape backup is safe from attack (both on-site AND off-site)

---------------------------

Hello fellow WHTers!

It's been pretty hectic around here, but I wanted to make sure as many members as possible know what's going on. At approximately 8:30 pm EST on Saturday, March 21 The malicious attacker deleted all backups from the backup servers within the infrastructure before deleting tables from our db server. We were alerted of the db exploitation and quickly shut down the site to prevent further damage.

We've tried to answer any questions or concerns in the following thread posted at http://www.webhostingtalk.com/showthread.php?t=729727.
Be sure to subscribe if you want to stay informed.

Remember, you can follow us on Twitter @WebHostingTalk.

WHT Data - Q&A Information
========================

What do we know about the damage done?
This attack was very deliberate, sophisticated and calculated. The attacker was able to circumvent our security measures and access via an arcane backdoor protected by additional firewall. We are still investigating the situation, but we know the attacker infiltrated and deleted the backups first and then deleted three databases: user/post/thread. We have no record or evidence that private message data was accessed. Absolutely no credit card or PayPal data was exposed.

Do we know the motivation behind the attack?
We don’t know enough at this time, so any insight would be purely speculative in nature. WHT is a platform where positive and negative information is shared and exposed about business and individuals. Under TOS policy, we cannot edit or remove user-generated content at the request of an unsatisfied third party. Therefore, WHT tends to become the target for disgruntled individuals and businesses.

Have we been able to restore more recent back-ups?
The offsite backup, the onsite backup and the operational data were destroyed by the attacker, so we’ve resorted to a physical back-up of last resort. Unfortunately, we are experiencing difficulty restoring from our most recent physical backup. At this point, October is the most recent backup that we were able to restore. We continue to work to extract data from a more recent set of DVDs. What is WHT focused on doing now?

The first priority, which kicked in immediately upon discovering the hack while in process, was locking down the infrastructure to avoid further damage and restoring the site. We also had to block the potential for a repeat attack. Now we are working on investigating how much prior data is restorable, reinstating premium memberships, contacting business partners, and communicating with the community members. We are also doing everything possible to identify the attacker and bring them to justice. Disappointments happen – we are working hard to restore trust among community members and to bring things back to normal.

Is WHT doing anything different due to this attack?
WHT has been targeted before and our infrastructure has withstood previous attacks. However, following this well-planned and targeted attack, we will be altering aspects of our architecture to ensure that this type of attack does not happen again. Needless to say, we have learned from this situation and will address any discrepancies accordingly.

We had three, protected data back-up units with one offsite behind a firewall and a fourth physical data back-up layer. We evaluated our disaster recovery plan as recent as late-2008, and carefully reviewed how to recover from a disaster situation. The attacker appeared to have deliberately targeted our data back-up systems, a scenario that our disaster recovery plan did not fully anticipate. We have implemented changes to our data backup and disaster recovery plans to address this weakness. And we advise others to consider a scenario of deliberate, malicious data destruction in their backup and recovery plans.



What should members do now?
The password encryption technology we use is strong for securing non-financial data. However, we suggest that members change their passwords frequently and do not use the same user name and password for the forum as they may use for more sensitive services like online banking. If a member feels more comfortable changing their password, then we recommend that they do what makes them feel more secure.

A concern is that members may receive more spam because the attacker posted stolen email addresses on file sharing sites. I haven’t personally seen an increase in the amount of spam I usually receive to my email address, but it is a risk that we cannot easily alleviate. As we become aware of specific file sharing sites with these email addresses, we are requesting that the emails be removed promptly. So far, most have been quick to comply.

What if I can’t use my WHT account?
We are temporarily using a version of the database from October 2008. This means that if you joined WHT after October 2008, you’ll need to register again to post now. We may still be able to recover your account, but we don’t know yet. Please register with the same username you used before.

If you joined WHT before October 2008 and get a password error, the system is probably asking for the password you were using in October 2008. If you don’t remember your previous password and have access to the email address for your WHT account in October 2008, please use the password recovery tool.

Get updates on this topic here.

For help accessing your account, please open a helpdesk ticket.

If you’ve subscribed to a Premium or Corporate membership prior to October 2008, someone from iNET has contacted you by now. If you’ve subscribed (or re-subscribed) since October 2008 and haven’t heard from iNET, please contact us on the helpdesk.

Moving forward ...
We take the protection of user-contributed data very seriously, and we strongly regret what happened. iNET has a sophisticated infrastructure with advanced security. Yet even institutions that spend millions of dollars a year on Internet security are exploited. Anyone recall NASA being hacked some years back?

It’s not what you’ve done, it’s what you do. And from this day forward, we continue.

We’ve been overwhelmed by all the offers of help and support we’ve received from our members. What can I say about that beyond my heartfelt thanks? I love this community!

HOWTO: Enable and Configure Database Mail, Add SQL Agent Operator

Why
If you are a DBA and want to receive ANY kind of alert/notification email from the SQL Server, you need to setup the Database Mail first, just like you have to configure Outlook to connect with Exchange before receiving any email.

Who
DBAs who want to get notified for any pre-configured alerts or job notifications

How
Prerequisites

• External SMTP mail server, or Microsoft exchange server

Steps

• Creates a Database Mail Profile
• Creates a Database Mail Account
• Adds SQL Agent Operator
• Enables SQL Agent to use the Database Mail Profile (needs to manually restart SQL Agent)

When
Run this setup only for initial SQL Server configuration.
Normally there is no need to re-run afterwards

Where
SQL Server 2005/2008

Reference: this code was taken and modified from Jonathan's "Configuring SQL Server 2005/2008 Database Mail"

What

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE 
GO
----------------------------------------------------------------------------------------------------------------
-- Create a New Mail Profile for Notifications
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'DBA_Notifications',
    @description = 'Profile for sending Automated DBA Notifications'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'DBA_Notifications',
    @principal_name = 'public', @is_default = 1 ;
GO
-- Create an Account for the Notifications
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLMonitor',
    @description = 'Account for Automated DBA Notifications',
    @email_address = 'sqlnotify@domain.com',  -- ************ Change This ************
    @display_name = 'SQL Monitor',
    @mailserver_name = 'exchange.domain.com'-- ************ Change This ************  
GO
-- Add the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DBA_Notifications',
    @account_name = 'SQLMonitor', @sequence_number = 1
GO

----------------------------------------------------------------------------------------------------------------
--Adds Operator for Job Notification
USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name = N'SQLDBAs', @enabled = 1,
    @weekday_pager_start_time = 90000, @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000, @saturday_pager_end_time = 180000,
    @sunday_pager_start_time = 90000, @sunday_pager_end_time = 180000,
    @pager_days = 0, @email_address = N'sqlnotify@domain.com',-- ************ Change This ************
    @category_name = N'[Uncategorized]'
GO

----------------------------------------------------------------------------------------------------------------
-- Enable SQL Server Agent to use Database Mail profile (in Alert System tab)
-- restart SQL Agent after
USE [msdb]
GO
EXEC MASTER.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
    N'REG_SZ', N'DBA_Notifications'
GO

HOW TO: Setup Cluster Failover Alert Email

To be creative, I will try to model my posts into the below format, dragging my lazy bum to finally come back and post.

Why
It is strange a few years as DBA I have not yet seen a de-facto Cluster Alert solution. One can monitor event logs, SQL error logs, but there is no wizard in SQL/Windows Cluster to setup alerts for failover. With help from Google, I used this solution to send me email when a cluster fails over (i.e. SQL Server Agent will fail over too and cause the job to run).

To my surprise, not that it is something I want to happen, it worked and sent me an email Tuesday
I am certain there is a better way, someone please let me know.

Who
DBAs who want to get notified when cluster failed over

How
Prerequisites (I will post on this tomorrow)
- Database Mail profile & account configured
- SQL Server Agent operator created

The job sends an Email to the specified Operator when it runs

JOB RUN: 'Cluster Alert' was run on 3/17/2009 at 4:06:40 PM

DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Start Sequence 0. The last step to run was step 1 (Cluster).

When
The job is scheduled to run WHEN SQL Server Agent Starts, which happens when the cluster service fails over

Where
SQL Server 2005/2008

What


USE [msdb]


GO


/****** Object:  Job [Cluster Alert]    Script Date: 03/19/2009 16:27:37 ******/

BEGIN TRANSACTION


DECLARE  @ReturnCode INT


SELECT @ReturnCode = 0


/****** Object:  JobCategory [Database Maintenance]    Script Date: 03/19/2009 16:27:37 ******/

IF NOT EXISTS (SELECT name

               FROM   msdb.dbo.syscategories

               WHERE  name = N'Database Maintenance'

                      AND category_class = 1)

  BEGIN

    EXEC @ReturnCode = msdb.dbo.Sp_add_category

      @class = N'JOB' ,

      @type = N'LOCAL' ,

      @name = N'Database Maintenance'



    IF (@@ERROR <> 0

         OR @ReturnCode <> 0)

      GOTO quitwithrollback

  END


DECLARE  @jobId BINARY(16)


EXEC @ReturnCode = msdb.dbo.Sp_add_job

  @job_name = N'Cluster Alert' ,

  @enabled = 1 ,

  @notify_level_eventlog = 0 ,

  @notify_level_email = 3 ,

  @notify_level_netsend = 0 ,

  @notify_level_page = 0 ,

  @delete_level = 0 ,

  @description = N'No description available.' ,

  @category_name = N'Database Maintenance' ,

  @owner_login_name = N'sa' ,

  @notify_email_operator_name = N'SqlDbas' ,

  @job_id = @jobId OUTPUT


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


/****** Object:  Step [Cluster]    Script Date: 03/19/2009 16:27:37 ******/

EXEC @ReturnCode = msdb.dbo.Sp_add_jobstep

  @job_id = @jobId ,

  @step_name = N'Cluster' ,

  @step_id = 1 ,

  @cmdexec_success_code = 0 ,

  @on_success_action = 1 ,

  @on_success_step_id = 0 ,

  @on_fail_action = 2 ,

  @on_fail_step_id = 0 ,

  @retry_attempts = 0 ,

  @retry_interval = 0 ,

  @os_run_priority = 0 ,

  @subsystem = N'TSQL' ,

  @command = N'print ''cluster''' ,

  @database_name = N'master' ,

  @flags = 0


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_update_job

  @job_id = @jobId ,

  @start_step_id = 1


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_add_jobschedule

  @job_id = @jobId ,

  @name = N'SQLAgentStart' ,

  @enabled = 1 ,

  @freq_type = 64 ,

  @freq_interval = 0 ,

  @freq_subday_type = 0 ,

  @freq_subday_interval = 0 ,

  @freq_relative_interval = 0 ,

  @freq_recurrence_factor = 0 ,

  @active_start_date = 20090309 ,

  @active_end_date = 99991231 ,

  @active_start_time = 0 ,

  @active_end_time = 235959


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_add_jobserver

  @job_id = @jobId ,

  @server_name = N'(local)'


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback





GOTO endsave


QUITWITHROLLBACK:

IF (@@TRANCOUNT > 0)

  ROLLBACK TRANSACTION


ENDSAVE:

GO

SQL Server Enterprise Policy Management

I came across the Enterprise Policy Management that utilized the SQL 2008 policy and serves a DBA's desire to centralize monitor/govern multiple database servers

Downloaded and set it up yesterday mostly fine, and the last obstacle was addressed today thanks to Lara's help - it really does NOT support "nested/2nd level" Central Management Server group

So far, my 2 test servers are 100% green :-)

Project Description

The Enterprise Policy Management Framework is a reporting solution on the state of the enterprise against a desired state defined in a policy. Extend Policy-Based Management to all SQL Server instances in the enterprise. Centralize and report on the policy evaluation results.

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server 2008 Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define the defined intent, desired configuration, and deployment standards.