This is a followup to a post I made on the Network54 Essbase forum: Yes, you can use Windows integrated authentication (indirectly) to perform Essbase data loads. It’s not necessarily supported, but it works.

First you start off with the connection definition in the Windows ODBC connection manager. This is where you setup a normal ODBC connection to SQL Server:

ODBC connections in Windows to be used by Essbase data loads

One of the configuration screens is where you specify the credentials, like this:

Connecting via integrated authentication

The SQL Server connection configuration lets you specify how to connect to the database instance. I would guess that much of the time this is configured with a particular native SQL Server ID. Note that it’s also possible to connect using “Windows NT authentication”. You can select this option to try and have the local machine connect via its own credentials to the SQL Server instance. That is, the user running the Essbase service on the local machine would need to have access to the given SQL Server instance.

With the ODBC connection setup, you can reference it in a load rule as normal:

Next when you go to test the connection/data load, just specify anything you want for the user name and password when it prompts you in this dialog box:

I have gotten into the habit of typing in ‘dummy’. As Peter Nitschke unhelpfully points out, in this case you I don’t need to document the automation author because because it’s already documented (heyoooo). Pete, I know you read this, by the way, is that rash cleared up yet? You actually have to put in something, otherwise the load won’t work.

Similarly if you do a normal data load, you have to put something in:

Essbase Data Load from SQL Load Rule

Or else you’ll get this helpful error message:

The error when the username is not specified

Do note that the username and password are both discarded or otherwise unneeded assuming the network credentials are sufficient to connect to the database. As a somewhat interesting aside, this seems to be a quirk in the Essbase code path that processes the data loads. It guards against NULL (empty) values and if it picks up one or the other for username and password, it errors out the load. If memory serves, you can get away with only filling in a username in the data load rule SQL editor test but you need both when doing a data load. This is due to differences in how the EAS GUI is verifying data. Also, if you write MaxL automation that relies on this Windows integrated authentication, you need to fill in some dummy or otherwise ignored values in the MaxL.

My guess is that Essbase actually tries using the username/password but when it is able to make a successful connection via the integrated authentication, the SQL Server driver just ignores any other connection efforts, so the “bad” username and password have no effect.

Apparently connecting this way is unsupported but I have set this up and used it numerous times without any issue. I think if Oracle really wanted to polish this up and make integrated authentication a more supported option, that’d be great, and I might start with some consistency on the EAS side of things as well as a modified MaxL syntax alternative that lets you explicitly specify integrated authentication (or put another way, a MaxL syntax that doesn’t need username/password in it).