Building a mixed cloud model for SharePoint – Part 2

In previous article, you created a SharePoint 2016 virtual machine by given built-in template. You were also introduced about Azure AD DS and joined the virtual machine to your Azure AD DS. Everything you have done so far by only PowerShell. Of course, you can create all resources via Azure Portal. However, such a way takes time and hard to control and organize parameters.

Keep track the series:

In this article, let’s dig into another part of the series which is deploying database system for your SharePoint farm. Basically, it is just a virtual machine running Microsoft SQL Server. However, that is not my intention. I’d like to take advantages of Azure SQL Database to achieve more high availability and scalability with less administration effort and compute resources.

Deploying Azure SQL Logical Server

Azure SQL logical server is not a virtual machine running Microsoft SQL Server. By Microsoft definition, the term “logical server” is like a control panel which provides administration capabilities to help you manage your SQL databases. From logical server, you can not only create single or elastic pool of databases but also configure security features including firewall rules, auditing rule, threat detection policies.

First, let’s create a logical server with the following PowerShell

You will be asked to provide credential when the script reads to Get-Credential . Next step is to add an account to act as admin.

Creating a blank database

Creating a blank database can be done by using the following PowerShell script. This step is to create a slot for SharePoint configuration database. Note that I specify supported collation name Latin1_General_CI_AS_KS_WS for SharePoint.

Network configuration for SQL logical server

There are a few things to do. First, you need to create a new subnet for the logical server and set Microsoft SQL endpoint for it. Secondly, create a new virtual network rule and associate the newly created subnet into it. The two steps can be done via the following PowerShell:

Go to the logical server and find Firewall/Virtual Networks setting under the blade. Under Allow access to Azure services select ON. This is just a temporary option to let network traffic through. You can also check whether the virtual network rule is successfully created.

After the logical server is added into the same virtual network with the SharePoint virtual machine, use PortQuery UI to verify.

SQL Management Studio configuration

The network part is sorted out. Now let’s focus on some final configuration before creating a SharePoint farm. Download SQL Server Management Studio to your personal computer and connect to the logical server at the address <your_db_server_name> . Connecting from a public network needs further logon asked by Microsoft. If you connect from your SharePoint virtual machine, it’s completely smooth.

I tried two approaches of creating a SharePoint farm:

  • Approach #1: Running SharePoint Product Configuration Wizards.

  • Approach #2: Use  New-SPConfigurationDatabase  to create a new configuration database.

Both approaches resulted to the fact that old-fashioned integrated authentication is not supported in Azure SQL as of this article. Right now I’d confirm that connecting your SharePoint farm with integrated authentication to Azure SQL Database is not possible. There could be another approach to achieve the seamless authentication but I’ve figured yet.

What about SQL Server running on Azure VM?

To dig into a little more about the mixed cloud model, I want to provision a virtual machine running Microsoft SQL Server to see how possible my goal is achieved. The following PowerShell can be used to quickly provision a virtual machine with SQL Server 2014 SP 2 with Windows Server 2012 R2. All resource information is referenced to the previous article, including resource group, virtual network, subnet

Once the SQL Server virtual machine is successfully provisioned, connect to it to join to the Azure AD DS

Now you have two virtual machines already joined to the existing Azure AD DS.

SharePoint Farm Configuration

The last step is to run the SharePoint Product Configuration Wizard or PowerShell and complete configuration steps. I’m not going to write down here every step. Make sure your account has appropriate permission (e.g dbcreator and securityadmin) and MAXDOP setting in SQL Server is set to 1 (by default it is 0 with the given template). Fortunately, the setup and farm configuration is done without any error.

You have done setting up SharePoint farm whose database server is a hosted Azure virtual machine running SQL Server 2014 SP 2.

Hosting Content database on Azure SQL Database

If hosting content databases on SQL Server virtual machine, I’d need a very powerful virtual machine and premium storage to optimize performance because all business data is stored in SharePoint content database. The configuration database is only to store farm configuration which is not an intensive-workload database. That comes to me an idea to test web application whose content database is stored in a pre-created blank database. (The one you created in Creating a blank database section)

There are a few things to note:

  • $contentDb: must be the exact name of the database you created
  • $dbServer: this is the connection string of your PaaS database
  • $dbCred: it is just an SQL account. You need to use T-SQL to create a login and add db_owner role for it.

It should not take long since the Azure SQL database is put on the same virtual network with SharePoint so it doesn’t have to route to Azure SQL database service endpoint.

You can run the following command to verify whether the target content database is Azure SQL database

Note that there is not any trick here.  Get-SPContentDatabase  is a SharePoint cmdlet and it is able to recognize Azure SQL database.

Now I’m going to create a new site collection under the newly created web application.

Finally, hosting SharePoint content database gets done. There is a key difference from the common setup is the database authentication method. Because Windows integrated authentication is not supported, the only mode support is SQL database authentication. Of course, this account to do the job is not maintained by Azure AD.


In this article, I just walked you through steps to create an Azure SQL logical server and a blank database. My purpose was to see whether SharePoint could connect to Azure SQL database with integrated authentication. Unfortunately,  the mode is not supported. This kind of old-fashioned should be deprecated in the future. In other words, the next SharePoint version should allow us to use identity cloud service for the initial setup. Next article, let’s go through more on SharePoint deployment scenario.

Go to Building a mixed cloud model for SharePoint – Part 3


  1. January 16, 2018 — 9:08 pm

    Awesome series of posts (and blog in general)

    I think you could go a bit further using Amazon RDS, at least provisioning the farm, since it supports “integrated” security (but not DB roles). Of course, that would mean using AWS, not azure 🙂

    i checked recently for a customer, and there is a full template with high availability available as a quickstart. i figure it’d be a 8-15 k$ / month quickstart, but it was an interesting read 🙂

    • Thuan Soldier
      January 16, 2018 — 9:59 pm

      Thank you for the information Emmanuel. I have zero experience with AWS but will try to grab some of my times looking into it. Running Microsoft workload on Microsoft Azure would have several hidden advantages. I believe the underlying Azure PaaS Framework is optimized to work best with Microsoft products such as SharePoint or SQL Server.

      The integrated authentication could be obsolete because it is still based on NTLM protocol. In a modern software we would love to have decoupled authN service to increase scalability, and to simplify management. I don’t advocate for integrated auth.

      I hope you find something helpful in my blog.

      Keep in touch!

      • January 16, 2018 — 10:38 pm

        hi Thuan,

        I already found many subjects of interests in your blog, which i discovered in 2013 with your LAB series 🙂

        I wouldn’t be overly optimistic on WIA disappearing from SPS soon, sadly. Too many hardcoded things, starting with the crawl component, which still expects a default NTLM zone in 2016. Also, SharePoint makes heavy use of service accounts and impersonation, strongly tied with SQL.

        That’s why i checked the AWS template solution, out of curiosity, and they solved the problem using *one* account (SP_Farm, shockingly). I’m totally working on Azure or hybrid, do not worry.

        Saying that, MS ditched (at last!) that awful FIM fork for user profile sync, so i can hope crawl will be refactored for 2019. i’m just thinking i’ll keep my BA Insight contacts until then 🙂

Leave a Reply

© 2018 The Soldier of Fortune.