Connecting to an Oracle Db from PowerShell

Sometimes you need to be able to connect to databases from PowerShell, in this case an I needed to connect to an Oracle database. First, you’ll need to download and install the Oracle client. In my case, I used the win64 11gR2 client. Make sure you get the appropriate version to suit the version of Oracle (e.g. Oracle 11g) and the Windows machine you are installing it on (win64). You can download clients from here.

After that, all you need is the PowerShell code and knowledge of the database you’re going to connect to. The PowerShell code below can be used to connect by either an Oracle SID or Service Name – choose which works best for you.

## To connect by Service Name
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_servicename = "servicename"

## To connect by SID
$ora_server = "hostname"
$ora_user = "username"
$ora_pass = "password"
$ora_sid = "sid"

## by SID
$connection = new-object"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SID=$ora_sid)));User Id=$ora_user;Password=$ora_pass;")

## by ServiceName
$connection = new-object"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ora_server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=$ora_servicename)));User Id=$ora_user;Password=$ora_pass;")


$query = "select attribute from table where (attribute = 'value') order by attribute"

$list_set = new-object
$list_adapter = new-object$query, $connection)
$list_adapter.Fill($list_set) | Out-Null
$list_table = new-object
$list_table = $list_set.Tables[0]
foreach ($entry in $list_table) {


Office 365 License assignment using AD Groups

If you’ve searched for Office 365 licensing using AD groups, you usually find PowerShell using AD groups to provision Office 365 licenses, but that’s not what this is.  It’s also not using Azure Automation to run PowerShell scripts within Azure to perform licensing.  This is Azure AD functionality (in Preview) to perform Office 365 licensing based on AD group membership.

It’s been announced only recently, and if you’re currently performing licensing of users in Office 365 with a PowerShell scripts, you should definitely have a look at this functionality.

Azure AD Group Licensing using E3

Of course, this functionality is inside Azure Active Directory (currently in preview in the new Azure Portal). In order to assign licences via groups, you need to have an Azure AD licence. The Microsoft documentation states only an Azure AD Basic licence is required.  You can check that out information here.

If you just want to have a look at the group licensing functionality and don’t want to buy a license, you can sign up for a trial for the Basic and the Premium licences inside your Office 365 tenancy for no cost.  You could use a trial for either of these to give this a go.

Azure AD Basic License ScreenshotAzure AD Premium P1 License Screenshot

If you don’t have Azure, you can sign up with a free trial. There is currently an offer to sign up and get $200 credit for 30 days.  Check it out here.

If you’re signing up for the Azure with a different account or have signed up to Office 365 and azure with different credentials, check out here to find out a how to connect them together. To assign the licences in Office 365 through Azure AD Preview, you need to be able to access the Azure AD that your Office 365 tenancy uses from within the Azure portal.

Once you’ve made it into Azure, it’s actually pretty straight forward. Microsoft have put a guide together on how to do this.  I didn’t think it was easy to find, so here is a link to it – link.

If you’re interested in the full process to apply the functionality to groups, I took a screenshot for the entire process in the Azure AD Preview GUI.

Azure License Application Screenshot

Of course, groups are used to assign the licences. It requires a security group and works with groups synced from an on premise directory and Azure Cloud only groups, both direct and dynamic groups. However, you should be aware that currently nested groups are not supported. If you want to use them regardless, be aware that licenses will only be applied to those users with direct membership to the group and not those in the nested groups.

I was pleasantly surprised as the licences re-evaluated quite quickly when the group was updated, both adding and removing licences according the group membership.


One of things you should be careful of with the groups is changes to the license configuration. Every time this is changed, it removes licenses from every user that is in that group and after that it reapplies the licenses to he new configuration.  If you’ve got a lot of users in the group, it could potentially cause an outage to users and risk data loss.

To work around this, I would lean towards two methods.

1. Create a new group with the wanted license configurations.  Add all the users from the old group into the new one.  Make sure the new licenses apply and remove the users from the old group.

2. Create multiple groups, with each group having a specific license applies to it. (E.g. A group which only applies the E3 Exchange Online part of the license).  For each part of the license you want to assign, create a new group – although you’ll have to watch out for license dependencies. (E.g. OneDrive requires SharePoint.)

Migration is quite interesting, as you can have the same license on the same user both assigned directly from PowerShell and inherited from groups. Both will exist and you can get conflicts between them.  The good thing is that if there is a conflict you can easily get results of the license application in Azure to find any conflicts.

The recommended migration path is to leave your PowerShell script in place while group licensing is configured to provide exactly the same licenses for your users that the PowerShell script applies.  Once the same licences have been applied by group, disable the licensing PowerShell Script.  Then you need to start to remove the licenses assigned with PowerShell. Of course this is recommended to be performed in batches.

If you’re used to checking if users are licensed, both the Office 365 admin portal and the existing PowerShell cmdlets cannot see the group license application – they can only see if there is a license applied.  There are some PowerShell scripts available to provide information on the number of licences assigned and what type, but it’s still limited.  You can check those out here.  Once Azure AD is out of preview, hopefully we’ll get visibility in Office 365 and PowerShell.