I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for the backup job, but the startup account of the database engine and the SQL Server Agent are local accounts. Using proxies did not help me with the situation so I am now thinking of providing the service account credentials in the SQL Server Configuration Manager. What I want to know is how do I proceed? This is what I have planned. Please let me know if I have missed something in the flow 1. Change service account on the Node2 (secondary) - both **SQL Server (_InstanceName_)** (database engine) and **SQL Server Agent (_InstanceName_)** (job engine) 2. Pause synchronization 3. Restart the **SQL Server (_InstanceName_)** service and the **SQL Server Agent (_InstanceName_)** 4. Failover the **AOAG** to the secondary, then change the account details of the primary (now the seconday) 5. repeat step 2. and 3. 6. Fail back to the primary again Please let me know if I am missing any step here Thanks!

Is there any scenario where it is the best choice to make the Oracle installation/service account (oracle) an LDAP/AD account versus a local-only account? More generally, is creating the Oracle account locally the best practice? I don't see this stated explicitly in their installation documentation.

I'm currently assessing a setup of Data warehouse implementation in our environment, based on the requirements: 1. A functional/service ID to be setup to access multiple DB servers of other applications in the company for replication purpose. 2. Password of the service ID cannot be changed and won't expiry Based on some researches, I learned that Windows authentications are always recommended as it is more secure (here and here ). Further exploring on windows authentication, I noticed the guides always linked it to Kerberos authentication, which involve domain controller where the an AD account is being used (link ). It make the ID management easier but due to it is a functional ID, I'm not sure if this is the best approach. There is also an article here mentioned to use a local account instead of domain account when possible. Question: 1. Since it is a service ID, and it will connects to many applications, shall I really avoid domain account? My concern is that the password isn't expiring and it has accesses to most of the DB, the risk is higher if this single account is compromised. 2. Does setting up login using local Windows account in respective DB server provide the same advantages of domain account (e.g. use Kerberos protocol)? 3. Is creating multiple local Windows account the recommended approach based on above requirements? Or if there is any resources regarding this can be shared for me to further study on my own. I hope my questions are subjective but not opinion based/open-ended, and I appreciate for any input and sharing.

I am trying to update service account password for windows service(example: SQL service) on remote server but keeps getting following error. I am using **PowerShell version 7.0** which supports -Credential as parameter for Set-Service Error: >Set-Service: A parameter cannot be found that matches parameter name 'Credential'. Script:

$servers = Get-Content "C:\folder\servers.txt"

$ServiceName = read-host "Enter Service Name Here"

$Cred = Get-Credential
$Username = $Cred.username
$Password = $Cred.GetNetworkCredential().password

foreach ($server in $servers){
Invoke-Command -ComputerName $server -ScriptBlock {
get-service $using:ServiceName | stop-service 
$svc = Set-Service -Name $using:ServiceName -Credential $using:Cred
if ($svc)
{$OUT = "$Using:server Service Account Password Change Succeed"
$OUT}
else {$OUT = "$Using:server Service Account Password Change Failed"
$OUT}
Start-Sleep -Seconds 5
get-service $using:ServiceName | Start-service
}}

My SQL server agent service account is running under the following domain user account: mydomain\svc_svr1_sql_agent I have a ssis package that must be able to read/write files in a folder. The ssis package is invoked by a SQL agent job. The execution context (the execute as drop-down) is the SQL server agent service account (I am not using the proxy/credentials approach). Should I configure the folder permissions for agent service account mydomain\svc_svr1_sql_agent or the agent virtual account NT SERVICE\SQLSERVERAGENT or both? The reason for asking this question is that, based on https://dba.stackexchange.com/questions/331488/what-are-the-post-steps-after-changing-sql-engine-and-agent-accounts/335928#335928 , the SQL server engine account accesses the SQL data and SQL log files, etc using the NT SERVICE\MSSQLSERVER account irrespective of the SQL server engine service account configuration. So I would like to know how if this concept also applies to the SQL agent.

I am in the process of converting all of our user-based service accounts on all MSSQL servers to gMSA and I have used PowerShell to script most of the required changes. I have hit a rather large roadblock when it comes to changing the SQL services to running under the new gMSA as the method I wrote will not preserve dependencies:

# Define the current account and the new gMSA
$currentAccount = "DOMAIN\\CurrentAccount"
$newAccount = "DOMAIN\\NewgMSA$"

# Get all services running under the current account
$services = Get-WmiObject -Class Win32_Service -Filter "StartName='$currentAccount'"

# Loop through each service
foreach ($service in $services) {
    # Stop the service
    $service.StopService() | Out-Null

    # Change the service account to the new gMSA
    $service.Change($null, $null, $null, $null, $null, $null, $newAccount, $null, $null, $null, $null) | Out-Null

    # Start the service
    $service.StartService() | Out-Null
}

Is there any way to do this programmatically or will I have to do this using Sql Server Configuration Manager? I have seen similar questions asked in the past but I was hoping that it might now be possible. I would appreciate any help I can get even if it is to tell me I have to do the manual approach. Thank you.

In a default SQL installation the engine account is NT Service\MSSQLSERVER, and agent account is NT Service\SQLSERVERAGENT In SQL security I can see them both listed as logins with sysadmin role. These are also having the necessary security permissions on the various folders automatically. If I change the engine and agent account via the config manager to domain or local accounts (like mydomain\sqlengine, mydomain\sqlagent), then: 1. Do I need to create these accounts under SQL security and assign sysadmin role to them? 2. Are there any other post steps (like any other permissions assignment - like following: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver16#Windows - or is this implicitly applied via the virtual account?)? 3. Can I delete the above 2 NT Service accounts from my SQL server logins? The reason for asking is that - I know that behind the scenes SQL engine makes use of the service sid NT SERVICE\MSSQLSERVER account, and agent makes use of NT SERVICE\SQLSERVERAGENT, for the various folder permissions (data/log/etcetra), and windows privileges and rights like Log in as a service, Replace a process level token, etcetra. But does it still use this virtual account to access the folders/files even after changing the SQL server engine service account to a domain user account? **When I changed the SQL server service account via the configuration manager to a domain user account, it made no automatic changes to the data or log folder or file permissions. The folder/file security popup had always been showing MSSQLSERVER (not the domain user account) and the SQL server works perfectly fine.** Does this mean SQL server engine uses NT SERVICE\MSSQLSERVER even though the SQL engine service account is changed to a domain user account?

I am trying to install SSRS on a new server to report on our new database server and want to use a MSA account on our domain (server is on our domain) but I keep getting errors when trying to start SSRS as the first step to installation. This is the error: >System.InvalidOperationException: Cannot start service SQLServerReportingServices on computer 'MYSSRSSERVERNAME-NOTREALNAME'. ---> System.ComponentModel.Win32Exception: The service did not start due to a logon failure --- End of inner exception stack trace ---\ at System.ServiceProcess.ServiceController.Start(String[] args)\ at ReportServicesConfigUI.Panels.ConfigurationPanelWithErrors.StartOrStopServiceTask(Boolean start, String serviceName) I have tried a domain account and that works, so it has something to do with the MSA account. Event Viewer states the failure reason as Unknown user name or bad password. I never set a password for the MSA account as I used MSA GUI to set the password and it never prompted me to create one. The MSA account has logon on service permissions and is a domain user. I have added the account to local administrators groups to test but that still didn't work. Anyway to resolve this?

In the event of a DBA leaving an organization, what options can the surviving team members pursue to change passwords for service accounts portfolio-wide? While using Configuration Manager appears to be the de-facto method, can PowerShell or another scripting or batch language be used to reset them in bulk? I figure if you're changing the login name for the service accounts, Configuration Manager is essential as registry permissions and the like need to be propagated. For just a password change to the existing account however, is a scriptable method OK as long as it's performed during approved maintenance windows for the service restarts?

I'd like to follow best practices. However, is it overkill to create a new service account (with minimal access and no special rights) and then use that account to install SQL Server? Are there downsides if I do the installation under my administrator account? Side note: I already have separate service accounts that will run SQL Server engine and SQL Server Agent service.

I'm trying to establish the best practice standards for configuring service account permissions in Group Policy. Based on Microsoft documentation, SQL Server setup requests permissions for the per-service SIDs or local Windows groups used by SQL Server components. E.g. SQL Server Database Engine: **************************** The Default instance: NT SERVICE\MSSQLSERVER.Named instance: NT Service\MSSQL$ is granted the permissions below during SQL Server setup. The local group policy permissions are visible under user rights assignment. · Log on as a service (SeServiceLogonRight) ·Replace a process-level token (SeAssignPrimaryTokenPrivilege) · Bypass traverse checking (SeChangeNotifyPrivilege) ·Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) SQL Server Agent ***************** Default instance: NT Service\SQLSERVERAGENT. Named instance: NT Named instance: NT Service\SQLAGENT$.) is granted the following permissions during setup. ·Log on as a service (SeServiceLogonRight) · Replace a process-level token (SeAssignPrimaryTokenPrivilege) ·Bypass traverse checking (SeChangeNotifyPrivilege) ·Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) I also noticed that the SQL Service and Agent start-up accounts are also automatically granted “Log on as a service” permissions during setup. Question ********** Is anyone using domain level group policy to apply the above permissions to the service start-up accounts-(e.g. gMSA’s) + per service SID? Trying to determine if there is any benefit of using domain group policy to enforce these permissions on SQL Server VM’s rather than relying on the local group policy permissions assigned by SQL Server during setup ?

I am having a strange issue and am hoping you guys might be able to help! Problem: I have a 2 node SQL Server 2012 Availability Group Cluster utilising a FSW. Both nodes are using the same DBEngine Service account (as is normal in these setups). There are currently 3 Availability Groups on this cluster, and it's been working fine for quite some time. Today I restarted the passive node DBEngine account (as I was adding an SSL Certificate for a new application, new availability group). When the node came back up, it was no longer synchronising with node 1. The state of the replica was disconnected, and I could see lots of login failures on Node 1 (active node) SQL Logs. I found that the DBEngine service account had locked. I had it unlocked, but it soon locked again. I removed the SSL Certificate and restarted the passive node again (so it was in the same state I started with) and it started up fine, but within a minute, the service account had locked out again. Steps I tried: - created a new service account to rule out the account being used elsewhere, started both nodes under the new account.... account locked out when node 2 started - unlocked the account, stopped node 2. restarted node 1. Account fine... waited.. account still fine. Started node 2 service... account locked out. - recreated mirroring endpoints on both nodes and reapplied connect permissions to the dbengine service account. - this didn't fix it. - restarted both VM's - removed the node 2 replica from the availability group, removed all databases (from node 2) and dropped the mirroring endpoint on node 2. restarted node 2 service. - at this point both nodes were happily running under the same service account. - tried re-adding node 2 as a replica using the wizard. It added it, backed up the database, restored to node 2, and got to the very last step where it connects it, and the password locked out again! Has anyone got any ideas? Any input would be greatly received! Sam

Did somebody happen to make a GRANT CONNECT ON ENDPOINT:: operation to a group managed service account work? Personally, I can't. I am always facing the so-called self-explaining exception: Msg 15151, Level 16, State 1, Line 1 Cannot find the login 'DOMAIN\GMSA-ACCOUNT', because it does not exist or you do not have permission. the login I use to issue this command is sysadmin-role, so permission shall not be a problem the DOMAIN\GMSA-ACCOUNT account is actually starting the SQL Server engine service, so I guess its existence has been at least validated As a consequence, I can't make my service broker communication work with gMSA account like it previously was, with a regular service account. Does somebody have an answer/tip? Thanks in advance. ---------- # Edit Happens that I have mistakenly been asking this question on StackOverflow. Answer from David Browne to the original thread was: > Try again after a while, and try granting to a security group > containing the GMSA Thanks David, I must check this workaround.

When installing the SQL server, when asked for the sql service account, I have entered a domain user and its password. In future, when the password of the domain account will change, then will I have to open the configuration manager to enter that new password or if this is automatic then how does SQL server handle this process?

I have to migrate 8 SQL Server instances to a new SQL Server 2019 AlwaysON cluster. Each instances are going to be replicated to a passive secondary node. We globally want to use gMSA instead of classicals domain accounts. I cannot find the best practises related to this : * Should I use the same gMSA for all sql services on all nodes ? * Should I use a dedicated gMSA for each instance ? (primary and secondary node). * If yes, Can I install more than one gMSA service on each node ?( Install-ADServiceAccount gMSAsqlservice) Thank you

I am looking for guidance on Microsoft recommended best practice regarding setting the SPN for the service account that runs the SQL Server service (MSSSQLSERVER) When setting up a new server (or changing the service account on an existing server) should I always explicitly set the SPN (even if the server has no linked servers defined) or should it only be set in cases where you experience issues such as a double hop authentication issues when using a linked server? I want to set the SPN for every service account that runs SQL and our security/AD person only wants to set it for those servers where a linked server is defined. Is there a downside or security risk in setting SPN if you don't have linked servers. This question applies to SQL 2012, 2014 and 2016. I have found a few links that seem to suggest that setting the SPN explicitly should be the best practice so that Kerberos authentication (SQL's first choice) can be used rather than having to fall back to Windows NT Challenge/Response (NTLM) authentication Some articles that seem to suggest setting it should always be done - FAQS around Kerberos and SQL - Security Considerations for a SQL Server Installation - Resolving Connectivity Errors - SQL Server Kerberos and SPN Quick Reference

I was recently poking around our SQL Server (2014), and noticed the SQL Service account uses the LOCAL SYSTEM account. Are there any issues with using this account - Pros/Cons? Should we be using another account? Users do often execute OPENROWSET and XP_CMDSHELL commands. Not sure if that if lower-permissioned accounts work with advanced functions. Thanks!

I want to set up a server while multiple users could login at the same time, as long as they are not the "same user". How could I set it up so when the user with the account username "Bob" login again, whether it's from the same computer or another, the old connection will be terminated? Thanks for your help. Much appreciated! **Edit:** "Bob" is a Database user account, the "User ID" in the "Connection String", I'm sorry I don't really know what it is called.

In Microsoft's SQL Server [documentation](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Serv_Perm) on Windows service account configuration, the decision to use either a VA (virtual account) or a MSA (managed service account) hinges on whether > resources external to the SQL Server computer are needed What exactly does this phrase mean here? I'm seeking an explanation that makes sense to an 'accidental DBA' with minimal experience of SQL Server, or Windows Server, configuration. What counts as a 'resource' here, and what kinds of 'need' are relevant? Context: I have a fresh SQL Server 2019 VM on Azure, which was configured with VAs out-of-the-box, and I'm trying to decide whether we need to switch to using MSAs. I have found multiple other questions concerning this same documentation page and/or the same basic decision between VAs or MSAs (or regular AD Accounts) - but none really explain this specific phrase in a way that helps me apply it to my particular scenario. Which is essentially a data warehouse use-case: data will be coming into this SQL instance from external sources, but that'll be managed by a third-party DW automation application running SSIS scripts, not directly by the SQL engine. (This application has its own AD service accounts.) Whilst I've explained my specific scenario here to try to clarify the question, I'm keen for a generic answer so anyone with any SQL Server use case can evaluate this "resources external to the SQL Server" phrase for their needs. Specific answers for my use case are also welcome.

I am looking at the Configure Windows Service Accounts and Permissions doc (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15) for SQL Server 2019 and it states that the SQL Service Account needs to have the **Adjust memory quotas for a process permission.** Can anyone confirm why this is required and what it's function is within the SQL Engine. In my Dev environment, I do not have it enabled and all seems to be functioning correctly.