7

We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change it - my question isn't about why we should change it, but about working around it.

I'm considering renaming the SA account to something else (like "SysAccount" or something like that, and giving it a new password) and then creating a new account called "SA" with the old password and granting it rights appropriate for the application (obviously not membership in the sysadmin role). Are there any pitfalls to doing this? Are there known problems SQL Server will have if the account called SA isn't actually a sysadmin?

I assume that since I'm just renaming the account, I'm safe - it will still have a uid of 0x01, and I've tested this and it's physically possible and appears to work properly in testing, but I want to make sure I'm not overlooking anything that will clearly break as a result. If it breaks things, I can always delete the new SA and rename the old one back to undo the damage, but hoping to avoid trying and failing.

I'm using SQL Server 2012, though I suspect the same answer will apply to any modern version. I've seen the bug where the upgrade from 2005 to 2008 can break if you've done this, but I imagine that's long since resolved.

Improve this question
  • Check to see if any jobs or DB's are owned by it too. – Ali Razeghi Jun 11 '15 at 20:53
  • 1
    What do you think renaming the account gains you? – Aaron Bertrand Jun 11 '15 at 21:17
  • 1
    @AaronBertrand Normally it wouldn't gain anything, but in this case there are applications that run using the SA account but which don't need SA rights (previous bad habits from developers) and I'd like to give them an "SA" account with the password they expect but limied rights, and move the actual sysadmin account to a new name where I can disable it. I just can't disable the account called SA because then their deployed applications break (getting this to fix this is another fight, but looking for what's under my control). – SqlRyan Jun 12 '15 at 20:00
  • Fix the connection string in the application. Having an sa account that is named sa but has insufficient privileges is a rat's nest waiting to happen. Obscurity is NOT security. – Aaron Bertrand Jun 12 '15 at 23:31
  • 1
    @AaronBertrand Agreed that's what's best, but the code isn't under my control and the team responsible is dragging their feet on fixing it (from their perspective, nothing is wrong and it works now). I'm trying to ensure that things don't break when we move to a new server, but that I gain the control of security and stability that I've been wanting for a while. – SqlRyan Jun 15 '15 at 5:14
8

It can be renamed. It's often considered a Best Practice for security, but there can be SQL Agent fallout if you don't bounce the Agent service or change job owners, and I can find reports of it borking Server 2008 upgrades, too.

That's enough to make me not want to bother renaming it. I say assigning a complex password and disabling the login is enough, and it's generally a better option since it actually closes the vulnerability instead of merely obfuscating it. Obfuscation gains you very little security. In the future, if you truly need an SQL authorization sysadmin, create a new account for it. That's not particularly difficult. You have the same attack surface as renaming without having to remember that SID 0x01 isn't sa anymore (even though it still is sa). And, of course, since the SID never really changes, it's not difficult to find which account is SID 0x01 (or a member of the sysadmin role, for that matter). Of course, if possible, don't even use mixed mode. Best practice is to use Windows authentication only mode, AFAIK.

Improve this answer
3

Yes, the 2005 to 2008 problem is fixed. Now you can disable the SA account, you can rename it to SomethingElse, you can give it Chinese password, or you can mix and match.

See: http://www.mssqltips.com/sqlservertip/2221/different-ways-to-secure-the-sql-server-sa-login/

Notice that since the SID of SA (whatever you rename it) still remains, the new name can be discovered by:

SELECT * FROM sys.syslogins WHERE sid = 0x01

You rightly cannot get rid of the built-in administrator login, of course. So you just need to decide whether the minor side-effects are a problem for you.

I never use "SA" for my work, but use my own special domain account with sysadmin permissions.

Improve this answer
0

I can think of one thing that may cause problems - does the application call IS_SRVROLEMEMBER('sysadmin') to check the account is a member of the 'sysadmin' fixed server role? If so this won't work if 'sa' becomes a different account.

Improve this answer
  • No, they didn't do anything advanced like this - it was just code left over from a lazy developer who used SA for everything so they didn't need to set up permissions. Back in SQL 2000, I suppose that was a lot more common than it is now and the new code finally uses named accounts so you know which application has a session open :) I can report there were no other ill impacts to this change at all and their code was non the wiser about the swap. – SqlRyan Dec 11 '17 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.