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.
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