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.
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.
- Open the Start menu and then “Manage” the computer
- Open the System Tools node (if not already open)
- Open the Local Users and Groups node
- Open the Users folder
- Right-click in the user list and click New User…
- Create the user based upon the requirements set forth by client (no spaces)
- Create a password (typically requires special characters and numbers) DO NOT FORGET THIS PASSWORD
- Uncheck the first box, and check the next 2 (see screenshot)
- 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.
- Navigate to the Cubes tab and open the OLAP Server User Rights
- Add the appropriate new role, and then “Add…” a new user as normal
- Ensure the server location is “localhost” or the name of the machine
- 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)
- Once the user is found, click OK
- Click OK to accept the members of the role
- Set the appropriate permissions on the role and deploy all roles
- In Excel, open a blank workbook and select the data tab
- Click “Other Sources” and add a new connection From Analysis Services
- Specify the DNS name or, if server is hosted remotely, the IP address
- 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.
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.
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.