SQL security over the LAN
-
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.
-
@tonyshowoff said in SQL security over the LAN:
@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.
Curious - why would you want to do it over SMB?
-
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
-
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
plus, it probably ties the customer in tighter into the MS ecosystem.
-
@Donahue said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
plus, it probably ties the customer in tighter into the MS ecosystem.
Exactly, same reason they have all that COM+ garbage and other things that just add layers and layers of complexity to things. Ever wonder why Exchange Server and AD are such a bitch to get working and their clones aren't? Part of the justification for all these extra products is Microsoft tries to put them to use in their own, making them more and more bloated and difficult to get working. They've started to fix this in recent years by abandoning their own product lines and just doing things a more proper way. But if you installed Exchange 2003 or 2007 and had issues with it or with AD around that time, and all the tons of countless tools, scripts, etc you had to use, you'd get why these things happen to Windows but not as often on the same protocols hosted elsewhere.
-
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
-
@Donahue said in SQL security over the LAN:
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
Not if the application does not have it built in, such as TLS/SSL connections. There are way to mitigate it over the network such as a tunnel between the client and server, but on the client there's no defence at all, and that's really your vulnerable part.
-
Setup a TS and run the app from there. RDP into TS.
-
@Dashrender said in SQL security over the LAN:
Setup a TS and run the app from there. RDP into TS.
That doesn't fix the problem because the weak point is the client, it would just be moving the problem to another place, in fact it may be worse because then they could see the traffic of all the clients in a single place.
-
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
Not if the application does not have it built in, such as TLS/SSL connections. There are way to mitigate it over the network such as a tunnel between the client and server, but on the client there's no defence at all, and that's really your vulnerable part.
that's what I thought.
I'm trying SQL injections now
