Archive for January, 2012

Task: to create a fully automatic database backup system by means of SQL Server Agent Job with an e-mail fails notification subsystem



EXEC [master].[dbo].[sp_addumpdevice]
     @devtype      = N'disk',
     @logicalname  = N'AdventureWorks2008R2',
     @physicalname = N'D:\SqlData\MSSQL\Backup\AdventureWorks2008R2.bak'
GO

Source code 1: SSMS -> Object Explorer -> [sql_server_instance] -> Server Objects -> Backup Devices -> AdventureWorks2008R2



EXEC [master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC [master].[dbo].[sp_configure] 'xp_cmdshell', 1
GO
RECONFIGURE
GO

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Backup]
AS
BEGIN
    DECLARE @Out INT
    
    -- NOTE: make sure the database is in Full Recovery Model and is online!
    ALTER DATABASE [AdventureWorks2008R2]
    SET RECOVERY FULL
    ALTER DATABASE [AdventureWorks2008R2]
    SET ONLINE
    -- NOTE: functional for a FileStream database as well!
    
    DBCC CHECKDB([AdventureWorks2008R2])
    
    -- Create a database backup
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_BEGIN.bat'
    
    CHECKPOINT
    
    BACKUP LOG [AdventureWorks2008R2]
               TO DISK='NUL' --WITH TRUNCATE_ONLY
    BACKUP DATABASE [AdventureWorks2008R2]
                    TO [AdventureWorks2008R2] WITH COMPRESSION --WITH INIT
    
    -- Copy the backup database to a tape
    EXEC @Out = [master]..[xp_cmdshell]
                N'D:\SqlData\MSSQL\Backup\BACKUP_FILO_END.bat'
END
GO

Source code 2: SSMS -> Object Explorer -> [sql_server_instance] -> Databases -> System Databases -> master -> Programmability -> Stored Procedures -> dbo.Backup



REM ***Creating a Database Backup***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATH="D:\SqlData\MSSQL\Backup\"

SET FILENAME="AdventureWorks2008R2"

IF EXIST %FILEPATH%\%FILENAME%.BA4 DEL    %FILEPATH%\%FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA3 RENAME %FILEPATH%\%FILENAME%.BA3 %FILENAME%.BA4
IF EXIST %FILEPATH%\%FILENAME%.BA2 RENAME %FILEPATH%\%FILENAME%.BA2 %FILENAME%.BA3
IF EXIST %FILEPATH%\%FILENAME%.BA1 RENAME %FILEPATH%\%FILENAME%.BA1 %FILENAME%.BA2
IF EXIST %FILEPATH%\%FILENAME%.BAK COPY   %FILEPATH%\%FILENAME%.BAK %FILEPATH%\%FILENAME%.BA1

SET FILENAME=
SET FILEPATH=

Source code 3: Creating a database backup – BACKUP_FILO_BEGIN.bat



REM ***Copying the Database Backup to a Tape***

REM Path: "D:\SqlData\MSSQL\Backup\"
SET FILEPATHSRC="D:\SqlData\MSSQL\Backup\"

REM Path: "\\[server_name]\SQL_BAK$"
SET FILEPATHDST="\\[server_name]\SQL_BAK$"

SET FILENAME="AdventureWorks2008R2"

COPY %FILEPATHSRC%\%FILENAME%.BAK %FILEPATHDST%\%FILENAME%.BAK

SET FILENAME=
SET FILEPATHSRC=
SET FILEPATHDST=

Source code 4: Copying the database backup to a tape – BACKUP_FILO_END.bat



USE [msdb]
GO

EXEC [msdb].[dbo].[sp_add_operator]
     @name                      = N'BACKUP',
     @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'test@example.eu',
     @category_name             = N'[Uncategorized]'
GO

Source code 5: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Operators -> BACKUP



USE [msdb]
GO

BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT  @ReturnCode = 0
    
    IF NOT EXISTS
    (
        SELECT [name]
        FROM   [msdb].[dbo].[syscategories]
        WHERE  [name]           = N'Database Maintenance'
               AND
               [category_class] = 1 -- Job
    )
    BEGIN
        EXEC @ReturnCode = [msdb].[dbo].[sp_add_category]
                           @class = N'JOB',
                           @type  = N'LOCAL',
                           @name  = N'Database Maintenance'
        IF (@@ERROR != 0 OR @ReturnCode != 0)
        BEGIN
            GOTO QuitWithRollback
        END
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_job]
		       @job_name                   = N'BACKUP',
		       @enabled                    = 1,
		       @notify_level_eventlog      = 2,
		       @notify_level_email         = 2,
		       @notify_level_netsend       = 0,
		       @notify_level_page          = 0,
		       @delete_level               = 0,
		       @description                = N'Creating a database backup by means of SQL Server Agent Job',
		       @category_name              = N'Database Maintenance',
		       @owner_login_name           = N'NB0046\Marian Placko',
                       @notify_email_operator_name = N'BACKUP',
                       @job_id                     = @jobId OUTPUT
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobstep]
		       @job_id               = @jobId,
		       @step_name            = N'BACKUP',
		       @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'EXEC [dbo].[Backup]',
		       @database_name        = N'master',
		       @flags                = 0
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_update_job]
                       @job_id        = @jobId,
                       @start_step_id = 1
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    DECLARE @guid UNIQUEIDENTIFIER
    SET     @guid = NEWID()
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobschedule]
		       @job_id                 = @jobId,
		       @name                   = N'BACKUP',
		       @enabled                = 1,
		       @freq_type              = 8,
		       @freq_interval          = 62,
		       @freq_subday_type       = 1,
		       @freq_subday_interval   = 0,
		       @freq_relative_interval = 0,
		       @freq_recurrence_factor = 1,
		       @active_start_date      = 20111230,
		       @active_end_date        = 99991231,
		       @active_start_time      = 200000,
		       @active_end_time        = 235959,
		       @schedule_uid           = @guid
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
    EXEC @ReturnCode = [msdb].[dbo].[sp_add_jobserver]
                       @job_id      = @jobId,
                       @server_name = N'(local)'
    IF (@@ERROR != 0 OR @ReturnCode != 0)
    BEGIN
        GOTO QuitWithRollback
    END
COMMIT TRANSACTION
    GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRANSACTION
    END
EndSave:
GO

Source code 6: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Jobs -> BACKUP



-- Enable a Database Mail
[master].[dbo].[sp_configure] 'show advanced options', 1
GO
RECONFIGURE
GO
[master].[dbo].[sp_configure] 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- Set up an Account
 -- Port number: 25
 -- SMPT authentication: anonymous
EXECUTE [msdb].[dbo].[sysmail_add_account_sp]
        @account_name    = 'BACKUP',
        @description     = 'Creating a database backup by means of SQL Server Agent Job',
        @email_address   = 'test@example.eu',
        @display_name    = 'BACKUP',
        @replyto_address = 'test@example.eu',
        @mailserver_name = 'localhost' -- NOTE: SMTP server must be set up!
GO

-- Set up a Profile
EXECUTE [msdb].[dbo].[sysmail_add_profile_sp]
        @profile_name = 'BACKUP',
        @description  = 'Creating a database backup by means of SQL Server Agent Job'
GO

-- Associate the Profile with the Account
EXECUTE [msdb].[dbo].[sysmail_add_profileaccount_sp]
        @profile_name    = 'BACKUP',
        @account_name    = 'BACKUP',
        @sequence_number = 1
GO

-- Add NB0046\Marian Placko to the Database Mail Role
EXEC [msdb].[dbo].[sp_addrolemember]
     @rolename   = 'DatabaseMailUserRole',
     @membername = 'NB0046\Marian Placko'
GO

-- Grant access to the Profile to NB0046\Marian Placko
EXEC [msdb].[dbo].[sysmail_add_principalprofile_sp]
     @profile_name   = 'BACKUP',
     @principal_name = 'NB0046\Marian Placko',
     @is_default     = 0
GO

-- Send a test e-mail
EXEC [msdb].[dbo].[sp_send_dbmail]
     @profile_name='BACKUP',
     @recipients='test@example.eu',
     @subject='Test message',
     @body='This is the body of the test message.'

Source code 7: SSMS -> Object Explorer -> [sql_server_instance] -> Management -> Database Mail -> BACKUP



-- Enable SQL Server Agent to use the Database Mail
 -- NOTE: make sure to start SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'QUERYSTATE','SQLServerAgent'
 -- Current Service State: Running.
 --EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO

EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
     @email_save_in_sent_folder = 1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'UseDatabaseMail',
     N'REG_DWORD',
     1

EXEC [master].[dbo].[xp_instance_regwrite]
     N'HKEY_LOCAL_MACHINE',
     N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
     N'DatabaseMailProfile',
     N'REG_SZ',
     N'BACKUP'
GO

-- Enable SQL Server Agent to set Fail-safe Operator
EXEC [master].[dbo].[sp_MSsetalertinfo]
     @failsafeoperator   = N'BACKUP',
     @notificationmethod = 1 -- E-mail
GO
 -- NOTE: make sure to restart SQL Server Agent!
 EXEC master.dbo.xp_ServiceControl 'STOP', 'SQLServerAgent'
 GO
 -- Message: Service Stopped.
 EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'
 GO
 -- Message: Service Started.

Source code 8: SSMS -> Object Explorer -> [sql_server_instance] -> SQL Server Agent -> Properties -> Alert System


Additional references:
Advertisements

The SQL professionals know that the built-in MS SQL Server encryption mechanism by means of WITH ENCRYPTION T-SQL keyword is ineffective and easily broken. For much more details see also Google’s results e.g.:

{3S} SQL Smart Security is an add-in which can be installed in Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, 2012 and their respective Express versions. It enables software companies to create a secured content for database objects (note: currently, in 1.1 version can be secured only the content of stored procedures, but in the future, there are considered user defined functions, triggers and views as well). It brings much higher level of protection in comparison with SQL Server built in WITH ENCRYPTION mechanism for software companies that want to protect their heavily acquired know-how by creation of application logic on the database server side on a daily basis.

None of the currently available SQL decryptors are capable of cracking {3S} SQL Smart Security. Most importantly, all encrypted database objects remain executable! Moreover, installing {3S} SQL Smart Security does not affect any settings and does not replace default encryption options entirely. The developers (end-users) always have an option to choose which encryption settings to use.

See a short story of a simple hack by means of built-in MS SQL Server WITH ENCRYPTION mechanism on the example bellow.

WARNING: In the following examples, an excellent free tool called dbForge SQL Decryptor by the Devart (formerly known as Core Lab) software development company (http://www.devart.com/) will be used for educational purposes, only for decrypting the stored procedure.

built-in WITH ENCRYPTION hack – stored procedure encryption by means of built-in MS SQL Server WITH ENCRYPTION mechanism

Figure 1: built-in WITH ENCRYPTION hack – stored procedure encryption by means of built-in MS SQL Server WITH ENCRYPTION mechanism


built-in WITH ENCRYPTION hack – lock on encrypted stored procedure by means of built-in MS SQL Server WITH ENCRYPTION mechanism

Figure 2: built-in WITH ENCRYPTION hack – lock on encrypted stored procedure by means of built-in MS SQL Server WITH ENCRYPTION mechanism


built-in WITH ENCRYPTION hack – stored procedure decrypting by means of dbForge SQL Decryptor

Figure 3: built-in WITH ENCRYPTION hack – stored procedure decrypting by means of dbForge SQL Decryptor


built-in WITH ENCRYPTION hack – decrypted stored procedure by means of dbForge SQL Decryptor

Figure 4: built-in WITH ENCRYPTION hack – decrypted stored procedure by means of dbForge SQL Decryptor

By applying dbForge SQL Decryptor to the encrypted stored procedure by means of built-in MS SQL Server WITH ENCRYPTION mechanism it is possible to see that the stored procedure body IS A PLAIN SOURCE CODE TEXT!!!

built-in WITH ENCRYPTION hack – encrypted stored procedure by means of {3S} SQL Smart Security

Figure 5: built-in WITH ENCRYPTION hack – encrypted stored procedure by means of {3S} SQL Smart Security


built-in WITH ENCRYPTION hack – decrypted {3S} SQL Smart Security stored procedure by means of dbForge SQL Decryptor

Figure 6: built-in WITH ENCRYPTION hack – decrypted {3S} SQL Smart Security stored procedure by means of dbForge SQL Decryptor

By applying dbForge SQL Decryptor to the encrypted stored procedure by means of {3S} SQL Smart Security it is possible to see that the stored procedure body IS NOT A PLAIN SOURCE CODE TEXT but includes the {3S} SQL Smart Security metadata only!!!

Additional references:


{3S} SQL Smart Security ''Protect your T-SQL know-how!''