August 16, 2012 at 10:02 am
BlackHawk-17 (8/15/2012)
Steve Jones - SSC Editor (8/15/2012)[hrWhy? Once the instance is up and running, the service account is almost never needed? What's the administrative overhead?
If there is separation of duties where you, as a DBA, do not have the rights to create accounts or drop them in OUs then it is a hassle.
I see a certain logic in what you propose but I wonder how well it scales... especially in an evironment where you can't convince other groups that keeping the same password indefinitely is fine for service accounts.
I scaled this to hundreds (300-400) of instances with no issue. We were under SOX governance, and with a long password, our auditors allowed this. If they wouldn't now, it's not that hard to use an API to generate passwords and change them periodically in script.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 16, 2012 at 10:21 am
Adam Seniuk (8/16/2012)
Pretty much the case, also I am doing 20 installs so it makes it easier to do a scripted install so there is less clicks.
Any chance the account name is derived from the server name? It won't solve both problems but it can solve one.
August 16, 2012 at 1:22 pm
Michael Valentine Jones (8/10/2012)
I agree on the having a different account of each SQL Server with a long random password, but I think you need to store the password. I have seen too many cases where I needed to be able to login with the service account to be able to resolve an issue.I used KeePass to store the passwords for over 300 servers, along with SQL Server application logins, linked server logins, etc. I generally used a 20 character or more password to make sure that even determined guessing will never succeed.
Using the same account for that many servers is just asking for trouble, and there are just too many ways that someone with sysadmin access on one server might be able to gain access to other servers.
An account lockout on one server takes everything down, like the case you mentioned where someone changed the password.
Also, what do you do if you have a security breach and need to change the common password? It's very unlikely that all servers would have a common maintenance window that would allow this without a major business disruption.
I also use this to be to generate long passwords that it is still possible type without making a lot of mistakes:
I do exactly the same thing, except I use KeePass to generate the passwords. Since I only need to use the sa password once during installation and the service account password once during installation - copy/paste works very well 😉
At a previous site, we had a single account for all services. Then one day, we started getting error notifications from SQL Server agent jobs on various servers. They were all failing because they could not login to SQL Server. Turns out, we had an enterprising DBA who didn't want to login to the servers to make changes with his own account. So, he would use this service account - fat finger the password a couple of times and lock the account out.
Took production systems down on a regular basis until we figured out what was going on. And yes, that account was a member of the local administrator group on every server.
I see no reason to require changing the password for the service accounts - as long as the password is never shared or used for anything but the initial installation and configuration. If the password were to get out, then I would change it for that instance - schedule the restart and make sure it didn't get out again.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 16, 2012 at 2:06 pm
cfradenburg (8/16/2012)
Adam Seniuk (8/16/2012)
Pretty much the case, also I am doing 20 installs so it makes it easier to do a scripted install so there is less clicks.Any chance the account name is derived from the server name? It won't solve both problems but it can solve one.
Er, No... yea the account names are varation of the server name but also password would need to be entered.
August 16, 2012 at 2:13 pm
Steve Jones - SSC Editor (8/16/2012)
BlackHawk-17 (8/15/2012)
If there is separation of duties where you, as a DBA, do not have the rights to create accounts or drop them in OUs then it is a hassle.I see a certain logic in what you propose but I wonder how well it scales... especially in an evironment where you can't convince other groups that keeping the same password indefinitely is fine for service accounts.
I scaled this to hundreds (300-400) of instances with no issue. We were under SOX governance, and with a long password, our auditors allowed this. If they wouldn't now, it's not that hard to use an API to generate passwords and change them periodically in script.
If you show auditors a 50, 100, or (maximum) 128 character long cryptographically random (computer generated) password, and you have controls to keep it secret, you should be able to stay within most guidelines even without changing it.
As Steve said, if you have to change that many passwords regularly, you're going to way to automate it anyway, so that shouldn't be much harder.
August 29, 2016 at 12:48 am
A separate service account for each service is of course the best practice for securing your SQL server services.
However, with Availability Groups, all replicas that participate in a group with a listener must use the same service account otherwise the SPN will fail. The SPN is bound to the account running the service.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2016 at 12:58 am
I have found that a lot of places who avoid managing individual sys accounts for each server are often logging into systems using the sole sys account on a daily basis to perform tasks as opposed to assigning permissions to roles/groups and assigning those to user logins. This not only gives more people open access but also significantly reduces the option to audit who does what.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 29, 2016 at 6:36 am
ddriver (8/10/2012)
I really like that idea and I will pass it on to our DBA's and admins.
Same here.
August 29, 2016 at 7:13 am
We (I really) use a different account for every instance of SQL Server. Every password is at least 15 characters. It isn't difficult, and allows us to change the password for one product without affecting all the others. I can't imagine why anyone would want the same password across instances/servers.
Dave
August 29, 2016 at 7:38 am
Don't the service accounts make this issue moot? I think they came about in 2012. I've used them in a couple of SQL 2014 setups now. They don't need a password and they seem to work really well. I'm surprised no one has mentioned them to the point where I wonder what I might not be understanding from all this. Someone feel free to enlighten me if I am, but if not, why wouldn't you go this route instead?
August 29, 2016 at 10:43 am
RonKyle (8/29/2016)
Don't the service accounts make this issue moot? I think they came about in 2012. I've used them in a couple of SQL 2014 setups now. They don't need a password and they seem to work really well. I'm surprised no one has mentioned them to the point where I wonder what I might not be understanding from all this. Someone feel free to enlighten me if I am, but if not, why wouldn't you go this route instead?
Depends on the situation.
If you have any need / reason for SQL to reach out and touch networked resources (backups going to a network share? Agent job steps that copy files to / from shares?) you're going to need an account which can be granted permissions to those locations, which typically means a Domain account.
At that point, you then need to start thinking about the decision to either use one account across all servers per-service, or one account for each service per-server.
The first raises the specter of someone cracking your sole account, and thus gaining access to everything it has access to, while the latter is a bit more work to maintain (changing the passwords for 30-40 service accounts can be time consuming.)
Currently, I'm looking to move all my new servers to the Virtual Service accounts where possible, because it'll save me work the next time we need to change our service account passwords...
August 29, 2016 at 11:07 am
We went to individual accounts when we were at a couple dozen servers. Now that we're at a couple hundred we're very happy we did. Administrative overhead is minimal, and it can help troubleshooting. We also have separate accounts for the agent, for SSRS, SSIS, etc.
It really furthers minimum permissions granted. And if a process is error'ing out, viewing the sql error log often pinpoints the issue- the exact account having problem is highlighted there, and our precise granularity of permissions points to where the account originates.
My question is, what are people's naming conventions? Generally we prefix a service account with "svc_" and the rest depends on the application, service type, and if it's dev/test/prod. For example:
svc_dw_sql_prd
would indicate the data warehouse service account for sql server in production.
This system helps administering also because they sort logically in AD, and it can be easily scripted because it's so predictable. Knowing the application abbreviation ("dw" in the example above) let's you script the service account name and makes rolling out new servers or other tasks that much easier.
That's our one example, how are others doing naming conventions?
Cheers,
Jason
August 29, 2016 at 12:44 pm
We used to go the route of different service accounts per instance. However, we quickly grew to a rather large SQL Shop and auditing required password changes every 30 days for these accounts. With over 200 production SQL instances and countless more dev/ qa/ staging instances you can imagine the anguish when we neared that time of the month.
Thanks you Microsoft for offering a much improved way to handle this scenario. We've opted to go the route of one global managed service account per environment level (4 total accounts now dev- qa- stg- prod). Microsoft manages changing the passwords behind the scenes and there is no need for system admins/ or us dbas to store or even need the service account password after initial setup. This route has a few requirements but I highly recommend looking into this approach for larger SQL shops.
For those with only a few instances...different accounts for each instance is still the preferred approach IMO. This can be done with either the old service accounts we all know or with MSA (managed service accounts) or gMSA (global managed service accounts). The latter two offer the benefit of set and forget. No need to know/ store or bother with changing the passwords as this is all handled by windows on the backend.
August 29, 2016 at 12:57 pm
RonKyle (8/29/2016)
Don't the service accounts make this issue moot? I think they came about in 2012. I've used them in a couple of SQL 2014 setups now. They don't need a password and they seem to work really well. I'm surprised no one has mentioned them to the point where I wonder what I might not be understanding from all this. Someone feel free to enlighten me if I am, but if not, why wouldn't you go this route instead?
Depends on the situation.
If you have any need / reason for SQL to reach out and touch networked resources (backups going to a network share? Agent job steps that copy files to / from shares?) you're going to need an account which can be granted permissions to those locations, which typically means a Domain account.
Pointing out the expectations is a good point. My understanding is that a virtual service account won't work for calls to another domain. For calls to another server within the domain they still work, but they have to use the machine account. I have this case for a couple of ETL processes. That might be unacceptable in some environments, but not sure why that wouldn't be allowed but this account that no one should be using but could would be. But agree, that is a factor to consider.
August 29, 2016 at 1:19 pm
RonKyle (8/29/2016)
RonKyle (8/29/2016)
Don't the service accounts make this issue moot? I think they came about in 2012. I've used them in a couple of SQL 2014 setups now. They don't need a password and they seem to work really well. I'm surprised no one has mentioned them to the point where I wonder what I might not be understanding from all this. Someone feel free to enlighten me if I am, but if not, why wouldn't you go this route instead?
Depends on the situation.
If you have any need / reason for SQL to reach out and touch networked resources (backups going to a network share? Agent job steps that copy files to / from shares?) you're going to need an account which can be granted permissions to those locations, which typically means a Domain account.
Pointing out the expectations is a good point. My understanding is that a virtual service account won't work for calls to another domain. For calls to another server within the domain they still work, but they have to use the machine account. I have this case for a couple of ETL processes. That might be unacceptable in some environments, but not sure why that wouldn't be allowed but this account that no one should be using but could would be. But agree, that is a factor to consider.
Actually, there's a very good reason you wouldn't want to use the machine account to access a network resource. Any service on the server using (I believe) the NETWORK SERVICE account would be able to access that resource. But, you say, I'd know which services were using that account, so it shouldn't matter, right? Sure, right up until some nefarious actor adds a *new* service that uses the NETWORK SERVICE account and now as gained access to that network resource...
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply