16

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? No. Service accounts do not affect performance in any way. Its all about security ! From the SQL Server 2012 security best practice whitepaper (...


10

I quite often have to setup MS SQL Server and wondered if anyone can provide advice on configuring the accounts the services should run as. IMO this has been vaguely documented by Microsoft, while they point you in the right direction I have never been able to find any concrete examples. It's actually documented quite thoroughly: http://msdn....


8

The real problem with using one Service Account for all of your servers is: How secure do you want to be? If someone is able to hack that one account, then all 35 servers are exposed in one swoop. I much prefer at least a Service Account per server. And having several Service Accounts for different uses is also recommended for security. See: Configure ...


5

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials? No, SQL Server service accounts can be different. We have a 3 node cluster running AlwaysON and on all the servers SQL Server is running with a different account. e.g. If you have one server in NY and other in LD, then Active Directory Accounts to ...


5

Separate service accounts for each server and for each service will restrict any issues to just one server and just one service in the following cases: Service account getting compromised Service account getting locked out Service account password changed Service account getting disabled/deleted/renamed by mistake Imagine what would happen with a single ...


5

If you're performing native SQL Server backups, the service account running the SQL Server database engine must have access to the backup location. The Agent job can run under a proxy to fire off the job itself, but the engine still performs the backup so still needs access. The only way to have the backup write to disk as another user would be to use a ...


4

If you run a BACKUP query under a certain Windows/SQL Server account, it is actually the SQL Server Agent account which "does" the BACKUP and needs full control permissions to the UNC path. So you must be able to allow that account (or Everyone) have full control permissions to that path. To find out which account SQL Server Agent runs as, query sys....


4

BACKUP will access the UNC under an impersonation context. This is a 'double-hop' situation and requires Kerberos constrained delegation to be configured. This is already answered at https://stackoverflow.com/questions/18749224/restore-database-from-a-shared-folder


4

Do you have the option of creating a folder on your local computer and create a share on that and grant full authority to everyone and then back up to that share? - you didn't indicate how big the backup might be


4

Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. Step 3 is "Erase Phantom System Health Records". This contains a PowerShell script that tries to connect to OTHER instances on the machine, causing the login ...


4

There is no negative performance impact to using the same account on all your servers. If a change happens to that account, however, it will impact all your services using that account. You may want to consider different accounts for different environments (e.g. DEV, TEST, PROD).


4

Have a look at the fixed db roles in SQL server: In your first example (db_datareader and db_datawriter), the service account can only read and modify data in the tables. With these 2 roles, the service account cannot modify the tables or perform and DDL. Nor does it have EXECUTE permissions. In your second example, you've given all the permissions above ...


4

The SQL Agent uses whatever is assigned as the login on the Windows Service. To access Windows services and see, click the start menu and type 'Services'. Locate the SQL Agent and view the properties. If the account is the Local System, then it is actually using the credentials of the machine. You confirmed that already when you saw that the DOMAIN\...


4

So i am assuming its not causing any issues yet, but regardless i would like to know if its something i can ignore in my scenario, or fix. Looks like since everything seems to be configured properly (I can't see the domain account information [which is redacted as it should be :) ]) so I wouldn't worry about it. If this happens on multiple servers in the ...


3

SERVERNAME$ is a machine account assigned to the server in Active Directory. That account maps locally as NT AUTHORITY\System. You'll see in the SQL Server security there is typically an account named NT AUTHORITY\System listed - this is how the SQL Server Agent is getting access when it is configured to run as Local System. Microsoft Docs has a great set ...


3

The job is running as the same identity in both cases. This is a difference only in how the information is reported. NT SERVICE\SQLSERVERAGENT is a least-privilege local identity used for running SQL Agent jobs. Like other built-in local Windows identities this account can access network resources using the computer account. So from the local point-of-...


2

Create a domain user that has read/write privileges to both the network share as well as NTFS read/write permissions on the folder. In Configuration Manager, set the SQL Agent user to this domain user. SQL Agent is the account that will execute the backup as a task. The user does not have to be a domain admin, as long as it has the NTFS permissions it needs....


2

So just in case someone wants to know what caused this, It was a group policy! Some time ago, unbeknown to me, the domain controllers for the domain in question had been upgraded to Server 2012. Along with this came a whole bunch of Windows Server 2012 group policies. Additional policies had been added to one of the parent server OU's with a filter applied. ...


2

I would do it in this order: Change on Async Replica and restart SQL. Make sure everything is still synchronizing after restart Change Sync Replica and restart. Make sure everything is still synchronizing after restart Failover to Sync Replica Change on former Primary (Current Sync Replica), and restart. Make sure everything is still ...


2

I wouldn't directly say it is insecure to give dbo permissions to that account but it is definitely more secure to have the application run under an account only it can use and apply permissions accordingly.


2

If you have installed the Active Directory PowerShell components, and the account under which you are running the session has sufficient permission to perform the action, then it is actually pretty simple: New-ADUser -Name "Jo Bloggs" -GivenName Jo -Surname Bloggs` -SamAccountName jbloggs -UserPrincipalName jbloggs@corp.contoso.com ` -AccountPassword (Read-...


2

If they are Windows domain accounts/groups, you shouldn't have to worry about it as they should be identical on both nodes. The orphaned user issue and SID syncing across both nodes is only applicable to SQL authorization logins. With SQL authentication, you will have to sync the SIDs as, after a failover, the SIDs wont line up between the server level SID ...


2

I would suggest that hackers love "uniform and consistent configuration on all servers", especially when it comes to security because if they can crack open one server, they possibly have access to other servers. Best practices (https://support.microsoft.com/en-us/kb/2160720) generally suggest a different service account for EACH Sql Service on a given ...


2

The information in Using a gMSA with SQL Server by Wayne Sheffield worked for me with the service issue. The pitfalls of using a gMSA with SQL Server As with almost all things, there is inevitably something that doesn’t work correctly. One thing that I found is that when the server is rebooted, the SQL Server services are not restarted. And I’m not ...


1

All nodes in a cluster should be using the same service account for SQL Server. This is true for both an FCI instance and an Availability Group setup. For security sake, consider using Group Managed Service Accounts.https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/ This will allow you to have a ...


1

SQL--> Powershell script i got from this article. Maybe something like this? http://hubpages.com/technology/Using-xp_cmdshell-to-Query-the-Windows-File-System. I ran this on my desktop's SSMS using a Registered Server connection to a server in my network. **Originally made for IPCONFIG testing, but you can just adapt it for net start. --Enable SQL->...


1

If you have Kerberos in the mix you may run into SPN issues. – Jeff A Depending on your domain functional level (need to be 2012 I believe), you could use Group managed service accounts – Bob Klimes Any manually created SPNs will need to be deleted and recreated. Any SPNs automatically created by the service on startup will be removed on shutdown, so that ...


1

As part of the troubleshooting I was doing to try and address the issue I ran Process Monitor while I was trying to start the service. I tracked as event that also had a result of "Access Denied" which was the service trying to read files inside the directory where reporting services was installed an running from. In my case it was: "C:\Program Files\...


1

If the same username and password is to be used on all servers, you will need to make that change on all machines, or rather the replication agent jobs. If you change the password but miss one of the subscribers, replication will not break. However, it will fail with login errors when the subscribers attempt to connect to the publisher and the password hasn'...


1

Machine accounts such as LocalService cannot be trusted over the network. There is essentially no way to authenticate the remote machine without domain authentication of some kind. Use SQL Server authentication; that's what it is meant for. Alternately, you could provide a setup procedure that allows your clients to provide a domain account for each ...


Only top voted, non community-wiki answers of a minimum length are eligible