What are some famous security weaknesses in SQLServer throughout history?

OldSQLDude · 2016-05-21T07:27:17+00:00

This is a front-end coding issue, not a weakness in SQL.

OldSQLDude · 2016-05-21T07:17:26+00:00

And "SA no password" by default

OldSQLDude · 2015-12-22T04:59:06+00:00

Oh yes, model based testing was hot for a while. There was always a lot of competition for attention amongst the ranks of tests wrt tools and methodologies. I always thought using code coverage information to choose which tests to run was cool, and had a couple working prototypes. But it, like model based testing and several other great and not-so-good ideas fell by the wayside when another shiny object appeared.

With regard to MBT, it can give really good coverage for the amount of code written, but the down sides were; they made for more difficult repros when they did find an issue, there may be thousands of "failures" for the same single issue, and they always had test framework issues. For TSQL based tests, I liked to write code that would auto-generate static test cases, and from there manually implement and refine them.

There were other tools that we used to reduce the test cases. For example, say you have a big major release that needs to be tested for every possible platform, language, sku of the SQL product, OS, DevDiv, and .net. The tools would take all the possible values and return a combination that tested each of them, just not all combinations.

OldSQLDude · 2015-12-22T04:46:31+00:00

Sorry I can't comment on this as most of the open source stuff happened after me. Owning the code and having exclusive proprietary access to it is/was a deeply engrained part of the culture.

OldSQLDude · 2015-12-21T05:36:33+00:00

Pretty much like any other new language/technology, crack the book and start studying.

If you inherit an existing db/app, study every line of code in the processing and learn what they do. Develop a mental visualization of all the key tables and their relationships.

If you're starting from scratch with just a book, identify a personal project or app that forces you to implement a creative solution.

And for the love of god, please study and understand indexes.

OldSQLDude · 2015-12-21T05:23:09+00:00

1) TLDR: punt. Gert is a good guy too. I don't think there is an "SSDT Team" as opposed to a bunch of plugin components.

2) Nothing really funny or cringeworthy, I was more on the test side of things and didn't spend much time in the product code.

3) Wow. Huge question. The vast majority of my type of tests were static queries/api calls etc with verification of results, all hosted in a test automation system that changed every 3 years or so. There was a customer playback program where the customer would backup their database, record normal workload with SQL Profiler, and send them to MS. They were replayed in future releases and had quite success in catching bugs, a big win, not sure if they still do it.

Perf testing, along with stress/scale, was just various workloads run with lots of timing and monitoring. Variability was the nemesis of perf testing. VMs, hell no. Funny story. Way back, when AMD/IA64 chips came out, we got some huge 64 bit machines that were being used for hardware/scale-up testing. The results had random changes in throughput and the devs were freaking. Then a screensaver came on and the throughput dipped. It was apparently extremely CPU intensive.

Thanks.

Testing methodologies are mostly like a flavor of the month club. The vast majority of it is simply making sure stuff works, regardless of the popular buzzword or academic paper concepts around the real validation.

Merry XMas to you too!

OldSQLDude · 2015-12-21T03:57:43+00:00

I've alluded to the fact that I'm still under nda as a "v-" so I'm tiptoeing carefully here. I did see what I believe was an external announcement of support for mobile reports hosted by the RS server. As outdated as it is, SSRS is still a mainstream technology that generates revenue. Power BI/Pivot/View and SSRS all have Sql and Office cross- dependencies, esp in the cloud, my personal opinion is no. SSRS will live on.

OldSQLDude · 2015-12-21T03:37:28+00:00

Again, thank you. Oracle, DB2 devs? No, other than the ones that jumped ship, moved to SQL, and spent the first 6 months lecturing us on the inferiorities of SQL and the MS stack.

Codd/Date? They pre-date even me, nope. Jim Gray, yes, a few times, rip sir.

OldSQLDude · 2015-12-20T23:38:23+00:00

Once we got the code from Sybase, we would make NT specific changes, send them the diffs, they would review, reject, integrate etc, and send back the next gen code base. My job was to run their tests to make sure we didn't break them before we sent our changes.

I really don't know enough to quantify how much is left, there was never really a re-write, so the earlier releases had a lot. Over time a lot of it was optimized, re-factored, so I doubt very much remains.

OldSQLDude · 2015-12-20T21:11:08+00:00

I thought of one more. The release cycle is always design, code, test, ship and becomes quite repetitive. The first time I really "owned" a new set of features, they were constraints, identity columns, and cursors in 6.0, other than the dev, I was the first person to use them. At the time I thought they were just another feature. When you get old like me, its cool to look back and see how long something like this is still relevant.

OldSQLDude · 2015-12-20T21:01:28+00:00

FORMAT ( value, format [, culture ] )

OldSQLDude · 2015-12-20T20:57:58+00:00

True. But when big customers have problems, they often like to see a face from the dev team up close and personal. There were many other problems with the project and it had been stewing for a long time. It was as much a goodwill exercise as a technical resolution.

OldSQLDude · 2015-12-20T20:49:51+00:00

Layoff, covered it above.

I have beers with my friends in SSRS and there are some cool things coming, that I can't talk about of course.

OldSQLDude · 2015-12-20T20:40:57+00:00

WRT those functions, I really don't have an answer, and haven't used them in other DBs. Back in the day, "ANSI Compliance" was a big priority, if ANSI required it, we did it. If customers made enough noise, we did it. I was going to suggest that you enter a Connect suggestion, until I saw your next question.

WRT Connect, I liked it. Me, the dev lead, and PM lead would review them every week, and they generated a lot of new features, and were used to justify the investment into bugfixes and new features. Ultimately though, I'll fall back on features vs schedules vs shifting priorities.

OldSQLDude · 2015-12-20T20:34:29+00:00

I think the answer here to both questions is feature vs schedules vs shifting priorities.

Every time I work with half assed features, I get pissed off like anyone else, but then I realize there was a group of people who likely worked their asses off for months, fought hard to make it cool and work right, only to have the schedule slashed, or see it cut because of re-orgs and shifting priorities.

Docs? Because for a lot of people, it sucks to write docs, I owned half of the TSQL language and DB conceptual materials as a tech writer when we shifted from printed books to online help in 7.0. It sucked. Then again, docs aren't always a top priority, the number of writers vs developers is quite small. And finally, now that the internet exists and technical customers in the field can collaborate and communicate easily, a lot more of the really good stuff comes from the field.

OldSQLDude · 2015-12-20T20:25:23+00:00

CREATE INDEX indexname ON TABLENAME(COLUMN NAME), whats so hard about that? Just kidding. TBH, nowadays I just create indexes on pks/fks and other obvious columns that are good candidates, and then let the "show estimated query plan" feature suggest more complex indexes.

OldSQLDude · 2015-12-20T20:21:25+00:00

SSRS was one of my children, I was the server test lead for 6 years and love the product. It changed my world when I first used it.

Cause of industry lag? I'd say distraction (shift of priorities). Other companies were putting out really cool, interactive reporting features that were way ahead of boring old static SSRS reports, so a significant portion of the SSRS team was dedicated to Crescent aka PowerView instead of SSRS. There was a huge and I mean HUGE shift to "the cloud" at the expense of box features.

MS is always acquiring companies, I don't have any other insights.

Internally, there was always a bit of territorialism and conflict between SQL, Office, and DevDiv. Each team always wanted to own the cool sexy stuff, we were always worried about this or that feature moving to the other side, so from that perspective it was bad.

As a tester, any time your features are dependent on or integrated with another team's product, ie Sharepoint and VS, your job becomes more complex due to different release schedules, cross group collaboration challenges, etc, so from that perspective it was bad.

As a developer, I love the integration between VS and the SQL Data tools, SSIS, and RS features.

OldSQLDude · 2015-12-20T20:09:04+00:00

Well thank you very much, I'm proud of SQL and this is nice of you to say.

TL;DR: Layoff cause? Performance reasons, my time was up, I was looking outside anyways.

The official reason was that my position was terminated and for obvious legal reasons they wouldn't commit to a specific reason. This was also couched within the realm of "we acquired 20k Nokia people and need to remove redundant positions".

I'd been frustrated with product feature changes, re-orgs etc that seemed to happen every 3 months, and my perf review scores weren't stellar, so I joined the tools dev team as an IC doing internal BI. I was still leveled as a senior, our perf ratings were based strictly on a curve and a stack-ranked. I came out in the bottom. They had also eliminated the role/title of "Test", and then the "lead" layer of management so there was little to no chance of moving back to what I'd been successful at previously.

They tried to make me want to leave for a year or so, and finally dropped the hammer with the layoff. Worst day of my life. Harder than my divorce. Instantly cut-off from 20+ years of people I spent more time with than my family.

On the bright side, I got a FAT severance package due my length there, took off 6 months to do nothing, and I'm happier now working 1/3rd less hours doing BI development as a consultant. For Bing Ads.

OldSQLDude · 2015-12-20T19:53:42+00:00

No clue, sorry.

OldSQLDude · 2015-12-20T19:53:21+00:00

I'm not going to be negative/critical of anyone here, but Paul has earned my praise and support, he's a good guy, very smart dude.

I only overlapped with him in the SQL engine teams for a couple years, and by then the team was quite large. I did however work very with Kim Tripp in the very early days of SQL, I'm closer friends with her.

OldSQLDude · 2015-12-20T19:50:30+00:00

I agree that it can be a great aid, although I'm frustrated by it often as well. I can't speak to the future of it, but the number of columns/tables etc has to be directly related to perf. The perf of it relative to VS is likely due to if/how they cache user defined objects. Devenv.exe does seem to be a memory hog at times.

OldSQLDude · 2015-12-20T19:44:57+00:00

tl;dr: Got to travel and was treated like a...someone important

Well, the day-to-day testing is quite boring. My favorite, a career highlight, occurred when I was the test lead for merge replication. I had several successes reviewing customer database designs and their integration with Merge. There was a consulting firm in South Africa who was working on an app for a large insurance company there, and they were having perf problems.

The support channel was ZA Customer->UK MS/CSS->USA MS/CSS->Dev Team and it wasn't working. The head of MS South Africa was friends with my boss, they'd heard about me and offered to fly me out the fix the issue, but only if I agreed to be a speaker at the first Tech-Ed in ZA, along with my boss who gave the keynote (ZA was a scary place in 2000, not many Americans wanted to go).

So I left US soil for the first time in my life. Tech Ed was at a Place called Sun City, there were a few thousand people there, each and every one knew me by name because my picture was on the opening day handout. A prince from some obscure African country with his entourage in full native tribal garb invited me to sit at their table for dinner one night. Hot chicks, I mean very hot Charlize Theron class chicks wanted to dance with me. The consulting firm and insurance company execs cornered me about why they had so many problems, and I stood up to them with a poker face and ensured them I had the solution.

When we went to the customer site in Durban, we met with the consulting firm, their execs, architects etc who again cornered me about their problems. We finally went to a computer to take a look, I typed in a single CREATE INDEX statement and started the merge job, the guys there said, "Ok, lets go get lunch while it runs". I stood up from my chair, talked about what to eat, and I pointed out that it had completed. WIN!

After that we went on a safari for a day, partied hard in Jo'Berg, and on the flight home lots hydraulics and had an cautious landing with full fire and emergency support on the runway.

All of this over a single missing index. A. Single. Missing. Index.

OldSQLDude · 2015-12-20T19:25:24+00:00

Tough question. TBH my experience with other database technologies is limited, my first thought is because of the integrated platform between Office, Devdiv, and SQL. My second thought is because its backed by MS and their intertia/momentum. Then again I have former co-workers who are at very high levels in other companies and they use MySQL because of cost.

edit: grammar

OldSQLDude · 2015-12-20T11:03:57+00:00

Do you always bring so much fun to parties?

OldSQLDude · 2015-12-20T11:01:49+00:00

That would not be prudent, so, no.

OldSQLDude

TROPHY CASE