Objective:
I will use this article to share how you can configure Azure AD Authentication with ADFS and SQL Server on Premises. We will configure an identity provider in Azure Active Directory; federate it with our on-premise Active Directory Federation Services (ADFS) providing SSO capabilities for our users’ access to other services through Microsoft Online Portal. To store users’ passwords we will configure an MYSQL server Database in a Windows Server Instance in the local network and map it to login in the federation service.
1- Infrastructure Preparation:
This task is not part of the configuration, but I want to make sure that you don’t miss any important information related to your infrastructure components before starting. In my case and for this article I will use:
1. Active Directory:
The directory service where users, computers, groups and policies are stored in my infrastructure. My domain is named “pkicloud.com “. You can find more information here.
2. Active Directory Federation Services (ADFS) Windows Server 2012 R2:
It is a software element that provides identity federation services for your on-premises applications and resources with cloud-based applications and resources in any public or private cloud or web environment whether Microsoft or non-Microsoft through federation using standard protocols like Security Assertion Markup Language (SAML). ADFS will also allow users from partner federations to log on using a single set of credentials that are managed by the federation infrastructure. You can find more information here.
3. Microsoft Online Portal:
An entry point for access to online services and a common sign-on experience for Office 365, Enterprise Mobility Suite (EMS) and Windows Intune Subscriptions. You can find more information here.
4. SQL Server 2012 R2: A
Relational database management system developed by Microsoft as an enterprise-class data platform for use in large organizations. It is built on the engine originally implemented by Microsoft in SQL Server 2008 R2 it offers better performance, new security features, greater scalability, availability, manageability, and interoperability with Azure Cloud Services. You can find more information here.
5. MySQL Community Server:
A multi-user, multi-threaded SQL database server. Installing and running a MySQL server has never been so easy. You can find more information here. Step By Step Guide to Install MySQL on Windows 7/8/10.
2- Active Directory Federation Service Configuration:
This task consists of creating the federation service in an Active Directory instance following Microsoft documentation steps located here. Basically, you need to create three main configuration files that are xml based using Microsoft Visual studio 2012, local computer administrator privileges are needed for this step. The first file will be the adfs federation service identifier, named “ADFSSO ” it is located.
Note: The following values are examples of what you should enter into the application configuration file. They will be used in subsequent sections to demonstrate various capabilities of this service. For more information, see the “Application Configuration File Entries” section below. You can find more information here. After creating the first file, you must create the second one named ” adfs config ” located <drive>:\windows\adfs\config\<instance name>.xml If there is only one federation server instance on the computer, instances are not supported and you must use a single-server configuration that uses localhost as its Federation Service Name (FSN). This security identifier (SID) identifies the federation service running on this computer. You can find more information here.
The final file is named <instance name> markup located at the following path <drive>:\windows\adfs\auth\<instance name>.asmx And is used to define authentication and error pages that are shown to users when they haven’t authenticated or when an error occurs during single sign-on (SSO). The file also defines any query string parameters required by your website’s login page. You can find more information here.
3- Installing MySQL:
Microsoft Windows provides native support for SQL server databases through an ODBC driver, but there are some limitations, so I will install the MySQL Database Server in my active directory instance which I can use to store information about users and groups. Basically, I’ll be using the MySQL Workbench software which is an integrated tool for managing the databases that you can download from here. You need to install it on your local computer.
4- Configuring Active Directory Federation Service:
The first step is creating a MySQL connection string through the visual studio by opening the file located at <drive>:\windows\adfs\.next () next () returns a reference to the current federation configuration object in the Web service context. The following example retrieves the value of the FSN attribute for this server’s federation configuration object.
Conclusion:
The reference will be used to configure the federation website in my active directory instance.
Now save the file with ctrl+s and restart the Microsoft Active Directory Federation Services service for changes to become effective. Remember that Visual Studio is case sensitive so type carefully.
Once the changes are done, open MySQL Workbench which I installed earlier on my local computer then clicks on new server registration by adding a new connection to your local system with host name 127.