Creating a BI test machine in the cloud and allowing anonymous access to it

I got a question from a colleague that wanted to know how he could create and connect anonymously to a BI machine in the cloud. There are several whitepapers available but they can be hard to follow if you never have done it before so I thought that I would write a small simple instruction on how to do it.

First of all go to https://manage.windowsazure.com and login with your credentials. In the portal then click the NEW button:

You will get into a small wizard where you can select the type of machine that you want to create,

Select one of the SQL 2012 Enterprise versions on either Windows 2008 R2 or Windows 2012.

Specify the size of the machine, machine name as well as the username and password that you wish to use for the account. In the next dialog you need to fill in more information about the machine.

In the last step you need to create the valid endpoints needed to connect to SSAS and SQL Server

More information about this can be found in the following articles:

https://technet.microsoft.com/en-us/library/ms174937.aspx

https://msdn.microsoft.com/en-us/library/windowsazure/jj992719.aspx#bkmk_endpoints

Now you can just provision your machine and connect to it through the remote desktop client. To do so first click on the newly created Virtual machine and then  click the CONNECT button in the management portal

The next step is to allow for connections to Analysis Services and SQL Server through the firewall. When you have logged on to the computer the Initial Configuration Tasks wizard will start, ensure that you first of all have downloaded and applied all the updates available for your machine. After that you can click the Configure Windows Firewall button.

In the Windows Firewall window click the Advanced Settings option.

Right-click on the Inbound Rules option and select New Rule

Configure the rule by following these steps. First specify that it should be using a Program rule.

In the next dialog specify the program path to be %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe, you may need to change the instance name if you have a named intstance.

In the next dialog choose that it should Allow the connection.

Specify that the rule should be valid for or network profiles.

Name the rule MSSQL and click Finish.

Perform the same steps for a rule that allows you to connect to Analysis Services. This time specify the path to the program to be %ProgramFiles%\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\msmdsrv.exe, and the name of the rule to be MSSAS.

You will now have two inbound rules that allows connections to SSAS and SQL.

Next you can download the sample databases from the following link:

https://msftdbprodsamples.codeplex.com/releases/view/55330

What you need is the following downloads:

Attach the AdventureWorksDW2012 database to your server, if you don’t know how you can follow the tips in this blog https://www.mssqltips.com/sqlservertip/2638/attach-sample-database--adventureworks-in-sql-server-2012/

Since I want to test how anonymous access works to my system over the internet I want to allow everyone the rights to read the sample cube. This is in general not recommended but here is how you can do it.

In the sample cube create a new role by right-click on Roles and select New Role.

Call the role Anonymous Access or something similar, make sure that the role have have Read definition enabled.

Add the NT AUTHORITY\ANONYMOUS LOGON as a member of the role.

Grant Read access to the cubes.

Now process and deploy your cube. What you then need to do is to allow anonymous access on the instance level as well. Connect to Analysis Services using Management Studio and right-click on the server and select Properties. Click on General and change the Security\RequireClientAuthentication to false. Then click OK.

Now in your connection string you need to specify Impersonation level=Anonymous to be able to connect to the server.  The simplest way to create such a connection is to create a new text file and rename it to <something>.udl. This will allow you to use the Data Link wizard to configure the connection string.

I usually right-click on my desktop and select New and Text Document

Then rename it to .udl 

Click OK in the following warning dialog

Double click on the file and then specify the Microsoft OLE DB Provider for Analysis Services 12.0 as the provider.

On the Advanced tab specify that you want to use Anonymous as the impersonation level.

Now you can click the connection tab and specify the server name and cube name

Click OK to save the UDL file, this file can now be used in Excel when defining the connection to the cube.