Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication

Technically when you connect to another network through a VPN connection you can see all allowed machines on that network. So it is easy to connect to a SQL Server instance using SQL Server authentication. However, I’m explaining this part for some of you guys that might be new to connecting from Excel directly to a database on SQL Server and create flashy reports on Excel.

But, what about connecting directly from Excel to a remote Analysis Services instance without using Windows Authentication? You’re right! I’m saying you can connect directly from your own Excel to a remote SSAS server without using windows authentication. Well, technically there is no SQL Server Authentication mode available for Analysis Services. So what does that actually mean when I say “without using windows authentication”? If you’re interested in finding the answer keep reading this article.

Scenario:

You’re working as a BI consultant, you’ve been told that a client needs to have some simple reports on Excel as follows:

·         You should connect to the client’s server using a provided VPN connection

·         The VPN connection could be established through a Windows VPN, Cisco VPN etc. so the VPN client or the port and protocol used don’t actually matter

·         Microsoft Excel is NOT installed on the client’s server

·         You’re NOT allowed to install Excel on the server

·         As it is a costly process the client will not setup a virtual machine in their network so that you can remotely connect to it and install Excel then connect to their SQL Server/Analysis Services instances

·         There is no trust relationship between your network and the client’s network, so your domain user name and password could not be authenticated on the client’s network

·         The client needs to have some reports on Excel on top of a SQL Server database and OLAP cubes on Analysis Services (SSAS)

·         You have the right to run an application as administrator on the remote server

·         You need to connect to the remote server directly from your own Microsoft Excel installed on your machine

·         The client also provided a remote desktop access to the server

·         On the remote desktop SQL Server Management Studio (SSMS) is installed

·         In the remote SQL Server your account is a member of the “securityadmin” server role so you can create a new SQL Server Login

Continue reading “Browsing Cubes Remotely from Excel Trough a VPN Connection without Using Windows Authentication”