February 9, 2010 at 5:31 am
Even if the admin couldn't log on, there'd be nothing preventing them stopping the service, making a copy of the raw MDB and bringing SQL Server back up again.
At this point, they can just take the copy, mount it on an external SQL Server and then perform whatever attack(s) they want on the data.
At the end of the day both your DBA and sysadmins, to all intents and purposes, are "god" on the servers. As other people have pointed out, you have to trust your administrators. Safeguards can be put in place (electronic locks to server rooms, so console accesses are logged and timed; deny remote desktop logins to the "administrator" account; etc), but anything can be worked around if the admin knows the system well enough.
February 9, 2010 at 5:38 am
If you are a local admin then on a non-clustered instance you can gain System Administrator access to SQL Server by simply adding your Windows account (local or domain) to the following local group (or equivalent):
SQLServer2008SSQLUser$LAPTOP$MSSQLSERVER
No SQL Server re-start required, although you may need to log-off then log-on for the change to take effect. I'm not sure how you can audit or prevent local group membership changes.
Chris
February 9, 2010 at 6:09 am
EdVassie (2/9/2010)
Problem is, they're local admins--they can get round stuff like that!
True for NT4, don't know for Windows 2000, but not true for Windows 2003 and above. There are lots of things that cannot be done by people with local admin authority.
The Windows Security log is locked down by Windows, and GPOs can prevent local admin from clearing it and preventing a local admin from getting round this restriction. Even if you do clear it Windows will initialise the log with a record saying 'Cleared by joe bloggs' or whoever did the deed.
Not if you're logged in as "Administrator" or "Admin" or some such. Might show that something happened, but won't show a name.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2010 at 6:44 am
Two quick stories from my early days in computers:
1) Back when a lot of beginning Microsoft programmers did things with Access, about once a year someone would create an application, lock it with a password, and then forget the password (thankfully, it didn't happen to me). Calls to Microsoft produced a sympathetic, but stern "sorry we can't help". A lot of work evaporated.
Point - It may be possible to create a system that no one can break into, but I'm not so sure it would be the best idea. You can lose a lot in rare but understandable scenarios like the guy who has the ultimate security having a heart attack.
2) Before learning programming, I was tracked towards Network Administration (back in the days when Novell was a player). On the first training video, the instructor made the statement, "The best security for your system is a simple door with a lock".
Point: Security's first job is to keep out the amateur trouble makers. After that, you can have all the electronic security in the world, but at some point you have to have people you trust handling your system. Based on #1 above, at least one or has to have a key to everything, front and back doors. You're better off getting a staff of competent people and treating them well so that they don't become disgruntled than you are locking the system so well that no one can get in when the worst case scenario hits. 'Back doors' known to a select few can be very helpful.
___________________________________________________
“Politicians are like diapers. They both need changing regularly and for the same reason.”
February 9, 2010 at 7:04 am
Steve,
Let me start by stating that most days your posts are informative, interesting, and and seem to contain facts learned in the real world. What happend?!
There are many encryption security solutions that protect against your specific scenario, and allow for disaster recovery.
Several things in this post go against my fifteen years of DB server experiance.
Some things, like starting a sentance with a preposition, do not even include being a DBA.
"And if they logged onto Windows as "administrator", you won't know who they are."
If anyone can access and login to a production SQL server that contains sensitive encrypted personnel or financial data as the local Administrator then you have a Physical Security issue that will fail any audit.
Single User mode, and the way it works, is necessary for all disaster recovery scenarios that involve restore from tape. Also, it is not M$ SQL excluesive.
What you are stating is not new or news. I feel like a student who's teacher just said with amazement that water is wet.
February 9, 2010 at 7:13 am
It's not that I did not realize an Admin could do something like attach a debugger or copy files. It's the fact that it is so easy for an anonymous admin to put themselves in as a sysadmin in SQL Server. There are so many articles listed that talk about removing BUILTIN/Adminstrators from SQL Server to prevent them from accessing data. However here's an easy way for them to anonymous add themselves, and with clear text, plain text files as the error log, restart SQL Server 10 times (or 20, 30, etc) and there would be no record of who or what had changed in SQL Server.
My issue is that I just realized this was a back door in case you've locked out your SQL Server, which makes sense, but it's an anonymous back door.
I think that's the issue here. Not that you find a way to prevent malicious activity, but that you audit it. That's the problem.
I think Ed has a good suggestion, but I wonder if we are past the time of having a generic "Administrator" account. At least one with a single password. Maybe we ought to require only specific accounts, and remove the ability to delete the last person from the Administrators group.
I think the SQL error logs also need a bit more security.
February 9, 2010 at 7:22 am
Steve Jones - Editor (2/9/2010)
However here's an easy way for them to anonymous add themselves, and with clear text, plain text files as the error log, restart SQL Server 10 times (or 20, 30, etc) and there would be no record of who or what had changed in SQL Server.
Well, I think that's more of a record-keeping issue than anything else. Ideally you should have a record of who has what rights on which server, and if your monitoring software (or whatever) shows that a SQL service restarted for no good reason, you should be doing an immediate audit of that server to make sure nothing's changed. After all, you'd do the same thing if you had reason to believe that an external attacker had somehow managed to provoke your server into restarting!
Of course, by far the easiest way for local admin to extract your data has already been mentioned--stop SQL server, copy the MDB file somewhere else, and restart the service. There'd be no changes required to the SQL server setup, and there'd be a considerably lesser risk for the nefarious personage performing the operation because they only have to keep the server down long enough to perform the copy; with a small enough database they might even be able to do it so quickly that the monitoring software didn't notice the service had gone down!
February 9, 2010 at 7:40 am
paul.knibbs (2/9/2010)
Steve Jones - Editor (2/9/2010)
However here's an easy way for them to anonymous add themselves, and with clear text, plain text files as the error log, restart SQL Server 10 times (or 20, 30, etc) and there would be no record of who or what had changed in SQL Server.Well, I think that's more of a record-keeping issue than anything else. Ideally you should have a record of who has what rights on which server, and if your monitoring software (or whatever) shows that a SQL service restarted for no good reason, you should be doing an immediate audit of that server to make sure nothing's changed. After all, you'd do the same thing if you had reason to believe that an external attacker had somehow managed to provoke your server into restarting!
Of course, by far the easiest way for local admin to extract your data has already been mentioned--stop SQL server, copy the MDB file somewhere else, and restart the service. There'd be no changes required to the SQL server setup, and there'd be a considerably lesser risk for the nefarious personage performing the operation because they only have to keep the server down long enough to perform the copy; with a small enough database they might even be able to do it so quickly that the monitoring software didn't notice the service had gone down!
Copying database files with the service down and possibly attaching the database files to another machine is useless if the data and log files are encrytped using (TDE) and they did not know the strong password of the Master Key. Handy little feature in SQL Server 2008. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 9, 2010 at 7:41 am
There is a saying that locks keep honest people honest. The same holds true for any security scheme. Yes, a rogue administrator could get into SQL through this feature of SQL; however, a trail is left in the audit trail. It may be difficult to determine who used the local administrator account, but the audit trail gives you information about how the person logged on, ie locally or remote IP.
Chances are, if there is such a person in your organization, they are getting into other things that they should not be. Through combined audit logs from all unauthorized access, you will be able to determine who the culprit is.
February 9, 2010 at 7:51 am
Steve Jones - Editor (2/9/2010)
It's not that I did not realize an Admin could do something like attach a debugger or copy files. It's the fact that it is so easy for an anonymous admin to put themselves in as a sysadmin in SQL Server. There are so many articles listed that talk about removing BUILTIN/Adminstrators from SQL Server to prevent them from accessing data. However here's an easy way for them to anonymous add themselves, and with clear text, plain text files as the error log, restart SQL Server 10 times (or 20, 30, etc) and there would be no record of who or what had changed in SQL Server.My issue is that I just realized this was a back door in case you've locked out your SQL Server, which makes sense, but it's an anonymous back door.
I think that's the issue here. Not that you find a way to prevent malicious activity, but that you audit it. That's the problem.
I think Ed has a good suggestion, but I wonder if we are past the time of having a generic "Administrator" account. At least one with a single password. Maybe we ought to require only specific accounts, and remove the ability to delete the last person from the Administrators group.
I think the SQL error logs also need a bit more security.
This is why we audit. We can audit who logged on to the server. We can audit when SQL Server was stopped and started. If SQL Server was stopped at an unusual time, then you go to the Windows security event log.
Granted, if the local administrator account has a known password, then you have an issue. It can be disabled, BTW. If it's enabled... you've got a security event log record. Also, there are now products that will keep that password changing, and you have to go to that third party product and temporarily get access. So there's a solution to coming in as the local Administrator.
And with respect to what an admin can or can't do, since I saw a couple of posts on this... if I'm an admin on the box, assume I can do anything. If you're not running Windows Server 2008 as the OS, I don't even have to use this trick. I'll use a DLL injection attack against LSA secrets and get the service account password in a few seconds. Then I'll log on to SQL Server as its own service account. And that's not something that will be audited, though it can be intercepted with the appropriate anti-malware packages (which is a reason to run AV on a SQL Server).
K. Brian Kelley
@kbriankelley
February 9, 2010 at 7:53 am
TravisDBA (2/9/2010)
Copying database files with the service down and possibly attaching the database files to another machine is useless if the data and log files are encrytped using (TDE) and they did not know the strong password of the Master Key. Handy little feature in SQL Server 2008. 🙂
But TDE gets beaten by examining memory, which is where the decrypted data is kept. So there are limitations to it.
K. Brian Kelley
@kbriankelley
February 9, 2010 at 8:04 am
K. Brian Kelley (2/9/2010)
TravisDBA (2/9/2010)
Copying database files with the service down and possibly attaching the database files to another machine is useless if the data and log files are encrytped using (TDE) and they did not know the strong password of the Master Key. Handy little feature in SQL Server 2008. 🙂But TDE gets beaten by examining memory, which is where the decrypted data is kept. So there are limitations to it.
True, but looking at memory would only give you small glimpses of some of the loaded decrypted pages and you would really have to know what you are looking at to really make any sense of it. I agree that it is a limitation but a small one IMHO compared to the overwhelming benefits of TDE. I would still not use memory access as a reason to not use TDE on a sensitive database. It's a great feature and goes a long way to securing sensitive databases and their associated files. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 9, 2010 at 3:09 pm
Here is a different but nevertheless fine back-door that got me out of trouble the other day. The situation was that an external consultant arrived and was given my organisation's standard PC image with SQL Server 2008 already installed locally under Windows XP, but of course he couldn't connect to it because his AD login didn't have a SQL login. This work-around allowed me, without restarting the SQL Server instance, to create a SQL login for him and grant him syadmin rights.
In my opinion, this is an even bigger security hole, because it doesn't require the SQL Server service to be restarted.
Dave.
February 9, 2010 at 3:13 pm
There are definitely ways around this, but the standard install, the "secure by default", IMHO misses out here. I've not worked with products that secure "administrator" from the sysadmins, and in groups as large as 20 people, you could easily have 5-10 people onsite that could end up making changes before an audit could alert someone.
That's if you have good auditing in there.
It might not be a big hole, but I think it is a hole.
February 9, 2010 at 3:41 pm
Brian and Gail (and others) have made excellent points. Gails points are thought provoking in that, a local admin really can do quite a bit on the server - even without direct access to SQL server. Admins may have access, and that is why there are processes and protocol assigned to performing certain actions on the servers. Then as Brian said, we perform audits to ensure people are following protocol.
I think the "backdoor" is a nice tool for just in case scenarios. There are other measures to be taken to try and prevent the need for the DAC - but it is nice to be able to recover using it if needs be.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply