Tuesday, February 14, 2017

Elbow Room

Duncan and Trixie cuddling
What do you do when you have an old SQL Server that is running out of space?   Well, you can free up space by deleting unneeded files, add additional space, or buy a new server, right?

Well, we have a server was constantly running out of space on its tiny 50 gig system partition and we were months away from retirement (best case), we've deleted all unneeded junk and there was no possibility of adding space.

I pulled out my favorite tool, SpaceSniffer, which shows disk usage.  Six gigabytes were taken up in the c:\windows\Installer folder.

We had adequate space on the data partition, so the plan was to move the installer folder to the D drive.  Since many software packages need the information in this folder in order to work, we needed to fool them into thinking the folder was still on C.

My Coworker, Mike, remembered an article he saw on Microsoft.  The solution was to create a symbolic link to the folder.  The kicker is that this has to be done at system level, which is higher than just administrator.  After some Googling, here are the steps to do what we did.

  1. Copy the c:\Windows\Installer folder to the D Drive
  2. Delete the c:\Windows\Installer folder
  3. Download the Sysinternals PSTools from https://technet.microsoft.com/en-us/sysinternals/pstools.aspx
  4. Unzip these tools to a folder on the server.
  5. Run CMD as administrator on the server
  6. change into the PSTools folder, and execute PsExec by typing
    PSEXEC -i -s -d CMD
  7. in the new Command Window that opens, test that you're running as system by typing
    whoami /user
    You should see "nt authority\system s-1-5-18"
  8. in the new command window, type
    mklink /d c:\Windows\Installer d:\Installer
    this will create the link, so software that navigates to C:\Windows\Installer will go, instead, to d:\Installer. 
  9. Reboot
Viola, the server now had 6 gig free on C: and was now happy!

Friday, June 10, 2016

When your backup is against the wall

Our flooded backyard on Christmas Day 2015
So, we had a developmental database which a developer wanted to clean up and put on a production server.  I did a backup, and cleaned up the database and had it moved to the new server.

But, communication is difficult.  He wanted to clean up the database once it landed on the new server, not while it was on the old server since there was software using it.

No problem, I'll restore the database.  Except, I made a boo-boo.  I used the GUI to do the backup, and wound up doing a transaction log backup.

If you're not familar, SQL Server (and most other enterprise database platforms), SQL can make a full backup of its data in a handy file.  It also records all changes made in what is called transaction log backups.  Transaction log backups are much faster, so you can do them much more often.  On this database, it was every 30 minutes.

In order to restore the database, I had to restore the full backup and every log file after.  So the key to this is the WITHNORECOVERY keyword.  Do the restore as a single script, with the "WithRecovery" tag on only the last entry

restore database OOPS from disk ='d:\OOPS_backup_2016_06_09.bak' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_2016_06_10_080020_0584990.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_083023_4130230.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_090017_4780062.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_093017_3391104.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_100019_1231400.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_103017_7468920.trn' WITH RECOVERY

NOTE:  If you are getting an exclusive access error, you'll have to put the database into single user mode by doing a 
Then, after you're done, put it back in multiuser mode by doing a
 Also, you might need to use the REPLACE option on the full backup by doing a WITH REPLACE, NORECOVERY

Tuesday, May 31, 2016

Do I have the Right?

So many times, I needed to see what groups I'm in, who else is in a group, or get a list of Domain groups.

This is trivial from the command line.

NET USER - NET USER does the following

  • Retrieve a list of users on the machine from which you execute the command.
  • Retrieve a list of users from the domain
  • Retrieve the user's account information for a local account
  • Retrieve the user's account information for a domain account


  • NET USER - Retrieve local user list
  • NET USER  /DOMAIN - Retrieve domain user list
  • NET USER UserName - Retrieve local user account 
  • NET USER UserName  /DOMAIN - Retrieve domain user account 

NET GROUP, NET GROUPS, NET LOCALGROUP- NET GROUP series of commands does the following.  Note:  NET GROUP and NET LOCALGROUP (singular) get information for a single group while NET GROUPS and NET LOCALGROUPS (plural) gets information about the groups that exist.
  • Retrieve a list of groups on the machine from which you execute the command.
  • Retrieve a list of groups from the domain
  • Retrieve the list of users that belong to a specific group on the local machine
  • Retrieve the list of users that belong to a specific group on the domain 
  • NET LOCALGROUP "group name"
  • NET GROUP "Group Name" /DOMAIN

Find the Computer's Make, Model and Serial Numbers

My Boy Benny
So we have a database of all servers, databases, applications, service accounts and such.  I had to make sure all of our servers are in the database correctly.

When I have only the computer name, but no information, I can find a great deal of info by getting in the command line after reporting in and using some handy commands.

Get computer make model and type
 - wmic computersystem get model,name,manufacturer,systemtype

Get serial number
- wmic bios get serialnumber

Friday, February 19, 2016

Copy and Paste Registry Keys

So you need to copy a key from one part of the registry to another?  Don't want to use a third-party program?  It's easy cheesy, with RegEdit and Notepad.

  1. In RegEdit, select the key you wish to copy.  right-click and select export
  2. Save the *.reg file somewhere
  3. Edit the Reg file with notepad, manually changing the key location.
  4. Save the Reg file
  5. In RegEdit, click on File then Import.  Navigate to the newly changed Reg File, and viola, you're done!

What's in a Name?

Plumbing under my sink
I had a problem.  A Microsoft SQL Server job I created needed to send a file via secure FTP. Since Microsoft's SSIS doesn't believe that secure FTP exists, I had to use a third-party application.

It works wonderfully.  Except (there is always an exception) the first run of the application prompts you to cache the fingerprint of the server's key in the registry.  If the fingerprint isn't in the registry, the process fails when it runs in batch mode.

Since I don't have the service account's password, I can't run the application under its credentials to store the fingerprint.  But, it's really a simple hack to copy the registry keys into the service account's registry.  Except the registry doesn't show the user hives by name, but by their SID(windows Security Identifier.)

You'd think that you would have a simple a command to tell you what the SID is for an account. There is if it's local, or if the account is currently logged in.  Our service accounts are domain accounts.  If I could log in as the service account, I wouldn't be hacking the registry.

So how do you find them?  Well, Windows creates a profile for each account.  If you go to Hkey_Local_Machine\Software\Microsoft\Windows NT\CurrentVersion\ProfileList, you'll see one entry for each profile.  The entry is the SID, but under each profile, you'll see the ProfileImagePath key, which will have a plain-text username for the account.

Friday, December 18, 2015

Liberating Excel

My Son, as Captain America
At work, we have to do a bi-weekly timesheet and a monthly timesheet.  The administrative types send out bi-weekly timesheets each every two weeks.

They didn't send me one.  That's not a problem, I think.  I can just create a copy, and change the dates.

Nope, they password protected the sheet.  It's nearly the end of the day, before a holiday week, I'm taking vacation the next week, and I have to have my timesheet in just a few minutes,

Thanks to The University of Wisconsin, Green Bay (https://uknowit.uwgb.edu/page.php?id=28850), here is a quick answer.  Simple brute-force the password with Excel's handy VBA editor.

Sub PasswordBreaker() 'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
Worked like a charm, and I submitted my timesheet with time to spare!