May 12, 2022 at 1:28 pm
Short version: SQL Agent will not start unless the domain service account is runs under is part of the local admins group on the server. SQL Server, running under same account, starts and runs fine.
Extended version:
This is a brand new installation of SQL 2014, on a Windows 2019 Datacenter VM. The installation popped up and error message at the end with: "Attempted to perform an unauthorized operation."
Ignoring that error and completing the installation, SQL agent would not start, even though SQL Server runs just fine, both using the same domain account, entered during the installation. SQL Agent, upon startup, would give me an "Access denied" error, with no more useful information in the message or the event logs. I reinstall SQL a couple of times, thinking there was something corrupt, as I have not run into this before. I also tried, on a previous installation, to change the agent account to a built-in account and back to the domain account using SQL Config Manager, but that did not help either. I have been running the install As Administrator after the first failed install, even though I have never had to do this in the past.
Finally, with the latest install, when that error showed up I added the domain account to the local admins group and clicked Retry. The installation completed successfully and SQL Agent was running.
Now, if I remove the service account from the local Admins group SQL agent will not start. I have never seen this behavior before and the internet has not been much help with info for this situation. I know that SQL service accounts do not have to be in the Admins group on a server, so I can't figure out why this is happening here.
Anyone have any ideas on this?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 13, 2022 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 16, 2022 at 2:48 pm
Short version: SQL Agent will not start unless the domain service account is runs under is part of the local admins group on the server. SQL Server, running under same account, starts and runs fine.
Extended version:
This is a brand new installation of SQL 2014, on a Windows 2019 Datacenter VM. The installation popped up and error message at the end with: "Attempted to perform an unauthorized operation."
Ignoring that error and completing the installation, SQL agent would not start, even though SQL Server runs just fine, both using the same domain account, entered during the installation. SQL Agent, upon startup, would give me an "Access denied" error, with no more useful information in the message or the event logs. I reinstall SQL a couple of times, thinking there was something corrupt, as I have not run into this before. I also tried, on a previous installation, to change the agent account to a built-in account and back to the domain account using SQL Config Manager, but that did not help either. I have been running the install As Administrator after the first failed install, even though I have never had to do this in the past.
Finally, with the latest install, when that error showed up I added the domain account to the local admins group and clicked Retry. The installation completed successfully and SQL Agent was running.
Now, if I remove the service account from the local Admins group SQL agent will not start. I have never seen this behavior before and the internet has not been much help with info for this situation. I know that SQL service accounts do not have to be in the Admins group on a server, so I can't figure out why this is happening here.
Anyone have any ideas on this?
Thanks, Chris
Grant the service account full control on the drives that contain the SQL database files. In my case, that's D, L, H, and T
Grant the service account full control over the folders where the binaries are kept, in my case that's C:\Program Files\Microsoft SQL Server and C:\Program Files (x86)\Microsoft SQL Server
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 16, 2022 at 7:23 pm
Nope, that didn't help. And the error remains an ambiuous "Access is Denied".
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 16, 2022 at 7:31 pm
Grant the service account full control on the drives that contain the SQL database files. In my case, that's D, L, H, and T
Grant the service account full control over the folders where the binaries are kept, in my case that's C:\Program Files\Microsoft SQL Server and C:\Program Files (x86)\Microsoft SQL Server
You don't actually need to grant the service account any permissions. The virtual accounts should have been assigned the rights to the folders it needs during the installation. If that hasn't occurred, then you need to add that account with full permissions to all of the folders needed.
It may be better to change the service account to a different account - and back to your service account using configuration manager. That should make sure all permissions are set appropriately for the per-service SIDs and set the appropriate ACL's.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 16, 2022 at 7:45 pm
Nope, that was one of the first things I tried. I just changed it and changed it back again, just for giggles, and still didn't help.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 16, 2022 at 7:56 pm
Nope, that was one of the first things I tried. I just changed it and changed it back again, just for giggles, and still didn't help.
Thanks, Chris
Have you verified the service SID's have the correct permissions? NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, etc...
Those are the accounts that actually need the permissions - and if those don't have permissions then you would have issues. The domain service account doesn't actually need any permissions directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 17, 2022 at 11:18 am
Jeffrey, I don't believe I have ever done that, so I don't know how.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 17, 2022 at 1:46 pm
Michael L John wrote:Grant the service account full control on the drives that contain the SQL database files. In my case, that's D, L, H, and T
Grant the service account full control over the folders where the binaries are kept, in my case that's C:\Program Files\Microsoft SQL Server and C:\Program Files (x86)\Microsoft SQL Server
You don't actually need to grant the service account any permissions. The virtual accounts should have been assigned the rights to the folders it needs during the installation. If that hasn't occurred, then you need to add that account with full permissions to all of the folders needed.
It may be better to change the service account to a different account - and back to your service account using configuration manager. That should make sure all permissions are set appropriately for the per-service SIDs and set the appropriate ACL's.
Correct. MOST of the time.
But while the data, log, tempdb, and backup drives may be allocated when the server is built, and the new installation asks for those locations, more often than not the perms do not get granted on the folders and drives.
We move the system DB's after installation to the data and log drives, and if we have not granted those perms, SQL fails to start.
In this case, the fact that SQL starts, and not the agent, indicates that there is a perms issue on some part of the server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 17, 2022 at 2:34 pm
Agreed. Now if it would just give me an error with a clue in it....
I have even tried running ProcMon (sysinternals), and filtering on SQLAgent.exe and the login account, but no results. Then I tried filtering on the login account and everything except the SQLServer.exe process, and still nothing. I was hoping I would see what the account was trying to access and getting a denial on.
Open to all ideas at this point.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 17, 2022 at 9:04 pm
Jeffrey Williams wrote:Michael L John wrote:Grant the service account full control on the drives that contain the SQL database files. In my case, that's D, L, H, and T
Grant the service account full control over the folders where the binaries are kept, in my case that's C:\Program Files\Microsoft SQL Server and C:\Program Files (x86)\Microsoft SQL Server
You don't actually need to grant the service account any permissions. The virtual accounts should have been assigned the rights to the folders it needs during the installation. If that hasn't occurred, then you need to add that account with full permissions to all of the folders needed.
It may be better to change the service account to a different account - and back to your service account using configuration manager. That should make sure all permissions are set appropriately for the per-service SIDs and set the appropriate ACL's.
Correct. MOST of the time.
But while the data, log, tempdb, and backup drives may be allocated when the server is built, and the new installation asks for those locations, more often than not the perms do not get granted on the folders and drives.
We move the system DB's after installation to the data and log drives, and if we have not granted those perms, SQL fails to start.
In this case, the fact that SQL starts, and not the agent, indicates that there is a perms issue on some part of the server.
I am not sure how you are performing your installations - such that you need to move system database files and log files. There really is no reason to move them and can in some situations prevent you from utilizing snapshot techniques for backups. If you put the master mdf file on the same volume as the user databases - and attempt to freeze/thaw that volume to take a SAN level snapshot - SQL Server will have issues because the master database would also be frozen.
I do not recommend moving any of those files - ideally you should create a separate drive just for the system databases.
Again, for permissions - you should not be granting access to the domain account. If you need to manually add permissions to a folder it should be added using the per-service SID. For the agent in a default instance that would be NT SERVICE\SQLSERVERAGENT. Those virtual accounts are created during the installation of SQL Server - and can be added in the security dialog by changing to the local system and typing in NT SERVICE\SQLSERVERAGENT.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2022 at 1:48 pm
Michael L John wrote:Jeffrey Williams wrote:Michael L John wrote:Grant the service account full control on the drives that contain the SQL database files. In my case, that's D, L, H, and T
Grant the service account full control over the folders where the binaries are kept, in my case that's C:\Program Files\Microsoft SQL Server and C:\Program Files (x86)\Microsoft SQL Server
You don't actually need to grant the service account any permissions. The virtual accounts should have been assigned the rights to the folders it needs during the installation. If that hasn't occurred, then you need to add that account with full permissions to all of the folders needed.
It may be better to change the service account to a different account - and back to your service account using configuration manager. That should make sure all permissions are set appropriately for the per-service SIDs and set the appropriate ACL's.
Correct. MOST of the time.
But while the data, log, tempdb, and backup drives may be allocated when the server is built, and the new installation asks for those locations, more often than not the perms do not get granted on the folders and drives.
We move the system DB's after installation to the data and log drives, and if we have not granted those perms, SQL fails to start.
In this case, the fact that SQL starts, and not the agent, indicates that there is a perms issue on some part of the server.
I am not sure how you are performing your installations - such that you need to move system database files and log files. There really is no reason to move them and can in some situations prevent you from utilizing snapshot techniques for backups. If you put the master mdf file on the same volume as the user databases - and attempt to freeze/thaw that volume to take a SAN level snapshot - SQL Server will have issues because the master database would also be frozen.
I do not recommend moving any of those files - ideally you should create a separate drive just for the system databases.
Again, for permissions - you should not be granting access to the domain account. If you need to manually add permissions to a folder it should be added using the per-service SID. For the agent in a default instance that would be NT SERVICE\SQLSERVERAGENT. Those virtual accounts are created during the installation of SQL Server - and can be added in the security dialog by changing to the local system and typing in NT SERVICE\SQLSERVERAGENT.
You are assuming that my environment is exactly like yours. We do not have a SAN, so we would never take SAN snapshots. My on-prem infrastructure is hyperconverged with Symplivity. Additionally, I haven't run an installation of SQL server since SQL 2019 came out. We have a template created in VMWare that gets leveraged when we need to spin up a new instance. Configuring different drives for the various parts and pieces allows us to configure the different SCSI adaptors in VMWare, as well as adjust various things that for performance.
So, instead of saying we are wrong, take a minute to read between the lines and attempt to understand the environment. I made the recommendations on the permissions because I have seen this behavior. I have had to do these things, although it's been a while, to get SQL to run.
My installation checklist is based upon this blog post by Jonathan Kehayias. https://www.sqlskills.com/blogs/jonathan/sql-server-installation-checklist/
Some of the things in that post are no longer required for newer versions of Windows, but it is still a good starting point.
Now, to attempt to solve the issue.
You have tried different AD accounts. Like Jeffery said, when you switch accounts the permissions should have been granted.
Throwing a few guesses out there: Are SQL Server and SQL Agent running under the same account? If so, that makes no sense. Have there been things specifically denied to the local windows groups? Maybe through group policy? Have rights been removed to the system databases, specifically MSDB?
Have you taken a different service account from another server that works properly, and attempted to use that? And, the same thing in reverse. Take this failing service account, and use it on a different server. If the same things occur when you switch the accounts, then you can assume it's something in the server configuration. Otherwise, it's probably at the AD level.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 18, 2022 at 5:35 pm
@michael-2 - I was not assuming anything. I provided one example of where moving system databases to the same disk/volume as user databases could cause issues. The point I was making is that moving system databases after installation can lead to other issues.
You also stated this:
But while the data, log, tempdb, and backup drives may be allocated when the server is built, and the new installation asks for those locations, more often than not the perms do not get granted on the folders and drives.
I have not found that permissions do not get granted on the folders and drives - and I have performed many SQL Server installations. What I have found is that permissions are not created on locations not included during the installation - and that is often the case when post-installation requires moving database files to those other locations.
Now I do agree that the issue here is certainly permissions. But I disagree that permissions need to be set for the service account - since the service account is not actually utilized for those permissions. Permissions on the folders and files are granted to the per-service SIDs and not the domain account. Therefore, granting any permissions - including adding the service account as a local administrator - won't necessarily fix those permission issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2022 at 11:11 am
I tried the account on SQL Agent on a different server, one of the three we set up at this time, and it works fine on the other server.
How would I check permissions for the built-in SQL accounts?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 19, 2022 at 2:31 pm
I tried the account on SQL Agent on a different server, one of the three we set up at this time, and it works fine on the other server.
How would I check permissions for the built-in SQL accounts?
Thanks, Chris
The same way you check permissions for domain accounts. Right-click on the folder, select security - and validate the accounts permissions to that folder. The permissions you should see are for the account MSSQLSERVER and/or SQLSERVERAGENT (for a default instance - it will be different for a named instance, but recognizable).
Compare what you see on the system that is working - with the system that is not working. You may need to work down the folder structure and validate permissions at every level - but hopefully not.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply