Welcome - Sharing information with the community related to Microsoft SharePoint security, information protection and permissions. Topics will also cover identity federation, claims and software development. Articles will at times be technical and focussed at developers/architects. They will also be higher level and discuss concepts and customer use cases. Have a look around, share your thoughts and I do hope you find some helpful content.

Follow me on Twitter @AntonioMaio2

Friday, August 21, 2015

Initializing Azure VMs to Host SQL Server for SharePoint 2013

This is another post designed to help people with some basic steps involved in setting up Azure VMs for a SharePoint farm in a lab or test environment. As mentioned in a recent post, I often use Azure VMs to test scenarios related to SharePoint 2013. When standing up a brand new SharePoint environment, I might setup a new VM specifically to host my SQL database. When doing so, I'll often quickly create a VM using a SQL Server template in the Azure VM Gallery.

As part of this process, Azure will ask me to specify a network (which I already have configured in my Azure instance) and an administrative user account. I cannot seem to specify a domain account here since the new server will not yet be domain joined. The administrative user account I do specify is created as a local administrator in my new VM and given ownership over the SQL database which is deployed as part of the template.

Once created, my next step is to domain join this new VM to my domain. You can see basic steps for how to accomplish that in a previous post here: Domain Joining New Azure VMs.

At this point, you would think that I could just start installing my SharePoint 2013 Server on a separate VM (using service accounts), and as part of that installation process specify the administrative account I created for SQL as the SharePoint Database Access account (allowing my new SharePoint farm to connect to this newly setup SQL Server VM). But, there are a few settings you need to configure first related to SQL before you can start setting up SharePoint 2013.

After my SQL Server VM was created, and even after I domain joined it, the only account that I specified which currently has ownership of the database was the local administrator account that was created as part of the VM creation process. I cannot and should not use that account to setup SharePoint and connect to the database. I can login to the SQL Server VM as a domain account, but it would not have any administrative capabilities over the database, nor could it connect to the database from a separate server (like the one you're installing SharePoint on). There is no domain account yet that has that type of access to the database.

Configuring a Domain Account as SQL Sys Admin

Typically the first domain account that you provide access to SQL Server would not be used to install SharePoint. When configuring this domain account to access SQL Server, you often give it the sysadmin server role so that it can have ownership over all databases and be used to perform any operation in SQL Server, including configuring other accounts with appropriate permissions in SQL Server with which to install/configure SharePoint. This is done by doing the following:

Login to the SQL Server VM as the local administrator account that was specified when you created the VM.

Launch SQL Management Studio and connect as that local administrative account. If you look in the top node of the Object Explorer in this screen shot, you can see that I am logged in as ALMAIOSQL-1\Antonio.Maio which is the local administrator account.

In the Object Explorer, open the Security node, then right click on the Login node and select New Login...

In the Login - New window, click the Search... button to specify the domain account that you wish to use as SQL administrative account.

Specify the domain account, click Check Names, and click OK.

Pretty basic stuff so far!

In the Login-New window click the Securables page (top right corner of the page), click Search..., select the The server <SQL Server Name> option, and click OK.

Click the Server Roles page, check the sysadmin option. Click OK.

This account may now be used to create and grant appropriate permissions to other accounts which will be used to install and configure SharePoint 2013 on another server. Before you can do this, you'll need to logout of the VM, and login again as the domain account you just configured.

Allowing the Database Access Account to Connect to SQL Server

In order to install SharePoint 2013 and specify a Database Access Account, the following is a process that you'll need to go through to give a domain account access to the database.

Login to the SQL Server VM as the domain account which you gave sysadmin access to the database.

Launch SQL Management Studio and connect as that domain account.

In the Object Explorer, open the Security node, then right click on the Login node and select New Login...

In the Login - New window, click the Search... button to specify the domain account that you wish to use as SharePoint's Database Access Account.

Specify the domain account, click Check Names, and click OK.

This account may now be specified when running the SharePoint 2013 install process as the Database Access Account.

There are other service accounts that are also required when installing a SharePoint 2013 farm (setup account, farm account) which have their own requirements for permissions, privileges and being part of local administrator groups. Its important to understand these requirements when creating a new SharePoint farm and there are some great resources available from Microsoft on the specific requirements of each account: Account Permissions and Security Settings in SharePoint 2013.

No comments:

Post a Comment

About Me

Antonio Maio is an information security architect with over 25 years of experience in cyber security practices and systems, product management, software development and leadership. Antonio is currently a Senior Manager and Senior SharePoint Architect with Protiviti. He has been awarded a Microsoft Most Valuable Professional award for 5 consecutive years, from 2012 to 2016, specializing in Microsoft SharePoint Server, Office 365 and Office Services. His background includes implementing cryptography and PKI systems, information security technologies, and both information governance and cybersecurity best practices. His experience with Microsoft SharePoint and Office 365 extends over the last 10 years. When he’s not helping enterprise, military or government organizations solve security challenges, you can catch him speaking at conferences or contributing to the community through this blog. In his spare time, Antonio likes to oil paint, run, make wine, read and spend time with his family.