So you’ve got a bunch of people using the SA account all over the place. It’s hard-coded into connection strings, embedded in linked servers, and the password is on post-it notes throughout the data center.
Step 1: Create an alternative SA account.
In a perfect world, you’d create a minimally-privileged AD login that only has limited access to specific databases.
However, when you’ve got a toddler running with scissors and razors, sometimes you’re happy just to get the razors out of their hands first, and then you’ll work on the scissors next. One step at a time. Preferably not running.
For now, create another SQL account with DBO permissions on all of the databases involved with the application. (If you’re dealing with multiple different tenants on the same server, give them each their own SQL login.) Let them be complete owners of their databases for now.
Step 2: Explain your concerns to the stakeholders.
If several different departments have databases on the same server, and the SA account is being used, email all of the departments to explain what’s going on:
Hi! Your application, ___, lives on SQL Server ___. During security checks, we discovered that ___ uses a highly privileged account. This means they can drop your database, delete your data, shut down the SQL Server, or leak all of the data to the public.
I’m totally sure they would never do this on purpose – but I’m worried about accidents. I want you to be aware that as long as they’re using this account, there is nothing I can do to prevent accidents like that.
If you’re not okay with that risk, I’ve got an easy plan to secure the server with no disruption to the end users. Are you interested in hearing more about that?
Most stakeholders will hit the roof when they read something like this, and they’ll become your political ally to help get things done. They’ll practically demand the other users to let go of the SA account – but to be ready, you
Step 3: Give the users the new account – and an SA expiration date.
Armed with the political support of your stakeholders, send an email to the SA-equipped users, and copy your stakeholders:
Hi! We’re going to be changing the SA account’s password on YYYY/MM/DD. To prepare, here’s a new account that you can use for your applications: username ___, password ___. That account has full owner permissions on all of your databases.
I know changing your connection strings will take some time, so we made sure to set that SA date two months from now to give you plenty of time to prepare. On the password change date, I’ll be right here in my office for four hours after the change to help you troubleshoot any problems that arise.
Inevitably, you’re going to get grumbles from the users who have to make the change. They may even say they have to be system admin on their SQL Server. Be prepared to offer them options:
- Postpone the transition until YYYY/MM/DD, or
- Move to your own SQL Server, thereby avoiding the risk for the other stakeholders, or
- Get the other stakeholders to sign off that they’re okay with the continued use of SA (but at the same time, they’re also okay with me no longer being on call to support this server, because I can’t support servers where non-DBAs have SA permissions)
That last one takes the most backbone – you have to stand up for yourself. There’s absolutely nothing wrong with the business letting lots of people be SA on the server – as long as they’re in the on-call rotation with me.
Step 4: Only people in the on-call rotation get SA access.
Not along with me – no, in the rotation.
I’ll gladly take more people in the SQL Server on-call rotation with me. And I’m perfectly willing to present that to the stakeholders. The conversation inevitably goes like this:
Me: “OK, cool, so you’ll be in the rotation then? You’re on next week, and I’ll be off. If jobs fail, backups fail, cluster goes down, etc, it’s on you.”
Developer: “No, wait, what? No, I don’t wanna be on call for SQL Server. I’m a developer – I don’t know how to troubleshoot that stuff.”
Me: “No? Are you saying you don’t know how to manage the database? But you want the rights to do it?”
Stakeholder: “Yeah, no, let’s take that SA permission away then.”
The conversation doesn’t always go that way, and in my next post, I explain Option 2.
29 Comments. Leave new
This is a very timely article for me. I’ve been tasked with taking over DB admin on the Production DB and the previous admin was quite lenient on who got the SA password or access into the DB. I am in the process of locking it down now and your words speak directly to what I am trying to accomplish.
Thanks!
This is a wonderful post, but how do you deal with system administrators who need local administrator access to a server to do their system administration, which also gives them sysadmin access to the database server?
Local admin rights no longer involve SA rights in SQL Server. Those two are separate things.
It is as if you read my mind, today I have been asked by a developer to give them SA access to a Dev server.
My response was if we setup the correct permissions in Dev when it comes to UAT/Prod then you will be used to having those permissions.
What would your thoughts be for SA on Dev Brent?
No, because I don’t want to have to support them if they change an sp_configure setting.
Why would they want to have sa permission on dev ?
Often they do things like restore databases, change server-level settings to test performance changes, etc.
That was humorous but true. And number 3 will not hold up really but can be carefully tried. That’s why if at all possible you avoid giving anyone SA in the first place or its carefully managed as a ‘temporary elevation in permissions’ like during an install of OBIEE and then removed afterwards. And even then it’s an account with sysadmin, not SA if at all possible. There are the odd applications that hard code SA in the product for some processing unfortunately but it is fairly rare. Like hard coding ‘localhost’ for the database connection in the application is.For databases with sensitive data however you stand a much better chance of making a good case and succeeding. Much harder when taking over old databases from others or when someone leaves or a consolidation.
I like the notion of incremental progress and scissors and razors. Certainly the approach I also take.
I would add one thing to your reassurances to the great unwashed – a description of how support will be assured through the on-call rotation, how to call the current rotator (rotary?), and that there is a backup plan if the rotation breaks down (the sealed envelop in the safe with two combinations and a security weasel keeping it protected).
Good time to recap what you do and don’t support to the community, as well.
Often I used to get requests for sa to install upgrades to apps . When I asked what functionality do you need sa for ? the answer was inevitably we dont know we just know we need sa .
Developers just were too lazy to work out what was really needed.
Worse still once a business critical app had the sa user and password hard coded and compiled . Then the source code lost !
I’ve found that most installations that ask for sa just need dbcreator role, if they need any elevated rights at all.
Usually dbcreator and securityadmin like for SharePoint. But sometimes as in if they create SQL Agent jobs they may need more. I figure as long as it is very temporary and controlled most of the time it will do. But could it be better planned so as not to need it? Sure but you cannot hold things up pushing too hard either.
I read this with great delight as it is indeed true – but people will not give up that access for anything. All of a sudden, corporate policies with which you are required to adhere to at all times, suddenly become a gentleman’s agreement to not fool with the databases of anyone else. As for getting written confirmation of this… GOOD LUCK.
I’ve had stand up arguments with CIOs and managers who tell me that I am (reading between the lines) to scrape and bow to vendors (being paid LOTS of money to provide a product built on a lack of planning and preparation) who somehow become the ones calling the shots when it comes to security over corporate data while demanding SA access.
Then again, the notion of me having arguments with anyone is probably not a stretch of the imagination 🙂
@Dave, I understand your reaction, but in my experience it isn’t necessarily that devs are too lazy, it’s that when things go belly up, they don’t want to jump through all the support call hoops to get the freedom they need to fix it. There is nothing as demoralizing as an ‘Access denied’ when the support queue is growing in the red.
To that regard I would like to turn Brent’s advice around in that it helps if you build a personal relationship with the dev so they know you’ll be there at the front line when they need you.
In my experience it is never black or white and no person is ever intentionally evil.
Boris – you may want to hold back on that last line until you read Part 2.
I have to agree with the start of the second para… I usually work WITH the Dev to get something in place rather than just saying “no” and taking everything away that they need to do their job. I’ve been on the flipside often enough to know how annoying that is.
One site I was at a few years ago: The Devs needed access to update something in production whenever a certain condition was met (usually a call from people needing support) and they always wanted SA. Nobody had ever told them anything different about how else to do it. The problem was that they never got back to me to say when I could remove the authority.
The way around it was to set up a job that, when run would grant the auth and send them an email saying that they have the auth they need at time X and it will automatically be removed in an hour (in case they were working on something else). That job would update the schedule of another to remove the auth and send another email when the auth was removed.
Worked quite well in my opinion 🙂
I don’t know why it is resisted that if you need to update something in production regularly then there should be an application of some kind done for that. It’s not like it is hard and fulfills requirements.
That was a case of requesting assistance from a vendor being paid over $100k a month for support that they weren’t providing – and nobody was prepared to call them on it.
I was banned from calling the vendor after about the second phone call because I work on the premise that they worked for US since we were paying them.
This was the around the time I realised that more and more vendors were doing the “undercut everyone” principle to get a foot in the door and then charge like wounded bulls for everything that needed fixing as it was somehow deemed a “product enhancement”.
If you knew how to make the update in production already, which it sounds like you did, then making an application to do that is pretty easy. Yet no one will do it. You weren’t using the vendor to do it then anyway. There are many times someone want a flag changed on a record, that’s all. And they need this over and over for production problems. But rather than make an app for that they will call me in the middle of the night to update the flag in a record according to the script they are giving me. Really any developer worth their salt could make something to update that in an hour. It is not hard for me to do but an application would be better really. They think about it after suggested… but it almost never happens.
@Ron Sexton,
It was one of those situations (and I’ve been in them many times) where the company and the Devs wouldn’t allow anyone to create something to do the job automatically or via a panel for fear that it might upset the vendor.
The Devs weren’t idiots – they were just terrified of the politics that went with it.
That was one of the organisations where I had a stand-up with the CIO as I refused to send an unencrypted database of client loan information to the vendor site out of the country because criminal punishment under The Privacy Act would no longer apply. I said that a corporate agreement was worthless to ensuring the safety of personal information.
He said to do it or it was worth my job. I needed my job 🙁
@Matt G
It you think those really relate (CIO thing vs. panel to update something) and that that makes sense (why would the vendor care how you are correcting something you are already correcting?) then you should read a link Brett posted in one of his mailings recently(1/4/2016) about ‘Messed Up Practices – Heres how they become part of a companies culture’. Pretty good read.
http://danluu.com/wat/
You have acclimatized!
It was certainly messed up.
The CIO thing was an example of the culture.
It came down to “do it or leave”.
So yes, I was forced to acclimatize. Doing things the correct way became a non-option.
I am not there any more… SEP (someone elses problem) 🙂
“No? Are you saying you don’t know how to manage the database? But you want the rights to do it?” — Love it!!
Or in rare circumstances
Developer: “Huh? Sure, I’ll be on call for SQL Server. I’m a developer – I’d love to know how to troubleshoot that stuff.”
True. Don’t suggest what you don’t really intend to do because some will say ‘What a great learning opportunity, thanks!’.
I just ran into this today. The explanation in step 2 is really helpful! Thanks.
[…] Stop using sa! Reset the password, and disable it. Yes, your Agent Jobs will still run just fine. […]
Great information, much needed, I needed some more guidance or information on this. Some developers say they need as access temporarily to do an upgrade or an update their tools/software like solarwinds, is this avoidable ? I always a tell them to work with their vendors and get another customized role or lesser access but they always say they need as access temporarily for the upgrade and since they are responsible tech users they will never accidentally or purposely mess up with a temp access, I there a way out without giving sa accesss
That’s not in the scope of this blog post. For other questions, head to a Q&A site like https://dba.stackexchange.com.