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

Wednesday, February 21, 2018

Windows 10 – Mobile Hotspot

Image by barek2marcin via pixabay

A few weeks ago, I was doing a demo on a couple of Alexa skills that I've developed using Amazon Echo Dot. However, the place where I have to do the demo use enterprise class Wi-Fi (it is where after connecting to the Wi-Fi network, we need to authenticate using web browser). Unfortunately, at the moment, as far as I know, Amazon Echo Dot doesn't support enterprise class Wi-Fi. To make matter worse, my cell phone signal was very weak in the meeting room, so I was unable to use my cell phone as hotspot.

Luckily, I have my Windows 10 laptop. Windows 10 has a feature where I can turn my laptop into mobile hotspot. To make it work, I would make sure that my laptop has Internet connection. Before the demo, I would connect my laptop to the Wi-Fi network (and when prompted, I would authenticate myself using the username and password provided by the company). Once I am connected to the Wi-Fi network and have Internet connection, I would then:

  1. On my Windows 10 laptop, go to “Settings”.
  2. Then I would select “Network & Internet”.
  3. On the left navigation area of the “Network & Internet” window, there should be an option for “Mobile hotspot”. I would click on that.
  4. On the “Mobile hotspot” screen, you should see a screen similar to the one below. By default, Windows would already pre-define the mobile hotspot network (SSID) name and password for you. However, you can change them by using the “Edit” button. To turn on the mobile hotspot, near the top of the screen, there is an on/off toggle button, click on that one and it will enable the mobile hotspot.

W10MobileHotspot

At this point, you can go to your other device (in my case Alexa app on my mobile phone to configure the Amazon Echo Dot) and configure it to connect to the mobile hotspot that has just been setup on the Windows 10 system.

In my case, the mobile hotspot feature in Windows 10 works great. I was able to demo Alexa skills that I’ve developed on Amazon Echo Dot without any issue on places that use enterprise class Wi-Fi.

Some Observations

  • Windows 10 seems to be smart enough to detect if the system has wireless (Wi-Fi) capability or not. If the system does not have Wi-Fi card, I would not see the “Mobile hotspot” option under “Settings” – “Network & Internet”.
  • When I am using VPN connection, I might not be able to enable the mobile hotspot feature
  • After some period of inactivity on the system, Windows 10 would automatically disabled the mobile hotspot.

    Tuesday, February 13, 2018

    T-SQL Tuesday #99 – Work-Life Balance

    This month T-SQL Tuesday is hosted by Aaron Bertrand (blog | twitter). For this month's T-SQL Tuesday, we are given two options. The first option is to write about something that we are passionate about outside of SQL and tech community. The second option is to write about T-SQL bad habit. Mr. Bertrand has a long list of them. For this entry, I choose to write about activities that I do outside SQL or tech community #sqlibrium.

    Cooking

    One activity that help me to decompress from a busy week at work is cooking. By no means, that I am a great, or even a good, cook. I love to research recipes online and try them. I would also try to experiments with the recipes, tweaking, substituting or adding ingredients. Cooking allows me to channel my inner creativity. Sure, there were times where the “experiments” end up to be disasters (plenty of times actually). Those times, I ended up ordering and eating take-out Pizza or Chinese food. But hey, Edison failed 9,999 times before he was successful in inventing the light bulb.

    It also helps to cook something that you love eating. It will give you that extra motivation. One example, I love eating ramen noodle (Not the instant one. Although, I do get instant ramen fix once in a while). Recently, I embarked on ramen noodle making journey. After reading books, blogs, watching YouTube (I think you can almost learn about anything on YouTube nowadays) and attending a weekend class on "The Art of Ramen”, for several weeks I tried to experiments making ramen noodles. Earlier on, it was not pretty. But after several attempts, I was able to make Miso Ramen. Some of the pictures from one of the successful experiment:

    Fresh Ramen Noodles

    Fresh Ramen Noodles

    Ramen with Pork and Egg

    Ramen with Pork and Egg

    Miso Ramen

    Miso Ramen

    It took a many failed attempts and several weeks. But I must say, success taste delicious (especially on cold winter days in New England).

    Exercise

    Health is very important. During the week, I try to run a few miles three times a week. I love running because when I am running, I am alone with my thoughts. There are times where I got great ideas during my runs. In addition to running, I also try to learn to play ice hockey. Mostly, I play in pick up games. It is a fun and great workout.

    Last year, I got the opportunity to join a dragon boat team. We practiced and raced in 38th annual Boston dragon boat festival and 19th annual Rhode Island Chinese Dragon Boat Races and Taiwan Day Festival. We had a great coach and also a great group of people for teammates. We encouraged and build one another up. Just like any other team sport, team work is very important. It is not enough for each individual to have the paddling technique correct; the team need to be in sync as well. The practices were hard (there were lots of pains and aches in the first couple of practices. I felt aches on muscles that I didn’t even know existed), but I really enjoyed them. Our practice sessions were in Charles River. We have a great view of the city from the river. Most importantly, I enjoy the camaraderie.

    AACA Dragon Boat Team

    Source: AACA

    How About You?

    Would love to hear from others in the SQL communities on their activities outside SQL or tech community.