Connecting to Analysis Services in Another Domain

  • Ian Massi

    SSCertifiable

    Points: 5931

    Comments posted to this topic are about the item Connecting to Analysis Services in Another Domain

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting trick, thanks for sharing.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Kick6Tiger

    SSCrazy

    Points: 2368

    Very interesting indeed. I wasn't aware of this trick and it could have saved me a lot of headaches in my previous job. I ended up having to use VPN and RDP anytime I wanted to use SSMS with the cubes on a separate domain. Our application was configured to access SSAS via http, but that method doesn't work with management studio.

    Aigle de Guerre!

  • Ian Massi

    SSCertifiable

    Points: 5931

    It took me some time to figure out, but I just figured there'd have to be some way to use runas to make it work. I can imagine the VPN/RDP solution would have been quite a headache to maintain. At least more people know now!

  • pedro.rivera

    Grasshopper

    Points: 11

    I tried this in the past with SQL server data tools and Excel, it works, but it causes authentication issues with my local domain, I get kicked out of my local domain (e.g. can't access shared drives, can't connect to Exchange, etc.) and my AD account is locked for 30 minutes, which is the setting in our AD environment. Has anyone experienced this problem?

  • Daniel Parkinson

    Old Hand

    Points: 300

    I was suffering with this same problem when I glanced at the SSC home page and saw this heading! Thanks for the tip. Now I can get to work from my local machine instead of having to fight for a remote desktop session!

  • Ian Massi

    SSCertifiable

    Points: 5931

    @daniel-2 - Glad it helped!

    @Pedro - It sounds to me like there's something else hooked into Excel that is trying to access resources in your local domain. You wouldn't be able to access shared drives using an instance of Excel running under the credentials of the second domain since those credentials are invalid in your domain. I imagine if you brought your domain admin a coffee and put your heads together for a bit, you might be able to trace out the connections that are being attempted.

  • jpr3ss

    Grasshopper

    Points: 17

    This approach is great however it appears to not work when you have other instances of Excel open that are not using the RUNAS credentials. I wonder if you’ve come across and/or if you know of a work around. Thanks for a helpful post.

  • Ian Massi

    SSCertifiable

    Points: 5931

    Doesn't seem like I've run into the same issue you've had. I've had 2 instances open at the same time with different credentials and it's worked fine until I lost track of which one was which but that's about it. I haven't heard from my users about issues having Excel open in both domains at the same time either. We're still using Excel 2007 here anyway, not sure of the level of Active Directory though.

  • kaharm

    SSC Enthusiast

    Points: 103

    Thanks for sharing it!

    Do you have experience creating Analysis Services connection string in SSIS to connect to Analysis Server in different domain? I'm working on figuring out a way to schedule a SQL Agent job to process SSAS cube located in different domain. Any idea/suggestion is highly appreciated.

    Thanks,

    Irina

  • Ian Massi

    SSCertifiable

    Points: 5931

    kaharm (12/2/2015)


    Thanks for sharing it!

    Do you have experience creating Analysis Services connection string in SSIS to connect to Analysis Server in different domain? I'm working on figuring out a way to schedule a SQL Agent job to process SSAS cube located in different domain. Any idea/suggestion is highly appreciated.

    Thanks,

    Irina

    Hi Irina, I can't say that I've tried something like that before. I suspect you'd need to build something between the job and the SSAS cube. Maybe you could get away with using CmdExec running something called ASCMD? Here's a link to download it:

    http://msftasprodsamples.codeplex.com/wikipage?title=SS2005!Readme%20For%20Command-line%20Utility%20Sample

    You'd have to install it on the server but perhaps if you run this from a job using CmdExec that has the RunAs and pass it the command to process the cube, it'll work? I haven't used ASCMD before but if it works be sure to share your result!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply