Excel Password Breaker

For my 100th post on my blog here at cyberfella.co.uk I shall tell you how to recover the passwords used to protect the cells, worksheets and workbooks in Microsoft Excel.  Unlike every other post on my blog, I cannot claim that the vbscript used in the macro editor shown below is my own work.  If I knew the original author I’d gladly credit them, so let me know via the comments if you know who came up with this little gem and once I’ve verified that they are indeed the original author, then due credit will be awarded here!

So, you’re working to a deadline which is fast approaching and you’ve attempted to change the contents of a cell, and received the highly annoying message…

The cell or chart that you are trying to change is protected and therefore read-only

protected

 

Subsequent attempts at unprotecting the spreadsheet results in you being prompted for a password that you have no idea of and neither does anyone else.

No problem.   You’re still gonna make that deadline.

Hit Alt F11 to open the Macro Editor, double click the sheet giving you a problem and paste the following vbscript code into the empty code pane.  

Note below that the tabs in your spreadsheet may not actually appear in the order that they were originally created in, so be sure you click the correct sheet/tab name.

click-sheet

 

WordPress is frankly terrible at formatting code in posts whether they be in quotes or not and syntax can be easily messed up resulting in code that then doesn’t work.

Click on the link below to go to a separate html page where the code will be properly formatted for you to cut and paste into your macro – and is guaranteed to work.

excel-password-breaker-macro

 

 

 

excel-password-breaker

Now run the macro by clicking on the “Play” button on the toolbar (shown below).

macro

There may be a delay of up to a minute or more but the likelihood is that within a few seconds a message box will appear and you’ll be presented with the password.  Make a note of it if you like, but in practice the cells in my spreadsheet were also automatically unlocked.

 

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Applying user specific NTFS permissions to all home directories using CLI

There are lots of useful command line tools for listing and modifying NTFS permissions such as cacls, icacls, subinacl, setacl and System Tools’ dumpsec and I will continue to enhance this post with useful real-world examples of each in time.

I’ll start with using cacls in a for loop to process a bunch of home directories, granting full control to each home directory for the specific user.  This can be useful after migrating home directories between domains, or just as a working example of a loop to process files or directories in some way.

for /f %f IN (‘dir /ad /b X:\homedirs\u*’) DO cacls X:\homedirs\%f /e /p %f:F

(Use %%f in place of %f in batch files)

The dir /ad /b command can also be written as dir /A:D /B and has the effect of listing only names of only directories.  This prevents files being processed, limiting the application of permissions to just the directories.  For additional safety, I’ve also used u* to only list directories beginning with u (this may vary in your own environment).

The cacls command simply adds username:F (username:Full Control) to the DACL on the folder, assuming that the username and directory names match (which they almost certainly do in any Windows environment).

If the command is successful, you should see “Processed dir: X:\homedirs\<foldername>“.  Check the permissions to see the new ACE.

As with all complex commands, test on some dummy data first.

A more detailed account of migrating home directories to emc VNX using the VNX Home Directory functionality can be found here http://www.cyberfella.co.uk/2014/07/30/vnx_homedirectory/

Note: This note was written hosting home directories on a NAS, however if you need to share individual home directories as their own hidden shares on an actual Windows Server, then this additional command will save you hours of repetitiously clicking the wizard over and over again…

Create a hidden share for each users home directory (Note: home directory must have appropriate NTFS security in place)

for /f %%f IN (‘dir /ad /b E:\MigratedData\homedirs\’) DO net share %%f$=E:\MigratedData\homedirs\%%f /GRANT:Everyone,FULL

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Migrating home directories to VNX

Installation and Configuration

The emc CelerraCIFSManagement snap-in for Microsoft Management Console can be used to set up VNX Home Directory.  It’s a nightmare to find unless you still have your CD’s that came with your Celerra/VNX but see my previous post on CAVA for a possible working link to it, assuming you already have a powerlink account with emc (free to set up).

emc Home Directory is a very neat piece of functionality whereby you change the users home directory path in AD to read \\cifsserver\home and upon hitting the home share on cifsserver the user will be automatically dropped into the subfolder containing their home directory files.  If a home directory folder doesn’t already exist, it can (and will by default) create one.

You don’t need to create a home share on the VNX upfront, and if you do, HomeDirectory won’t actually start.  You will need a filesystem though.  You start the homedirectory feature from the CelerraCIFSManagement snap in, but remember to do a custom install and untick UNIX Users and CAVA if you have no intention of using them, since Unix Users will attempt to change your AD schema (but will fail if you’re account is not a schema admins member).  You can always add the other features later by re-installing and HomeDirectory will not be affected.

Once the snap in is installed, you can enable Home Directory.  It’ll warn that theres currently no database and that one should be created.  Once this is created, all you need to do is create a rule for users * with a path to your filesystem\<d>\<u> whereby <d> is the domain name they’ve logged in on and <u> is their username.  This is what controls what folder they get dropped off at and/or the folder structure that needs to be created for them if there’s no pre-existing home directory for that user.

The default security is that the users home directory is fully secured so that only they can get to it.  This can be changed by setting domain admins full control on a top level folder for the domain, and making a registry change to the emc Home Directory settings in HKLM\Software\emc\homedirectory and setting 0 to 1 which turns on inheritance.

The full official doc can be read here  HomeDirectory , but that is the basic gist of it, and I found it worked first time, so it’s pretty straightforward to get installed and running and makes subsequent adminstration of home directories a cinch.

Migrating users Home Directories

You can view my previous post on using robocopy or emcopy to migrate data.  Note that since existing home directories are each individually shared in their own right, you may feel the desire to use sharedup to migrate the shares to the VNX – This is not required since each user maps the same share and the datamover paths them through to their own subfolder.  You may want to set up an adminstrative hidden share on the root of the filesystem though, so that you can get to ALL the home directories for the purpose of deleting old users, dealing with any support problems.

There is another issue which will affect your ability to migrate users home dirs (possibly) and that is that Windows 7 et al, tend to display a users home directory as “My Documents” occasionally.  This is due to the desktop.ini file misbehaving and can easily be fixed by simply deleting it without consequence.  Obviously desktop.ini would need to be deleted for all users, and this can be done using this example forfiles script.

 

forfiles /p x:\homedirs /s /m desktop.ini /c “cmd /c attrib -h -s @path”

forfiles /p x:\homedirs /s /m desktop.ini /c “cmd /c del @path”

Or alternatively, exclude desktop.ini from your robocopy or emcopy using /XF desktop.ini in the command line, e.g.

emcopy \\sourcesvr\homedirs\username \\destsvr\homedirs\username *.* /s /nosec /o /a /i /d /c /xf desktop.ini /th 32 /r:5 /w:3

robocopy \\sourcesvr\homedirs\username \\destsvr\homedirs\username *.* /e /fft /np /xf desktop.ini /r:5 /w:3

If a migrated user logs on and is directed to a non-existent home directory hosted on VNX using VNX HomeDirectory, then one will automatically be created and the permissions automatically secured so that only that user can access it*

*default behaviour assuming registry flag on cifs server is still set to 0 and the rule in cifsmanagement snapin home directory settings is set to autocreate

For all other users being directed by homedirectory to their migrated home directories, the security will be inherited from the root of the filesystem*

*assuming the root has everyone full control, or has specific perms set and the registry flag for home directory on the cifs server is set to 1 to inherit all the way to the users home directory

To subsequently secure migrated home directories, we need to list each home directory and then set the permissions for the user on each one*

*assuming the home directory has the same name as the user – probable scenario given the emc HomeDirectory rules use of the <u> variable to represent the users username when creating their homedirectory.

The following command has been used successfully to add a specific Access Control Entry to the permissions on the users home directory, allowing them Full Control.

for /f %f IN (‘dir /ad /b X:\homedirs\’) DO cacls X:\homedirs\%f /e /p %f:F

Note that to execute this command in a batch file, you need to replace %f with %%f

If your filesystem fills up and you want to move, say all users beginning with u6 to a separate filesystem, then the following command is a working example.  robocopy doesn’t allow you do only copy folders matching a wildcard pattern (although it does allow you to copy all folders, excluding certain patterns).

for /f “delims=” %f IN (‘dir u:\u6* /ad /b’) DO robocopy u:\%f j:\%f /MIR /R:1 /W:1 /B /TIMFIX

Update: 28/2/2017  Real World Example: Two passes, two different approaches.  One does inital copy of just usernames beginning with u5, the second generates a list of missing users after the first pass and does a second pass targeting the missing users.

for /f “delims=” %%f IN (‘dir s:\root\u5* /ad /b/ o’) DO robocopy s:\root\%%f t:\root\%%f /COPYALL /R:1 /W:1 /B /TIMFIX /NP /FFT /LOG+d:\mattb\u5mig.log (does first pass on all u5 users)

dir /ad /b /o s:\root\u5* | tr ‘[:upper:]’ ‘[:lower:]’ | tee t:\src.txt | wc –l      (counts 2113 and writes list of all u5 users to src.txt)

dir /ad/b /o t:\root\u5* | tr ‘[:upper:]’ ‘[:lower:]’ | tee t:\dest.txt | wc –l    (counts 2113 and writes list of all u5 users to dest.txt)

comm -23 t:\src.txt t:\dest.txt | tee t:\missing.txt | wc –l  (counts 0 differences and writes list of any missing u5 users to missing.txt)

for /f “delims=” %%f IN (cat t:\missing.txt) DO robocopy s:\root\%%f t:\root\%%f /COPYALL /R:1 /W:1 /B /TIMFIX /NP /FFT /LOG+d:\mattb\u5mig.log (does 2nd pass on any missing users only)

Note: Use /TIMFIX with /B to correct non-copying of datestamps on files, resulting in 02/01/1980 datestamps on all files copied with /B Backup rights.

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Adding additional mailboxes to Outlook

If you want to add an additional mailbox to Outlook, e.g. a shared mailbox for your team to view alongside your regular Inbox, then follow the following instructions.  It’s not intuitive, requires an understanding of the context of what you’re actually doing in Microsoft lingo, i.e. You are adding an additional Mailbox to your existing Outlook Profile, not adding an Additional Mailbox to Outlook per se, (which is done outside of Outlook via the Mail icon in Control Panel and effectively creates an Additional Outlook Profile which you’ll be prompted to choose from each time you subsequently start Outlook).

Tools, Account Settings

Highlight your Account, and click Change (the bit that isn’t obvious / intuitive in my opinion).

Click on More Settings in the lower right hand corner.

The Mailboxes dialog appears and may well be blank.  Click Add to add additional mailboxes.  You’ll likely need to know the name of it.

The additional mailbox will now be added to the folders treeview pane in Outlook in your existing profile.

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Combining multiple columns into one in Excel

This is the easiest way to take the contents of multiple columns and combine them into a single column.

My example uses the ip address 192.168.0.1 where each octet and each . between each octet exists in a different column.

Choose a cell, and enter the following formula…  It’s just like doing a SUM to add numbers, only you use CONCATENATE instead.

Hit enter and voila.

 

 

 

 

 

 

 

 

 

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:

Configuring an Excel ODBC Data Source in Windows 7

A very neat little trick  to compare tables of data in Microsoft Excel to reveal differences, is to highlight the data (all rows and columns containing data), and Define a name for the data, then use an ODBC Driver for Excel to subsequently query the tables as if they were a database.


Each tab in the Excel Workbook can contain a different table of (similar) data and the tables can be linked in Microsoft Query Editor so that SQL queries can be performed against the linked tables to reveal all rows of  data where the same information exists in two separate columns of two separate tables, or even more useful (in my experience) the rows of data where the data that exists in one column of one table doesn’t exist in the other.

In Windows 7, the Data Sources (ODBC) management console will only show drivers for SQL Server, and not the list of different drivers seen in previous versions of Windows.

In order to create an Excel Data Source, you need to close the 64-bit version of the management console and run the following instead..

In the 32-bit odbcad32.exe (shown below), upon clicking Add, you’ll see the long list from which you can choose Excel Driver and point it at your spreadsheet that has the defined data within it.

 

 

Note, if you intend to use this method to query the data in multiple Excel tables using the Query Analyzer, you will have to save a copy of the workbook in the older Excel 97-2000 .xls format before you create an ODBC Data Source for it.

Upon returning to Excel, Create a new tab in the same workbook or create a new Workbook if you like, and Open Query Analyzer as shown…

Choose the Data Source Name (DSN) created using the 32 bit ODBC dialog, choose what tables (Definitions) you wish to include in your query and manually join the tables containing the similar data, then edit the SQL statement as required (usually just changing = for <> produces the sort of results I’m looking for, i.e. differences between the two columns, not similarities.

My personal preference when it comes to comparing columns of data is to export the columns to separate text files, WinSCP the text files onto a Linux/UNIX machine, then use cat | sort | uniq on each file, then comm (not diff) to perform the comparison and show entries in one but not the other of the two files being compared.  I’ll endeavour to cover this method in another post to compliment this Windows 7 oriented post.

[paypal-donation]

Did you like this?
Tip cyberfella with Cryptocurrency

Donate Bitcoin to cyberfella

Scan to Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:

Donate Bitcoin Cash to cyberfella

Scan to Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:

Donate Ethereum to cyberfella

Scan to Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:

Donate Litecoin to cyberfella

Scan to Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:

Donate Monero to cyberfella

Scan to Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:

Donate ZCash to cyberfella

Scan to Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash: