Category: office365

Azure SQL, Azure Active Directory and Seamless SSO: An Overview

Instead of pure lift-and-shift migrations to the cloud, we often encounter lift-shift-tinker migrations. In such a migration, you modify some of the application components to take advantage of cloud services. Often, that’s the database but it could also be your web servers (e.g. replaced by Azure Web App). When you replace SQL Server on-premises with SQL Server or Managed Instance on Azure, we often get the following questions:

  • How does Azure SQL Database or Managed Instance integrate with Active Directory?
  • How do you authenticate to these databases with an Azure Active Directory account?
  • Is MFA (multi-factor authentication) supported?
  • If the user is logged on with an Active Directory account on a domain-joined computer, is single sign-on possible?

In this post, we will look at two distinct configuration options that can be used together if required:

  • Azure AD authentication to SQL Database
  • Single sign-on to Azure SQL Database from a domain-joined computer via Azure AD Seamless SSO

In what follows, I will provide an overview of the steps. Use the links to the Microsoft documentation for the details. There are many!!! 😉

Visually, it looks a bit like below. In the image, there’s an actual domain controller in Azure (extra Active Directory site) for local authentication to Active Directory. Later in this post, there is an example Python app that was run on a WVD host joined to this AD.

Azure AD Authentication

Both Azure SQL Database and Managed Instances can be integrated with Azure Active Directory. They cannot be integrated with on-premises Active Directory (ADDS) or Azure Active Directory Domain Services.

For Azure SQL Database, the configuration is at the SQL Server level:

SQL Database Azure AD integration

You should read the full documentation because there are many details to understand. The account you set as admin can be a cloud-only account. It does not need a specific role. When the account is set, you can logon with that account from Management Studio:

Authentication from Management Studio

There are several authentication schemes supported by Management Studio but the Universal with MFA option typically works best. If your account has MFA enabled, you will be challenged for a second factor as usual.

Once connected with the Azure AD “admin”, you can create contained database users with the following syntax:

CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;

Note that instead of a single user, you can work with groups here. Just use the group name instead of the user principal name. In the database, the user or group appears in Management Studio like so:

Azure AD user (or group) in list of database users

From an administration perspective, the integration steps are straightforward but you create your users differently. When you migrate databases to the cloud, you will have to replace the references to on-premises ADDS users with references to Azure AD users!

Seamless SSO

Now that Azure AD is integrated with Azure SQL Database, we can configure single sign-on for users that are logged on with Active Directory credentials on a domain-joined computer. Note that I am not discussing Azure AD joined or hybrid Azure AD joined devices. The case I am discussing applies to Windows Virtual Desktop (WVD) as well. WVD devices are domain-joined and need line-of-sight to Active Directory domain controllers.

Note: seamless SSO is of course optional but it is a great way to make it easier for users to connect to your application after the migration to Azure

To enable single sign-on to Azure SQL Database, we will use the Seamless SSO feature of Active Directory. That feature works with both password-synchronization and pass-through authentication. All of this is configured via Azure AD Connect. Azure AD Connect takes care of the synchronization of on-premises identities in Active Directory to an Azure Active Directory tenant. If you are not familiar with Azure AD Connect, please check the documentation as that discussion is beyond the scope of this post.

When Seamless SSO is configured, you will see a new computer account in Active Directory, called AZUREADSSOACC$. You will need to turn on advanced settings in Active Directory Users and Computers to see it. That account is important as it is used to provide a Kerberos ticket to Azure AD. For full details, check the documentation. Understanding the flow depicted below is important:

Seamless Single Sign On - Web app flow
Seamless SSO flow (from Microsoft @ https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-sso-how-it-works)

You should also understand the security implications and rotate the Kerberos secret as discussed in the FAQ.

Before trying SSO to Azure SQL Database, log on to a domain-joined device with an identity that is synced to the cloud. Make sure, Internet Explorer is configured as follows:

Add https://autologon.microsoftazuread-sso.com to the Local Intranet zone

Check the docs for more information about the Internet Explorer setting and considerations for other browsers.

Note: you do not need to configure the Local Intranet zone if you want SSO to Azure SQL Database via ODBC (discussed below)

With the Local Intranet zone configured, you should be able to go to https://myapps.microsoft.com and only provide your Azure AD principal (e.g. first.last@yourdomain.com). You should not be asked to provide your password. If you use https://myapps.microsoft.com/yourdomain.com, you will not even be asked your username.

With that out of the way, let’s see if we can connect to Azure SQL Database using an ODBC connection. Make sure you have installed the latest ODBC Driver for SQL Server on the machine (in my case, ODBC Driver 17). Create an ODBC connection with the Azure SQL Server name. In the next step, you see the following authentication options:

ODBC Driver 17 authentication options

Although all the options for Azure Active Directory should work, we are interested in integrated authentication, based on the credentials of the logged on user. In the next steps, I only set the database name and accepted all the other options as default. Now you can test the data source:

Testing the connection

Great, but what about your applications? Depending on the application, there still might be quite some work to do and some code to change. Instead of opening that can of worms 🥫, let’s see how this integrated connection works from a sample Pyhton application.

Integrated Authentication test with Python

The following Python program uses pyodbc to connect with integrated authentication:

import pyodbc 

server = 'tcp:AZURESQLSERVER.database.windows.net' 
database = 'AZURESQLDATABASE' 

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';authentication=ActiveDirectoryIntegrated')
cursor = cnxn.cursor()

cursor.execute("SELECT * from TEST;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

My SQL Database contains a simple table called test. The logged on user has read and write access. As you can see, there is no user and password specified. In the connection string, “authentication=ActiveDirectoryIntegrated” is doing the trick. The result is just my name (hey, it’s a test):

Result returned from table

Conclusion

In this post, I have highlighted how single sign-on works for domain-joined devices when you use Azure AD Connect password synchronization in combination with the Seamless SSO feature. This scenario is supported by SQL Server ODBC driver version 17 as shown with the Python code. Although I used SQL Database as an example, this scenario also applies to a managed instance.

Office 365 Identity Management with DirSync without Exchange Server On-Premises

This post describes how users, groups and contact are provisioned in Office 365 from the on-premises Active Directory. By using DirSync, these objects are created in and synchronized to Office 365. Without an Exchange Server and Exchange Management tools in place, it is not always obvious how these objects should be created.

The following sections describe the procedures you can follow without Exchange or the Exchange management tools in place.

IMPORTANT NOTE
The sections below only specify the basic actions you need to perform in Active Directory to have the object appear in the right place in Office 365 (user, security group, mailbox, distribution group, contact). Note that almost all properties of these objects need to be set in Active Directory. If you want to hide a distribution group from the address book or you want to configure moderation for a distribution group, you have to know the property in Active Directory that’s responsible for the setting, set the value and perform directory synchronization. You will also need to upgrade the Active Directory schema with Exchange Server 2010 schema updates. You cannot use the Exchange Server 2010 System Manager without having at least one Exchange Server 2010 role installed on-premises.

Create a user account

Create a regular user account in Active Directory. This user account will be replicated by DirSync and it will appear in the Users list in the portal (https://portal.microsoftonline.com).

Important: set the user logon name to a value with a suffix that matches the suffix used for logging on to Office 365. For instance, if you logon with first.last@xylos.com in Office 365, set the UPN to that value:
clip_image002

User accounts without a mailbox (or any other license) can be used in Office 365 to grant permissions such as Billing Administrator or Global Administrator. A user account like this is typically used to create a DirSync service account.

Create a user account for a user that needs a mailbox

Create a user account as above. Set the user’s primary e-mail address in the email attribute or you will get an onmicrosoft.com address only:

clip_image004

When this user is synchronized and an Exchange Online license is added in the portal, a mailbox will be created that has the E-mail address in the E-mail field as primary SMTP address. Automatically, a secondary SMTP address is created with prefix@tenantid.onmicrosoft.com:

clip_image006

What if the user needs extra SMTP addresses?

  • You cannot set extra SMTP addresses in Exchange Control Panel (or Remote PowerShell) because the object is synchronized with DirSync.
  • In the on-premises Active Directory you need to populate the proxyAddresses attribute of the user object. You can set the values in this field with ADSIEdit or Active Directory Users and Computers (Windows Server 2008 ADUC and higher with Advanced Features turned on)
  • In the proxyAddresses field, make sure that you also list the primary SMTP address with SMTP: (in uppercase) in front of the address. Secondary addresses need smtp: (in lowercase) in front of the address.
    clip_image008

Note: instead of editing the proxyAddresses field directly, you can use a free (but at this point in time beta) product: http://www.messageops.com/software/office-365-tools-and-utilities/office-365-active-directory-addin. The tool adds the following tabs to Active Directory Users and Computers:

  • O365 Exchange General: set display name, Email address, additional Email addresses and even a Target Email Address (for mail redirection)
  • O365 Custom Attributes: set custom attributes in AD for replication to Office 365
  • O365 Delivery Restrictions: accept messages from, reject messages from
  • O365 Photo: this photo will appear on the user’s profile and will be used by Lync Online as well
  • O365 Delegates: to set the publicDelegates property

When a user is created in AD, you can use the additional tabs this tool provides to set all needed properties at once.

To summarize the actions for a mailbox:

  • Create a user in ADUC with the user logon name (UPN) and e-mail address to the primary e-mail address of the user (UPN and e-mail address do not have to match but it’s the most common case)
  • Make sure the user has a display name (done automatically for users if you specify first, last and full name in the AD wizard)
  • Set proxyAddresses manually or with the MessageOps add-in to specify additional e-mail addresses (with smtp: in the front) and make sure you also specify the primary e-mail address with SMTP: in the front.
  • Let DirSync create and sync the user to Office 365
  • Assign an Exchange Online license to the user. A mailbox will be created with the correct e-mail addresses.

Create a security group

Create a security group in Active Directory. The group will be synchronized by DirSync and appear in the Security Groups in the portal. The group will not appear in the Distribution Groups in Exchange Online (obviously).

Create a distribution group

Create a distribution group in Active Directory. In the properties of the group set the primary e-mail address in the E-mail address field:

clip_image010

In addition to the e-mail address, the group object also needs a display name (displayname attribute). If the distribution group in AD has an e-mail address and a display name, the group will appear in the Distribution Groups list in Exchange Online after synchronization.

Note that specifying members and alternate e-mail addresses has to be done in the local Active Directory as well. If you have installed the MessageOps add-in, you can set easily set those properties.

Create a mail-enabled distribution group

You can add a display name and e-mail address to a standard security group to mail-enable the group. After stamping those two properties, the group will appear in the list of Distribution Groups in Exchange Online. When you list groups with the Get-Group cmdlet, you will see the following:

clip_image012

You can stamp the properties manually or use the MessageOps add-in to set these properties easily.

Create a contact

Create a contact object in Active Directory. In the properties of the created object, fill in the E-mail field in the General tab.

Conclusion

Although DirSync makes it easy to create directory objects in Office 365, without an Exchange Server and the Exchange management tools it is not always obvious how to set the needed properties in order to correctly synch these objects. If you find it too much of a hassle to set the required properties on your local Active Directory objects, there are basically two things you can do:

  • Turn off Directory Synchronization and start mastering directory objects in the cloud
  • Install at least one Exchange Server 2010 SP1 so that you can use the Exchange management tools

Issue with meeting invites during Office 365 staged migration

During a recent migration project, we migrated several mailboxes to Office 365 using the staged migration approach. Naturally, during the migration, users that had been migrated still sent meeting invites to on-premises users and meeting rooms. The problem was that meeting invites were actually received as clear text and stripped of the necessary attributes that make it a meeting request. The on-premises server was Exchange Server 2003 SP2.

As you might know, during a staged migration, DirSync creates MEUs (mail-enabled users) in the cloud. These MEUs actually represent on-premises users that have not been migrated yet and they make sure that the global address list in Office 365 matches the on-premises global address list.

When a user with a cloud mailbox sends a meeting invite to an on-premises user, he actually sends the invite to the MEU. The MEU has a TargetAddress that points back to the on-premises user. The MEU also has settings that control how mail should be formatted when sent to the actual on-premises user. The solution to the problem was to change the UseMapiRichTextFormat  to a value of Never using the following PowerShell command:

set-MailUser MEUname -UseMapiRichTextFormat Never

For all MailUser objects, use:

get-mailuser | set-MailUser -UseMapiRichTextFormat Never

After running this command, cloud users could successfully send meeting requests to on-premises users. Hope it helps!

Enabling and disabling DirSync in BPOS and Office 365

If you have some experience with BPOS or the Office 365 Beta, you know there’s a tool called DirSync that allows you to synchronize your Active Directory with Microsoft’s Online Services. During a course last week, I was asked if you can use DirSync temporarily to sync users, contacts and groups and, after migration, turn it off.

In BPOS, you can certainly do the above and essentially use DirSync as just a migration tool. It is in fact the easiest way to load users, contacts and groups into BPOS if you don’t mind setting up the software. When you turn off DirSync in the BPOS Administration website, all synchronized users become normal users which means their properties become editable. Turning off DirSync is done from the Migration / Directory Synchronization tab:

image

In Office 365, it’s a totally different story because DirSync is intended as a tool for permanent coexistence. As a result, you will not find a Disable button in the UI and there’s also no PowerShell cmdlet to turn it off. If you just want to migrate your users, contacts and distribution groups to Office 365 you should use other means such as CSV, PowerShell, etc…

Office 365 and Identity

imageMicrosoft has provided more details about Office 365 and the different identity options in a service description document (link at the bottom of this post).

There are two types of identities:

  • Cloud Identity: credentials are separate from your corporate credentials
  • Federated Identity: users can sign in with their corporate Active Directory credentials

With BPOS, there was only one type: cloud identity. Users had to logon using a Sign-In Assistant that stored the cloud credentials (name and password) and used those credentials to sign in in the background. For larger organizations, the Sign-In Assistant was a pain to install and manage so it’s a good thing it is going away.

With the new identity solution, as stated above, the Sign-In Assistant goes away and the logon experience is determined by the type of identity and how you access the service. The table below summarizes the sign-in experience:

image

1 The password can be saved to avoid continuous prompting
2 During the beta, with Federated IDs, you will be prompted when first accessing the services
3 Outlook 2007 will be updated to give the same experience as Outlook 2010

Note that it is required to install some components and updates on user’s workstations if rich clients are used to access Office 365. Although you can manually install these updates, the Office 365 Desktop Setup package does all that is needed. Office 365 Desktop Setup was formerly called the Microsoft Online Services Connector. Office 365 Desktop Setup supports Windows XP (SP2) and higher.

A couple of other things that are good to know:

  • Office 365 supports two-factor authentication if you implement SSO with Active Directory Federation Services 2.0. There are two options to enforce two-factor auth: on the ADFS 2.0 proxy logon page or at the ForeFront UAG SP1 level.
  • Active Directory synchronization is supported with the Microsoft Online Services Directory Synchronization tool. The tool is basically the same as with BPOS although there are some changes to support new features: security group replication, write back (which also enables some extra features), etc…
  • Note that the synchronization tool still does not support multiple forests.
  • The synchronization tool is required in migration scenarios like rich coexistence, simple coexistence and staged migration with simple coexistence.

The full details can be downloaded from the Microsoft website. If you are involved in Office 365 projects, this is considered required reading!

Office 365 SharePoint Online Storage

imageMicrosoft has recently published updated Office 365 Service Descriptions at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6c6ecc6c-64f5-490a-bca3-8835c9a4a2ea.

The SharePoint Online service description contains some interesting information, some of which I did not know yet. We typically receive a lot of questions about SharePoint online and storage. The list below summarizes the storage related features:

  • The storage pool starts at 10GB with 500MB of extra storage per user account (talking about enterprise user accounts here).
  • Maximum amount of storage is 5TB.
  • File upload limit is 250MB.
  • Storage can be allocated to a maximum of 300 site collections. The maximum amount of storage for a site collection is 100GB.
  • My Sites are available and each My Site is set at 500MB (this is not the 500MB noted above, in essence this is extra storage for each user’s personal data).
  • A My Site is not counted as a site collection. In other words, you can have 300 normal site collections and many more My Sites.
  • Extra storage can be purchased (as before) at $2,5USD/GB per month.

When it comes to external users (for extranet scenarios), the document states that final cost information is not available yet. It is the intention of Microsoft to sell these licenses in packs.

Check out the SharePoint Online service description for full details.

BPOS PowerShell Commands

While I am delivering BPOS courses for Microsoft partners, there’s always a lot of interest in using commands to control directory synchronization, the migration tools, provisioning users and so on. Microsoft actually provides several PowerShell cmdlets with the two main tools that come with BPOS:

  • Directory synchronization cmdlets become available when you install the directory synchronization software
  • Migration and configuration cmdlets become available when you install the migration tools

There is one directory synchronization cmdlet of interest and that is the Start-OnlineCoexistenceSync cmdlet. That cmdlet starts a synchronization run from the on-premises Active Directory to the customer’s BPOS environment. When you start c:\program files\microsoft online directory sync\dirsyncconfigshell.psc1, a PowerShell session is started that allows you to run the cmdlet. If you just want to run the command from an existing PowerShell session, first load the Directory Synchronization snapin with the following command:

Add-PSSnapin Coexistence-Configuration

After the snapin is loaded, you can use the Start-OnlineCoexistenceSync cmdlet to start a synchronization run. Although I have not yet seen the next version of DirSync for use with Office 365, I presume that the above cmdlet will still be available since the DirSync tools will be very similar.

The cmdlets that come with the migration tools are much more interesting because they can be used to provision users, enable users, enable POP3 access for users, grant Send As or Full Mailbox Access and so forth. When the migration tools are installed, you will have a shortcut in the Start Menu in Microsoft Online Services > Migration > Migration Command Shell. When you click that shortcut, a PowerShell session is started with the Microsoft Exchange Transporter snapin loaded. You can ask for the list of cmdlets loaded by this snapin using the following command:

get-command -PSSnapin Microsoft.Exchange.Transporter

Some interesting cmdlets:

  • Add-MSOnlineUser: can be used to add users to BPOS from a script; note that the users are added as synchronized disabled users; you will have to use Enable-MSOnlineUser to enable the user in a separate step
  • Add-MSOnlineMailPermission: can be used to grant Send As, Full Mailbox access or Send On Behalf Of rights on a mailbox
  • Enable-MSOnlinePOPAccess: can be used to enable POP3 access on a mailbox
  • Set-MSOnlineAlternateRecipient: can be used to set an alternate recipient on an Exchange Online mailbox; you can configure the mailbox to deliver e-mails to both the Exchange Online inbox and the alternate recipient
  • Set-MSOnlineUserPassword: can be used to set a password on an online user

Note that there are no cmdlets to create contacts and distribution lists in the Exchange Online global address lists. You can also see that these cmdlets are specifically created for use with Exchange Online in BPOS and that they have no relation at all with Exchange Server 2007 or Exchange Server 2010 cmdlets in an on-premise solution. To fix those issues and provide many more options, Exchange Online in Office 365 will actually allow full use of Exchange Server 2010 SP1 cmdlets over the Internet. Naturally, not all cmdlets will be available and some cmdlets will not support all parameters.

By the way, if you don’t like to work with PowerShell commands, a company called MessageOps has a free tool called BPOS PowerShell GUI. You need to install that tool on a system that has the Microsoft migration tools installed. The tool looks like this:

image

The tool provides easy access to allmost all the available PowerShell cmdlets using the above GUI. Highly recommended!

Enabling Forefront Administration and Quarantine in BPOS

Exchange Online, standalone or as part of BPOS (Business Productivity Online Suite), always comes with antivirus and anti-spam protection delivered by Forefront Online Protection for Exchange or FOPE. Mails are always scanned for viruses and, depending on your settings, checked for spam.

As a BPOS administrator you can control the anti-spam checking behavior by configuring safe and blocked senders in the BPOS Administration Center. E-mail addresses, IP addresses or domains added to the safe senders for instance are not checked for spam by the FOPE infrastructure.

When e-mail is marked as spam, it is held by FOPE. By default, a BPOS user gets an e-mail every three days (from FOPE) with a list of e-mails marked as spam. The user can then decide to move the e-mail to the inbox.

If you want more control and visibility into what FOPE does, and you want to grant users access to the quarantine website at FOPE for immediate action on blocked mails, you will need to ask support to enable the following for you:

You can initially ask that the BPOS administration account you are using is granted access to both. From the FOPE administration website, you can add other users so that they have access to their quarantine. Note that the password to access FOPE admin and quarantine is different from the BPOS password!

Now the question is: “What can you do in the FOPE administration website?”. The answer is simple: “Not that much.” Basically, you get read-only access to most settings. However, you can generate reports and, as stated above, add other users so that they can access the quarantine website. The screenshot below shows an example of a report (in Dutch):

image

A user with access to the quarantine, sees the following:

image

The user can easily select the e-mails that are not spam and move them to the inbox (or mark them as not spam).

If we look forward to how things will be in Office 365, administrators will have more control over the settings in FOPE. That, for sure, will be a welcome addition to the capabilities of Exchange Online and the administrative control customers will have over it.

Inviting external users to Office 365 SharePoint Sites

imageA question I often get while delivering the BPOS courses for partners in Belgium and Luxemburg is how to collaborate with external users on SharePoint sites. In BPOS (Business Productivity Online Suite), you can only grant access to users that have an account in the customer’s BPOS subscription and those users consume a license. Although you can use a deskless worker license, you still need to provision the account, manage it and pay a monthly fee for it.

In Office 365, it will be possible to grant external users access by using their e-mail address. The only caveat here is that the external user has to logon with a Live ID that matches the e-mail address you granted access to. If they do not have one, they will have to create one. Although I would have liked to see support for Open ID, Facebook Connect or other systems this new possibility will make it much easier to collaborate with external users.

The SharePoint Online Administration Guide (still beta of course) that is now available on TechNet documents the process in two sections:

%d bloggers like this: