Category: ITOps

There are several different ways to do this, I find the easiest is to create a view then use SQL Developer’s Database Export wizard.

Requirements:

• SQL Developer

Step 1: Create a view

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-VIEW.html

Step 2: Use SQL Developer’s Database Export wizard to export the view to your desired file format

In SQLDeveloper, Tools → Database Export

1. Select the correct DB connection
2. Uncheck ‘Export DDL’
3. Under ‘Export Data’ change Format to CSV or XLSX (or whatever file type is desired)
   1. Adjust file names and output dir as desired, click Next
4. Uncheck all except ‘Views’, Next
5. Ensure that you select the correct schema selected, if the schema is not the default schema for your user, click ‘more’ – select the correct schema and change type from ‘All Objects’ to ‘View’
6. Click ‘Lookup’ and you will see the view you created in Step 1
7. Select the View and hit the blue arrow to move the view into the lower box, then click next, review and Finish.. your export will now run with a status box for the task.

Step 1: List all repos

Step 2: Clone all repos (pulling where repo dir exists)

Why?

With almost all of our clients now preferring AWS and Azure for hosting VMs / Docker containers we have to manage a lot of AMIs / VM images. Ensuring that these AMIs are correctly configured, hardened and patched is a critical requirement. To do this time and cost effectively, we use packer and ansible. There are solutions such as Amazon’s ECS that extend the boundary of the opaque cloud all the way to the containers, which has a number of benefits but does not currently meet a number of non-functional requirements for most of our clients. If those non-functional requirements we gone, or met by something like AWS ECS, it would be hard to argue against simply using terraform and ecs – removing our responsibility for managing the docker host VMs.

Anyway, we are making some updates to our IaaS code base which includes a number of new requirements and code changes to our packer and ansible code. To make these changes correctly and quickly I need a build/test cycle that is as short as possible (shorted than spinning up a new EC2 instance). Fortunately, one of the benefits of packer is the ‘cloud agnosticism’… so theoretically I should be able to test 99% of my new packer and ansible code on my windows 10 laptop using packer’s Hyper-V Builder.

Setting up

I am running Windows 10 Pro on a Dell XPS 15 9560. VirtualBox is the most common go-to option for local vm testing but thats not an option if you are already running Hyper-V (which I am). So to get things started we need to:

1. Have a git solution for windows – I am using Microsoft’s VS Code (which is really a great opensource tool from M$)
2. Install packer for windows, ensuring the executable is in the Windows PATH
3. Create VM in Hyper-V to act as a base template (I am using Centos 7 minimal as we use https://www.centos.org/download/CentOs AMIs on AWS)
4. Install Hyper-V Linux Integration Services on the Centos 7 base VM (this is required for Packer to be able to determine new VMs’ IP addresses) – if you are stuck with packer failing to connect with SSH to the VM and you are using a Hyper-V switch this will most likely be the issue
5. Add a Hyper-V builder to our packer.json (as below)

...
  {
    "clone_from_vm_name": "sonet-ami-template",
    "shutdown_command": "echo 'packer' | sudo -S shutdown -P now",
    "headless": true,
    "ssh_password": "{{user `ssh_username`}}",
    "ssh_username": "{{user `ssh_username`}}",
    "switch_name": "Default Switch",
    "type": "hyperv-vmcx"
  }
...

Now, assuming the packer and ansible code is in a funcitonal state, I can build a new VM and run packer + ansible via powershell (run with administrative privileges) with:

packer build --only hyperv-vmcx packer.json

It has been a long while since I looked at RDS – with Azure, Office 365 and Server 2016 there seems to be a lot of new (or better) options. To get across some of the options I have decided to do a review of Microsoft’s documentation with the aim of deciding on a solution for a client. The specific scenario I am looking at is a client with low spec workstations, using Office 365 Business Premium (including OneDrive), Windows 10 and have a single Windows 2016 Virtual Private Server.

Some desired features:

• Users should be able to use their workstations or the remote desktop server interchangeably
• Everything done on workstations should be replicated to the RDS server and visa-versa
• Contention on editing documents should be dealt with reasonably
• The credential for signing into workstations, email and remote services should be the same (ideally with a 2FA option for RDS)

Issues faced:

• The Office 365 users were created several months before the RDS server was deployed
  • The Azure AD connect service which synchronizes users in an Active Directory deployment with Office 365 user (Azure AD) is a one way street, assuming the ‘on-prem’ active directory object exist already and only need to be create in Azure AD (Office 365) – see the work around for this here
• Office 365 licensing for ‘shared’ computers means that Office 365 Business Premium users can’t use a VPS – so entrerprise plans of business plus must be used.

How to configure Remote Desktop Services? After getting Active Directory installed and configure to sync with Azure AD I now need choose and implement the RDS configuration.

Starting with the Microsoft Doc we have the following options:

• Session-based virtualization – Many users per host
• VDI – Virtual machine for each user — note that if your server is already a VM this isnt really an option (nested VMs are not ideal)

Based on our clients situation – session-based make much more sense for now. Next up – what are we going to publish to the users logging into remote desktop service?

• Desktops – Providing users with the full desktop experience
• RemoteApps – Users run apps that seem to be running locally but are in fact being served via RDS

Desktops makes sense for now. So – how do we set up a Session-based desktop for remote access by multiple users?

1. Add the required roles to the RDS servers (see: https://docs.microsoft.com/en-us/windows-server/remote/remote-desktop-services/rds-deploy-infrastructure)
2. Create an AD service and link it to office 365 with Azure AD Connect

As Microsoft says:

• You still must have an internet-facing server to utilize RD Web Access and RD Gateway for external users
• You still must have an Active Directory and–for highly available environments–a SQL database to house user and Remote Desktop properties
• You still must have communication access between the RD infrastructure roles (RD Connection Broker, RD Gateway, RD Licensing, and RD Web Access) and the end RDSH or RDVH hosts to be able to connect end-users to their desktops or applications.

After setting all of this up I am very happy with the results. The single source of truth for user must be the ‘on-prem’ AD. Syncing an on-prem AD service to Office 365 is almost seemless with some miner tweak required that are fairly easy to find with some googling.

Importing users from Office 365 to an on Prem-AD can be required in cases where an organisation who has been using Office 365 wants to start using a Remote Desktop Service or alike. To reduce the number of passwords and provide single sign on (or at least same sign on) the Windows Server my have Azure AD connect installed and be syncing with the businesses Office 365 account. The problem is that out of the box Azure AD connect is a one way street. It only creates object on the Azure side – it does not import Office 365 users into the server’s Active Directory.

To get users from Office 365 created in a new Windows Active Directory Service:

• If not already installed, install:
  • Azure active directory powershell module: https://connect.microsoft.com/site1164/Downloads/DownloadDetails.aspx?DownloadID=59185
  • Microsoft Sign in Assistant: https://www.microsoft.com/en-us/download/confirmation.aspx?id=41950
• Open up PowerShell, connect to an Office 365 services, download a list of users and import to AD:

## Connect to Office 365 with PowerShell
Set-ExecutionPolicy Unrestricted -Force # can be reverted at the end
$O365CREDS = Get-Credential
$SESSION = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365CREDS -Authentication Basic -AllowRedirection
Import-PSSession $SESSION
Connect-MsolService -Credential $O365CREDS 
# For more details see: https://oddytee.wordpress.com/2013/03/21/connect-to-office-365-with-powershell/

## Get a list of Office 365 User Objects 
Get-User | Export-Csv "C:\O365Export.csv" -NoTypeInformation 

## Use that list to create new AD users - slightly modified from source material
import-csv C:\O365Export.csv -Encoding UTF8 | foreach-object {New-ADUser -Name ($_.Firstname + "." + $_.Lastname) -SamAccountName ($_.Firstname + "." + $_.Lastname) -GivenName $_.FirstName -Surname $_.LastName -City $_.City -Department $_.Department -DisplayName $_.DisplayName -Fax $_.Fax -MobilePhone $_.MobilePhone -Office $_.Office -PasswordNeverExpires ($_.PasswordNeverExpires -eq "True") -OfficePhone $_.PhoneNumber -PostalCode $_.PostalCode -EmailAddress $_.SignInName -State $_.State -StreetAddress $_.StreetAddress -Title $_.Title -UserPrincipalName $_.UserPrincipalName -Enabled $True -AccountPassword (ConvertTo-SecureString -string "Password" -AsPlainText -force) }

Sources:

• https://oddytee.wordpress.com/2016/03/14/sync-new-active-directory-with-existing-office-365-tenant/