Password management Office 365

[CAWJames]

Afternoon all,

 

We are entering the brave new world of adding students to AD and then up into O365, so they can benefit from ProPlus etc.

 

Our student login data is actually stored in an SQL database, that Moodle, etc get the info from. So we are planning to powershell import users from this database, adding a couple of fields together to make a unique username etc and then store them in a GPO locked down OU in our main AD. We will then use DirSync or AAD Sync to then move these users up to O365. Another powershell will then assign licences depending on a flag set in an attribute(current, alumni, withdrawn etc).

 

These scripts will be automated to run once a [insert time period here], and will hopefully be completely hands off.

 

So now for the quandary, these users have no passwords in AD, unless we give a specific password to the newADUser cmdlet, and therefore will be imported to AD and O365 as disabled users. Is there anyway along this process to have an automatic temporary password attached, as if you were creating a new user in O365 proper and then emailed to admin or the secondary email account of the student?

 

Any powershell gurus out there? or anyone have a better way of doing it, maybe don't use dirsync/aad sync for this, but script straight from SQL into O365(not sure if this is even an option, but it would get the students into O365 first, and then dirsynced backwards into AD!!)

 

I probably have asked this before, but I now have a plan and am trying to sort the password function.

 

Thanks in advance for any help.

 

James

[CAWJames]

I have this so far, but not sure if it will work, as its adapted from a blog based on New-ADuser

 

Import-Module MSOnline

Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance OurDB -Database moodle -Query `

"select FirstName, LastName, MoodleNumber, Department, otherEmail" |

select @{l='Name';e={$_.firstname+" "+$_.lastname}},

@{l='UserPrincipalName';e={$_.lastname.tolower().substring(0,1)+$_.MoodleNumber+"@ourdomain}},

@{l='DisplayName';e={$_.firstname+" "+$_.lastname}},

@{l='FirstName';e={$_.firstname}},

@{l='Department';e={$_.department}},

@{l='AlternateEmailAddresses';e={$_.otherEmail}},

@{l='Surname';e={$_.lastname}} |

New-MsolUser

 

I am not sure if I need to pass anything else to the New-MsolUser command or if its all there in the array? also might need some bits to actually connect it to our tenant, but does it look like i am on the right track?

 

Thanks

 

James

[altecsole]

I would have thought that you'd be better using Powershell to create users in Office 365 using data from your SQL database. The only advantage of using AD and Dirsync is that (normally) AD is your user logon database, and so AD user password changes are synced to Office 365 accounts and everything can pretty much be managed on premise - including deleting accounts.

 

When we manually created Office 365 accounts in Powershell, I used an Excel spreadsheet to create random passwords, which the Powershell script then assigned to each user.

[CAWJames]

Thanks altecsole, that is my current thinking, bypass dirsync and go straight from SQL to o365, see my code in post #2. I am not sure if this will trigger the temporary password email though or not, or if we have to input random passwords manually.

 

Thanks again

 

James

[CAWJames]

Aha, in answering my own thread!

 

This Scripting Guy blog post shows the New-MsolUser cmdlet spitting out a password which can be piped to send to user!

 

I will do some more checking, but I think my script can be expanded to autoconnect and send to the users alternative email

[altecsole]

Yes, it looks like that will work - also check out https://goo.gl/8rB3CE. We chose to use a CSV file for creating users as it gave us a bit more flexibility for adding licenses and also for using random password generated in Excel. Also, once you've got that info in a CSV you can then use it to mail merge login details to end users.

 

We used AD for our user accounts, so moving to Dirsync made good sense for us, but it'd probably be of little benefit in your scenario. Good luck.

[CAWJames]

Bit of an update, it didn't quite go to plan!! I have ended up outputting to a CSV from the database, so it formats everything right for the script. I then had it create a random password in the O365 form, and then used Set-MsolUser to set it. and then outputted using the details in the CSV to email to send passwords to their alternate address:

 

#Powershell script for Bulk update of Alternate Email addresses and reset passwords and send email notification to end users with their new passwords.

$From = "Office365Admin@ourdomain"

$SMTPServer = "OurSMTPServer"

$SMTPPort = "25"

$Username = "adminuser"

$Password = "password"

$subject = "Your Office365 Password is attached!"

 

$input=Import-csv C:\temp\Output365b.csv

Foreach($line in $input)

{

New-MsolUser -FirstName $line.FirstName -LastName $line.FamilyName -DisplayName $line.DisplayName -Department $line.Department -UserPrincipalName $line.UserPrincipalName -AlternateEmailAddresses $line.AlternateEmailAddress -LicenceAssignment DOMAIN:STANDARDWOFFPACK_IW_STUDENT

#Set-MsolUserPassword -UserPrincipalName $line.UserPrincipalName -NewPassword $line.Password

 

$To = $line.AlternateEmailAddress

 

$body = "Hi " + $line.FirstName + " Here is your new Office365 email address: " + $line.UserPrincipalName + " &" + " this is your new Office365 password: " + $line.Password + " Please use these credentials to login at https://login.microsoftonline.com"

$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer,$SMTPPort)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($Username,$Password)

$SMTPClient.Send($From, $To, $subject, $Body)

 

}

 

This works fine for the first run, BUT we want this CSV to be updated on a schedule and the script run on a schedule, so that it picks up new users and just errors on ones that already have accounts. The problem with this script, it will email the students every script run with their password, which isnt brilliant. What I need to do is somehow get the output from the New-MsolUser Password output and then input it into the email instead of the column from the CSV, AND to somehow get the password sending bit encapsulated in the New-MsolUser line, so it doesn't send to all in the CSV only the ones that output a new user.

 

Does that make sense? My script-fu is not working at the moment!!

 

Thanks James

[altecsole]

You could test whether the user exists:

 

$msoluser = Get-MsolUser -UserPrincipalName $line.UserPrincipalName -ErrorAction SilentlyContinue

 

if($msoluser){

write-host "User exists"

}

else{

write-host "Need to create user"

# create user and send email

}

[CAWJames]

Excellent, should have thought of that!

 

So adding your snippet, the below should take care of only sending to new users, just need to work out the passwords now, make SQL create a random password on creation/update of the CSV and then use the hashed out Set-MsolUser line to change the users password and send that OR find someway to pass the O365 generated password into the email, is it even me worth working on the latter?

 

Thanks again

 

James

 

#Powershell script for Bulk update of Alternate Email addresses and reset passwords and send email notification to end users with their new passwords.

$From = "Office365Admin@ourdomain"

$SMTPServer = "OurSMTPServer"

$SMTPPort = "25"

$Username = "adminuser"

$Password = "password"

$subject = "Your Office365 Password is attached!"

 

$input=Import-csv C:\temp\Output365b.csv

$msoluser = Get-MsolUser -UserPrincipalName $line.UserPrincipalName -ErrorAction SilentlyContinue

Foreach($line in $input)

if($msoluser){

write-host "User exists"

}

else{

write-host "Creating user"

New-MsolUser -FirstName $line.FirstName -LastName $line.FamilyName -DisplayName $line.DisplayName -Department $line.Department -UserPrincipalName $line.UserPrincipalName -AlternateEmailAddresses $line.AlternateEmailAddress -LicenceAssignment DOMAIN:STANDARDWOFFPACK_IW_STUDENT

#Set-MsolUserPassword -UserPrincipalName $line.UserPrincipalName -NewPassword $line.Password

 

$To = $line.AlternateEmailAddress

 

$body = "Hi " + $line.FirstName + " Here is your new Office365 email address: " + $line.UserPrincipalName + " &" + " this is your new Office365 password: " + $line.Password + " Please use these credentials to login at https://login.microsoftonline.com"

$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer,$SMTPPort)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($Username,$Password)

$SMTPClient.Send($From, $To, $subject, $Body)

 

}

[altecsole]

Nearly. You need to check for the MSOLUser within your Foreach.

 

You can generate random passwords in Excel. This example will create a six char password that starts with upper case and finishes with two number. You could expand it further if you need more characters.

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)

[CAWJames]

Ah good point, the 2 lines need to be swapped out, as $line is referenced before at the moment, so:

 

#Powershell script for Bulk update of Alternate Email addresses and reset passwords and send email notification to end users with their new passwords.

$From = "Office365Admin@ourdomain"

$SMTPServer = "OurSMTPServer"

$SMTPPort = "25"

$Username = "adminuser"

$Password = "password"

$subject = "Your Office365 Password is attached!"

 

$input=Import-csv C:\temp\Output365b.csv

Foreach($line in $input)

$msoluser = Get-MsolUser -UserPrincipalName $line.UserPrincipalName -ErrorAction SilentlyContinue

if($msoluser){

write-host "User exists"

}

else{

write-host "Creating user"

New-MsolUser -FirstName $line.FirstName -LastName $line.FamilyName -DisplayName $line.DisplayName -Department $line.Department -UserPrincipalName $line.UserPrincipalName -AlternateEmailAddresses $line.AlternateEmailAddress -LicenceAssignment DOMAIN:STANDARDWOFFPACK_IW_STUDENT

#Set-MsolUserPassword -UserPrincipalName $line.UserPrincipalName -NewPassword $line.Password

 

$To = $line.AlternateEmailAddress

 

$body = "Hi " + $line.FirstName + " Here is your new Office365 email address: " + $line.UserPrincipalName + " &" + " this is your new Office365 password: " + $line.Password + " Please use these credentials to login at https://login.microsoftonline.com"

$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer,$SMTPPort)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($Username,$Password)

$SMTPClient.Send($From, $To, $subject, $Body)

 

}

[altecsole]

Yep, that look right. Glad I could help.

[CAWJames]

I will get it installed tomorrow on a server and post back my results. Hopefully this will be useful to others, so makes sense to keep it updated.

 

Thanks again

 

James

[CAWJames]

Right, back at the rock face... i am now creating the SSIS package to export everything to the csv. This is fine with a source, a data conversion(some field types are incompatible for the next step), a derived column(to make UPN etc,) and an output to CSV. I am currently trying to work out how to do the passwords and where. Do I use a script transformation or an expression in the derived column, I am currently running around in circles!! I need something that creates an O365 compatible complex password that adds into a column called Password. It could do this at any point, even at SQL level before the SSIS package.

 

Anyone have any insight into this?

 

Thanks

 

James

[altecsole]

Maybe something like T-SQL: How to Generate Random Passwords - TechNet Articles - United States (English) - TechNet Wiki

 

My example was for generating passwords in Excel, but I guess you're exporting straight to CSV and don't want the extra hassle of using Excel.

[CAWJames]

Yeah, unfortunately, it is a flat file export from an SSIS package so goes no where near Excel. I need the package to create a password in a new field before exporting, so just not sure which task to use for this, derived column or such like. I have a validation, so it checks the CSV first for names already created(as it would create a new password every time for every user, although that would be ignoed by the creation script it would render the passwords different every time the SSIS package is run.

[CAWJames]

Ok, cracked it, below is an auto script that creates O365 accounts from a CSV file, checks if the user is already in 0365 and emails them an HTML email with their password details, which is the actual one outputted by O365. I hope this script will help others achieve the same goals It also auto logs into o365 powershell, so if you run this more than once in a session, comment out the 2nd,3rd,4th,5th lines. The password is held in a text file on the hard drive, this can be created by running the following and entering your password in the box:

 

read-host -assecurestring | convertfrom-securestring | out-file C:\admincred.txt

 

Apart from that, HTML can obviously be infinitely adjusted, and you can add as much instruction as you want in the body.

 

Again hope it helps someone else, it has taken me a long time to piece it together! Also thanks to all those in this thread who have added bits too

 

#Powershell script for Bulk update of Alternate Email addresses and reset passwords and send email notification to end users with their new passwords.

import-module msonline

$pass = cat C:\admincred.txt | convertto-securestring

$mycred = new-object -typename System.Management.Automation.PSCredential -argumentlist "[email protected]",$pass

$O365Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Authentication Basic -AllowRedirection -Credential $mycred

Import-PSSession $O365Session

Connect-MsolService -Credential $mycred

$From = "[email protected]"

$SMTPServer = "IP of Exchange server/SMTP server"

$SMTPPort = "25"

$Username = "domain\administrator"

$Password = "PASSWORD"

$subject = "Your Office365 Password is attached!"

$input=Import-csv D:\Sources\O365Sync\StudentOutput.csv

Foreach($line in $input){

$msoluser = Get-MsolUser -UserPrincipalName $line.UserPrincipalName -ErrorAction SilentlyContinue

if($msoluser){

write-host "User exists"

}

else{

write-host "Creating user"

 

$To = $line.AlternateEmailAddress

 

$messageSubject = "Here are your new Office 365 login details!"

$message = New-Object System.Net.Mail.MailMessage $From, $To

$message.Subject = $messageSubject

$style = "

$style = $style + "p { margin: 0px 0px 15px 0px; font-size:13px; color:#333;}"

$style = $style + "h1 { font-family:Verdana; font-size:32px; color:#B82234; margin-top:15px; margin-bottom:7px; text-align:center;}"

$style = $style + "h2 { font-family:Verdana; font-size:18px; color:#333; margin-top:15px; margin-bottom:7px;}"

$style = $style + "h3 { font-family:Verdana; font-size:16px; color:#333; margin-top:15px; margin-bottom:7px;}"

$style = $style + "h4 { font-family:Verdana; font-size:13px; color:#333; margin-top:15px; margin-bottom:7px;}"

$style = $style + "a {color: #B82234; font-family: Verdana; font-size: 13px; text-decoration:underline;}"

$style = $style + "a img { border:0px;}"

$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"

$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"

$style = $style + "TD{border: 1px solid black; padding: 5px; }"

$style = $style + ".coloured p {color:#fff;}"

$style = $style + ".coloured h1 {color:#fff;}"

$style = $style + ".coloured h2 {color:#fff;}"

$style = $style + ".coloured h3 {color:#fff;}"

$style = $style + ".coloured h4 {color:#fff;}"

$style = $style + ".coloured a {color:#fff;}"

$style = $style + ".reded p {color:#B82234;}"

$style = $style + ".reded h1 {color:#B82234;}"

$style = $style + ".reded h2 {color:#B82234}"

$style = $style + ".reded h3 {color:#B82234;}"

$style = $style + ".reded h4 {color:#B82234;}"

$style = $style + ".reded a {color:#B82234;}"

$style = $style + ""

$body = @"

http address of logo Welcome to Office365 Please find your username and password below, you can use these at https://login.microsoftonline.com "@ $footer = @" {PromoTitle} {PromoBody} Contact Us Tel: Phone | Fax: Fax | Email: [email protected] http://www.domain.com Address, Address Centre Information If you no longer wish to receive these emails, please unsubscribe.   "@ $message.IsBodyHTML = $true $message.Body = New-MsolUser -FirstName $line.FirstName -LastName $line.LastName -UserPrincipalName $line.UserPrincipalName -AlternateEmailAddresses $line.AlternateEmailAddress -Department $line.Department -DisplayName $line.Displayname -UsageLocation GB -LicenceAssignment DOMAIN:STANDARDWOFFPACK_IW_STUDENT | Select-Object UserPrincipalName,Password | ConvertTo-Html -head $style -body $body $smtp = New-Object Net.Mail.SmtpClient($smtpServer) $smtp.Send($message) } }

Edited October 8, 2015 by CAWJames

[EduTech]

Hi,

 

I am curious to understand why you are not just letting your students go and sign-up to get Office Pro Plus by themselves if that is all you want them to get initially, if they have access to an e-mail account that uses your school domain name / eligible domain namespace your users can do these via self-service.

 

All the users will then get created in the AAD Instance which the Domain Name Space is verified against (as I see you have already done that part).

 

Then, as they will be signing up with there E-Mail ID when you setup to sync from AD to AAD later, the AD accounts will soft-match based on Primary SMTP.

 

It seems that rather then letting the student sign up, go through the experience.. you are just handling that aspect for them and then providing them with the password to there own account to which I would be interested on your reasons/feedback behind this.

 

Regards,

James.

[CAWJames]

All well and good if our students had an email address in the first place... Our student accounts all reside in an SQL database for Moodle use, with no email. We are now giving them email and ProPlus, but with no AD structure for ADSync to pick from, we have basically made this script to convert Moodle accounts in to O365 accounts automagically hope that makes sense

 

James

Edited October 13, 2015 by CAWJames

[CAWJames]

Again I am tweaking my script! Need to change the write-hosts to something that will log the outcome to a file, so it will show date time username and when an account was created. Write-host was great for testing, but not when a script is running in the background!

 

If I remove write-host "User exists" what would I put in its place or can I go leave the if blank and curly bracket straight to the else?

 

write-host "Creating user" is the bit I need to log and then I need to add UPN and display name, would it be as simple as write-output "Creating User" $line.Displayname $line.UserPrincipalName | Out-File C:\filename.txt will this append a file? Is there anyway to output a new file name every time that is date stamped with a variable or some other way of doing it, so that every time the script runs it outputs a log list of names and UPN we can interrogate?

 

Thanks to all the powershell gurus in advance

 

James

[Marshall_IT]

Again I am tweaking my script! Need to change the write-hosts to something that will log the outcome to a file, so it will show date time username and when an account was created. Write-host was great for testing, but not when a script is running in the background!

 

If I remove write-host "User exists" what would I put in its place or can I go leave the if blank and curly bracket straight to the else?

 

write-host "Creating user" is the bit I need to log and then I need to add UPN and display name, would it be as simple as write-output "Creating User" $line.Displayname $line.UserPrincipalName | Out-File C:\filename.txt will this append a file? Is there anyway to output a new file name every time that is date stamped with a variable or some other way of doing it, so that every time the script runs it outputs a log list of names and UPN we can interrogate?

 

Thanks to all the powershell gurus in advance

 

James

 

I'd do something like this

 

$filename = "C:\" + (Get-date).ToString('yyyy.M.d') + ".log"
write-output "Creating User" $line.Displayname $line.UserPrincipalName | Out-File $filename

Edited October 13, 2015 by Marshall_IT

[CAWJames]

Ok tested it and while it silently creates the account, and makes a log file it only outputs the last person created, on 3 seperate line, which is weird as Write-host output each one as it creates, is my Out-file in the wrong place?

 

Thanks for this again,

 

James

[CAWJames]

needed an -append on the outfile still strange it is on 3 lines, rather than all on the one line though?

[Marshall_IT]

sorry, add -Append after the filename.

[CAWJames]

Added a blurb variable with Creating user in it and went with the below:

 

write-output "$($blurb) $($line.Displayname) $($line.UserPrincipalName)" | Out-File -append $filename

 

Happy bunny now

 

Thanks all, think I am ready to deploy to the whole student population now, scary