OCI Teleport

Here's a fun lil project, configure Gravitational Teleport on Oracle Cloud Infrastructure Free Tier

Teleport is a "Unified Access Plane" for your infrastructure. In this example I'm going to show how I configured Web SSH Access to OCI via Teleport.

Setup OCI

• Sign up for a free account here: https://www.oracle.com/cloud/free/
• Verify email
• In creating an account you will need to give them a credit card. You shouldn't ever be charged anything as long as you don't use any non-free resources.
• Once you create an account you will get an email after the setup completes.

Create Virtual Cloud Network (VCN)

• OCI Cloud Console > Set up a network with a wizard
• Select root compartments from Comartments drop down on left side of screen.
• This should start the VCN Wizard
• You want "VCN with Internet Connectivity" > Start VCN Wizard
• VCN Name: vcn-demo
• Compartment: username(root)
• Defaults for the rest:
• VPN CIDR Block: 10.0.0.0/16
• Public Subnet CIDR Block: 10.0.0.0/24
• Private Subnet CIDR Block: 10.0.1.0/24
• Use Hostnames in this VCN: Checked
• Next
• Create
• View Virtual Cloud Network

Create Teleport Host

• Settings > Compute > Instances
• Create Instance
• Name: instance-teleport
• Compartment: username(root)
• Placement and Hardware > Edit
• Change Image to Oracle Linux 8
• Make sure Always Free AD & Shape selected
• Networking: Make sure vcn-demo is selected, and in public subnet
• Add SSH Keys
• - Generate & Save Private Key
• Click Create
• Wait a few minutes for Instance to be created

Verify Connecitivity

• Settings > Compute > Instances
• Make a note of the Public IP, use it in the following as $node_ip_addr
• Go to noip.com
• Sign up for free teletele.ddns.net hostname
• Setup hostname teletele.ddns.net to point to $node_ip_addr
• nslookup teletele.ddns.net
• chmod 600 ssh-key-2021-03-12.key
• ssh -i ssh-key-2021-03-12.key opc@teletele.ddns.net

Install Teleport

• sudo su -
• sudo yum-config-manager --add-repo https://rpm.releases.teleport.dev/teleport.repo
• sudo yum install teleport

Setup TLS

• Install Certbot
• Prereq install snapd, docs here https://snapcraft.io/docs/installing-snap-on-red-hat
• sudo dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
• sudo dnf upgrade
• sudo yum install snapd
• sudo systemctl enable --now snapd.socket
• sudo ln -s /var/lib/snapd/snap /snap
• Log out and back in to update paths
• sudo snap install core
• sudo snap refresh core
• sudo snap install --classic certbot
• sudo ln -s /snap/bin/certbot /usr/bin/certbot
• # Fix firewall issue on  next command by opening port 80
• sudo firewall-cmd --zone=public --permanent --add-port=80/tcp
• sudo firewall-cmd --reload
• Update network firewall
• OCI Settings > Networking > VCNs > Root Compartment > vcn-demo
• Security Lists > Default Security List for vcn-demo
• Add Ingress Rules
• Source CIDR: 0.0.0.0/0
• Destination Port Range: 80
• sudo certbot certonly --standalone
• Enter email address
• Read TOS and Agree to share email address
• Enter domain name: teletele.ddns.net
• Delete Network firewall rule allowing traffic on port 80

Configure & Launch Teleport

• vi /etc/teleport.yaml which should be a new file and consist of the following:

• teleport:
      data_dir: /var/lib/teleport
  auth_service:
      enabled: true
      cluster_name: "teleport"
      listen_addr: 0.0.0.0:3025
      tokens:
      - proxy,node,app:f7adb7ccdf04037bcd2b52ec6010fd6f0caec94ba190b765
  ssh_service:
      enabled: true
      labels:
          env: staging
  app_service:
      enabled: true
      debug_app: true
  proxy_service:
      enabled: true
      listen_addr: 0.0.0.0:3023
      web_listen_addr: 0.0.0.0:3080
      tunnel_listen_addr: 0.0.0.0:3024
      public_addr: teletele.ddns.net:3080
      https_keypairs:
      - key_file: /etc/letsencrypt/live/teletele.ddns.net/privkey.pem
        cert_file: /etc/letsencrypt/live/teletele.ddns.net/fullchain.pem

• Start Teleport
• teleport start --config=/etc/teleport.yaml
• Open firewall to port 3080
• Host firewall
• sudo firewall-cmd --zone=public --permanent --add-port=3080/tcp
• sudo firewall-cmd --reload
• Network firewall
• OCI Settings > Networking > VCNs > Root Compartment > vcn-demo
• Security Lists > Default Security List for vcn-demo
• Security Lists > Default Security List for vcn-demo
• Add Ingress Rules
• Source CIDR: 0.0.0.0/0
• Destination Port Range: 3080
• Verify teleport is accessible
• https://teletele.ddns.net:3080/

Running teleport

• Cancel the session we previously started teleport and start via
• service teleport start
• Create a user
• adduser devuser
• /usr/local/bin/tctl users add devuser --logins=devuser,root --roles=admin
• Should see something like the following:
• User "devuser" has been created but requires a password. Share this URL with the user to complete user setup, link is valid for 1h:
  https://teletele.ddns.net:3080/web/invite/sometoken
  
  NOTE: Make sure teletele.ddns.net:3080 points at a Teleport proxy which users can access.
• Visit the tokenized URL to configure a password & MFA for devuser

This was a fun exercise to tinker with a new app. The next iteration of this install would be to include OCI VPN options so the app is never intentionally exposed at any point, and spend a little more time with certs to see if I can simplify the install & config process.

Finding product key for sql server 2008

Great help from this site in finding the product key for an existing sql server 2008 install.

Paste the following into powershell:

function Get-SQLserverKey {
    ## function to retrieve the license key of a SQL 2008 Server.
     param ($targets = ".")
    $hklm = 2147483650
    $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup"
    $regValue1 = "DigitalProductId"
    $regValue2 = "PatchLevel"
    $regValue3 = "Edition"
    Foreach ($target in $targets) {
        $productKey = $null
        $win32os = $null
        $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
        $data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)
        [string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
        [string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue
        $binArray = ($data.uValue)[52..66]
        $charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
        ## decrypt base24 encoded binary data
        For ($i = 24; $i -ge 0; $i--) {
            $k = 0
            For ($j = 14; $j -ge 0; $j--) {
                $k = $k * 256 -bxor $binArray[$j]
                $binArray[$j] = [math]::truncate($k / 24)
                $k = $k % 24
         }
            $productKey = $charsArray[$k] + $productKey
            If (($i % 5 -eq 0) -and ($i -ne 0)) {
                $productKey = "-" + $productKey
            }
        }
        $win32os = Get-WmiObject Win32_OperatingSystem -computer $target
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Computer -value $target
        $obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
        $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
        $obj | Add-Member Noteproperty SQLver -value $SQLver
        $obj | Add-Member Noteproperty SQLedition -value $SQLedition
        $obj | Add-Member Noteproperty ProductKey -value $productkey
        $obj
    }
}

Then enter the following:

Get-SQLserverKey

Boom, should tell you the version of the OS, of SQL Server, and the SQL Server Product Key.

Create java keystore using wallet for Digicert wildcard cert

Had a heck of a time getting a DigiCert wildcard cert converted to a Java Keystore for use with Oracle Fusion Middleware 10.3.6.

Here's the procedure I ended up using to get this working:

1. Create wallet
1. mkdir $MW_HOME/owm
2. Start OWM
3. Create new wallet, store it in $MW_HOME/owm
4. Enter password for wallet (make note of this for later)
5. Create certificate request
1. CN: *.domain.com
2. OU: YourDept
3. Org: YourCo
4. Locality: City
5. State: MI
6. Key Size 2048 << important, our CSR will reject without keysize at least 2048
6. Click on certificate, select operation >> export certifciate request
1. Enter request.csr in name
7. Send request.csr to your unix admins
8. Save wallet, back it up in case you make a mistake with the following
9. Unix admins send back the following
1. star_domain_com
2. DigiCERTCA.crt
10. Get root.crt from https://ev-root.digicert.com/info/index.html
1. Copy every thing starting with -----BEGIN CERTIFICATE----- to -----END CERTIFICATE----- in a text file root.crt
11. In OWM, import certificates
1. Operations, import trusted certificate, select root.crt
2. Operations, import trusted certificate, select DigiCertCA.crt
3. Operations, import user certificate, select star_domain_com
12. Do not check auto login
13. Wallet > save wallet
2. Convert wallet to keystore
1. export ORACLE_HOME=$MW_HOME/oracle_common
2. export PATH=$ORACLE_HOME/bin:$PATH
3. export JAVA_HOME=$MW_HOME/Oracle_WT1/jdk
4. orapki wallet pkcs12_to_jks -wallet $MW_HOME/owm -pwd notapassword -jksKeyStoreLoc $MW_HOME/owm/keystore.jks -jksKeyStorepwd notapassword
3. Verify keystore
1. keytool -list -v -keystore keystore.jks -storepass password
1. make note of alias for your specific cert
4. Implement in Fusion Middleware
1. Login to FMW console
1. base_domain > environment, servers, click on server name
2. Keystores tab 
1. Custom Identity and Java Standard Trust
2. Custom Identity Keystore: $MW_HOME/owm/keystore.jks
3. Custom identity keystore type: jks
4. Custom identity keystore passphrase: notapassword
5. Save
3. SSL Tab
1. Identity and Trust Locations: change to keystores
2. Private Key Alias, alias from step 3.1.1 above
3. Private Key Passphrase: notapassword
4. Confirm Private Key Passphrase: notapassword
2. Go to general tab
1. Enable SSL listen port enabled
2. Set listen port to port 7003
3. Telnet from another machine to servername:7003 to verify connectivity
5. You should now be able to connect to servername:7003 with a browser and not get an ugly cert error message.

Hopefully this will help whoever stumbles for this in the future.

Useful notes:

1. Convert Wallet To Keystore for WebLogic. (Doc ID 1363979.1)
2. http://docs.oracle.com/cd/E16340_01/core.1111/e10105/walletmgr.htm#CJGGFCGC

Shoring up SQL Skills

Goal for 2014:  Revisit weak points in my knowledge of SQL.  Going to try http://www.sql-ex.ru/ to help improve those skills.  I'll report back on how it goes.

Unable to register database with DBCA

Attempting to register a database with DBCA and got the following error message:

TNS-04409: Directory service error caused by: oracle.net.config.DirectoryServiceException: TNS-04405: General error caused by: oracle.net.ldap.NNFLException

Thought thought perhaps the problem was something like this issue with password policys, but it turns out it was simply that I needed to change how anonymous binds are handled. Per Doc ID 947285.1, check the value of orclanonymousbindsflag. I had it set to "2", which didn't work. Setting it to "1" allowed me to register the database, and then I set it back to 2 as it is a more secure setting.

RMAN cannot restore from backupset copied to disk from tape

Attempting to work around an issue with our backup system, I copied the backup pieces from a known-good backup from tape to disk. Then I attempted to catalog those pieces for a recovery attempt. Cataloging those pieces failed.

RMAN> catalog backuppiece '/u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1';

using target database control file instead of recovery catalog
ORA-19870: error while restoring backup piece /u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27067: size of I/O buffer is invalid
Additional information: 2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 02/17/2012 09:37:20
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/oracle/admin/scripts/restore/filetest/DB_3rn2tp0j_1_1

Eventually found Oracle Support Doc ID 1111603.1 on the subject. Essentially, no, you can't catalog a backup piece that's been restored from tape to disk because the header information will be different than the file at rest on tape.

A bad use of find in a nightly script

I was going through cleaning up files on one of the database servers. I happened to find this script.

> cat /home/oracle/scripts/rm_old_alogstrc.shl
cd /u03/oradata/SID/archivelog
find . -mtime +2 -exec rm {} \; 
cd /u01/app/oracle/admin/SID/adump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/bdump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/cdump
find . -mtime +7 -exec rm {} \;
cd /u01/app/oracle/admin/SID/udump
find . -mtime +7 -exec rm {} \;
cd /home/oracle/datapump/SID
find . -mtime +7 -exec rm {} \;
cd /home/oracle/exports/SID
find . -mtime +7 -exec rm {} \;

For the benefit of those not cringing as you read that. Two major things I see wrong with it. The first is that someone had entrusted the OS to clean up old archivelog files older than 2 days. While it's not so common with all the safeguards we put in place for backups, but I can still imagine scenarios where archivelogs don't get backed up off the server in 2 days. This script runs and poof there goes recoverability.

And the second which makes me cringe is the two step file removal. Specifically cd to some directory and then rm all files in that directory. Just imagine what would happen if that script didn't change directory. Next you know whatever the working directory was is effectively wiped out. It was likely the home directory.

The much better way is to combine it into one command, that way there's no accidental deletions.

find /home/oracle/exports/SID -mtime +7 -exec rm {} \;

In a way I wish I didn't see this. Now I wonder what other gems are out there on this server that someone left me.