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