SQL security over the LAN
-
@IRJ said in SQL security over the LAN:
I would assume the connection is encrypted, but with such poor database practice, who knows.
How does the client store the SA account information?
I would assume not, if this was good devs doing a good job, sure, I'd expect that it was. But this is so not that.
-
@Dashrender said in SQL security over the LAN:
@scottalanmiller said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
What I am wanting to know is if the traffic being sent to and from the database is able to be sniffed ....
By default, it's wide open. There's no "intended production-quality use case" that would allow connections like this, so it isn't the database's job to provide any protection. The connections are supposed to be behind a layer of security on an isolated channel (client / server apps don't really allow that.)
If your stuff is up to date enough and you control enough of the settings, you can enable encryption for this traffic. But the app has to support it.
Where is the app in this case? Is it the thing installed on the client machines? or is there an application also on the DB server, or some other server sitting in front of the DB server?
Client / Server (aka two tier) means "fat client", the app sits on the end user machines, only a database connects them together. If the app sat in front of the DB, it wouldn't be client/server.
-
@Donahue said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@travisdh1 said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
Yeah - one area where they don't check the data - someone could field input drop table and bye bye.
yeah, I would be surprised if there was any santiation going on within the program. I've never seen any yet.
That suggests that anyone can inject code with unlimited authority. Basically, everyone runs as the admin, all the time. Anything that can read the disk can act as the admin.
-
@Donahue said in SQL security over the LAN:
... and if this vulnerability would go away if we had a more modern ERP.
Correct, in modern systems (n-tier design), as long as you do the most minimum of standard practices like using usernames, passwords, and SSL (HTTPS) you have none of these risks. Standard modern design mitigates that particular, enourmous risk naturally and completely. It's simply not something someone should ever have to worry about today (or, for about 20 years now.)
-
Here is what a focused attack on the network would look like...
- Get to any fat client device physically (or remotely if you can get in that way.)
- Read the connection details for ODBC.
- Set up another piece of software that connects to the database from anywhere on the network, doesn't have to be one of the pre-assigned fat clients.
- Do literally anything you want, you have unlimited power at this point. No security restrictions, no limits, no nothing. They can do anything from dumping the entire database, modifying it, deleting it, corrupting it, slowing it, etc.
-
Things that either are wrong, or are likely wrong...
- Fundamental design. Client server is a ridiculous way to be making, or to have been making software.
- Using shared accounts to connect to the database. Modern database systems have addressed this and this shouldn't be how this is working.
- Using the SA account rather than a limited use account.
- Encrypted communications end to end for ODBC (this might exist, but seems very unlikely.)
It's a lot for them to have gotten wrong. This is all stuff that anyone building software like this should have known from day one not to do. None of these mistakes are acceptable for the vendor to have made in isolation, let alone to have compounded, let alone to have kept after decades of running this software.
-
I couldn't imagine it not being encrypted connection using SA account. If it truly is an unencrypted connection with admin credentials it's unfathomable even for 20 years ago.
Either way using a service account with no accountability is fucking train wreck. Using SA account as a service account is 10x worse.
Honestly, I'd document everything and bring it to senior management. Because if it's really unencrypted using the SA account it is beyond reckless. C levels could actually be held personally liable in a breach if any PII is stolen.
-
@IRJ said in SQL security over the LAN:
I couldn't imagine it not being encrypted connection using SA account. If it truly is an unencrypted connection with admin credentials it's unfathomable even for 20 years ago.
That's the point, though, everything else matches "unfathomable for twenty years ago." That the SA account is used, that it is shared, that it is client/server... all match unencrypted.
-
@IRJ said in SQL security over the LAN:
Honestly, I'd document everything and bring it to senior management. Because if it's really unencrypted using the SA account it is beyond reckless. C levels could actually be held personally liable in a breach if any PII is stolen.
Easily. I don't think that they have any PII there, so it's not likely that kind of risk. But if that kind of stuff was stored in there, yeah, it would be quite the risk to personal liability.
I think in this case, the risk is just having corporate financials or details stolen and/or a competitor use it to disrupt operations. Or, of course, makes it trivial for an employee to steal or damage the company with no way to trace back who did it as the connections are anonymous.
-
Of all of the things, I'd say that the encryption is actually of the second least concern. Technically, the client/server architecture is of least concern. The biggest item, but if you wanted to do client server well, you can (within reason.) You can secure it, make it work, even make it fast. It's basically like a SAN... if you have the resources and really know what you are doing, you can make it work. But if you don't, you will screw yourself.
Getting "some" data in flight by tapping the network is possible, but it's not in any way the big concern. It's the concern that scares managers, and it's a real concern, but it's trivial compared to the shared creds and the lack of access control. Those are the things that expose the entire system to not just theft, but also to tampering.
On a scale of 0-10 of security concern. I'd put the c/s at a 2, the encryption at a 7, the completely absence of access control at a full 10.
-
what I know for sure:
- An encrypted version of the SA password is stored on a .ini file that must be able to be read by client applications, which means that any user can at least see the file and open it, since the client application runs under the windows user account.
- The SQL instance name is in the same ini file. I dont know if this hurts the situation or is non issue.
- All the changes that are made within the application record the application user for auditing, but that field remains blank if I edit the database directly. Any malicious activity would have no way of tracing who did it, because it would only show the SA account.
- There is an application on the database server, but I suspect that it is just the master copy of the client application, and not doing any real work.
- I've just confirmed that the ODBC connection is not encrypted currently. I would assume this means that the queries and results from queries are sent in plain text over the wire?
- We don't store any customer related PII other than things like phone numbers and email addresses, but we do have HR records that have some information for employees.
-
well hell. I fired up wireshark and I can see the full queries, returns, and everything else in plain text. I cannot seem to find the user name or password, but everything else is there.
-
@Donahue said in SQL security over the LAN:
well hell. I fired up wireshark and I can see the full queries, returns, and everything else in plain text. I cannot seem to find the user name or password, but everything else is there.
No surprise. Sad. But not a surprise.
-
@Donahue said in SQL security over the LAN:
- The SQL instance name is in the same ini file. I dont know if this hurts the situation or is non issue.
It aids in trivializing an attack. No effort needed to find it or look it up.
-
This situation is fairly common, I saw the same thing with Everest/Greenestep. Even though they had "an application server" all it did was handle licensing, the clients did literally everything else. I demonstrated to one of their developers (and this was nearly 10 years ago) how with little effort one could easily execute anything on the database. He said it wasn't possible if the client machine was properly locked down, and I explained that's the literal exact opposite of how clients are supposed to be treated. Forget the fact that it wasn't just creating a simple TCP proxy to sit between the client/server and watch all traffic (which I showed and shared) and could be done with minimal Windows rights, but also in their custom reports you could do SQL injections since no fields had any sort of sanitation... they "fixed" this in some fields (in things other than reports) by banning the use of semicolon. Really.
I also pointed out other issues such as with the timeclock, since it didn't rely on the time of the SQL Server or their "application server" you could adjust the clock time in Windows and clock yourself in/out in the past or future. Again, claims about properly locked down clients. I asked if most of their customers use proper GPO and so forth to lock down as much of this as they can, he said he didn't even think they knew how to do that. I spoke to an old friend recently who still uses their software, their latest version which finally supports Microsoft SQL Server 2012, and no, none of these things have been fixed despite the software costing $100,000 a year.
This also is the case with Eaglesoft dental software, except they require the client have total local administrative rights to work properly at all. And they do SELECT queries with no TOP (or other functionality in newer versions of Microsoft SQL Server) and instead request every row, slowing down the network, and then sorting in the client. They abandoned multioffice for this reason. Further, for updating it, you have to send a bak of your database via plain FTP so they can run scripts or whatever on it, then you have to get the new version before updating the software. This is the most popular dentist software there is. When I did this for a client, I saw on the FTP server literally hundreds of dumps of other company databases and I had read access to all of them. They can't seem to figure out how to properly update their software.
Incompetence and just sheer stupidity is almost a requirement for ERPs and medical software and nobody wants to change. It's honestly a surprise to me that more information isn't leaked more often.
There needs to be someone who can shake things up by simply doing things correctly, as shocking and innovative of an idea that is.
-
@Donahue said in SQL security over the LAN:
well hell. I fired up wireshark and I can see the full queries, returns, and everything else in plain text. I cannot seem to find the user name or password, but everything else is there.
Wow...
-
so more digging. I have found both the SA password and my application password being passed encrypted, I did not find any clear text versions of those. Man, there is a lot of extra junk that goes over the wire. Simple actions in the application generate a lot of requests to SQL, for stuff that isnt even related to the request. Other times, it get a lot of data that may be too much from a security standpoint. When I open one of my timecards, it pulls a list of every employee for example. It probably does this to cache it or something, but that seems unnecessary.
I tried several things to try and see if I could gain confidential information based just on what was already traveling over the wire. There is some good news and bad news. The good news seems to be that it appears like all numeric or date type fields are obscured or encrypted in some way. I see a lot of "dummyTS" and "dummy textptr" where the results from those columns should be. This means that when I look up things like payroll, I cannot see actual amounts. But the bad news is that all string types look to be sent in the clear, so with the payroll example, I would be able to see if someone had to pay child support, just not how much. It got worse when I checked my employee record. I can see basically all of my personal information include SSN and full bank details because we use optional direct deposit.
-
@Donahue said in SQL security over the LAN:
so more digging. I have found both the SA password and my application password being passed encrypted, I did not find any clear text versions of those. Man, there is a lot of extra junk that goes over the wire. Simple actions in the application generate a lot of requests to SQL, for stuff that isnt even related to the request. Other times, it get a lot of data that may be too much from a security standpoint. When I open one of my timecards, it pulls a list of every employee for example. It probably does this to cache it or something, but that seems unnecessary.
I tried several things to try and see if I could gain confidential information based just on what was already traveling over the wire. There is some good news and bad news. The good news seems to be that it appears like all numeric or date type fields are obscured or encrypted in some way. I see a lot of "dummyTS" and "dummy textptr" where the results from those columns should be. This means that when I look up things like payroll, I cannot see actual amounts. But the bad news is that all string types look to be sent in the clear, so with the payroll example, I would be able to see if someone had to pay child support, just not how much. It got worse when I checked my employee record. I can see basically all of my personal information include SSN and full bank details because we use optional direct deposit.
Wow, that's still so very not good.
Is anyone really surprised that they're incompetent writing SQL Queries as well?
-
I don't know this this is to be expected, but a lot of the traffic is also smb2
-
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
