Server configuration in Get Started with Datazen and Analysis Services

 

Applies To: SQL Server 2016

To complete this walkthrough, your system should be configured as described below.

System configuration

• Window Server 2012 with Active Directory, joined to a domain.

• SQL Server default instance with an Analysis Services multidimensional instance and the relational database engine, plus a second named instance for tabular mode if you want to write queries for both.

• Adventureworksdw-2014-multidimensional database restored on SSAS (see Install Sample Data and Projects for the Analysis Services Multidimensional Modeling Tutorial for instructions). If you are using tabular, download and attach a sample tabular model database as well.

• Datazen server on the same Windows server as SQL Server. For this walkthrough, Datazen is installed in a non-distributed configuration, with all of its constituent services running together on same physical server.

• Datazen Publisher on a remote Windows 7, 8.1 or 10 client.

All computers (server and client) and data sources are on-premises.

Tip

Get the best MDX query support possible using Datazen Server version 3.0.3305 or later.

Firewall configuration

Because the client will be accessing data remotely, you should check port configuration to avoid hitting one of the more common connection errors. Established SSAS installations will already have ports configured, but to be sure, verify that your Windows server allows inbound requests for both the SQL Server relational database engine and Analysis Services default and named instances.

• Create an inbound rule for the relational database engine over TCP 1433.

• Create an inbound rule for Analysis Services default instance over TCP 2383.

• Create a second inbound rule for the Analysis Services named instance over TCP 2382 (for the discovery service) if you installed a second, named instance for a tabular server.

• Verify World Wide Web Services (HTTPS Traffic-In) allows connections on TCP 443. This is the port used by Datazen Publisher and Viewer to connect to the Datazen server.

Note

Running the Datazen services in a distributed environment would require additional port configuration (TCP 28952 and 28953), but we can skip this step when all services are co-located on the same physical server.

The best way to validate port configuration is to connect to your data source from a remote client. If you can import data from SQL Server or Analysis Services into Excel, you know that basic connectivity is working correctly.

Modify server configuration

This walkthrough assumes that you started with a default Datazen installation, configured with the built-in values presented in the setup wizard. Given a default installation, let’s review the customizations required to support SSAS workloads in a development/test environment. Recall that we’re provisioning all Datazen services and SQL Server instances on the same Windows server. Where possible, the following steps include the security and deployment best practices noted in the Datazen documentation.

If you haven’t installed Datazen yet, you can save yourself some extra work by following the Datazen installation instructions, configuring Datazen for Active Directory on first install.

In this exercise, you will make the following changes:

• Core service, initially configured to run as Local System, must use a domain user account.

• Data Acquisition service, initially configured to run as Local System, uses a domain user account.

• URL reservations are updated to use the revised account information.

• Authentication, initially set to Default, is changed to Active Directory in the config file.

• Data Acquisition service account is granted administrator rights on SSAS.

Run Core and Data Acquisition services under low-privileged domain user accounts

The Active Directory authentication option introduces a requirement for running the Core service as a domain account. Additionally, connection requests to SSAS are made by the Data Acquisition service, which must now issue these requests in the security context of a Windows security principle. In this section, tasks include:

• Change the Data acquisition service account from Local System to a domain user account.

• Change the Core service account from Local System to a domain user account.

First, in the Services console application, find the Datazen Server Data Acquisition service and change the login to a domain user account. The following screenshot uses a fictitious account named advwrks\datareader.

Repeat this step for the Datazen Server Core Service.

Restart both services.

If you created a local domain user account for the service (an atypical but feasible configuration when Datazen and SQL Server are on the same machine), it was created with the right to log on locally, a requirement for this service account. You should now manually make this account a member of the Performance Log Users group, and give this account Modify permissions on the Datazen program files folder.

Update URL reservations

Changing service accounts post-installation requires that you run the following netsh commands from an Administrator command prompt:

netsh http delete urlacl url=http://+:28952/
netsh http delete urlacl url=https://+:28953/
netsh http add urlacl url=http://+:28952/ user=[new service account username]
netsh http add urlacl url=https://+:28953/ user=[new service account username]

Edit Active Directory settings in the services.config file

Analysis Services uses Windows authentication exclusively. If you chose the Default authentication option during installation, you now need to modify it to use Active Directory or ADFS. See Security Primer on Datazen User Authentication (blog post)S for a description of each authentication option. The following steps are for the Active Directory option.

1. Stop the Datazen Server Core Service.

2. Edit the Datazen.Server.Service.exe.config in Notepad. You will need to copy the file to a different location to make your changes. You can find the file in \Program Files\Datazen Enterprise Server\Service folder.

   

3. Set authtype to “ad”. This is the only setting that is required. Other AD-related settings are optional unless you want to further constrain how connections are formed.

4. Save the file as UTF-8, with a .config file extension, replacing the original file.

5. Restart the Datazen Server Core Service.

Note

Reference documentation for the configuration settings can be found here.

Grant SSAS admin rights to the Data Acquisition service

At minimum, SSAS must have a server role membership for the Data Acquisition service account. Defining this role will grant full access to any SSAS database on the server. More granular permissions will be necessary if you want to restrict permissions to specific cubes or models, or set per-user access control at the row level.

Note

See cube or model permissions or dimension permissions for instructions on object-level permissions. See implementing row-level security in Datazen for details about that scenario.

1. In SQL Server Management Studio, connect to Analysis Services, right-click the server name.

2. In Server Properties | Security, add the Datazen Data Acquisition service account.

Add users and a hub in Datazen

Now that server configuration is complete, we can move on to setting up users and data sources in the Datazen Control Panel. This section provides instructions for registering users, creating a hub, and creating a data source and data view.

1. Use a browser to open the control panel on the Datazen server (https://localhost/cp).

2. Login as Admin using the administrator password set during installation.

   

3. Check the Authentication page to verify the system is running Active Directory authentication. If you see the Datazen Default Authentication option instead, you probably missed one of the steps in the previous section and you should retrace your steps before going any further.

   

Register Datazen users based on Windows domain user identities

In this step, you’ll add server users via the Datazen Control Panel. Anyone who accesses data sources defined on the server must be registered with the service first.

You need to create a minimum of one registered user to complete the remaining steps in this walkthrough.

• The registered user will be designated as the hub owner in the next section.

• The registered user must be a domain user account (as opposed to a local user account). You can’t specify a local user account when registering users with the Active Directory option. To complete all of the tasks in this walkthrough, make sure the domain user account has log on rights on the client computer.

1. In Datazen Control Panel | Server Users, click Create User.

2. Username is required and must be specified as a fully qualified UPN (alias@domain.corp.adventureworks.com). Windows security groups cannot be registered as a Datazen user when you use the Active Directory option. Datazen requires a one-to-one mapping between an individual user’s Windows identity and Datazen identity.

Create a hub

Data sources are created within hubs so in this step you’ll create a BI hub for the SSAS data source.

1. In Control Pane, click Create BI Hub.

2. Set all three properties: hub name, owner, maximum connections. For the owner, specify a registered user that is not a Datazen server administrator.

When you’re finished, the left navigation pane shows that the current hub is AdventureWorks BI. The data source that you’ll create next will exist in this hub.

Next step

Continue to the next task, Create data sources and data views.

See Also

Get started with Datazen dashboards using Analysis Services data and MDX queries