0

We have a company we recently acquired and we would like for them to access our SQL Server Analysis Services (via Excel file) on our company's domain. They are external users with separate Windows domains.

I was reading about AD Forest trusts and I don't know if this is the right path. If we set up a trust relationship between the two domains, can I add NewCompany\NewUser to our SSAS security role and they would have access?

It seems like if they're in Azure, following the below would work? https://docs.microsoft.com/en-us/azure/architecture/reference-architectures/identity/adds-forest

The end result I want is:

  • for NewCompany\NewUser to refresh the cube/PivotTable in Excel on their PC without having to VPN (this is the key thing)
    • edit: Using a jumpbox would be ok too.

Is this possible and if so, is there a high-level overview of the steps needed? Would I be able to add NewCompany\Sam to SSAS security role, for example?

I'm a software developer so don't know much about infrasture/AD. This is mainly for my curiosity - I don't think we'll implement this. Thanks!

My company uses Windows Server 2016. New company uses Azure AD Services.

Azure AD Services forest trust

Gabe
  • 167
  • 8
  • Don't use user accounts in a SACL/SQL role; stick with groups. – Semicolon Jun 07 '19 at 18:47
  • 1
    Are their workstations also in Azure? I mean, the diagram covers the trust itself - and access from the on-premise AD environemnt to the cloud AD environment, but it doesn't cover access from a separate on-premise environment. The clients would still require access from their premises to your premises - and I don't think you can (or would want to) route that through Azure and back. – Semicolon Jun 07 '19 at 18:52
  • @Semicolon - I see what you mean. Their workstations are not in Azure. The diagram shows a jumpbox, could they just remote into there and use Excel connecting to our company's SSAS? I understand that's slightly different than my original demand (extra step of jumpbox) – Gabe Jun 07 '19 at 19:17

1 Answers1

3

I think there are two questions here. Trusting an acquired company is usually problematic, and you're definitely in VPN territory. If you create accounts for them in your forest, that would obviate the need for a trust to access your application.

How they get network access is a separate worm can. If it's only for SQL Server, it's possible to access SQL Server over TLS using a certificate, but this would only be secure if it were required at the server - not something everyone is prepared to do. If that were in place, creating a DSN with the credentials and server name/port to access a database in Excel is fairly straightforward using the Microsoft ODBC Driver 17 for SQL Server:

https://www.microsoft.com/en-us/download/details.aspx?id=56567

Greg Askew
  • 34,339
  • 3
  • 52
  • 81