Question

kimdav111 on Tue, 08 May 2018 15:45:41


XXXXXXXXXXXXXXXXXXXXXXXX

Do we apply the below on the sql service account only? 
Recall we did not use ADSI, but do not recall where we granted full control to AD objects.  Can we provide full control to the object(s) (SQL servers and AG lisener to the SQL service account)?  Recall we added permission of read, write, create object at the OU level for the service accounts and full control at the AD object level of the lisener and cluster.  Why is this not enough because to me it appears that this grants the same permissions as the ADSI?  
Why do we need to delete all local services / network services from the above AD objects.  If we provide permission using ADSI or providing full control at the OU or full control at the Lisener/Cluster level?

XXXXXXXXXXXXXXXXXXXXX

https://cmatskas.com/fixing-error-cannot-generate-sspi-context-after-changing-sql-service-account/  
Log in to the server running your Active Directory service and execute the following steps:
Run Adsiedit.msc 
In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties. 
In the CN= AccountName Properties dialog box, click the Security tab. 
On the Security tab, click Advanced. 
In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row 
Click Edit, Open Permission Entry dialog box. 
Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below: 
Read servicePrincipalName 
Write servicePrincipalName
Click OK to apply all changes and exit the ADSI Edit snap-in
Finally, you need to restart the SQL Service(s) that use the account in question

dsk




Replies

Pirlo Zhang on Thu, 10 May 2018 05:57:17


Hi kimdav,

Would you like to configure SQL Server Always on Availability groups?

What's the error message you got?

Assume your accounts are incorrectly configured for AG:

a. If the partners run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration the database and is recommended.

b. If two server instances run as different accounts, the login each account must be created in master on the remote server instance, and that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

c. If SQL Server is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If your service accounts are using domain accounts in the same domain, you can choose to grant CONNECT access for each service account on all the replica locations or you can use certificates.

See: Troubleshoot Always On Availability Groups Configuration

Regards,

Pirlo Zhang