January 6, 2023 at 5:27 pm
Fellow SQL people,
I posted this in the 2019 bard, but as it relates to our environment, it's really about SQL 2014 and up.
We all know what that policy does, but I have come to ask about it anyway. We have a group of sysadmins who refuse to allow us to set SQL service accounts (AD accounts) with that policy and point to the following MS doc as their proof of being the bearer of all things SQL:
while Ozar's write ups and inumerous other sources do it as per MS's doc: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16
So my questions for you are:
We setup all of our instances at install time to have the servcie account with the policy, not the service SID, but they are overriding the policy with a GPO and we now have idientified multiple issues being caused by the override.
The reason I'm interested in well known names in the industry is because our group is made up of 4 DBAs all of which have at least 20 years of production dba experience and aparently that does not count for anything, so maybe authors, conference speakers, even anyone who works or worked at MS and knows about this would help us out.
To be clear, if setting it up for the service SID works just as well, I'm ok with setting that up. But I don't want to do that until we're certain it works exactly the same way as setting it up with the service account, because we want to make sure we know that what we're getting out of it is what we know we should get from it and nothing weird.
Thank you in advance. And happy new year!
January 6, 2023 at 10:25 pm
I am not sure why you are questioning using the service SID in that rights assignment. The installation media from 2016 and up now includes the ability to set that up during installation - and the installation adds the service SID and not the service account.
By adding the service SID - you can easily change the service account without having to update the local policies.
And just for an added bonus, the recommendation now is to use a managed service account (or gMSA) instead of a domain service account. Using the service SID for the rights assignment means you don't have to try and add the managed service account to a local policy.
You should make sure you are adding the per-service SID to the policies.
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
January 7, 2023 at 12:41 am
I missed that bit on the original post.
We've automated our SQL installs. Since 2012, the scripts have added the service account to that local policy (and a few others too) in the servers.
As databasees have become larger and larger, some import automated QA restores have not been finishing on time and cutting into morning processes. This too turned out to be due to file initialization. We were very puzzled why that happened.
After a lot of looking around, we finally figured out the local policy on the servers are being overriden with a domain policy.
The arguments started when it was explained they were overriding something we're supposed to have on. Aparently they have it setup in a way that is going to take cosiderable effort to make it work. I don't know how it is setup, honestly I don't care, I just need the policy to be setup so it works for the SQL Servers.
Instead, they want us to test with adding the service SID to the local server's administrators group. Which works for a test, yes, but is not the solution for the obvious security issues. Meanwhile, adding the sid/account to the local policy does not work because of the AD policy.
So the first thing we need to iron out is account vs sid. They keep pointing to the doc from MS about file access even though all books we keep on pointing to the MS doc which tells you to use the account (linked above) and other references like Ozar's page on the subject, books and even a SQL Server 2008 Bible that I managed to find in a box at home.
Questions were asked such as does it work exactly the same way? Does it do anything different? Anything in addition to what doing it with the account does? Any possible unwanted side effects? Etc. And they don't have answers, only that they don't want to do it using the account because they have that doc on the file access.
So I came here to ask and see what is the current consensus on how to setup the SQL Server with those servrer policies.
January 7, 2023 at 6:01 pm
Since the installation now includes enabling that feature - and when checked it places the per-service SID in the appropriate local policy, I am not sure why there is any argument about what is needed.
The per-service SID needs to be added to that local policy to enable instant file initialization.
So the question of account vs SID is an easy one - use the per-service SID since that is what the installation will do anyways. It is also the recommended method, since it allows you the ability to easily change the service account without having to modify/update/change the local policies or any of the defined ACL's.
If the global policy is removing the per-service SID's from the local policy - that needs to be changed. Adding anything to the local administrator's group is not the answer either - even if only to 'test' since doing that isn't a valid test anyways.
It isn't clear what the argument is here - if the system admins are stating that no accounts will be allowed in that local policy, then they are wrong. If their argument is that only the per-service SIDs will be allowed - that would be something that could be done. If their argument is that the per-service SIDs should be added to the local administrator's group, then I would escalate with appropriate security teams since that would definitely be a security concern.
If the issue is that you want to allow the service accounts to be added - and not the per-service SIDs, then I think your position is not a favorable position to take.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply