November 13, 2013 at 5:59 am
Hi. Sorry if this is a bit of a silly question, but how can I find out what minimum permissions I need to give the NT AUTHORITY\SYSTEM account?
Also, I've noticed that somebody has given himself a login on some of my instances without checking with me first; he is a Windows sysadmin, so would he have got in using this account? (He doesn't know the sa password.)
Thanks
November 13, 2013 at 6:42 am
Beatrix Kiddo (11/13/2013)
Hi. Sorry if this is a bit of a silly question, but how can I find out what minimum permissions I need to give the NT AUTHORITY\SYSTEM account?
You don't need to give it anything. It probably shouldn't be added as a SQL login at all.
Also, I've noticed that somebody has given himself a login on some of my instances without checking with me first; he is a Windows sysadmin, so would he have got in using this account? (He doesn't know the sa password.)
Not unless you either have granted local administrators or domain administrators groups a login with enough permissions to create new logisn (or he restarted SQL Server in single user mode)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2013 at 6:49 am
Thanks. I did try Googling but I can't find anything that explains quite what I am after.
Not unless you either have granted local administrators or domain administrators groups a login with enough permissions to create new logisn
I haven't done that. How weird.
(or he restarted SQL Server in single user mode)
Can this be used as a way to get access if you don't know any of the passwords? I definitely didn't give this guy access- he doesn't need it and didn't ask me for it.
November 13, 2013 at 6:52 am
Beatrix Kiddo (11/13/2013)
Can this be used as a way to get access if you don't know any of the passwords? I definitely didn't give this guy access- he doesn't need it and didn't ask me for it.
Yes.
Connect to SQL Server When System Administrators Are Locked Out
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2013 at 6:54 am
Beatrix Kiddo (11/13/2013)
(or he restarted SQL Server in single user mode)
Can this be used as a way to get access if you don't know any of the passwords? I definitely didn't give this guy access- he doesn't need it and didn't ask me for it.
Yes, it's so that you can never lock yourself out of a SQL Server. If he has, it'll show up in the error logs.
However, if you've granted <local machine>\Administrators or <domain>\DomainAdministrators sysadmin permissions, then you've given this guy access already as he'll be part of those groups if he's a domain administrator.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2013 at 6:55 am
Oh yes, I did know about that actually (but thanks!), but it didn't occur to me that somebody would use it as a back door and not just in a real emergency. The cheeky bastard!
November 13, 2013 at 6:57 am
So have you checked the logs and proven that he did that? If you haven't, then you can't assume.
Check what logins you have. Make sure you haven't granted local administrators or domain administrators sysadmin access. It's incredibly common to see on SQL Servers.
Edit: and make sure you don't have sysadmin granted to public. Or to <domain>\Users or <domain>\AuthenticatedUsers (seen all of those done before)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2013 at 7:10 am
I'm just checking back now... the trouble is I don't know exactly when it would have happened. I'll stand back from DEFCON1 for now :-).
(Am I looking for something like "Setting database option SINGLE_USER to ON for user < >", or would that not apply since it was started up in single user mode?)
November 13, 2013 at 7:11 am
Not database set to single user mode. Instance started in single user mode.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2013 at 7:17 am
Thanks. I just need to take a sec to eat my words a bit because there's no evidence that he's done that. (He's still somehow got accounts on instances he has no business going near, but it wasn't that way, I don't think.)
November 13, 2013 at 8:27 am
Is there a BUILTIN\Administrators group in your login list?
November 13, 2013 at 8:31 am
Koen Verbeeck (11/13/2013)
Beatrix Kiddo (11/13/2013)
Can this be used as a way to get access if you don't know any of the passwords? I definitely didn't give this guy access- he doesn't need it and didn't ask me for it.Yes.
Connect to SQL Server When System Administrators Are Locked Out
IMO I find this pretty unlikely in a business situation except in an emergency. Not impossible, just unlikely.
I think its more likely he's in a group that has sysadmin permissions and added themselves accidentally (through an installer maybe). Why would you add yourself as a login if you already had access?
November 13, 2013 at 8:59 am
MysteryJimbo (11/13/2013)
Is there a BUILTIN\Administrators group in your login list?
No, that's what puzzled me. Well, it's there on one but not the other, but it only has the 'public' Server role, and under User Mapping too.
IMO I find this pretty unlikely in a business situation except in an emergency. Not impossible, just unlikely.
It's not what you might call a well-operating workplace.
Why would you add yourself as a login if you already had access?
It's a good point- I don't know. The guy in question is the sort who thinks the rules don't apply to him. He has absolutely no official need to go near this database.
November 18, 2013 at 5:15 am
Just seen this blog post, which I thought was timely.
Members of the local Administrators group can still login to SQL Server. With full sysadmin privileges. How? By impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. You can easily do this with PsExec (using the –s switch) or creating a Windows Scheduled Task running under the SYSTEM account.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply