Monday, March 4, 2019

Trigger Windows Scheduled Task to Run Upon the Successful Completion of Another Scheduled Task

Images by PublicDomainPictures

In one of the projects that I worked on, I have two tasks/jobs on Windows Task Scheduler, let say the name of the first job is “FirstJob” and the second job is “SecondJob”. I have a requirement where the “SecondJob” should run immediately after the “FirstJob” has been completed successfully. There are several ways to achieve this. For this particular project, to fulfil the requirement, on the “SecondJob”, I would trigger the “SecondJob” based “On an event”, which the the successful completion of the “FirstJob”.

The steps that I’ve taken to create the trigger on the “SecondJob”:

  • On the “SecondJob” property, within the Task Scheduler, go to “Triggers” tab. And click on the “New” button.
  • On the “New Trigger” window, for “Begin the task”, choose “On an event.” Then on the “Settings” section, click on the “Custom” radio button. And click on the “New Event Filter…” button.
  • On the “New Event Filter” window, click on the “XML” tab, and check the “Edit query manually” checkbox.
  • You might get the following message: “If you choose to manually edit the query, you will no longer be able to modify the query using the controls on the Filter tab. Would you like to continue?” Click the “Yes” button to continue.
  • At this point you just need to enter the XPath of the event filter. This will be used to query the Windows Event. In my case, this is where I want to specify the trigger to start “SecondJob” when the “FirstJob” has been completed successfully. The XPath event filter that I was using:

The XPath event filter code:

<QueryList>
<Query Id="0" Path="Microsoft-Windows-TaskScheduler/Operational">
<Select Path="Microsoft-Windows-TaskScheduler/Operational">*[EventData[@Name='ActionSuccess'][Data [@Name='TaskName']='\FirstJob']] and *[EventData[@Name='ActionSuccess'][Data [@Name='ResultCode']='0']]</Select>
</Query>
</QueryList>

You can adjust for the above code to your situation. The most likely would be the Task Name.

Notes: In Windows 10, by default the Task History for Task Scheduler is disabled. In order to make this work, you will need to enable it. To do this you can go to the Task Scheduler, and without selecting any task, click on the “Action” on the top menu and then “Enable All Tasks History”.

Tuesday, January 15, 2019

Books in Review - 2018

Image by Wikimedia Common

Back in 2017, one of my personal goal for the year was to read more books. In 2017, I managed to read 25 books. For a complete list of books that I read in 2017, it can be found on Books in Review - 2017. I tried to keep it up in 2018. In 2018, I was able to read 25 books as well. There were a lot of good books to read.

The Complete List

Below are the complete list of books that I've read in 2018:

  1. I'll Give You the Sun. Jandy Nelson. Dial Books. Fiction. 386 pages.
  2. How Not to Be Wrong: The Power of Mathematical Thinking. Jordan Ellenberg. Penguin Books. Mathematics. 466 pages.
  3. Men Without Women: Stories. Haruki Murakami. Vintage. Fiction. 242 pages.
  4. Fierce Conversations: Achieving Success at Work and in Life One Conversation at a Time. Susan Scott. Berkley. Communication. 365 pages.
  5. Make Your Bed: Little Things That Can Change Your Life...And Maybe the World. William H. McRaven. Grand Central Publishing. Self-Help. 125 pages.
  6. Pocket Prayers: 40 Simple Prayers that Bring Peace and Rest. Max Lucado. Thomas Nelson. Religion. 63 pages.
  7. Paper Boats. Dee Lestari. AmazonCrossing. Fiction. 400 pages.
  8. Horrible Histories Special: Twentieth Century. Terry Deary, Philip Reeve. Scholastic Non-Fiction. History. 176 pages.
  9. 1st to Die (Women's Murder Club). James Patterson. Little, Brown and Company. Fiction. 488 pages.
  10. The Everything Store: Jeff Bezos and the Age of Amazon . Brad Stone. Little, Brown and Company. Business & Money. 386 pages.
  11. Hagakure: The Secret Wisdom of the Samurai. Yamamoto Tsunetomo. Tuttle Publishing. History. 288 pages.
  12. Creativity, Inc.: Overcoming the Unseen Forces That Stand in the Way of True Inspiration. Ed Catmull, Amy Wallace. Random House. Business & Money. 368 pages.
  13. Rich People Problems. Kevin Kwan. Anchor. Fiction. 418 pages.
  14. Everybody Lies: Big Data, New Data, and What the Internet Can Tell Us About Who We Really Are. Seth Stephens-Davidowitz. Dey Street Books. Big Data. 357 pages.
  15. The Magnolia Story. Chip Gaines, Joanna Gaines, Mark Dagostino. Thomas Nelson. Biographies. 189 pages.
  16. Crazy Rich Asians. Kevin Kwan. Anchor. Fiction. 546 pages
  17. Ready Player One. Ernest Cline. Broadway Books. Fiction. 386 pages.
  18. China Rich Girlfriend. Kevin Kwan. Anchor. Fiction. 394 pages.
  19. Daemon. Daniel Suarez. Dutton. Fiction. 489 pages.
  20. Fantastic Beasts and Where to Find Them: The Original Screenplay. J. K. Rowling. Pottermore Publishing. Fiction. 304 pages.
  21. No Middle Name: The Complete Collected Jack Reacher Short Stories. Lee Child. Dell. Fiction. 476 pages.
  22. Harry Potter and the Chamber of Secrets. J. K. Rowling. Pottermore Publishing. Fiction. 357 pages.
  23. Harry Potter and the Prisoner of Azkaban. J. K. Rowling. Pottermore Publishing. Fiction. 475 pages.
  24. The Art of Asking: How I Learned to Stop Worrying and Let People Help. Amanda Palmer. Grand Central Publishing. Biographies. 342 pages.
  25. Pivot: The Only Move That Matters Is Your Next One. Jenny Blake. Portfolio. Business & Money. 283 pages.

Fave Five

There are a lot of good books that I read in 2017, below are top 5 books:

  • Make Your Bed: Little Things That Can Change Your Life...And Maybe the World. William H. McRaven. Grand Central Publishing. Self-Help. 125 pages. - This book is based on University of Texas Austin 2014 commencement speech by Admiral William H. McRaven. You can watch the speech here. Admiral William H. McRaven is a former Navy SEAL Commander. In in his book (and in his commencement speech), he gave 10 life lessons from his experience during Navy SEAL basic training. We can use these 10 lessons to change our life and possibly the world.
  • Creativity, Inc.: Overcoming the Unseen Forces That Stand in the Way of True Inspiration. Ed Catmull, Amy Wallace. Random House. Business & Money. 368 pages. - Ed Catmull is one of the founders of Pixar. I love watching most of Pixar's movies and shorts. It's amazing that Pixar has been able to constantly deliver successful movies, such as Toy Story, Finding Nemo, Cars and The Incredibles. The book gives a lot of insights on Ed Catmull's thoughts and wisdom in managing creative company, Pixar cultures and some events that occurred, such as Steve Jobs interaction with Ed Catmull and Pixar and also changes that happened after Disney acquired Pixar. It was interesting to read how Ed Catmull try to preserve Pixar's culture and change Disney animation's cultures. The book contains a wealth of information for leaders, not only for leaders in creative industry, but also for leaders in other industries as well.
  • Everybody Lies: Big Data, New Data, and What the Internet Can Tell Us About Who We Really Are. Seth Stephens-Davidowitz. Dey Street Books. Big Data. 357 pages. - If you like to read Freakonomics and SuperFreakonomics by Steven D. Levitt and Stephen J. Dubner, you will most likely enjoy reading this book. Similar to Freakonomics and SuperFreakonomics, this book tries to make sense (meaningful information) from data collected. It is interesting to learn that people lies on surveys (even anonymous surveys) and, not surprising, people turn to Google/ Internet search for answers. It is fascinating to know that you can actually learn a lot of things from people's Google search terms and suggestions.
  • The Magnolia Story. Chip Gaines, Joanna Gaines, Mark Dagostino. Thomas Nelson. Biographies. 189 pages. - Chip and Joanna Gaines are husband and wife who starred on HGTV's Fixer Upper show. I love watching the Fixer Upper. The show was about finding houses with potential. Chip and Joanna Gaines would remodelled those houses into dream homes for their clients. The book would tells their story. How Chip and Joanna met each other. How they got that unexpected call from HGTV to do the Fixer Upper show. How they are different from each other but complete each other. The book would also document their struggles and success. I enjoyed reading the book. It was entertaining and also inspiring.
  • Crazy Rich Asians. Kevin Kwan. Anchor. Fiction. 546 pages - The main characters in the novels are Nick Young and Rachel Chu. Both of them are NYU professors and they are in love with each other. Nick is from Singapore, while Rachel is from California. Nick decided to take Rachel to Singapore to meet his family. Unbeknownst to Rachel, Nick came from ultra rich family. The novel has almost everything, from romance, drama to humor. The novel was fun to read. The novel is part of a trilogy. After this novel, we have China Rich Girlfriend and Rich People Problems.

Friday, January 4, 2019

Executing xp_cmdshell with Non SysAdmin Account

Image by The Blue Diamond Gallery

In SQL Server, xp_cmdshell is an extended stored procedure that is stored in the master database that would allow us to execute Windows command shell. By default, the ability to execute xp_cmdshell is disabled. We could enable the ability to run xp_cmdshell by executing the following query against the SQL Server instance:




USE master;
GO
EXEC sp_configure 'show advanced options', 1;
reconfigure;
GO
EXEC sp_configure 'xp_cmdshell', 1;
reconfigure;
GO

Once we run the above T-SQL query, any account that is part of the sysadmin role in the SQL Server instance has the ability to run the xp_cmdshell extended stored procedure. On the background, when the user with sysadmin privileges runs the xp_cmdshell, it will execute the Windows command shell using the SQL Server Service Account (So if you are executing xp_cmdshell to access certain resource on the network, for example, and you are having permission issue, you might want to make sure that the SQL Server Service Account has permission to that resource).

Now, what if you have a non-sysadmin account that needs to run xp_cmdshell? In order to do that, we would need to do some additional configuration. For this example, let say that we want to grant SQL Server login 'johndoe' the ability to execute xp_cmdshell extended stored procedure (Notes: It doesn’t have to be SQL Server login, it can also be assigned to Windows authentication). And we want to execute the command passed to xp_cmdshell using account Domain\RunUser Windows account.

First, we would need to create a cmd shell proxy account on the SQL Server Instance. This will essentially tell SQL Server to use the proxy account when executing the Windows command shell. To do that we can run the following T-SQL statement:

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE [name] = '##xp_cmdshell_proxy_account##')
BEGIN
CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = 'Domain\RunUser', SECRET = 'PasswordForTheRunUser'
END
GO

Next, we would need to grant the non-sysadmin account (in this case the SQL Server login 'johndoe'), access to master database and the execute permission for the xp_cmdshell extended stored procedure. To do this, we need to execute the following T-SQL Statement:

USE [master];
GO
EXEC sp_grantdbaccess 'johndoe';
GO
GRANT EXEC ON xp_cmdshell TO johndoe;
GO

Now, if the proxy account that we use (in this case, the domain account 'Domain\RunUser') doesn’t have local administration to the Windows Server where the SQL Server Instance is installed, we would need to grant it "Logon As Batch Job" privilege on the "Local Security Policy". To do that, we can go to "Local Security Policy" and then go to "User Rights Assignment" and look for "Logon As Batch Job". Add 'Domain\RunUser' to the "Logon As Batch Job" policy. If we don’t do this, we would most likely get the following error when 'johndoe' SQL Server login account tries to execute xp_cmdshell:

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

A quick way to test, you can run the following T-SQL statement:

EXEC AS LOGIN = 'johndoe'; 
EXEC xp_cmdshell 'whoami';
REVERT;

The above T-SQL statement would run the xp_cmdshell 'whoami' by impersonating 'johndoe' SQL Server login. If things are set up properly, you should see 'Domain\RunUser' as the result.

To Clean Up or Revert Back:

First, we would remove 'Domain\RunUser' from the "Local Security Policy" - "User Rights Assignment" - "Logon As Batch Job".

Then, we would run the following T-SQL statement to revoke access for 'johndoe' user:

USE [master]; 
GO
REVOKE EXEC ON xp_cmdshell TO johndoe;
GO
EXEC sp_revokedbaccess 'johndoe';
GO

Lastly, we would run the following T-SQL statement to remove the proxy account:

USE [master]; 
GO
EXEC sp_xp_cmdshell_proxy_account NULL;
GO

Refrences