Blog‎ > ‎

MSSQL, Data Polling, RDP, Mobile Auth, Security, Credentials, Mental Models

posted Aug 20, 2017, 2:32 AM by Sami Lehtinen   [ updated Aug 20, 2017, 2:33 AM ]
  • Read a few more articles about MS SQL (Transact SQL, T-SQL) performance fixes and issues. Most of the tips were of course extremely obvious. Like fixing I/O, limiting I/O, making sane queries, reuse queries (for query plan reuse), use indexing (don't over use), separate data & log, don't use production database / server for scratch / temp data, too small VLFs, do not over allocate memory for MS SQL (causing memory deprivation of other processes and operating system), semi-slow queries which are run repeatedly like polling. Nothing but obvious stuff. But it's surprising how often these facts are forgotten.
  • One polling query is a good example. It's not actually slow, but it's run 50 milllion times per day. Even if it wouldn't return anything, it still requires lot of resources from server side. Especially if there's data which needs to be filtered and or sorted on server side.
  • More interesting observations about Remote Desktop Protocol / Remote Desktop Connection design fails. It seems that there isn't any kind of activity / networking timeout. Addresses getting banned on firewall level can linger as established TCP connections indefinitely. I guess this is also one of the reasons the RDP is so crappy and extremely easy to DoS. Negotiating connections with server to certain state and then just disappearing leaves the server with tied resources lingering forever. - Great, just great. Some protocols are just (a lot) better than others. I would understand this kind of 'quality' if it would be my code for a customer which wanted 'cheapest possible crappy ad-hoc' implementation. Build something which mostly works in a few hours. Copy paste sample code from net and make extreme naive shoddy experimental piece of code which just works when everything is OK. But when production code from major corporation is just as bad. Well, it is. Nothing more to say. Restarting remote desktop service, throws out all active users. As well as terminates these lingering connections.
  • So guys, next time you're writing production server code, just copy paste something like this. Python http.server - Who needs nginx or anything else, when we've got full featured robust and attack resistant web server which we can simply use. Actually I've been planning to do exactly that. But only for a project which handles a small quantity of request from a trusted sources and IP addresses.
  • One important mobile user identification application by DNA, doesn't allow user changing personal PIN code at all. That's just absolutely wonderful. There's no way to change PIN. Except than to terminate the contract agreement with customer service and then re-enabling it with new PIN. I'm not talking about lost PIN code recovery. I'm talking about changing known PIN code.
  • Even after double and triple checking, situation remains the same: For some reason discard doesn't seem to be working for my SSD with ext4. Funnily it works great with vfat on same drive. Should I see discard on #mount option row when checking what mount says? I would assume it should read there. What's the best way to verify that discard is actually active? I did see tons of guides with mostly only bad hints and incorrect ways of checking it.
  • Python 3.6.0 standard library hashlib also includes scrypt, blake2, shake and SHA-3 aka Keccak - Awesome - It's very important to have modern and compatible tools for key derivation, password protection & data hashing. Dupe from previous post, but doesn't matter. I studied and played quite a lot with that stuff.
  • Some security / design flaws are just so devastatingly horrible that those can't be even mentioned. - So I'll shut up. - But these are really serious. - Let's hope those get fixed, but I'm highly skeptical.
  • Also found out tons of basic stuff, like using default credentials. Which basically means that key business data is not protected at all. But actually, nobody cares, or gives a bleep. And this is the norm, most of companies actually got. So no news here. At least there is authentication, even if it requires attacker to guess the default credentials.
  • Excellent article: Mental Models I Find repeatedly Useful - This article covers many many models, which I've been talking about as well as plenty which I haven't. Especially liked the Deciding section: business case, opportunity cost, intuition, local vs global optimum, decision trees, sunk cost, availability bias, confirmation bias, loss aversion. Yet naturally all of the listed items were familiar. Virtual team is something, I've been talking for decades, and often been a part of from early 90s. I like high-context documents. There are many things, which are 'obvious' and therefore doesn't need to be mentioned. Technical Debt, such a classic. Unfortunately it's often hard or nearly impossible not to end up collecting (lots) of technical debt. It's a constant struggle. Zawinski's Law, hmm. Uncomftable laugh. Metcalfe’s Law aka Network Effect. Classics, MVP, Product/Market Fit. "First-mover advantage vs First-mover disadvantage", that's a very good question.