How to sync SSAS 2008R2 databases via HTTP(S) across domain/forrest and firewall boundaries?

Question

Hey People,

I'm fairly new to SSAS and SSIS, but I've ended up needing to sync an internal SSAS DB into our DMZ SSAS instance.

This crosses domain/forrest, subnet and firewall boundaries.

I've been reading about using IIS to publish SSAS internally.

On an internal -> internal environment (same domain/forrest, subnet and inside the firewall), I can get it working using Windows Auth in IIS.

However, when I try to switch to HTTP Basic auth in IIS, it doesn't appear to attempt to authenticate. (Yes, I'll change this is HTTPS Basic auth later)

I'm using a basic SSIS package, running on the destination server (it's running SQL, SSIS and SSAS), which just consists of an Analysis Services Execute DDL Task containing the following XMLA:

<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Source>
    <ConnectionString>Provider=MSOLAP.4;Data Source=http://10.x.x.x/olap/msmdpump.dll;ConnectTo=10.0;UserName=DOMAIN\USER;Password=PASSWORD;Initial Catalog=SSAS_SOURCE_DB</ConnectionString>
    <Object>
      <DatabaseID>SSAS_DESTINATION_DB</DatabaseID>
    </Object>
  </Source>
  <SynchronizeSecurity>CopyAll</SynchronizeSecurity>
  <ApplyCompression>true</ApplyCompression>
</Synchronize>

I've seen lots of posts and comments about hooking the likes of Excel into an HTTP published SSAS and being prompted to authenticate, but this needs to be an automated process.

Anyone got any ideas? or can you point me at some decent documentation, as the MS docs don't seem to go any further than "here's how you use IIS to publish SSAS"

Note: this may be better served in the SSIS, so I'll cross-post and link them together.

SSIS forum

Thanks
Craig

Answer 1

Hi Craig,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

Thanks,

---

Bin Long

TechNet Community Support

Answer 2

Hello Craig,

Thanks a lot for your post. Please check the IIS log, take a fiddler trace and a profiler trace to see if the connections are going through, to IIS and ultimately to SSAS. Here is one of the previous post where the similar issue was addressed:

http access using msmdpump and basic authentication

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/79d2f225-df35-46da-aa22-d06e98f7d658

While the link shows what works, for your scenario, the log might show ultimately why it is failing.

Also, you can try testing, outside of SSIS, using an ADOMD.NET sample application (reference: http://technet.microsoft.com/en-us/library/ms123487.aspx to see if that gives you the same behavior, to isolate if something needs to be changed in SSIS.

Hope this helps.

Thanks.

---

Meer Al - MSFT

Answer 3

Hey Meer,

thanks for that. From SSIS (or BIDS) the IIS logs show an HTTP 401.2 error (Logon failed due to server configuration) and no-attempt to retry.

If I go to the same URL with my browser http://10.x.x.x/olap/msmdpump.dll the logs also show a 401.2, but then my browser retrys, prompting me for a login, which I give, but then get an HTTP 500 error (in my browser, however the IIS log has an HTTP 200 status, presumably because the call has been passed on to SSAS, which then returns an error), which presumably is because I'm calling the DLL directly, with no parameters.

I'm not sure fiddler will help, as I'm not sure if I can force SSIS to go via a proxy (is there a config for this?), but I can give NetMon a go and I'll plug a SQL trace in to see if anything is getting through, though I suspect not.

Thanks
Craig

Answer 4

Hey Meer,

OK, plugged in NetMon and SQL Profiler.

NetMon shows two HTTP+SOAP requests coming from the destination SSAS server, both being returned with HTTP 401 Unauthorised.

SQL Profiler shows nothing at all.

Looks like I'm not getting past IIS, which is what I would expect.

I might try anonymous auth, just to make sure I can put a connection through.

I'll give that code sample a go too.

Thanks
Craig

Answer 5

Hey Meer,

OK, next update.
Running IIS with anonymous access works fine, as long as "NT Authorisy\IUSR" is in the list of server administrators for SSAS on the source server.  That's not going to happen in production, but at least I know the concept of syncing via HTTP/IIS works.

Next I'll try the ADOMD.NET sample app and see if that reveals anything.

What I am noticing is that the user/pass provided in the connection string is being completely ignored and only the IIS context user is being used to authenticate with SSAS, so if I can just find a way for SSIS to provide HTTP Basic credentials to IIS as part of connecting...

Slow progress...

Later'ish
Craig

Answer 6

Hey Meer,

OK, just tried the code sample, which is full of bugs (at least on my VS.Net2010, targeting Net3.0, with ADOMD.NET 2008R2SP1 installed).

After correcting the backslashes (\) to forward slashed (/) in the URL for SSAS, correcting the path to msmdpump.dll, correcting "ProtectionLevel" to "Protection Level" and "PktPrivacy" to "Pkt Privacy", I ran the code inside VS.Net and I get an HTTP 401 unauthorised error.  The app only makes a single attempt to connect and doesn't retry with credentials.

So then I read HERE that the "Integrated Security" setting should be set to Basic ("An HTTP connection can use only the setting of Basic."), but if I set that in the connection string, I get an exception "The integrated security 'Basic' is not supported for HTTP or HTTPS connections."

So now I'm stuck again. So far, the only way I can see this working is to use Windows Integrated security in IIS and have the connecting App run under an account, which would be possible internally, but I need to go across forrests.

Any ideas?

Thanks
Craig

Answer 7

OK thanks to some help from MS Premier Support, I fixed up the connection string in the ADOMD.NET sample (it's soooo wrong), be be based on:

"Provider=MSOLAP.4;Data Source=http://SERVER_NAME/olap/msmdpump.dll;ConnectTo=10.0;User ID=xxxxx;Password=xxxxx;Initial Catalog=DB_Name;"

And now the code sample works over HTTP/Basic.

However, my SSIS package was already using a similar connection string and it still doesn't work.

I've sent a NetMon packet capture and SQL Profiler trace off to MS Premier...

Answer 8

Hello Craig,

Thanks for the update. Since you are now working with your premier support, they would be able to best help, looking at the netmon and profiler trace, why it is failing. It is possible that we are unable to delegate the user, from the IIS server, to the Analysis Server but, without looking at the traces, it would be hard to understand why it is failing.

Please continue working with the MS Premier support to resolve this issue.

Thanks.

---

Meer Al - MSFT

Answer 9

RESOLVED!

Yay!

Turns out the ConnectionString was wrong:

UserName=DOMAIN\USER

should be

User ID=DOMAIN\USER

Doh!

Thanks Premier Support!