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 http://manage.windowsazure.com and login with your credentials. In the portal then click the NEW button:

image

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

image

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

image

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.

image

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

image

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

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

http://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

image

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.

image

In the Windows Firewall window click the Advanced Settings option.

image

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

image

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

image

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.

image

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

image

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

image

Name the rule MSSQL and click Finish.

image

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.

image

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

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

What you need is the following downloads:

image

Attach the AdventureWorksDW2012 database to your server, if you don’t know how you can follow the tips in this blog http://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.

image

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

image

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

image

Grant Read access to the cubes.

image

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.

image

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

image

Then rename it to .udl 

image

Click OK in the following warning dialog

image

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

image

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

image

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

image

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