How to REALLY solve DbProviderFactories section can only appear once per config file error

Had this issue after installing SQL 2017, then installing SQL 2012 side-by-side and trying to create an SSIS package to load data from flat files to SQL. No idea if the dual installation caused it but it definitely caused many hours of frustration!

Many places on the web will have you only going to ONE folder to fix the machine.config file, took me much digging to find out there are two folders to check!

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

So here's how to do it:
First, go to one of the two folders listed above and you'll see the different versions. First go to the folder v4.0.30319 and open the CONFIG folder > Machine.Config file in a text editor (NOT WORD, use Notepad or something similar....oh hell, just use Notepad to be safe). In that file, search for the string “<DbProviderFactories/>”. If you found that, you'll see something similar to this (please note that this can be an issue with ANY provider, I'm showing DB2 here):




<system.data>
    <DbProviderFactories>
        <add name="IBM DB2 for i5/OS .NET Provider" invariant="IBM.Data.DB2.iSeries" description=".NET Framework Data Provider for i5/OS" type="IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26"/>
        <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
    <DbProviderFactories/>
</system.data>


The <DbProviderFactories/> doesn’t belong there so delete this line:

<DbProviderFactories/>

Save it and look in the other folders of the other versions for that line. It’s possible that this line also appears in v2.0.50727. Don't forget to check both folders (Framework and Framework64)!

Changing the service account for SSIS/SSAS, service won't start, error 1069

I've decided I'm going to post answers to issues that make me go ARGH instead of just sticking them into my Evernote; especially because it's frustrating when I Google the issue and can't find an answer then have to stumble around til I can get it fixed. The one today made me walk around with a red mark on my forehead from slapping myself when I finally got it figured out. Here's the story:

Where I work we had one domain acct for SQL. This was used to run all of the services, and also was a Domain Admin. I know, real secure. So we were migrating everything to a new, non-Domain Admin account, but still it's one account to run everything.

I'm sure you're rolling your eyes by now but hey, one step at a time!

Anyways, we had to change the accounts. So I wrote up a how-to: changing it in Configuration Manager, don't ever start services from there only in Failover Cluster Manager, don't forget to check the runas account in jobs,check credentials and linked servers etc.

So everything is going smoothly...until we get to SSAS. It wouldn't start after we changed the service account! This is the error:

analysis service could not be brought online with error 1069

So I tried it with my domain account. Still a no go, and giving me a bad credentials error.

I'm thoroughly confused because I'm not seeing any more information on what's going on, just the dang thing won't start. And, as we update more servers, I'm getting pings that SSIS is having the same issue.

Looking in error log, just says bad credentials, Looking in Cluster logs, no info there either.

And then the slap!

What do these things have in common? They're SERVICES! What do services need? LOG ON AS A SERVICE right! Yep, even though they're local administrators on the machine, that account also needs to be explicitly granted the Log on as a Service right, in Local Security Policy > Local Policies > User Rights Assignment.

I deserve my red forehead, but it's fixed :)