Oak Leaf Clusters

Just Trying to Keep Up

There are times when you just don’t know what you don’t know. Every day the universe expands its horizon, usually because I step over the edge. Typically, I stumble across a new feature that I goggle at, and wish that I had the time to dive down that particular rabbit hole.

So, today, I start mapping the rabbit holes, with the fond hope that I will remember to come back to them when some segment of empty time becomes available. Here’s the list:

I will update this list as I run into more topics to explore.

PowerShell Remoting

So, you’ve got a new laptop? Congratulations!

Do you need to look at or get files from the old laptop? No problem! We’ll just fire up PowerShell and do a little remote computing to salvage what’s necessary from your previous work.

$cred = Get-Credential
$so = New-PSSessionOption -NoMachineProfile 
New-PSSession -SessionOption $so -Credential $cred -ComputerName "Server1"

What? You say you get an error message?

Set-WSManQuickConfig : <f:WSManFault xmlns:f="http://schemas.microsoft.com/wbem/wsman/1/wsmanfault" Code="2150859113"
Machine="localhost"><f:Message><f:ProviderFault provider="Config provider"
path="%systemroot%\system32\WsmSvc.dll"><f:WSManFault xmlns:f="http://schemas.microsoft.com/wbem/wsman/1/wsmanfault"
Code="2150859113" Machine="win81"><f:Message>WinRM firewall exception will not work since one of the network
connection types on this machine is set to Public. Change the network connection type to either Domain or Private and
try again. </f:Message></f:WSManFault></f:ProviderFault></f:Message></f:WSManFault>
At line:1 char:1
+ Set-WSManQuickConfig -Force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WSManQuickConfig], InvalidOperationException
    + FullyQualifiedErrorId : WsManError,Microsoft.WSMan.Management.SetWSManQuickConfigCommand

You can fix that.

Set-Item WSMan:\localhost\Client\TrustedHosts -Value *

At least that’s what worked for me. Your mileage may vary.

SQLCredentials

The transition from “old guy who’s an Oracle DBA” to “new old guy who’s a SQL Server AND Oracle DBA” continues, slowly … oh so painfully slowly. One of the complications is adopting Microsoft tools with all the complexity and that come with them … and putting aside the familiar Linux/Unix/Solaris tools like bash.

For me, the premier Microsoft tool of choice is PowerShell. That, in itself, is a long slog. There is a lot to learn, and the PowerShell community has techniques to accomplish tasks that I am learning, one by one.

Jupyter notebooks

The real, underlying issue is the difficulty I experience trying to install enough software to begin to learn how to build a .NET Jupyter notebook. I still haven’t completed that task.

It’s a relatively straight path from “fat, dumb, and happy” to stressed-out old guy with an predisposition to use strong, abusive language. Somebody, somewhere recommended “BridgeConf” and I gaily went down that rabbit hole. The Microsoft .NET team had whipped up Jupyter notebooks so data scientists can work, research, code, and preserve the results in one handy little package.

Rob Sewell gave a heck of a presentation on .NET Jupyter notebooks and I was hooked. Unfortunately, things just didn’t go right … The initial set of instructions (https://devblogs.microsoft.com/dotnet/net-interactive-is-here-net-notebooks-preview-2/) persisted in failing for me. The blog post author, Maria Naggaga, hasn’t responded to anybody’s comments for three months. And I’m left with an incomplete install; more installed software than an honest DBA needs; a foul mouth; and uncharitable thoughts about Microsoft systems, software, and practitioners.

Here’s the comment I left:

The trials and tribulations of a New Guy.

Following the blog post, I
1. Download and install Anaconda
2. Download and install dotnet Core SDK (v. 3.1.201)
3. (At the Anaconda prompt), run jupyter kernelspec list. And receive
python3 ~\jupyter\kernels\python3
4. (In the command shell), I invoke > dotnet tool install –global Microsoft.dotnet-interactive
5. I return to the Anaconda prompt to run > “C:\Program Files\dotnet> dotnet interactive jupyter install
6. And I’m rewarded for my efforts with:
Could not execute because the specified command or file was not found.
Possible reasons for this include:
* You misspelled a built-in dotnet command.
* You intended to execute a .NET Core program, but dotnet-interactive does not exist.
* You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

Where did I go wrong?

I’m still searching for a solution …

SQL Server: Encryption

These are study notes for my transition from Oracle DBA to SQL Server DBA.

  • SQL Server database is provided by a series of “keys”.
  • Windows Data Protection API provides the Service Master Key (SMK). (Footnote: What happens when SQL Server is installed on a non-windows machine?)
  • The SMK protects the Database Master Key (DMK).
  • The DMK protects symmetric private keys, stored in certificates, and asymmetric private keys that protect the symmetric key certificates.
  • These private keys are used to finally encrypt the SQL Server data.

Cell level encryption:

  • is also known as column level encryption or granular encryption.
  • Only encrypts data at rest.
  • Encrypts entire columns (not individual cells).
  • Builtin functions to implement column level encryption
    • EncryptByKey/DecryptByKey
    • EncryptByAsymKey/DecryptByAsymKey
    • EncryptByCert/DecryptByCert
  • To implement cell level encryption, applications will need to change (or be customized for cell level encryption)
    • Source column needs a complementary VARBINARY target column
    • Required permission – ControlDatabase
    • Required permission – CreateCertificate
    • Required permission – AlterTable
    • Required permission – at minimum, ViewDefinition of the key or certificate
  • Because the encrypted data changes with every encryption, the data cannot be indexed, primary and foreign keys are do not work
  • Range scans, equality scans must be executed by full table scans

GRANT VIEW DEFINITION ON CERTIFICATE::<cert name> TO <principal>

  • <principal> is the database user

Technical minutia

This post discusses a technical problem that I very recently resolved. The mission is to load Kali Linux in a Virtual Box hosted by a Windows 8.1 laptop. After installing Kali in its virtual machine (VM), the next step is to update and upgrade the operating system. All of this is accomplished using apt, high-level commandline interface for the package management system. Now, what makes this all so interesting is that before I started this, I couldn’t even spell apt, much less know what it means or how it does its work. I’m doing all this work by following instructions in a pdf file.

So, the command to update and upgrade the Kali Linux operating system is apt-get update && apt-get update -y. apt responded with:
Get:1 http://archive-5.kali.org/kali kali-rolling InRelease [30.5kB]
Err:1 http://archive-5.kali.org/kali kali-rolling InRelease
The following signatures were invalid: EXPKEYSIG ED444FF07D8D0BF6 Kali Linu Repository <devel@kali.org>
Reading package lists... Done
W: GPG error: https://archive-5.kali.org/kali kali-rolling InRelease: The following signatures were invalid: EXPKEYSIG ED444FF07D8D0BF6 Kali Linux Repository <devel@kali.org>
E: The respository 'http://archive-5.kali.org/kali kali-rolling InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for respository creation and user configuration details.

Not what I was looking for. The search for the answer began with the author of that pdf recipe I was following. Give the author credit, he did finally send the correct solution to the problem. But I was impatient and needed to be smarter about the commands I was running. The next step was RTFM … read the feckless manuals.

Reading the manpage for apt-secure(8) was intimidating. The job of apt-secure is to prevent unauthorized altered archives from being circulated in the wide, wide world and masquerading as the genuine article. I will return to that manpage in the near future and study it very closely until I make it all clear to myself. But, at the end of the day, the solution to this problem was not found here.

The Stack Exchange actually answered the mail for me.  On their Unix and Linux page, marshki got me in the right neighborhood with a recommendation to first update the signature

sudo apt-key adv --keyserver http://http.kali.org --recv-keys ED444FF07D8D0BF6,

That made sense to me. The string, ED444FF07D8D0BF6, figured prominently in the error messages I received. Intuitively this must be the key itself, or the key identifier. As it turned out though, this didn’t do the trick.

The follow on post, by Steve (and edited recently by PeterH) actually solved the riddle.

apt-key adv --keyserver hkp://keys.gnupg.net --recv-keys 7D8D0BF6 

Clearly, this is going to work better when I go to the correct key server. And I find it very interesting that of the eight hexadecimal characters in the key signature, the last four (7D8D0BF6) appear to corelate to the receive keys.

Once the correct keys were obtained from the correct key server, I was able to update and upgrade the Kali installation.

Here are some final observations:

Nothing, but nothing, ever gets done without a lot of very hard work.

It is always a risky practice to attempt to simply “follow a recipe”. Read the procedure, understand what the commands are doing, and pay attention to detail.

Kali is a specialized tool, an off-spring of debian Linux, designed for ethical hackers to perform penetration testing.

Time Zones

I thought it was going to be easy. The issue is:

Given a global enterprise that records world-wide transactions with a timestamp in UTC, display for each transaction the local time of the transaction.

Hmm … the Oracle NEW_TIME function ought to fill the bill. Now, how do I determine which timezone the transaction belongs to?

It appears I will need to use tz_world, an efele.net tz map (or its successors) to assign a time zone to each transaction location, and calculate the new time from there.

This is a work in progress … stay tuned as I dream up the solution.

Getting started with Vagrant

Getting started with Vagrant

So, I got sucked down another rabbit hole and decided to explore “Vagrant”. The environment here is

  • MacBookPro 15″ with
  • macOS Sierra, v 10.12.6;
  • processor 2GHz Intel Core i7;
  • 8 GB 13333 MHz DDR memory
  • VirtualBox v 5.1.28

Installation appeared to complete smoothly, without complications.

The next section in the “Getting Started” document gives two command line commands:

$ vagrant init hashicorp/precise64

$ vagrant up

First command appeared to complete normally. vagrant init hashicorp/precise64 returned:

A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

And I did indeed read through the Vagrantfile. Fascinating! These are good comments. They are explanatory, lucid, and not too opaque. For example:

# Create a forwarded port mapping which allows access to a specific port
# within the machine from a port on the host machine. In the example below,
# accessing "localhost:8080" will access port 80 on the guest machine.
# NOTE: This will enable public access to the opened port
# config.vm.network "forwarded_port", guest: 80, host: 8080

But wait a minute! The actual configuration directive, “config.vm.network “forwarded_port”, guest: 80, host: 8080″ is commented out. Shouldn’t that hash mark be deleted? Is this comment simply showing the vagrant defaults? Or am I supposed to select what I think are the relevant, desired config directives and un-comment them.

I’m kind of a literal guy … Absent specific, enumerated instructions, I don’t make self-initiated changes.

That is especially true when we get to the segment of the Vagrantfile that deals with VirtualBox. The Vargrantfile lists:

# config.vm.provider "virtualbox" do |vb|
# # Display the VirtualBox GUI when booting the machine
# vb.gui = true
#
# # Customize the amount of memory on the VM:
# vb.memory = "1024"
# end
#

Hmmm.

So, I proceed to the next step.

$ vagrant up

That command completes with errors. Here’s the text the command returned:

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'harshicorp/precise64' could not be found. Attempting to find and install...
default: Box Provider: virtualbox
default: Box Version: >= 0
The box 'harshicorp/precise64' could not be found or
could not be accessed in the remote catalog. If this is a private
box on HashiCorp's Vagrant Cloud, please verify you're logged in via
`vagrant login`. Also, please double-check the name. The expanded
URL and error message are shown below:

URL: ["https://vagrantcloud.com/harshicorp/precise64"]
Error: The requested URL returned error: 404 Not Found

Well! That wasn’t expected!

Did I miss an instruction to “login to vagrant”? Nope. I’ve gone back to the documentation and looked very carefully an instruction to “login”. I can’t find it.

Not to be deterred, I call for “vagrant login” and receive the following message:

In a moment we will ask for your username and password to HashiCorp's
Vagrant Cloud. After authenticating, we will store an access token locally on
disk. Your login details will be transmitted over a secure connection, and
are never stored on disk locally.

If you do not have an Vagrant Cloud account, sign up at
https://www.vagrantcloud.com

OK, I can go along with the gag. I toddle off to vagrantcloud.com and create an account. I return to my terminal try the vagrant up command again… And still get the http error 404: file not found. <!SIGH/>

Let’s go see where this precise64 file is.

It turns out that navigating to https://vagrantcloud.com/harshicorp/precise64 actually lands me in https://app.vagrantup.com/harshicorp/boxes/precise64 AND a very politely worded HTTP 404 says:

404 Not Found

Sorry, the page /harshicorp/boxes/precise64 could not be found.

This error could mean one of three things:

The resource doesn't exist
The resource exists, but requires proper authorization to access
The resource exists, but its owner organization is delinquent

If someone has linked you to this resource, ensure that they have given you proper permissions to access it.

So what does a DBA have to do to get a working URL to this demo box?

Old dogs …

Old dogs can learn new tricks. I’ve seen it with two eyes and it’s inspiring. On the other hand, old dogs struggling to keep up with the technology pack is mind crushing. It is hard for me to have to accept that I need to work this hard to simply build a virtual machine.

Something to look out for!

It’s a new laptop … and virtualization technology hasn’t been enabled. Only a moderate amount of document scrambling was required to figure out I needed to get into the BIOS and enable it.

Managing stale alerts in dbConsole (Oracle OEM 10.2.0.4)

Just to add context, I am a subcontractor DBA on a contract that was wrested from a 20 year incumbent. The transition was poisoned by high passions as one after another of the incumbent’s employees left to find new work. The state of the databases that we inherited is only now becoming apparent, three years into a four year contract.

We are gradually cleaning up all the loose ends left untidy by the abrupt change of custody. The alert feature of the dbConsole screen is one of these loose ends. This applet was rendered useless by the hundreds of stale alerts, most of them complaining that one historical tablespace or another was 99% full and needed attention.

Up to now, the only way I’ve been able weed the useless tablespace reports is to directly manipulate the table that supports applet. These reports are found in the table, SYSMAN.MGMT_SEVERITY. What I cannot yet tell is whether or not there are other features or functions that depend on this table and its rows.