Sign Up for Training |
Jet Global Company Site
Submit a Request
Give Feedback

Configuring OLAP Permissions without Active Directory or a Domain


Occasionally it will happen that Jet Enterprise is installed in an environment without an Active Directory or a domain, or the BI server is isolated from the domain. While Analysis Services uses Windows authentication by default, following the steps below allows for a workaround that doesn't require a network to use Active Directory by using local users on the server and creating an ODC file in Excel to use other credentials. To do this, roles are created for each group of users which share credentials.




It is highly recommended that the Jet Data Manager be closed prior to proceeding with this process. If it is not, you must restart it before you will be able to see the user accounts created in the OLAP security plugin.


Create a local user 

A local user needs to be created on the server for each role to be created in Analysis Services. The following are steps to add a new user on a server. 

  1. Open the Start menu and then “Manage” the computer
  2. Open the System Tools node (if not already open)
  3. Open the Local Users and Groups node
  4. Open the Users folder
  5. Right-click in the user list and click New User…
  6. Create the user based upon the requirements set forth by client (no spaces)
  7. Create a password (typically requires special characters and numbers) DO NOT FORGET THIS PASSWORD
  8. Uncheck the first box, and check the next 2 (see screenshot)
  9. Click create


When the above process is complete, you should see a "New User" box similar to the one below:


Continue this process for each additional role that you need to create.



Add the created users to OLAP Server User Rights in the Jet Data Manager

Once all users are created, open the Jet Data Manager and the project in which you wish to add the new roles.


  1. Navigate to the Cubes tab and open the OLAP Server User Rights
  2. Add the appropriate new role, and then “Add…” a new user as normal
  3. Ensure the server location is “localhost” or the name of the machine
  4. Type in the FULL account name as created in step 2 (it will likely not find it if you don’t type in the full name)
  5. Once the user is found, click OK
  6. Click OK to accept the members of the role
  7. Set the appropriate permissions on the role and deploy all roles
  8. In Excel, open a blank workbook and select the data tab
  9. Click “Other Sources” and add a new connection From Analysis Services
  10. Specify the DNS name or, if server is hosted remotely, the IP address
  11. Specify the FULL NAME WITH DOMAIN of the user this will not use the IP, rather it will use MACHINENAME\USER and click Next


If the credentials were correct, after a short delay, the cube list should show up. Select the appropriate cube (or all) for this role and click Next.


If when selecting the cubes to connect to the "Connect to a specific cube or table:" box is unchecked, the connection will be valid for any cube the user has been granted access to.

Once cube selection has taken place, you will be prompted for additional details about the ODC file that will be created. Make sure to update all information to make the file more meaningful to the client. Especially important is including the Role Name in the File name. For most clients who will require this workaround, you’ll want to click the “Save password in file” box so they don’t have to continually enter the password every time they connect.


If you select "Save password in file" in the "Save Data Connection File and Finish", you will be prompted by a security warning alerting you that the password will be stored in plain text. Doing so will make connecting to the cubes seamless to the end users but it must be understood that this represents a risk to security. If you do not save the password, you will encounter an error on occasion stating "An error occurred in the transport layer" and the user must re-enter their password each time they connect.


Once the connection settings are as you would like them, you may click Finish.

The user now has access to the cubes. The connection file is stored at C:\Users\LOGINNAME\Documents\My Data Sources, from which they can copy it out to a secured file share if they so choose.



Considerations for data warehouse access without a domain

In addition to the steps above for OLAP, to grant access to the data warehouse in this situation, another single user can be created, this can be solely a SQL server login. You must map the user to the data warehouse database and grant the user db_datareader rights. When creating a connection to the data warehouse in Jet Essentials or the Jet Configuration service, utilizing Database authentication allows for this password/username to be entered for all users thus granting access to the data warehouse that way. Jet Essentials will still handle viewer/designer rights for the end users.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request