SA Spoof

14

September 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.

14 thoughts on “SA Spoof

  1. Alphonso Delard says:

    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.

  2. 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

    • Andre Ranieri says:

      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.

  3. 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.

  4. Gabriele Massari says:

    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’).

  5. Gerald says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: