Can Sql Server Service Account Be A Non Domain User

This article is a 6^th article in the series for SQL Server E'er On Availability Groups. It covers the configuration of the group managed service account (gMSA) for SQL Services.

Introduction

DBA uses services accounts to run the various SQL Services. Usually, we should apply a divide service account for an individual server SQL Services.

• You should run SQL services having the least permissions
• You should use a circuitous password and store it in a secure place
• Its password should never expire
• You should besides change the password regularly, depending upon your organization's security policy

If you maintain a large inventory of SQL Servers, you might think it is a cumbersome task to change and maintain the passwords for these servers. In one case you alter the service account countersign using SQL Server Configuration Manager, it as well requires the restart of SQL Services. It might exist a challenging task besides to get downtime for highly transactional applications.

We tin can leverage Group Managed Service Accounts (gMSA) in these cases. Let'southward explore it in the subsequent department.

Prerequisites

1. You should follow the article series, Install SQL Server 2019 on Windows Server 2016 with SQL Server E'er On Availability Groups and configure the following

   • The virtual machine acts as a domain controller and active directory
   • Iii SQL nodes with SQL Server 2019 installed
   • You lot should configure SQL Server Ever On Availability Groups for these 3 nodes in synchronized way
   
   

2. Windows PowerShell with active directory module installed. We installed it every bit a function of an active directory configuration

An Overview of Managed Service Accounts

We have two kinds of managed service accounts in an agile directory configuration.

• Standalone Managed Service accounts(SMSA) provides the functionality of automatic password management. In simplified terms, users do not manage credentials for these users. It automatically changes the countersign and synchronizes with the services equally per the active directory policy. Nosotros tin can use a standalone managed service account for a unmarried server
• Grouping Managed Service accounts (gMSA) extend the functionality of SMSA. You lot can use gMSA for multiple servers. We ascertain an AD group and provide permissions for all required servers that tin can use the credentials of the specified gMSA

To summarize, you lot get the following benefits using gMSA as the service account for SQL Services.

• Automatic password management
• Administrators do not require to store the passwords in a password vault
• It uses a very complex password(120 characters), and it is not known to administrators equally well. It avoids the adventure of circulating the password unknowingly equally well
• Automatic SPN registration
• Yous can use them across multiple servers and services
• You lot can use gMSA on standalone servers or services that run on top of a failover cluster service such as Windows service, app pool, scheduled task

Let's commencement configurations of the Group Managed Service accounts (GMSA) for SQL Server Always On availability groups.

Configuration of gMSA for SQL Services

Nosotros can configure and use the gMSA service accounts for Windows Server 2012 or later. In this commodity, we will piece of work with Windows Server 2016.

Stride 1: Create a Security Grouping for gMSA

Take an RDP of the active directory server and Launch agile directory (AD) using DSA.MSC command.

Correct-click on the domain name and choose New -> Grouping. Specify a group name equally per your requirement or naming convention.

Click Ok, and it creates the Advert group. Open this group and enter a description. It helps y'all to identify the security group and its purpose efficiently.

Click on Members. In the members, add the failover cluster nodes. I have SQLNode1, SQLNode2, and SQLNode3 virtual machines configured in a SQL Server Ever On availability group.

Step 2: Configure key distribution service (KDS)

Group managed service accounts require a key distribution service (KDS) using the Advertisement PowerShell module. It uses an Add together-KdsRootkey PowerShell cmdlet. It takes 10 hours for full synchronization between all AD domain controllers.

For the demonstration purpose, you can apply either -EffectiveImmediately parameter or specify a past timestamp.

Open the Windows PowerShell on the active directory VM (in this case vditest3.mydemosql.com) and run the following command.

Add together - KdsRootKey - EffectiveTime ( ( Get - Date ) . AddHours ( - x ) )

It returns a GUID, every bit shown below.

You can also validate the key using the Go-KdsRootKey to verify that the KDS key exists in the active directory.

Y'all get the value, effective appointment, domain controller, and GUID.

Stride 3: Create a new grouping managed service account

In this footstep, nosotros create a new gMSA account using the New-ADServiceAccount PowerShell cmdlet.

It uses the following arguments.

• Name: Specify a gMSA service account proper noun
• DNSHostName: Enter the FQDN of the service business relationship. In my case, FQDN is gMSAsqlservice.mydemosql.com
• PrincipalsAllowedToRetrieveManagedPassword: Specify the Advertizing group proper noun we created in Step ane: Create a Security Group for gMSA

New - ADServiceAccount - name gMSAsqlservice - DNSHostName gMSAsqlservice . mydemosql . com - PrincipalsAllowedToRetrieveManagedPassword SQLServer

You must besides permit the gMSA account to register its service chief proper name ( SPN) for Kerberos authentication in SQL Server.

dsacls ( Get - ADServiceAccount - Identity gMSAsqlservice ) . DistinguishedName / G "SELF:RPWP;servicePrincipalName"

Information technology gives you the following output.

Yous can check the account properties such as encryption blazon and SAM account name. By default, it uses RC4, AES128, and AES256 encryptions. Yous can note the business relationship name uses the $ suffix in the business relationship proper noun.

Get - ADServiceAccount gmsasqlservice - Properties * | FL DNSHostName , KerberosEncryptionType , SamAccountName

Stride 4: Enable Advert Windows feature for the target servers

We demand to perform these steps in the target servers. Have RDP to the target server and enable the AD DS and AD LDS Tools in the Windows features.

Click Next and confirm the characteristic installation. In the description box, you tin note it installs the Agile Directory module for Windows PowerShell on the target server as well.

Click on Install to enable AD feature along with its dependencies on the target nodes.

You can perform this step on the remaining servers in the Windows failover cluster as well.

Pace 5: Install Group Managed Service business relationship on the target node for SQL Server Always On Availability Grouping

In one case the Ad PowerShell cmdlets are available on the target node, nosotros demand to install the gMSA service business relationship. It uses the Install-ADServiceAccount cmdlet.

Install - ADServiceAccount gMSAsqlservice

Information technology does not return any output. You can utilize some other cmdlet Examination-ADServiceAccount to verify the group managed service business relationship condition. It returns true if gMSA is valid and ready to use.

Test - ADServiceAccount gMSAsqlservice

You can check the last password reset for the managed service account using the Get-ADServiceAccount cmdlet.

Get - ADServiceAccount gMSAsqlservice - Property PasswordLastSet

Step 6: Configure gMSA to run the SQL Services

Now, we are prepare to apply the gMSA accounts in the SQL Services. Open the SQL Server Configuration Managing director and become to Services.

Now, search the gMSA account in the active directory service business relationship object. Y'all can specify the account name as [mydemosql\gmsasqlservice$] also. It does not requite you a countersign prompt.

Y'all might find that we do not have a password for this user. It is not required to configure the services running under grouping managed service account equally well.

Click Ok and restart SQL Services. You can meet SQL Service is running under the [mydemosql\gmsasqlservice$] security context.

Connect to SQL Server using SSMS and view the mistake logs.

• Verify the entry in SQL Server logs for the service business relationship

  

• Verify Service Primary Name(SPN) registration for SQL Server

It successfully registers the service principal name ( SPN) for the Kerberos authentication and logs an entry in the error log, as shown below.

• [MSSQLSvc/SQLNode1.MyDemoSQL.Com:INST1]
• [MSSQLSvc/SQLNode1.MyDemoSQL.Com:I433]

Similarly, change the service accounts in the SQLNode2 and SQLNode3 for SQL Server Always On Availability Group and start SQL services.

SELECT @ @ Servername AS server , status_desc , service_account FROM sys . dm_server_services WHERE status_desc = 'Running'

• Add together these accounts into all SQL instances and provides database mirroring endpoint to [MyDemoSQL\gMSsqlservices$] account. If the account does not have permissions for the endpoint, databases won't come across in the SQL Server Always on availability group, and you lot go disconnected status.

  Change Authorisation ON ENDPOINT :: mirroring_endpoint TO [ MyDemoSQL \ gMSsqlservice $ ] ;

You lot can hold for some fourth dimension, and you would see a healthy AG dashboard. In the below screenshot, we see that the [SQLShackDemo] database is in the synchronized country without whatever data loss.

Conclusion

In this commodity, nosotros explored Group Managed Service Accounts (gMSA) for SQL Server Always On Availability Groups. It automatically manages SQL Service accounts and changes them without restarting SQL Services. Information technology besides eliminates the risk of password hacking or misuse for connecting to SQL. You can also configure the Windows task scheduler using this gMSA business relationship.

Table of contents

A comprehensive guide to SQL Server Ever On Availability Groups on Windows Server 2016

Configure Domain Controller and Active Directory for SQL Server E'er On Availability Groups

Configure failover clusters, storage controllers and quorum configurations for SQL Server Ever On Availability Groups

Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups

Add together a new node into existing SQL Server Always On Availability Groups

Configure Managed Service Accounts for SQL Server E'er On Availability Groups

Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts

Database-level wellness detection in SQL Server Always On Availability Groups

Automatic Page Repair in SQL Server Ever On Availability Groups

Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups

An overview of distributed SQL Server E'er On Availability Groups

Deploy a distributed SQL Server Always On Availability Grouping

Monitor and failover a Distributed SQL Server E'er On Availability Group

Transparent Data Encryption for SQL Server Always On Availability Groups

Configure SQL Server replication for a database in SQL Server Always On Availability Groups

Configuring SQL Server replication for distribution databases in SQL Server Ever On Availability Groups

Explore Cross-database MSDTC for distributed transactions in SQL Server Ever On Availability Groups

Deploy MSDTC for distributed transactions in SQL Server Ever On Availability Groups

Restore an existing availability group database participating in SQL Server Ever On Availability Groups

Exploring AG dashboards for monitoring SQL Server E'er On Availability Groups

Fill-in compression in TDE enabled databases in SQL Server Always On Availability Groups

Impact of dropping a login in the active directory tied to SQL Server Ever On Availability Groups

Refresh SQL Server Always On Availability Group databases using DBATools PowerShell

Refresh SQL Server E'er On Availability Grouping databases using DBATools PowerShell

Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups

Suspend and Resume Data Motility in SQL Server Always On Availability Groups

Explore failover types in SQL Server Always On Availability Groups

Explore SQL Server PolyBase External Tables in SQL Server E'er On Availability Groups

SQL Server Always On Availability Groups for SQL Server Linux instances

Column-level SQL Server encryption with SQL Server Always On Availability Groups

Brand the near of secondary replicas in SQL Server E'er On Availability Groups

Utilize SQL Server patches or cumulative updates in SQL Server E'er On Availability Groups

Monitor SQL Server Always On Availability groups using extended events

The Hub and Spoke model of policy-based direction for SQL Server Always On Availability Groups

Custom policies for AG dashboards of SQL Server E'er On Availability Groups

Explore dynamic direction views for monitoring SQL Server Always On Availability Groups

Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability

Configure SQL Server Always On Availability Groups using Windows PowerShell scripts

Configure Integration Services Catalog Database SSISDB in SQL Server E'er On Availability Groups

Synchronize logins between Availability replicas in SQL Server Always On Availability Group

Session timeouts in SQL Server Ever On Availability Groups

Charter Timeouts and Health Checks in SQL Server Always On Availability Groups

• Author
• Contempo Posts

Howdy! I am Rajendra Gupta, Database Specialist and Builder, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related problems, and Functioning Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server customs, I accept been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2d Rank) at SQLShack and the MSSQLTIPS champions honor in 2020.

Personal Web log: https://www.dbblogger.com
I am e'er interested in new challenges and so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Can Sql Server Service Account Be A Non Domain User,

Source: https://www.sqlshack.com/configure-managed-service-accounts-for-sql-server-always-on-availability-groups/

Posted by: kittconew2000.blogspot.com

Share this post