Let everyone connect as DBO!
Continuing the discussion from Navicat WTF:
Can you connect navicat to the database by a user without ALTER permissions? This is obviously only helpful if you only use navicat to read the database, unless you fetch a priest and give them ALTAR permissions.
So, the Navicat thread got me thinking about the giant ball of WTF that is our primary billing system (and how our
programmermanager in charge of reporting out of it interacts with it).
- The program front-end is a very low-def "look mom, I just learned Delphi 2.0 forms" type of PC app. Mercifully, it is at least 32-bit, so it does run on modern systems, but it relies on making an ODBC connection (and if you let the setup installer create the connection, it defaults to using the Windows native SQLSRV32.DLL driver, rather than bundling the SQL Native Client from TechNet or such).
- The connection that you then make within the program using your ODBC connection requires that the user that connects to the database is DBO. Obviously, no security hazards at all there, we're good.
- Their reporting process allows you to create custom reports... using Crystal Reports. Now, I'm not sure whether this is a program limitation or a limitation of my manager's programmering abilities (though I lean the latter), but the Crystal Reports process as we implement it cannot specify database.schema.table for which table to access. So if you decide to extend the billing system with an additional database for things like inventory tracking (since the inventory tracking in the billing system sucks giant purple dildos), you can't re-use any of the table names, or your Crystal Reports will go boom.
- I suspect the CR limitation is my manager in large part because he persists in doing all of his maintenance tasks manually... in Access... Not to mention whenever he needs to go plumbing for things in order to try to build a report or whatever, he steadfastly refuses to connect with an ODBC connector that is set up with limited permissions (though, since the client that every employee uses on a day-to-day basis always connects with a DBO user, I suppose that's not an escalation in security holes...)
- Their system supports scheduled tasks, for batching payments, sending reports, etc etc. You get a nice little interface within the client where you can choose frequency of trigger, action to perform, the whole nine yards. It kinda looks like what a six year old might re-implement the Windows Task Scheduler interface as. So, the server side runs the scheduler within a service that gets set up at install time, right? Nope! Well, at least the installer (which has to run elevated and all) will create a default scheduled task that you can modify as needed for timing? Nope! You get to manually create a Windows Task Scheduler task! Better still, the task will work as follows:
-- Windows task fires, spawns off a process of the billing system client that logs in as the user the task is running as (hope you remembered to do all the ODBC and other registry entries so that user can open the client!)
-- Billing system client spins up, looks for any tasks which have a "scheduled next run time" <= NOW.
-- Tasks get executed, scheduled next run time updates, billing system client exits, Windows task shows successful run.
-- Things wait for next Windows task firing before anything else will ever happen again. That's right! If you schedule things hourly in the server, but the admin set up the Windows task to run once a week, well then fuck you, your scheduled tasks will run once a week!
Oi. And, of course, this has become the system that runs our everything (authentication for customers, integration with the e-mail servers, etc etc etc). So after a few incidents created because they originally installed this on a C2D (desktop) processor box with non-ECC RAM and a couple desktop hard drives mirrored with the (desktop) motherboard's on-board RAID -- shockingly, as the load on this system has increased this year, the system tends to fall over when the authentication load suddenly spikes! -- I get to upgrade it this weekend.
And for better or for worse, I insisted on "Doing It Right", so we're migrating to a SQL 2014 Always-On AG, and splitting the front end web server to a different VM. *headdesk*
This was boooooring!
I like the custom made scheduler that relies on Windows Task Scheduler.
Which is, of course, not amazingly well documented either. Their documentation basically references that you have to create the Windows Task, but was apparently written back in the dark ages before service accounts were invented, so doesn't make any reference to the fact that the user the task runs under effectively has to be able to open the desktop client.
Nor does the documentation reference the whole "tasks will run at the lesser of the requested interval or the Windows Task interval", but that is at least disco-coverable.
requires that the user that connects to the database is DBO
As WTFey as this is, I've walked into an environment like this every single time I've taken a new job. In the current job, the guy who was already here actually defended the practice with the statement "we've never been hacked".
Their reporting process allows you to create custom reports... using Crystal Reports
How do you "allow" someone to use Crystal Reports? That pretty much means that there is no reporting system and customers are encouraged to go buy Crystal Reports. And, yes, everything you've experienced with Crystal Reports is a CR WTF, not specific to your situation.
This was boooooring!
Needs more link-bait sentences. Perhaps "When I saw the front-end, my jaw dropped" and "5 reasons our billing system is TRWTF" have a place here.
Nah! It's just, I don't know, boring! The whole database stuff is always so boring, with the reports and the managers. Boring! It's like the accountants of the IT world.
So, having completed the migration to SQL 2014 AAG, we've found a few more WTFs with our billing software!!!
- The Crystal Reports 11.x SDK doesn't support accessing stored procedures while connecting to the AAG with SQL Server Native Client 11.0, but it does work with 10.0 or older.
- No big deal, right? Just use the older driver. Weeeeeeellllll... the NC 10 driver (from SQL Server 2008/R2) doesn't support MultiSubnetFailover. What's MultiSubnetFailover for? Well, if you set up an AAG on multiple subnets (say, for georedundancy), the AAG will always register all of its possible cluster IPs in DNS, and then rely on the MultiSubnetFailover parameter (new in the SQL Server 2012 Native Client -- aka 11.0) to cause the ODBC connection to try all IPs at once and form the connection with whichever one is active (which is the only one that will respond). If you're not using MSF, then you get to rely on your connecting program timing out fast enough on the first IP and failing down to the second IP before the max connection timeout is reached and the ODBC connector gives up. Naturally, the way to configure this is well hidden and will require $largeConsultingFee for us to get a plugin developed.
- Even with the SQL Native Client 10.0 drivers, and the special version of the 8 reports that had a specific SQL Native Client version because Crystal Reports, 3 of those 8 reports kick errors and 4 of the other 5 return blank results. Much re-writing of reports has ensued.
sister parentit's complicated companies are using Trend Micro AV (except for the one office that is using AVG CloudCare), and Trend Micro shits all over the MultiSubnetFailover approach to connecting to an AAG. 3 weeks of trading e-mails with support later (and the support drone has been pretty helpful and pleasant), Trend Micro has confirmed with me that the problem is probably related to their filter driver, though it might still be TCP Chimney and would you please follow this TechNet article to turn it off1. AVG also breaks everything, but they just flat out haven't responded to me. The IT guy for the other companies is throwing a fuss about not wanting to switch to Symantec Endpoint Protection, but since our main office uses SEP and isn't having problems, I'm (probably, eventually) going to win that argument.
Why do I work in IT again?
1Said article, of course, explicitly states that TCP Chimney is off by default in new installations of Windows, and I already let the support drone know earlier in the support process that the computer I'm testing things on for him is a clean load with nothing but our billing software and his AV on it.
Oh, and vis-à-vis the CR thing...
Crystal Reports 13 works wonderfully with the SQL NC 11 driver and stored procedures.
Me: Do you have an ETA on the necessary software from CR?
Support - Lead Developer: I'm afraid it's not possible for us to fix the problem. Crystal Reports 2013 only works from .NET. Since Platypus is written in Visual FoxPro, it would require a rewrite. The only reason I was able to confirm the problem was fixed in Crystal Reports 2013 is because of some C# prototypes I wrote up last year. So, we have to made due with Crystal Reports 11.5
head, meet desk. desk, head.
One of my pet projects had almost convinced me this was a good thing to do.
Almost. Those were fairly dark times.
I'm still trying to figure out how to properly schedule an instance of Excel to start up interactively without the user being logged on that has network privileges that also dynamically updates the user password when the 45-day max password age expires without hijacking any possible existing Excel process.
Yeah, I might submit my project one day...