Access Web Apps in Sharepoint Online – An overview

One of the many features of SharePoint Online is the ability to have Access Web Apps in your SharePoint sites, allowing you host some of your data in the cloud.

Previously this has been a complex task, using requiring a specific set of skills, but not with Access Web Apps.

Creating a new Access Web App
The first thing that should be said is that you still need Access 2013 installed on your computer. More to the point, someone will. The Access Web App you build has to be built using Access 2013, but once its built it can be used by anyone with access to the SharePoint site, regardless of the device they are on.

Creating your first web app is straightforward. From your SharePoint Site, you can go to the ‘cog’ and select ‘Add an app’

access1

Once you’re here, on the of options you’ll get is the Access App. Clicking this will simply ask you for a name. You can also upload an access app you made somewhere else.

Access2

Once that’s done and you try use it, you’ll get presented with a holding page asking you to open the app in Access to start building. Once you’ve opened your app in access, you’ll see a familiar interface, similar to any other new Access 2013 database.

Building your database
I’m not going talk much about creating databases and managing access databases in this post. Instead, I’ve just created a tiny 2 table database as a sample to have a play with.

I’ve been using Access for year (since Access 2003), and have worked with some very large, complex databases, so I didn’t find this difficult. But, I don’t think anyone will.

It is really quite easy to build a basic Access Web App using Access 2013. Some of the more advanced features will take some work, but there is plenty of support and documentation available for those looking to take it further.

There are also loads of templates to get you going or, as I’ve done you can start with a completely blank database and built it yourself.

As with any other Access Database, you can also import data from another Database, spreadsheet or other source.

access7

Using your Database
Once you’ve got your database built and you launch the app, you’ll be able to use your forms just like any other web app, and very quickly be able to add the data.

Each table automatically gets a list view and datasheet view which will be available in the web app.

access5

access6

Not everyone needs Access
One of the major advantages here is that you can anyone with access to the SharePoint App will be able to use the database. You need Access installed in order to design the database, but the app can be used by anyone, reducing the licensing requirements along with the need to train other users on

Where is my data?
When working with an Access Web App, the data is stored in an Azure SQL Database for your app, and there are a number of ways you can access this data, which I have covered here: Accessing your Access Web App’s Data

Conclusion
They won’t be for everyone, but Access Web Apps are an easy, quick way to start getting data into the cloud. I’d definitely recommend that you investigate it further before making any decisions. It may just be the thing you need.

Features available in access web apps
For some more information on the differences between and Access App and an Access Database have a look at this post:

http://office.microsoft.com/en-us/access-help/should-i-create-an-access-2013-app-or-an-access-desktop-database-HA104136335.aspx

Accessing your Access Web App’s Data

Maybe you’ve been using Access Web Apps for a while now and have got to the point as I have where i want to do something more with the information. I want to be able to report on it, export it, play with etc.

Immediately there’s a bit of an issue, where are all my export options, my reporting options? They aren’t there.

Access Web App’s has been built, unfortunately, with no native reporting, which means I need another method for getting to my data.

Where is my data
Firstly, we need to consider where the data is actually stored. When you add your Access App in SharePoint, and new, dedicated Azure SQL Database is created for it.

If you open your Web App in access on your desktop and go to the ‘info’ tab, you’ll be able to see where the database is stored.

accessData1

Accessing the data
In the ‘info’ tab, you’ll also notice that there are 2 other options

Report on my Data
This option will create local access database which links to the table in the web app’s Azure database.

This will also you to use the full power of the Access Reporting services on your data.

Connections
This option will allow you to manage how your web app’s data can be accessed. There are a number of options here, so you need to carefully consider what is best for your app and the integrity of the data inside it.

accessData2

Accessing my Data from outside of Access
I’m only looking to report on my data and don’t need manipulate it in anyway, so I’ve opened connected and enabled the ‘Enable read-only connection’.

Once this is enabled, the option ‘view read-only connection information becomes available to be viewed, which will give you the Server name, database name and the credentials needed to connect other software to this database.

Accessing your Data from SQL Management Studio
Now you have enabled the connection, you can access the data from lots of differnet places, including SSMS (SQL Server Management Studio)

To this you need to specify the server and credentials as you would normally, but as this is server we don’t own or have any control over, you also need to specify the exact database you wish to work with from the ‘Connection Properties’ tab.

AccessData4sql2

Once you are connected, you have access the Database as you would normally and can work with it.

AccessData4sql3

Note: As the whole web app is stored in the SQL database you need to be very careful not to mess with any of the tables that don’t look like your data.

Accessing your data from Excel
Similarly, you can use Excel to access this information using the ‘From Data Connection Wizard’

accessData5.excel

 

Salamander Active Directory – Feature Focus – Calendars

One of the most used features of Salamander Active Directory is the ability to import Pupil and Staff Timetables into Exchange, both on-premise and in Office 365.

A Staff Calendar from SIMS.net displaying in Exchange 2013

A Staff Calendar from SIMS.net displaying in Exchange 2013

Categorisation
Events can be categorised to suit your needs. Here we have events which are categorised by Subject, but this could equally be the Year of the group or the Room.

cal2

 

Salamander Active Directory – Exchange Calendars

How much does it cost?
This feature is built into Salamander Active Directory at no extra cost.

This is available to all Salamander Active Directory customers using Sims.net/Facility CMIS or iSAMS, with Exchange 2007/10/13 on-premise or Exchange Online.

Salamander Soft
For more information about salamander soft or our products, please contact us: info@salamandersoft.co.uk or visit our website: www.salamandersoft.co.uk

 

Yammer? What’s it all about?

Since Yammer was added to our office 365 package, we’ve been using it internally at work and I’ve been thinking about how our customers might be able to utilise it in the future.

What is it?
Yammer is being billed as the ‘Enterprise Social Network’. It’s use has a familiar feel, and students and staff alike will have no difficulty in picking it up. Much like Facebook, there is a newsfeed, but this is much more than that. Yammer is also great for sharing files between co-workers, and having discussions in groups.

So, how much will it cost me?
The good news here is that if you are an Education institution using Office 365, the answer will be nothing.

From April 1st 2014, Microsoft are adding Yammer Enterprise to all of the Office 365 Education plans (A2,A3 and A4)

How might we use it?
Initially, you might think that this is just a gimmick, another toy for people to use with very little useful points. I know I did.

However, after spending some time using it, and thinking about it, I’m confident that there will be a future for Yammer in Education.

In a similar way to Facebook, Yammer supports groups, which allows people to communicate and collaborate on specific topics, or even class by class.

Imagine if each class had a Yammer group where they could share ideas and chat about work, from anywhere.

If your using SharePoint, you can also embed the feed from a group in the class site, and collaborate directly through that, in effect adding to your SharePoint environment.

There are also plans to integrate Office Web Apps with Yammer too, so your staff and pupils could collaborate directly on documents directly from the document itself.

Use it from anywhere
As yammer is part of Office 365, there are many apps available for almost every device and platform, allowing users to collaborate from wherever they are, using whatever they already have. No extra hardware required.

In short, there is a lot more to Yammer than meets the eye, and its well worth investigating what it can do for you.

Finding old, inactive users and computers in on-premise Active Directory using Powershell

One of the questions I get asked frequently is “How can I work out how many of my users or computers are inactive or old?”

Well, there are a number of ways to do this, but I’ve found that the easiest and certainly the quickest has been to use PowerShell.

Now, ill say right at the outset, that all I’m doing here is showing you the quickest way I’ve found to get a list of inactive or disabled users, nothing more.

I know there is much, much more that we can do with it, and get really clever with the scripting. I know, I’ve done it, but for the most part, the people who ask me literally want a list of users, nothing more.

So that’s all I’m going to do.

What am I looking for?
Active Directory stores a whole lot more information than you expect inside the objects for users and computers.  A couple of these are useful in determining how long account has been dormant or inactive.

These might include ‘lastLogonTimeStamp’ or looking at the ‘userAccountControl’ to see what their status is. There’s a load more you can look at too, but you don’t need to. You can get the basic details you need from a single command-let.

So, How do I get this information out using PowerShell.

NOTE: The following assumes that you are in PowerShell, have added the Active Directory modules and have relevant AD Permissions.

There are a number of solutions for this, most of them are using the Get-ADUser or Get-ADObject cmdlets. There are many articles around on how to do this, but for the most part, it is much easier to use the cmdlet ‘Search-ADAccount’

Search-ADAccount can be used with a number of switches, but the most common ones are:

-PasswordExpired
-PasswordNeverExpires
-AccountDisabled
-AccountExpired
-AccountInactive

Today, we’ll briefly look at -AccountInactive and -AccountDisabled

Disabled Accounts
We all disable accounts regularly, but remembering which accounts can often be a memory challenge. We can address this simply by using the -AccountDisabled switch.

#Return all ADAccounts which are disabled
Search-ADAccount -AccountDisabled

This will quite simply list all the current AD accounts (users and computers which are disabled)

You can filter this to just Users or Computers using one of the 2 parameters below:

-UsersOnly
-ComputersOnly

You may want to export this data to a csv file that you can use later. This can easily be done with using Export-CSV

#User Search-ADAccount to export a list of all the users which are disabled
Search-ADAccount -AccountDisabled -UsersOnly| Export-Csv "c:\export.csv"

Inactive Accounts
Very similarly to the disabled accounts, it is very straightforward to identify those accounts which are inactive using -AccountInactive

#Return all AD Accounts which are inactive
Search-ADAccount -AccountInactive

You can also filter them using the -UserOnly / -ComputerOnly parameters.

Filtering Inactive Accounts after a certain time
With the -AccountInactive switch you can also quickly find those users that have been inactive for a period of time, such as 90 days, using the -TimeSpan parameter.

Search-ADAccount -AccountInactive -TimeSpan 30

Again, you can export this to a csv or similar using the Export-csv as above.

What’s next
There’s a lot more you can do with this disabled or inactive accounts, and we’ve barely touched on this cmdlet itself.

This may not be the best way for you, but if you just need a really quick overview of your disabled or inactive accounts, then this is probably the quickest and easiest way to get it.

Active Directory Advanced Mode

As a domain or enterprise administrator, you’d like to think that opening Active Directory users and computers (ADUC) will give you all the access you could ever need.

Well, you’d be wrong. Some of the features you may need are hidden away in ‘Advanced Features’

These include:

  • Access to the ‘object’, ‘security’ and ‘Attribute Editor’ tabs
  • The revealing of some hidden containers (including Lost and Found and System)

Enabling ‘Advanced Features’
The Advanced Features option is easy to find, as its a check box under ‘view’

ad-advancedMode

Do I need it?
The answer here is possibly, but once you’ve used it, you’ll want it on everywhere you look. The features i use the most in Active Directory are the Attribute Editor and the Object tab.

Here’s why:

Object Tab
The object tab is great for looking at when a user was created/modified or using the ‘Prevent object from Accidental deletion’ check box.ad-objectTab

Attribute Editor
As someone who works with Active Directory every day, I use the Attribute Editor more than most. Firstly, its great for finding out what attributes are really called, and what’s really in them.

I also use it extensively to store data that I need in Active Directory, but I don’t want to put in a field that anyone can see.

ad-attributeEditor

OneNote is now Free… Everywhere!!

One piece of news that I picked up on last week is that Microsoft was going to make OneNote free for all, from every device. Well, this morning, that became a reality, alongside OneNote for Mac.

OneNote had been available on iOS, Windows Phone and Android for a while now, but this is the first time its been available for Mac OSX, and about time too.

This has probably been the most underrated Microsoft Office product for some time, and like many I was sceptical about its use a couple of years ago. Now though, I can’t live without it.

OneNote Cloud API
Alongside the release of the free version, comes the news that the OneNote service now includes a new Cloud API, which will allow the development of more and more apps to work with OneNote and make this product even better.

Theres already a whole bunch of apps for OneNote, and I’m sure this list with expand greatly over the coming weeks as developers get hold of it.

Do more with OneNote today

For more information:

MS Office Blog – OneNote now on Mac, free everywhere and service powered
MS Office Blog – Introducing OneNote for Mac
WinSuperSite – Introducing OneNote for Mac

Connecting to Office 365 Using PowerShell

More and more the focus is on Office 365, and a lot of the niftier management can/should be done with PowerShell.

Recently, i have been working more and more with Office 365, and connecting to it can be a bit of a pain.

Here, I will demonstrate a couple of ways that you can connect to Office 365 easily, as something you will be doing more and more.

I will also demonstrate a way that you can save the password securely in a separate file, so you don’t have to keep entering it, or have it available in plain text.

Connecting to Office 365 – Prerequisites
Firstly, in order to connect to Office 365 you must have the Windows Azure AD Module for PowerShell installed.

You can find this here:
http://msdn.microsoft.com/en-us/library/windowsazure/jj151815.aspx#bkmk_installmodule

Connecting to Office 365
A quick Google will give you then commands you need to connect to Office 365 from your new Shell, but i tend to use this set of commands.

#Connect to Office 365, Prompting for Credentials
Import-Module MSOnline
$O365Cred = Get-Credential
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365Cred -Authentication Basic -AllowRedirection
Import-PSSession $O365Session
Connect-MsolService –Credential $O365Cred

In its most basic form, this will Import the MSOnline powershell module (the one you have just installed) and connect to the Office 365 Service, prompting for your username and password.

This works well, but if you use it often, it can be a pain to keep giving your credentials.

Saving the Password Securely
You can simply add the password to the script, but this will be in plain text, which is not ideal.

To save the password in a more secure fashion, I use the PowerShell script below:

#Set location of TXT file to store the password
$secureFilePath = "D:\365SecureString.txt"

#Enter the password when prompted
#It will be stored in a secure string
Read-Host -Prompt "Enter password to be encrypted in secure string text file" -AsSecureString |
ConvertFrom-SecureString |
#Location of the file to secure the string inside
Out-File $secureFilePath

This will prompt you for a password and save it in a more secure fashion to the file you specify at the top.

Once this file is saved, it can be accessed by any other PowerShell script, simply by reading the file

$pass = GetContent $secureFilePath | ConvertTo-SecureString

Want more on Passwords?
Check out Todd Klindt’s post on using Passwords with PowerShell HERE

Disclaimer on Secure Passwords
Unfortunately, I have to say that there are a couple of ways that this password can be read later on, but only by someone who has the knowledge and access to your file. It will certainly stop someone stumbling upon it.

Putting it together
Now that i have my password stored, you can add this location to the script to connect, and with a couple of tweaks have as script that will connect to Office 365 without any prompting.

Here, I’ve added it as a function. This was, i could save this into my PowerShell Profile so i could access it easily every time I needed it.

Function Connect365 {
    #Location of Password in TXT file
    $secureFilePath = "D:\365SecureString.txt"
    $userName = "myuser@mydomain.onmicrosoft.com"
    $pass = Get-Content $secureFilePath | ConvertTo-SecureString                                                           
    $O365Cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $userName,$pass     

    #Function to connect to MSOL using the secure string
    Import-Module MSOnline

    #$O365Cred = Get-Credential
    $O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365Cred -Authentication Basic -AllowRedirection
    Import-PSSession $O365Session
    Connect-MsolService –Credential $O365Cred
}

Connect365

I’ll cover the use of functions a little more in another blog.

Working with Active Directory in Powershell – Finding and exporting user information

Most of us work primarily with Active Directory Users and Computers, and for the most part this works fine. Sometimes though, its easier to do the things we need to do with PowerShell.

In this post, ill cover the basics of using PowerShell with Active Directory to get user information.

Connecting to Active Directory
If you are working on a Server with an Active Directory Role (Domain Controller etc.) you will already have all the tools you need. You can either open the Active Directory Module for PowerShell from start.

Or you can open PowerShell and add the Active Directory Module

Import-Module Active Directory

If you are working on a machine which is not a Server with an AD role, you will need to be on a machine which is a member of the domain you want to query and has the ‘Remote Server Administration Tools’ or ‘RSAT’ installed.

For more information on installing RSAT, see this post on TechNet

Once you have RSAT installed, ensure you have the Active Directory Module for PowerShell installed.

RSAT

 

Testing the Connection to Active Directory.
Once you have PowerShell open, you can check that you can communicate Active Directory easily using the command

Get-ADDomain

This will simply connect to Active Directory and output some basic details above the domain.

4-domain

The Get-ADUser Commandlet
The only cmdlet we’ll use here is ‘Get-ADUser’

As with every PowerShell CommandLet you can use the Get-Help to get detailed information on the parameters you can use.

Get-Help Get-ADUser

You can also use -Examples to get some sample code

Get-Help Get-ADUser -Examples

Finding Users
Firstly, we can use the Get-ADUser cmdlet to retrieve all the users in the Domain:

Get-ADUser -Filter *

This will retrieve every user in Active Directory and output this as list.

However, this isn’t very useful, as it most likely won’t contain the attributes you need.

By default, only a small set of attributes are retrieved. You can specify the attributes to be retrieved using:

-Properties *

This will add every property for a user, but you can then us the ‘select’ option to display only the attributes you want.

| select <attributeName>, <attributeName2>
Get-ADUser -Filter * -Properties * | select sAMAccountName, givenName, surname

You can select as many attributes as you want, but if you add more than 4, the way the information is displayed will change from a tabular format to a 1 line per attribute.

PowershellADUser

Exporting the retrieved information
your probably querying active directory in order to do something with the information, and for that you may want the data exported into a usable format. You can export to a csv file by adding

| Export-Csv "C:\pathtoexportto\filename.csv" -noType
Get-ADUser -Filter * | select sAMAccountName, givenName, surname


Filtering which OU’s to query
So far we have only queried the full domain. To choose where the base of the query should be, you can add the Property -SearchBase

-SearchBase "OU=nameofou,DC=domain,DC=local"
Get-ADUser -Filter * -SearchBase "OU=Staff,OU=Users,OU=Salamander-Sims,DC=salamandertest,DC=co,DC=uk" | select sAMAccountName


Find a specific user
If you want to select a user specifically you can specify the parameter -Identity followed by the account name

Get-ADUser -Identity myusername


Adding filters based on attributes

You can also filter users using their attributes with the Filter Parameter

Get-ADUser -Filter {givenName -eq "Ben"
Get-ADUser -Filter {(givenName -eq "John") -and (sn -eq "Smith")}

You can also use the -like parameter when filtering. Here we query any user where they have an email address.

Get-ADUser -Filter {mail -like "*"}

NASCAR – SPC14 Attendee Party

I realise that i’m out in the US on business, but when there is a FREE party as part of the package, you can’t complain.

The party in question was at the Las Vegas Motor Speedway which is most commonly used for NASCAR.

Leaving the hotel by coach, we headed out to the speedway, and upon arrival were immediately given the opportunity to have a ‘ride round’ the track. Not in NASCARs unfortunately, but in some normal ‘stock’ Mercedes E-Classes.

Still, who would say no to that!
We only got 1 lap round, but it was still an experience that most would never get.

After the ride round, we walked round the speedway to see what else was on offer. In short we had:

    • As much free food and drink as we could manage
    • A live rock tribute band (which were actually pretty good)
    • A garage full of supercars to look at
    • A Ferrari F430 that we could have our photo taken with
    • and some more stuff I didn’t look at

All told, a good night out, for free which was well needed by all.

Having to be in a session at 9am the following morning though, not so good!