April 2, 2013 at 4:16 pm
I read an article posted by GilaMonster about remedies for some errors preventing SQL from starting.
One section was on the Service Account being locked out.
This reminded me of a time when the Service Account got locked out during a new SQL installation here.
The incorrect password was entered for the respective services and the Service Account got locked out during the authentication attempts (depends on the password policy of course) -- so all all the production SQL services would now have a locked account and would not be able to be restarted.
So when you perform new installs of SQL do you enter the main prod service account at this point in the installation or do you just use the local system account to get SQL installed, and then use SQL Configuration Manager to change the service accounts post install? This way you can at least do one at a time therefore reducing the chances of a locked account!
Just curious.
thanks
April 2, 2013 at 5:16 pm
Why would the service account get locked out? Are you using the same service account on more than one server?
To answer your question, we setup new service accounts for each new server, generate a strong password (15+ characters with upper, lower, numbers and special characters), store the account and password info in a password safe (KeePass), set the new account to that password, and use that password during the installation.
April 2, 2013 at 6:45 pm
That is correct. We have one service account for each service type (SSIS, SQL etc) but the same account is used for multiple production servers.
No excuse but legacy and small company mentality I suppose!!!
April 3, 2013 at 6:35 pm
UncleBoris (4/2/2013)
That is correct. We have one service account for each service type (SSIS, SQL etc) but the same account is used for multiple production servers.No excuse but legacy and small company mentality I suppose!!!
That is just asking for trouble. 🙁
If the account gets locked out, all your servers will go down, and it makes it impossible to change the password without major downtime.
April 4, 2013 at 6:41 am
i find it absurd that you're able to consistently type the password wrong that many times lol
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2013 at 11:36 am
Michael Valentine Jones (4/2/2013)
Why would the service account get locked out? Are you using the same service account on more than one server?To answer your question, we setup new service accounts for each new server, generate a strong password (15+ characters with upper, lower, numbers and special characters), store the account and password info in a password safe (KeePass), set the new account to that password, and use that password during the installation.
I thought I was the only one who did this...
I also use KeePass to generate a 20 character strong password for the sa account. For those systems that I need to setup mixed-mode, I use that password during the installation.
Both the service account and sa account passwords are never shared with anyone - and the accounts are only used to run the services and setup SQL Server.
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
April 4, 2013 at 12:01 pm
So to clarify, you guys use a separate Service Account for each Service on each Server? So as an example if you have ten SQL Instances each with four Services you would have forty separate Domain Service Accounts?
thanks
April 4, 2013 at 1:32 pm
I'd also suggest following best practice and using different domain accounts for each sql server instance at a minimum. I use different accounts for each service (sql server, sql agent, reporting services, etc).
I'd also recommend Keepass. Not only do you never even have to know the password, you simply copy and paste it from Keepass into the dialog.
April 4, 2013 at 2:49 pm
tafountain (4/4/2013)
I'd also suggest following best practice and using different domain accounts for each sql server instance at a minimum. I use different accounts for each service (sql server, sql agent, reporting services, etc).
Yes I do what you mention above already although I do not use KeePass which I will have a look at.
I might be struggling to get the Network Admin to create separate account per service, per server though!!!
April 4, 2013 at 3:29 pm
keepass or password safe will work.
I would not use the same account for multiple instances, or services. Think about this. If this gets compromised, or you do need to change it, how many machines are you rebooting?
Especially in a small company, I'd use one account per instance per service, so in general, 2-3 per instance (Agent, SSRS, SSIS, etc)
April 4, 2013 at 4:32 pm
Steve Jones - SSC Editor (4/4/2013)
Especially in a small company, I'd use one account per instance per service, so in general, 2-3 per instance (Agent, SSRS, SSIS, etc)
I have only worked in small companies, so out of interest only why do you say "Especially in a small company" -- are suggesting other security (passwords, network access etc etc) may not be as thorough as a larger company there security is more likely to be compromised?
thanks
April 4, 2013 at 4:41 pm
There's not just hacking to worry about. I've seen a situation where we have a Production server and a Test Server running a copy of the production database. We run a process on Test that deletes a large number of records. Part of this process failed, because an obscure part of the software configuration contained a connection path to the production database! If we had been using the same login accounts & service accounts for Test & Production, it would have been a disaster. Since then I've made sure that accounts used on test systems have no permissions on production systems.
April 4, 2013 at 4:52 pm
A larger company is more likely to have its security comprimised. If you have 100,000 employees or more, chances are there are people outside IT with the both the technical knowledge and lack of moral fiber needed to do things they shouldn't. That's not so likely in a non-tech company with 20 people. Secondly, it's a certainty that large companies like Microsoft & Boeing are targeted by skilled hackers every day, while a small company who serves only local customers may not be.
Still, that's no excuse, because the possibility of both scenarios exists for all organizations regardless of size.
April 4, 2013 at 6:33 pm
Dan makes great points, but the reason I say especially at a smaller company is that you have less to manage. It's easier to set service accounts for a dozen or so instances.
At a larger company, imagine 1000 instances. It becomes a chore if you deploy instances at any scale. We had hundreds (300-400) at one place I worked, and we had separate accounts, but developers, Windows admins, etc. constantly complained. Getting new accounts, setting things up, was a small issue (to me), but it felt large (to some people).
I typically use a long, 25char, one time password for service accounts. Someone creates the account, writes down the password (no digital trails), we enter it in on setup (or later) and then we destroy the paper. If we need to get to the service account, we just change the password.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply