The problem:
Recently I was installing the stand-alone version of IT Analytics and the DLP Cubes and kept running into problems processing the cubes in regards to invalid username and password. When the cubes were processed within IT Analytics a large error was created but the full text was not generated. So I jumped in SQL Management Studio, connected to the Analysis Services, found my DLP Cubes and tried to process them. It was within SQL Management Studio and found I was getting an error with invalid username and password.
This error message confused me as I was using the same account that I installed SQL with and installed IT Analytics with. So I tried some troubleshooting and checked various roles within SQL and checked what permissions were set on the Database, Analysis Services, etc.
And it was time to panic, I had a demo today at a new to me customer and needed to have strong showing…. So it was off to Symantec Support to try and resolve the problem. I had great success getting my incident resolved at 3:26pm EST when my demo started at 3:30pm EST.
So here’s the solution
The solution to my problem:
The first part that was wrong with my installation and configuration was I did not read the installation manual all the way to the end and missed a step. Also there was another step that needed to be changed.
NOTE: After making these changes you will need to restart your SQL Server Services to make sure everything is updated and fixed correctly.
Changes made to the provider
This first step is documented in the install guide and I just did not read all the way through it. The OraOLEDB.Oracle provider needs to have a setting changed in order for the processing to work. We need to select “Allow inprocess” in order for the cubes to process correctly.
In SQL Management Studio, connect to the Database portion of your server and then we need to find the list of providers. This is found under “Server Objects -> Linked Servers -> Providers and then right click on the OraOLEDB.Oracle provider and select properties.
As shown in the screenshot, we need to select “Allow inprocess.” Check this box and select “OK”
Changes made to the Data Source
The next change that needs to be made to the ITAnalytics Data Source. This setting is found under the Analysis Server portion of your SQL Server. If you already closed Management Studio, you will need to reopen it up and connect to the Analysis Server, if it is still open connect to the Analysis Server. Under Databases find the ITAnalytics Database and expand “Data Sources” where you will find a listing for the “ITAnalytics” Data Source. Right-click and select properties and you should see something that looks like the following screenshot
What we will change is the “Security Settings from “Default” to the service account we are using for IT Analytics. When you click on the “…” a new window will open up, select “Use a specific Windows user name and password” and provide the correct information. My system looks like the following screenshot
Restart the SQL Server Services and your DLP cubes will process correctly.