SA Spoof
14September 8, 2016 by Kenneth Fisher
This is nothing new but it’s still a fun idea.
The problem:
- The vendor app we just purchased (and are told we must keep) requires the sa password.
- The vendor insists that sa is hard coded into the app and can’t possibly be changed.
- The sa account is the built in super user for SQL Server and we certainly aren’t going to give it to an application.
- This might even be a shared server where giving one application sa means a security risk for all of the other applications.
A possible solution:
Did you know that sa can be renamed? It can. And that lets you do something like this:
ALTER LOGIN [sa] WITH NAME = [Not_sa]; GO CREATE LOGIN [sa] WITH PASSWORD = 'StrongPassword', CHECK_POLICY = OFF; GO USE Test; GO CREATE USER [sa] FROM LOGIN [sa]; GO ALTER ROLE db_datareader ADD MEMBER [sa]; ALTER ROLE db_datawriter ADD MEMBER [sa]; GRANT EXECUTE TO [sa]; GO
The user sa now only has read/write and execute permissions on a single database. So now you can give them the sa password with no risk.
There is a warning here though. Some patches require sa to be the actual sa. That means that before you patch you’ll have to make a point of switching back. Then switch back again when done.
Also I wouldn’t exactly call this a best practice but it’s certainly a fun trick.
SQL Studies 
nice 🙂
Thanks 🙂
Quick question, “Some patches require sa to be the actual sa.”
Do you mean vendor patches, or SQL Server patches?
SQL Server patching. Some vendor code may require additional permissions also but that can be added & removed from the “spoof” as needed.
My understanding (and I’ve been wrong before) is that with some SQL Server patches if it’s not actually the one and only sa then it’s going to crash. Potentially messily.
You hit me, I had a customer installing an app and leave 3 accounts with sysadmin when their document requirement request read and write permisions
That’s been a fairly consistent issue on our end. We have a dedicated install account which has sysadmin rights; we usually try to have our engineers install using this account during a WebEx screen sharing session with the vendor. It’s a full time job keeping logins with sysadmin server role membership from popping up like mushrooms.
It certainly can be! Security in general can be a hard job to deal with. It really helps if you have buy in from Sr management though. 100x harder when you don’t.
Very cool!
I have tried this in the past and found some vendor installers check the SID to ensure that the account is actually ‘The’ sa account but as we always renamed, removed permissions and disabled the sa account this was not acceptable or approved by the security team (on the advice of a bearded DBA 🙂 ) We managed to get that vendor to alter their installer before we bought the product
Yea, I can see that happening. Of course that that point the vendor isn’t just being willfully ignorant of best security practices they are deliberately going out of their way to create a security problem. I’d be a little worried about the rest of their practices. Glad they were willing to change. Of course I’m also wondering if they used the modified installer for everyone or just ya’ll 🙂
It was “The way they had always done it since v1 and no one had complained” it was updated for everyone with their next release which was good. Once it was explained they were pretty good about actually.
Actually, it is my understanding that as a best practice the sa login in SQL server should be disabled and renamed in any and all instances.
However, nice point… should have thought about this in the past, instead of gaining a not-so-nice chat with the management about how I wasn’t giving or typing the sa password to a new application (when I was telling them ‘I cannot give you the sa, but will give you your dedicated login with the minimal required permission, please tell me what you need’).
Disabled is certainly a best practice. Renaming I’m not so sure about. It’s certainly not something you want to do and forget about.
Definitely a “no go” on a shared SQL Server. Customer would have to get a dedicated SQL Server (hosted) without any Support of the SQL Team.