What is Azure Active Directory? A Complete Overview

 https://www.varonis.com/blog/azure-active-directory/

 What is Azure Active Directory? A Complete Overview

What is Windows Active Directory?

Windows Active Directory (AD) is Microsoft’s predecessor to Azure AD. Microsoft released Active Directory in Windows 2000 server, and it became a standard for enterprise identity management.

Active Directory lives on-premise in servers called Domain Controllers (DC). Each DC contains a catalog of users and computers that are authorized to access resources on the network. Users authenticate to DCs via Kerberos or NTLM authentication.

AD security is one of our favorite topics because many attacks the Varonis Incident Response team researches involve AD at some point in the cyber kill chain. It could be a simple brute force attack to crack an old NTLM password or a privilege escalation attempt to take over an administrator account. AD security has been the topic of many conference talks and we even wrote a comprehensive guide to pen testing your AD environment to ensure its resilience to common off-the-shelf attacks.

Any conversation about Azure AD has to mention AD classic, and we will explain why further along in the blog.

Difference Between Windows and Azure AD

Azure AD and Windows AD are both created by Microsoft, and they are both IAM systems, but that’s pretty much where the comparisons stop. They are fundamentally different systems that exist in an interconnected enterprise environment.

Azure Active Directory

• REST APIs: Azure AD uses Representational State Transfer (REST) APIs to support communication to other web-based services
• Authentication: Azure AD uses cloud-based authentication protocols like OAuth2, SAML, and WS-Security for user authentication
• Network Organization: Each Azure AD instance is called a “tenant” which is a flat structure of users and groups
• Entitlement Management: Admins organize users into groups, and then give groups access to apps and resources
• Devices: Azure AD provides mobile device management with Microsoft Intune
• Desktops: Windows desktops can join Azure AD with Microsoft Intune
• Servers: Azure AD uses Azure AD Domain Services to manage servers that live in the Azure cloud virtual machine environment

Windows Active Directory

• LDAP: Windows AD uses Lightweight Directory Access Protocol (LDAP) to pass data between clients and servers and DCs.
• Authentication: Windows AD uses Kerberos and NTLM to validate user credentials
• Network Organization: Windows AD is organized into Organizational Units, Domains, and Forests
• Entitlement Management: Admins or data owners assign users to groups, and those groups have access to resources on the network
• Devices: Windows AD does not manage mobile devices
• Desktops: Desktops joined to Windows AD are governed by Group Policy (GPOs)
• Servers: Servers in Windows AD are managed and governed by GPOs or other on-premise server management system

The answer to the question, “so which one do I use?” is probably both. If you are running an established enterprise network, you most likely already have Windows AD, and you are adding Azure AD to manage your cloud infrastructure.

If you are starting a brand new organization from scratch, Azure AD could meet all of your needs, especially if you plan on using an entirely cloud-based infrastructure.

The other question you might ask is “which one is harder to configure than the other?” And I would say that neither one is more or less configurable than the other, and neither one is more or less secure than the other.  Both systems require a qualified expert to manage and protect your network for companies larger than 100 users or so. Smaller shops will find Azure AD easier to manage overall.

Azure AD Connect for Hybrid Deployments

Azure AD Connect is Microsoft’s solution to enable hybrid Windows AD and Azure AD deployments. Azure AD Connect syncs data between the on-premise DCs and the cloud.

Azure AD Connect will let you sync user accounts from your on-premise system to your Azure tenant. It also provides password hash synchronization, pass-through authentication, federation, and health monitoring.

Those features allow your users to have the same user id and password on-premise and in the cloud and to ease the management of your hybrid environment. In short, you need Azure AD Connect if you have a hybrid environment.

As a sysadmin or security pro, it’s important that your security solutions give you a unified view of each user regardless of whether they’re accessing cloud or on-prem resources. The Varonis Data Security Platform, for example, makes it easy to pinpoint a user and see their activity in Azure AD and Windows AD. Even though there are two user repositories behind the scenes, Varonis treats them as a single user with a comprehensive user behavior profile that includes on-prem and cloud activity.

Azure Active Directory Considerations

OK, so if you have made it this far, you might be considering implementing Azure AD for your organization. Now you have real decisions to make.

1. Licensing: Azure AD licensing follows the same monthly subscription licensing as the Office 365 licenses. There are four license levels – Free, Office 365 Apps, Premium P1, and Premium P2.

Office 365 Apps comes as part of your Office 365 subscription, and the Premium packages are a separate item. You get the Free license as part of a subscription to Azure, Dynamics 365, Intune, and Power Platform.

The Premium tier adds features like advanced password protection, self-service password management for your users,  advanced group access management, and conditional access.

The features lists for Azure AD and Microsoft 365 are separate, and you need to look at both of them to understand everything available to you so you can build your implementation strategy.

Ed. Note: Office 365 recently got renamed to Microsoft 365. At the time of this writing, Microsoft’s documentation contains both names, but they are the same thing.

2. Choose your scenario: Hybrid Azure AD or Azure AD? If you already have Windows AD, Hybrid might be your best option. If you are trying to build a cloud-only infrastructure, Azure AD is the better choice.

For your Hybrid environment, you can go with Managed or Federated configurations. If you are going to create users in Windows AD, you need to have Azure AD Connect to sync with Azure AD.

Are you going to use the device management in Azure AD? If so, you need Windows 10 on all those devices.

3. SSO: Are you going to enable Single Sign-on(SSO) with Azure AD? You will need to configure your cloud apps and services to use the Azure SSO, and set up a hybrid cloud for printing.

4. User Provisioning: How are you going to add your existing users to Azure? You can set up self-enrollment where users run the process themselves, Windows Autopilot, or have an admin enroll your users.

Those four steps will set you on the right path. You will have to do some more homework to figure out all the answers, which will lead you to more questions that need different answers.

How Does Azure Active Directory Work?

Azure AD is a new system that Microsoft designed from the ground up to support cloud infrastructure. Azure AD uses REST APIs to pass data from one system to other cloud applications and systems that support REST (which is most cloud applications).

Unlike Windows AD, Azure AD is a flat structure in a single tenant. Think of the tenant as a circle that surrounds all your stuff. You can control the stuff inside the tenant, but once it leaves that circle you lose some agency over what happens to your stuff.

At Varonis, our approach to data security aligns with zero-trust principles, so as we continue we will weave in zero-trust when appropriate.

Users and Groups

Users and groups are the basic building blocks for Azure AD. You can further organize users into groups that will all behave similarly. For example, you may put your Product Management team in one Azure AD group and grant permissions at the group level, so when users leave the organization, you only need to deactivate one account, and the rest of the group stays the same.

Users in Azure AD can come from both inside and outside of Azure AD. Let me restate that. Your Azure AD can contain identities for users inside of your organization and users from outside your organization that have a Microsoft account. See below:

What this means is that you can bring people outside of your organization inside your tenant and grant them specific permissions just like they are part of your organization. When done correctly, this provides an additional level of security to the organization’s data.

Adding User and Groups to Azure AD

There are several methods to populate your users and groups in Azure AD.

• Use Azure AD Connect to sync users from Windows AD to Azure AD. Most enterprises that already have Windows AD use this method.
• You can create users manually in the Azure AD Management Portal.
• You can script the process to add new users with PowerShell.
• Or you could program the process with the Azure AD Graph API.

No matter which option you start with or use, later on, there are a few key points to make about adding users in Azure AD.

1. Establish your authentication method and password policies, and enforce multi-factor authentication.
2. Only add users that you need to Azure AD. Leave service accounts or stale accounts in Windows AD, or delete them.
3. Keep privileged access in Azure AD to a minimum and follow Microsoft’s guidance to keep privileged access secure.
4. Organize users into groups, and only give groups access to the applications and resources they need to do their job.
5. Connect users to their devices (mobile phones, laptops, etc.), so you can establish limits on how confidential data is downloaded or saved from approved and monitored devices.

Custom Domains

Adding a custom domain to Azure AD will reduce the frustration that your users’ experience as they migrate to the new system. The default Azure AD domain looks like this:

• @notarealdomain.onmicrosoft.com

That’s a lot to type. If you configured Azure AD to use a domain that you own, your users would thank you. It would look something like @notarealdomain.com instead. That’s much easier to deal with.

Common Attacks Against Azure AD

I’d like to say that the transition to Azure AD was smooth and without issue, but alas. Any significant transformation to a cloud-enabled infrastructure is bound to attract malicious attackers that want to infiltrate the new frontier. And so they did.

The Varonis IR team investigates many brute force attacks against Azure AD. Attackers love to use vast collections of usernames and passwords from data breach dumps to try to break into Azure AD accounts—a method known as credential stuffing.

Azure AD is available from the internet, so it’s a relatively easy target. A good password policy and multi-factor authentication, as well as behavioral monitoring of login activity and geo-hopping, can thwart most brute force attacks. Most. You still need to monitor your data to detect malicious activity inside your tenant in the event an attacker succeeds with a single login attempt.

Phishing is the other top attack we see against Azure AD users. Phishing can lead to credential theft or malware infection, which can provide attackers with a foothold to access your tenant. One of the better enhancements Azure AD provides is warnings when you open an email from an outsider or untrusted source.

You can enable this setting, and other email protections in the Azure AD Management Console. The Varonis IR team demonstrates how to use phishing to infiltrate and steal data in this Live Cyber Security Lab.

Azure Skeleton Key Attack

This attack has to with Azure AD Connect, which we described above as the way to synchronize your Azure and on-prem AD. Azure AD Connect can be configured via a method called Pass-Through Authentication. When this method is used, a server called the “Azure Agent” is installed on-prem.

Should an attacker compromise an organization’s Azure agent server they can create a backdoor that allows them to log in as any synchronized user. Varonis created a proof-of-concept that manipulates the Azure authentication function to 1.) give us a ‘skeleton key’ password that will work for all users, and 2.) dump all real clear-text usernames and passwords into a file.

You can read the details and see the Azure Skeleton Key attack POC in action here.

What Else Can I Configure in Azure AD?

Microsoft provides enhancements and tools to Azure AD and Microsoft 365 to further securing and protecting your organization’s data in the cloud. Here are a few more options that you can enable to keep your organization more secure.

• Integrate applications with Azure AD to enable Single Sign-On (SSO)
• Automate application provisioning to new users based on group membership
• Restrict user’s ability to consent to applications – this can be a phishing attack, and once the user clicks the attacker has a foothold in your tenant
• Block legacy protocols that have security issues, like SMTP, POP3, or MAPI
• Enable Microsoft Cloud Access Security (MCAS) to provide monitoring inside your tenant, and augment that monitoring with Azure Skeleton Key attack
• Now that you have Varonis, classify all of your sensitive data and tag it with Microsoft Azure Information Protection (AIP)

That’s not nearly a comprehensive list of tools to manage and secure Azure AD. Do check out the webinar about Microsoft Teams and see some other ways to prevent data leaks and to learn why one security professional said, “We wouldn’t even be considering OneDrive if we didn’t have Varonis in place.”

How to create a Folder using a SQL Query?

How to create a Folder using a SQL Query?

 

EXEC xp_cmdshell 'MD C:\MyNewFolder'

Export Documents saved as Blob / Binary from SQL Server

 https://www.jitendrazaa.com/blog/sql/sqlserver/export-documents-saved-as-blob-binary-from-sql-server/

Export Documents saved as Blob / Binary from SQL Server

T-SQL Scripts to Export Blob or Binary data stored in SQL Server

I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .

1	CREATE TABLE [dbo].[Document](

2	[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

3	[Extension] [varchar](50) NULL,

4	[FileName] [varchar](200) NULL,

5	[Doc_Content] [varbinary](max) NULL

6	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

1	INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )

2	SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*

3	FROM OPENROWSET

4	(BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

5

6	INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )

7	SELECT 'html', 'Progress.html',[Doc_Data].*

8	FROM OPENROWSET

9	(BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database

Export Blob From SQL Server and save it as a file

For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.

Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.

1

USE [POC]

2	DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob'

3	, @i bigint

4	, @init int

5	, @data varbinary(max)

6	, @fPath varchar(max)

7	, @folderPath  varchar(max)

8

9	--Get Data into temp Table variable so that we can iterate over it

10	DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )

11

12	INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])

13	Select [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[Document]

14

15	--SELECT * FROM @table

16

17	SELECT @i = COUNT(1) FROM @Doctable

18

19	WHILE @i >= 1

20

BEGIN

21

22

SELECT

23	@data = [Doc_Content],

24	@fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],

25	@folderPath = @outPutPath + '\'+ [Doc_Num]

26	FROM @Doctable WHERE id = @i

27

28	--Create folder first

29	EXEC  [dbo].[CreateFolder]  @folderPath

30

31	EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created

32	EXEC sp_OASetProperty @init, 'Type', 1;

33	EXEC sp_OAMethod @init, 'Open'; -- Calling a method

34	EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method

35	EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method

36	EXEC sp_OAMethod @init, 'Close'; -- Calling a method

37	EXEC sp_OADestroy @init; -- Closed the resources

38

39	print 'Document Generated at - '+  @fPath

40

41	--Reset the variables for next use

42	SELECT @data = NULL

43	, @init = NULL

44	, @fPath = NULL

45	, @folderPath = NULL

46	SET @i -= 1

47

END

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File