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

The VPN connection could be varied from client to client. Some use Cisco VPN connection, some use Windows VPN, Fortinet VPN and so on. Actually it doesn’t matter at all which VPN Client you’re going to use. What matters is that you can connect to the client’s network using the VPN connection provided.

·         Open command prompt on your machine and ping the client’s server to make sure you can see the server from your machine

·         Connect to the server using remote desktop. Use the user name and password the client provided

·         To connect to the remote SQL Server instance from your own Excel:

a.       Open SSMS on the remote desktop and connect to the SQL Server instance

b.      Check the SQL Server authentication mode. To do so, right click on the server and click properties. Then select the “Security” page. In my sample the server authentication is already set to mixed mode.

c.       If the server authentication is set to “Windows Authentication mode” then click on “SQL Server and Windows Authentication mode” and click OK

d.      As you see SSMS says you need to restart SQL Server to all configuration changes take effect. Click OK

e.      Make sure you are allowed to restart the server. Especially if you’re doing this in TEST or PRODUCTION servers. Restart SQL Server by right clicking on the server and click “Restart”

f.        In SSMS expand the server, expand “Security”, right click on “Logins” then click on “New Login”

g.       Configure the new login as below

 

h.      So far we created a SQL Server use login. We’ll use this user login when we want to connect to the remote server from our own machine.

i.         Open Microsoft Excel on your own machine

j.        Select “From SQL Server” from the ribbon

k.       Enter the remote server name. Click “Use the following User Name and Password” then click Next

l.         Select the database from the dropdown list. Then select the table you need to create the report on and click next then click finish on the next page.

m.    Now you can create the report on your own Excel pointing to a remote server on the client’s network.

·         To connect to the remote Analysis Services instance from your own Excel:

a.       Open “Control Panel” and click on “User Accounts” to try to create a new “Local” user account. Obviously, you don’t have access rights to create a “Domain” account.

b.      If you couldn’t create a “Local” user through the “User Accounts” UI, then open CMD as administrator. Type the below command, put a particular user name and password as you want and press enter:

 

net user <YOUR USER NAME HERE> <PASSWORD HERE> /add

 

c.       After pressing enter, if CMD says “The command completed successfully.” then you’re good to go.

d.      The above command creates a new user account with the password you typed on the local machine. The user access level for the new user would be “Standard user”. You can check this from “User Accounts” in Control Panel.

e.      If CMD says “Access is denied.” then you need to contact the client’s system admin asking for create a local user with “Standard user” level of access. You’ll need this user to be mapped on an SSAS role accessing the OLAP database that you’re going to create the report on top of it.

f.        Open SSMS and connect to the corresponding Analysis Services instance and expand “Databases”, expand the corresponding OLAP database and right click on the “Role” and click “New Role”

g.       From General page, enter a name like “Reporting” for the role then tick the “Read definition”

h.      Select “Membership” page then add the new local user you created. In our sample it is “Test”

i.         Select “Cubes” page and select “Read” access to for the cube(s), then click OK.

j.        Now we are done with the remote desktop, so you can now logoff

k.       Open Excel from your own machine and click “From Analysis Services” from “From Other Sources” from “DATA” ribbon

l.         Enter the remote server named followed by the SSAS instance name

m.    Click on “Use the following User Name and Password”

n.      Enter that new windows local user you created in the remote machine on step “C”

o.      Click “Next”

p.      Select the cube or any other perspectives you need from the list and click “Next”

q.      Click “Finish”

r.        Now you can create any reports you needed

Mission accomplished, enjoy it!

UPDATE: I’m asked about security concerns applied to the above solution, so I’d like to raise some important points:

1. Security is highly important and the above solution could be used if there is:
   • no domain trust relationship between your network and the other party so that you can connect to their network using your domain credentials
   • no VM (or a real server box) on the client’s having Excel so that you can connect to a database and create requested reports
2. You should go for the above solution only after getting your client’s approval. Otherwise, your client will probably blame you for creating SQL Server OR local Windows user accounts
3. The above solution should be a TEMPORARY solution for a short period of time so that you can deliver some Excel reports to your client quickly without loading any extra costs on your client’s shoulder