July 13, 2018 at 9:08 am
Little background........where used to work, always disabled sa acount. Usually would make another SQL login with sysadmin privileges.
At a new place, I see they have sa enabled and we discussed this.
They brought up a scenario. The reason I'd always made another SQL login with sysadmin rights covers their scenario, but it's made me re-look at
this and make sure I'm thinking correctly about this.
They said what if sa is disabled, don't have any other SQL login sysadmin and for some reason Active Directory for Windows Authentication
was down/unavailable. I don't know what situation that would occur in and if AD was down I'd imagine there will be other issues in your system, but
for sake of argument, if you wanted to get onto SQL Server in that situation, how would you do that?
In my testing using the Dedicated Administrator Connection (DAC), it just connects as either your windows account or whatever supplied SQL login. That login
has to be enabled and resolvable(though never tested, assume if AD is "down", windows cannot authenticate). I read one blog that said you can connect with DAC using
sa even if it's disabled, but that's not what my testing is showing and I wouldn't think that'd be true.
Would it be true that there wouldn't be any means to get on SQL Server in the above scenario?
As I had done at another location, I'd recommend setting up a non-sa acct with sysadmin rights, but just want to make sure the above scenario would prevent you from accessing SQL Server.
Also.........one thing I don't understand...........I see several people recommend renaming sa and then disabling it.
If you're going to disable it, what is the purpose/benefit of renaming it first?
Also, we've always set up a separate login with sysadmin. Could you just rename sa and leave it at that? It'd have the SID = 0x01 so may not be good to just rename it and leave that enabled?
TIA.
July 13, 2018 at 9:26 am
I completely agree that, if you are in a Mixed Authentication environment that you disable the sa account, and create an AD account with the permissions. I also completely agree, if AD is down, you have far bigger problems than not being able to log into the SQL Server. Anything that uses an AD account on the SQL Server (which i would suggest would be all of the Agent jobs) are going to fail as well, and most of the users aren't going to be able to log in either. The service may not even be able to run either, as (I would expect) the service is also running under an AD account.
If, however, you do need to access the server without AD, you can still do so, and there is even documentation on it: Connect to SQL Server When System Administrators Are Locked Out. So, even without AD, there's a way to log on as a System Administrator, as the Server will always have a local administrator account. If your workplace has lost those details... well, then that's just really bad Network Adminsitration and the problem isn't the SQL Server account, it's the fact that the server user and password management is a mess/lost.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply