Perhaps one of the most useful features at your disposal when working with Azure SQL Databases is the ability to integrate your Azure Active Directory (Azure AD) login accountsa la Windows Authentication for on-premise SQL Server. There are numerous benefits in shifting away from SQL Server-only user accounts in favour of Azure AD:

  • Ensures consistent login identities across multiple services.
  • Can enforce password complexity and refresh rules more easily.
  • Once configured, they behave exactly the same as standard SQL Server only logins.
  • Supports advanced usage scenarios involving Azure AD, such as multi-factor authentication and Single Sign-On (SSO) via Active Directory Federation Services (ADFS).

Setup can be completed in a pinch, although you will need to allocate a single/group of user(s) as the Active Directory admin for the Azure SQL Server. You may also choose to take due care and precautions when choosing your Active Directory admin(s); one suggestion would be to use a unique service account for the Active Directory admin, with a strong password, instead of granting such extensive privileges to normal user accounts.

Regardless of how you go about configuring the feature, I would recommend using it where-ever you can, for both internal purposes and also for anyone who wishes to access your SQL Server from an external directory. This second scenario is, you may be surprised to hear, fully supported. It assumes, first off, that you have added this account to your directory as a Guest/External User account. Then, you just follow the normal steps to get the account created on your Azure SQL Server.

There is one major “gotcha” to bear in mind when doing this. Let’s assume that you have added john.smith@domain.co.uk to the Azure AD tenant test.onmicrosoft.com. You then go to setup this account to access a SQL Server instance on the tenant. You will more than likely receive the following error message when using the example syntax below to create the account:

CREATE USER [john.smith@domain.co.uk] FROM EXTERNAL PROVIDER

The issue is, thankfully, simple to understand and fix. When External user accounts are added onto your Active Directory, despite having the same login name that derives from their source directory, they are stored in the new directory with a different UserPrincipalName (UPN). Consider the above example – the UPN in the source directory would be as follows:

john.smith@domain.co.uk

Whereas, as the Azure AD tenant name in this example is test.onmicrosoft.com, the UPN for the object would be:

john.smith_domain.co.uk#EXT#@test.onmicrosoft.com

I assume that this is done to prevent any UPN duplication across Microsoft’s no-doubt dizzying array of cloud Active Directory tenants and forests. In any event, knowing this, we can adjust our code above to suit – and successfully create our Database user account:

CREATE USER [john.smith_domain.co.uk#EXT#@test.onmicrosoft.com] FROM EXTERNAL PROVIDER

I guess this is one of those things where having at least a casual awareness of how other technologies within the Microsoft “stack” work can assist you greatly in troubleshooting what turn out to be simplistic errors in your code. Frustrating all the same, but we can claim knowledge of an obscure piece of Azure AD trivia as our end result 🙂

I would not recommend setting up a Windows Server Domain Services role for the first time flying blind. Whilst not necessarily classing myself as a “newbie” in this respect, I have only run through this a few times in the past within lab environments. The process is always tricky – not just in deploying out the role in the first instance, but more via the many quirks that get thrown up as you try to accomplish what should be simple tasks, such as domain joining devices or getting DNS settings correctly mapped out. These and other tiresome rat races can leave you with a severely scratched head and distract you from your ultimate goal.

For this reason, you could argue that Azure Active Directory Domain Services (or AADDS) is the perfect solution for “newbies”. The thing I like the most about it is that a lot of the hassle I make reference to above is something you will never see a sight of, thanks to the fact that Microsoft manages most aspects of the deployment behind the scenes. In addition, the step-by-step guides available on the Microsoft Docs website provide a very clear and no-nonsense holding hand through every step of an Azure Domain Services rollout. What this ultimately means is that you can spend more time on achieving your end goal and reduce the need for extensive administration of the solution following its rollout. Having said that, it is always useful to ensure that you have thoroughly tested any solution as extensively as possible for your particular scenario, as this will always throw up some potential issues or useful tips to remember in future; AADDS is no exception to this rule.

Having recently worked closely with AADDS, in this weeks blog post, I wanted to share some of my detailed thoughts regarding the solution in practice and a few things to remember if you are checking it out for the first time.

Resetting AADDS User passwords could become the bane of your existence

If you are creating an AADDS resource in isolation to any existing identity providers you have in place (i.e. there is no requirement to use Azure AD Connect with an On-Premise Domain Server), then be aware that you will have to set up a users password twice before they will be able to login to the domain. Microsoft explains better than I can why this is:

To authenticate users on the managed domain, Azure Active Directory Domain Services needs credential hashes in a format that’s suitable for NTLM and Kerberos authentication. Azure AD does not generate or store credential hashes in the format that’s required for NTLM or Kerberos authentication, until you enable Azure Active Directory Domain Services for your tenant. For obvious security reasons, Azure AD also does not store any password credentials in clear-text form. Therefore, Azure AD does not have a way to automatically generate these NTLM or Kerberos credential hashes based on users’ existing credentials…If your organization has cloud-only user accounts, all users who need to use Azure Active Directory Domain Services must change their passwords

Source: https://docs.microsoft.com/en-us/azure/active-directory-domain-services/active-directory-ds-getting-started-password-sync

The above article goes into the required steps that need to be followed for each user account that is created and, at the time of writing, I do not believe there is any way of automating this process. Whether you choose to complete these steps yourself or get your end users to do so instead is up to you, but there is a good chance that if a user is experiencing login issues with an AADDS account, then the steps in the above article have not been followed correctly.

Make sure you’re happy with your chosen DNS Name

When first creating your Domain Services resource, you need to be pretty certain your desired DNS domain name will not be subject to change in the future. After some fruitless digging around on the portal and an escalated support request to Microsoft, I was able to confirm that there is no way this can be changed after the Domain Services resource is deployed; your only recourse is to recreate the resource from scratch with your newly desired DNS domain name. This could prove to be problematic if, say, you wish to change the domain name of your in-development domain services account from the default onmicrosoft.com domain to a bespoke one…after you have already joined Virtual Machines to your new domain : / Some efficient use of the Azure templates feature can save you some aggro here, but not if you have already expended considerable effort on bespoke customisation on each of your VM’s operating systems.

Be aware of what’s supported….and what isn’t

There are a few articles that Microsoft have published that can help you to determine whether AADDS is right for your particular scenario:

Whilst these are invaluable and, admittedly, demonstrate the wide-feature array contained with AADDS, there are still a few hidden “gotchas” to be aware of. The articles above hint towards some of these:

  • Only one AADDS resource is allowed per Azure tenant. You will need to configure a clean Active Directory tenant (and therefore a separate Azure portal) for any additional AADDS resource you wish to setup, which also requires an appropriate subscription for billing. This could result in ever-growing complexity to your Azure footprint.
  • AADDS is a continually billable service. Unlike VM’s, which can be set to Stopped (unallocated) status at any time and, therefore, not incur any usage charges, your Domain Services resource will incur fees as soon as you create it and only cease when the resource is deleted.

One unsupported feature that the above articles do not provide any hint towards is Managed Service Accounts. Introduced as part of Windows Server 2008 R2, they provide a more streamlined means of managing service accounts for applications running on Windows Server, reducing the requirement to maintain passwords for these accounts and allowing administrators to provide domain-level privileges to essential service account objects. I try to use them whenever I can in conjunction with SQL Server installations, particularly if the service accounts for SQL need to access network-level resources that are secured via a security group or similar and I would encourage you to read up on them further to see if they could be a help within your SQL Server deployments.

Back to the topic at hand – if you attempt to create a Managed Service Account via PowerShell, you will receive an error message saying that they are not supported within the domain. So, assuming that you are wanting to go ahead and deploy SQL Server on an AADDS joined VM, you would have to revert back to using standard Active Directory user accounts for your Service Accounts to achieve the same functionality. Not great if you also have to enforce password refresh policies, but it would be the only supported workaround in this situation.

Conclusion or Wot I Think

When reviewing potential new IT vendors or products, I always try and judge “how dirty handed” I would need to get with it. What I mean by this is the level of involvement myself, or a business, would need to invest in managing physical server hardware, backend elements of the infrastructure or any aspect of the solution that requires an inordinate amount of time poking around the innards of to troubleshoot basic problems. The great benefit of services such as Azure is that a lot of this pain is taken away by default – for example, there is no need to manage server, firewall and networking hardware at all, as Microsoft does this for you. AADDS goes a step further by removing the need to manage the server aspect of a Domain Services deployment, allowing you to focus more on building out your identities and integrating them within your chosen application. Whilst it does need some work to get it up to an acceptable level of parity with a “do-it-yourself” Domain Server (for example, extensive PowerShell support for the completion of common tasks), the service is still very much in a developed and user-friendly state to warrant further investigation – particularly if you have a simplified Active Directory Domain in place or are looking to migrate across to Active Directory from another vendor. £80 per month for a directory smaller than 25,000 objects is also not an exorbitant price to pay as well, so I would definitely recommend you check AADDS out to see if it could be a good fit for your organisation/application in the near future.

Getting to grips with how to use Dynamics CRM/365 for Enterprise (D365E) is no easy feat. You can imagine just how difficult it is for an end user to get to grips with how the application works and functions; with more detailed knowledge around customisation and development being an entirely different ball game altogether. Compounding this problem further is the fact that the product has evolved at an increasingly more rapid pace over recent years, to the point where it is literally impossible to become a master of everything that you can do within CRM/D365E. Those venturing into the product for the first time may find their learning journey significantly simplified if they already have a good general knowledge about some of the underlying technology that powers CRM/D365E. This was certainly true in my case; I had a good background already in managing Office 365, writing SQL queries/reports and some experience with C#. This is all incredibly useful knowledge to have in your arsenal and is all directly applicable towards CRM/D365E in some way. For those who are getting to grips with the product for the first time, either without this previous experience or as part of an apprentice/graduate type role, your journey may not be as swift and issue-free. With this in mind, here’s my list of essential knowledge that you can add to your own “swiss army knife” of personal knowledge. Experience and good knowledge of these technologies will not only help you greatly in working with CRM/D365E, but present an excellent learning opportunity for Microsoft technologies more generally and something that you can add to your CV with pride:

SQL Server

What is it? : SQL Server is Microsoft’s proprietary database knowledge, based on the ANSI standard. SQL stands for Structured Query Language and is one the most widely used database programming languages on the planet.

Why Knowing It Is Useful: The underlying database technology that CRM/D365E uses is SQL Server, so having a general awareness of relational database systems work and how SQL Server differs from the standard goes a long way in understanding what is capable from a customisation/development viewpoint. For example, you can very quickly grasp which data types the application supports, as they will all ultimately be based on a supported SQL Server column data type. If you are running on-premise versions of CRM/D365E, then knowledge of SQL Server immediately moves from being a nice bonus to an essential requirement. This is because administrators will need to have good knowledge of how to manage their Dynamics CRM database instance, perform backups and also, potentially, write transact-SQL (T-SQL) queries against your database for reporting or diagnostic work.

Recommended Area of Study: Focusing your attention towards SQL Server Reporting Services (SSRS) report writing will benefit you the most. Through this, you can begin to establish good knowledge of how SQL Server databases work generally, and be in a position to write FetchXML for Online/On-Premise deployments of the application or Transact-SQL (T-SQL) based reports for On-Premise only. Having a good awareness of what is capable via a standard SQL query will also hold your good stead when working with FetchXML, as you can immediately make a number of assumptions about what is possible with FetchXML (for example, filtering results using an IN block containing multiple values and performing grouping/aggregate actions on datasets)

Office 365

What is it? : Office 365 is Microsofts primary – and perhaps most popular – cloud offering for businesses, individuals or home users. Through a wide array of different subscription offers, home and business users can “pick ‘n’ mix” the range of solutions they require – from Exchange-based email accounts to licenses for Microsoft Visio/Project, through to PowerBI.

Why Knowing It Is Useful: Although it is arguable that knowledge of Office 365 is not essential if you anticipate working with on-premises versions of the application, you may be doing yourself a disservice in the long term. Microsoft is increasingly incentivising organisations to move towards the equivalent cloud versions of their on-premise applications, meaning that as much knowledge as possible of how CRM/D365E Online works in the context of Office 365 is going to become increasingly more mandatory. If you are looking to secure a career change in the near future, and have not had much experience with Office 365, then this is definitely an area that you should focus on for future learning. From a day-to-day management point of view for the Online version of the product, some basic awareness of how to navigate around and use Office 365 is pretty much essential if you are going to succeed working with the product on a day-to-day basis.

Recommended Area of Study: Spin up a D365E trial, and you can very quickly start getting to grips with how the product sits within the Office 365 “ecosystem”. Practice licensing users, configuring security group level access to your D365E trial tenant and modify the details on Office 365 user accounts to see how these details are synced through into D365E. The Microsoft Virtual Academy also has a number of general courses related to Office 365 however, due to the frequent updates, it may not always be in-line with the current version. The official curriculum/certification paths for Office 365 may also suffer the same from this but are worthwhile in demonstrating your experience and ability to integrate D365E with the various related Office 365 services.

Active Directory

What is it? : For the rookie, intermediate and experienced IT admins, Active Directory needs no introduction. It is essentially Microsoft’s implementation of the Lightweight Directory Access Protocol (LDAP), having first being introduced in Windows Server 2000, providing a means of managing user, security and access rights for domain objects. There are now two distinct versions of Active Directory that are available – the more traditional, Windows server based, on-premise Active Directory and Azure Active Directory, which is utilised primarily by Office 365.

Why Knowing It Is Useful: User account records for both On-Premise and Online versions of CRM/D365E use Active Directory objects, with a number of important information synchronised between an Active Directory user and the equivalent User entity record. For example, as indicated in this MSDN article, the only way in which you can synchronise a user’s Job Title through to CRM/D365E is by updating the equivalent field on the Azure Active Directory. Active Directory objects are also the only way in which you can authenticate with the application via the Web Interface or other means, with no option to create a database user or other kind of authenticated user type.

Recommended Area of Study: It’s free to set up your own Azure Active Directory, so this is an excellent starting point for getting to grips with the technology. There’s also nothing preventing you from downloading a trial of Windows Server and installing the Active Directory server role on this machine. Once configured, you can then start to create users, update attributes, configure permissions and setup roles that contain collections of privileges. If you already have an Office 365 tenant with CRM/D365E Online, then you can use the Office 365 portal to manage your user accounts and test the synchronisation of attribute values from the Active Directory through to the application.

PowerShell

What is it? : A good way to remember what PowerShell is that it is essentially a blue command prompt window 🙂 . Traditionally only being relevant and important for those working extensively with Windows Server or Exchange, PowerShell is now increasingly important as part of administrating on-premise CRM/D365E, Office 365 and Azure, to name a few. Indeed, one of the major shock announcements this year was that PowerShell became open sourced and can be installed on Linux; representing the increasing demand and importance of Linux-based resources within the Microsoft cloud.

Why Knowing It is Useful: Similar to SQL Server, PowerShell is something that is instantly more applicable for on-premise CRM/D365E deployments. For example, the only way to modify the default number of Dashboard items is via executing the Get-CRMSetting cmdlet against your on-premise organisation. I would also, again, argue having a general awareness of PowerShell can help greatly when performing administration work against an Office 365 tenant that contains a CRM/D365E organisation, such as user provisioning or license assignment. If you are utilising the Azure Service Bus to integrate CRM/D365E for Azure-based applications, then PowerShell immediately becomes a desirable skill to have in your arsenal, allowing you to remotely administer, deploy or update Azure resources programmatically.

Recommended Area of Study: The fact that PowerShell is now open sourced means that there is a plethora of online tools and guides to refer to, and you can be assured that you can get it working on your platform of choice. The GitHub page for PowerShell is a great place to get started. Beyond that, you have a few options about how you can practice further. If you have spun up a D365E trial, then you can choose to hook up PowerShell to Office 365 to see what you can do from a remote management perspective (such as granting Send On Behalf permissions for a shared mailbox). Alternatively, you can run it from your local Windows machine, connect it up to a Windows Server instance or attempt to create new services in Azure and experiment that way.