Difference between revisions of "SQL Repository/Version 9"
(→Role Based) |
m (Text replacement - "XLCubed" to "FluenceXL") |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 2: | Line 2: | ||
| − | [[Web Edition| | + | [[Web Edition|FluenceXL Web Enterprise Edition]] can run in conjunction with a SQL Database, this gives various options to provide different functionality for Excel and Web-based users. |
==Modes== | ==Modes== | ||
| − | There are several options when you first setup | + | There are several options when you first setup FluenceXLWeb and have access to SQL Server to run a SQL Database. |
===Full Sql Database=== | ===Full Sql Database=== | ||
| Line 22: | Line 22: | ||
The security model can be based on roles or Active Directory groups. | The security model can be based on roles or Active Directory groups. | ||
| − | ====Role based==== | + | ====Role based - Recommended ==== |
Active-directory groups and users are assigned to Roles - these roles are then assigned permissions to reports/functionality. This has the advantage that no Active Directory import is required. | Active-directory groups and users are assigned to Roles - these roles are then assigned permissions to reports/functionality. This has the advantage that no Active Directory import is required. | ||
| Line 46: | Line 46: | ||
==== File-based reports ==== | ==== File-based reports ==== | ||
| − | Excel users connect to | + | Excel users connect to FluenceXLWeb to publish and the reports are stored in the file system - permissions are applied using standard NTFS security. |
[[Image:ntfssql.png|350px]] | [[Image:ntfssql.png|350px]] | ||
| Line 52: | Line 52: | ||
==== SharePoint-based reports ==== | ==== SharePoint-based reports ==== | ||
| − | Excel users connect to SharePoint to publish - permissions are applied in SharePoint. Reports are available and viewed in SharePoint, mobile users connect to | + | Excel users connect to SharePoint to publish - permissions are applied in SharePoint. Reports are available and viewed in SharePoint, mobile users connect to FluenceXLWeb which presents the SharePoint published reports. |
[[Image:sharepointsql.png|350px]] | [[Image:sharepointsql.png|350px]] | ||
| Line 66: | Line 66: | ||
Regardless of the mode you run in the follow needs to be done initially: | Regardless of the mode you run in the follow needs to be done initially: | ||
| − | * You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to | + | * You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to FluenceXLWeb without any changes to the setup process |
* Create a blank SQL Server database, and grant full access to it for the user doing the installation: | * Create a blank SQL Server database, and grant full access to it for the user doing the installation: | ||
[[File:SQLRepository1.png|400px]] | [[File:SQLRepository1.png|400px]] | ||
** Set the "Recovery Mode" to "Simple" | ** Set the "Recovery Mode" to "Simple" | ||
** Ensure the database is set to a case-insensitive locale and that the default locale for the server isn't case-sensitive | ** Ensure the database is set to a case-insensitive locale and that the default locale for the server isn't case-sensitive | ||
| − | * On the | + | * On the FluenceXL Web Edition server, run the Configure FluenceXL Web Edition application |
* Select the appropriate Web Edition instance (this will usually be done for you) | * Select the appropriate Web Edition instance (this will usually be done for you) | ||
* Switch to the {{Menu|Sql Repository}} tab: | * Switch to the {{Menu|Sql Repository}} tab: | ||
[[File:SQLRepository2v9.png|400px]] | [[File:SQLRepository2v9.png|400px]] | ||
| − | * Select the {{Menu|Use SQL for | + | * Select the {{Menu|Use SQL for FluenceXL Repository}} box |
* Enter the server and database names for the previously created blank database, and click {{Menu|Connect}}. | * Enter the server and database names for the previously created blank database, and click {{Menu|Connect}}. | ||
* The application will ask you if you want to populate the database. Click {{Menu|Yes}}: | * The application will ask you if you want to populate the database. Click {{Menu|Yes}}: | ||
| Line 87: | Line 87: | ||
===Role Based=== | ===Role Based=== | ||
| − | # Using SQL Server Management Studio, you should now add a Login for the | + | # Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\FluenceXL}} as the Windows Authentication login name - see [[Creating default IIS application pool user in SQL]]) |
# On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role | # On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role | ||
# If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role | # If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role | ||
| Line 96: | Line 96: | ||
===Active Directory Based=== | ===Active Directory Based=== | ||
| + | |||
| + | ''NB. We would always recommend Role-based setup'' | ||
You will now need to import the active directory users: | You will now need to import the active directory users: | ||
| Line 101: | Line 103: | ||
# Check the Domain name and click the {{Menu|Load Domain Information}} button to load the users and groups from the domain, or see [[#Using Domain Users and Groups|below]] for advanced options | # Check the Domain name and click the {{Menu|Load Domain Information}} button to load the users and groups from the domain, or see [[#Using Domain Users and Groups|below]] for advanced options | ||
# If desired, click the {{Menu|Import Repository}} button and enter the path to import an existing file system repository. Existing permissions will be transferred wherever possible but this should be verified once the system is available again as an initial test. | # If desired, click the {{Menu|Import Repository}} button and enter the path to import an existing file system repository. Existing permissions will be transferred wherever possible but this should be verified once the system is available again as an initial test. | ||
| − | # Using SQL Server Management Studio, you should now add a Login for the | + | # Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\FluenceXL}} as the Windows Authentication login name) |
# On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role | # On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role | ||
# If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role | # If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role | ||
| − | # If you need to restrict access to | + | # If you need to restrict access to FluenceXLWeb: |
| − | #* In the Configure | + | #* In the Configure FluenceXL Web Edition application, click the {{Menu|Administration}} button |
#* Select the {{Menu|Admin|System Settings}} menu item | #* Select the {{Menu|Admin|System Settings}} menu item | ||
#* Select the group that should be allowed access to the Web Edition then click the Save toolbar button: [[File:SQLRepository4.png|center]] | #* Select the group that should be allowed access to the Web Edition then click the Save toolbar button: [[File:SQLRepository4.png|center]] | ||
# Define which users will have "Root" folder access to create new folders and to manage permissions from in Excel, Select {{Menu|Admin|Folder Permissions}}: [[File:SQLRepositoryRootAccess.png|frame|center|Select SYS Reports Root Folder - for the standard report publication area or the required item for connections, templates etc.]] | # Define which users will have "Root" folder access to create new folders and to manage permissions from in Excel, Select {{Menu|Admin|Folder Permissions}}: [[File:SQLRepositoryRootAccess.png|frame|center|Select SYS Reports Root Folder - for the standard report publication area or the required item for connections, templates etc.]] | ||
| + | ===Hybrid Mode=== | ||
| − | + | # Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\FluenceXL}} as the Windows Authentication login name - see [[Creating default IIS application pool user in SQL]]) | |
| + | # On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role | ||
| + | # If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role | ||
If the file system is your chosen location for storing the reports, then no further configuration is necessary. | If the file system is your chosen location for storing the reports, then no further configuration is necessary. | ||
| − | If SharePoint is your chosen location for storing the reports you need to add the following line to the ApplicationSettings of the | + | If SharePoint is your chosen location for storing the reports you need to add the following line to the ApplicationSettings of the FluenceXLWeb web.config file: |
<add key="SharePointServerUrl" value="http://sharepointserver"/> | <add key="SharePointServerUrl" value="http://sharepointserver"/> | ||
| − | Where sharepointserver is the SharePoint web server address. Then to publish Excel users should connect directly to SharePoint, for scheduling and mobile app access they should connect to the | + | Where sharepointserver is the SharePoint web server address. Then to publish Excel users should connect directly to SharePoint, for scheduling and mobile app access they should connect to the FluenceXLWeb server |
Continue settting up SharePoint with the [[SharePoint Integration]] guide. | Continue settting up SharePoint with the [[SharePoint Integration]] guide. | ||
Latest revision as of 09:36, 4 July 2023
 |
This article refers to FluenceXL version 9. For other versions, see SQL Repository/Versions. |
FluenceXL Web Enterprise Edition can run in conjunction with a SQL Database, this gives various options to provide different functionality for Excel and Web-based users.
Contents
[hide]Modes
There are several options when you first setup FluenceXLWeb and have access to SQL Server to run a SQL Database.
Full Sql Database
Everything is stored in the SQL Database, this gives you access to the following features:
- Excel-based dialogs for controlling permissions and access to reports and folders
- full version history of published items, as well as locking the live version
- logging of report accesses, including rendering times for usage and performance monitoring
- Extended options around integrating with existing "Forms Authentication" websites
- Operational reporting - report usage, user activity etc.
- Report commentary
The security model can be based on roles or Active Directory groups.
Role based - Recommended
Active-directory groups and users are assigned to Roles - these roles are then assigned permissions to reports/functionality. This has the advantage that no Active Directory import is required.
Active Directory based
Active directory import needs to be run and configured, but no further maintenance is needed. Permissions can be assigned directly to Active-directory groups and users so closely matches a file-based repository.
Hybrid SQL Database
Only report usage and commentary is stored in the database, this gives you access to the following features:
- Report commentary
- Logging of report accesses, including rendering times for usage and performance monitoring
- Operational reporting - report usage, user activity etc.
The reports can be stored in either the file-system or SharePoint
File-based reports
Excel users connect to FluenceXLWeb to publish and the reports are stored in the file system - permissions are applied using standard NTFS security.
Excel users connect to SharePoint to publish - permissions are applied in SharePoint. Reports are available and viewed in SharePoint, mobile users connect to FluenceXLWeb which presents the SharePoint published reports.
In addition to these features, the SQL Repository feature also enhances the following:
- scheduling: this is also accessible from Excel, and features an easier to use interface as well as integrated run history
- error logging: available from a dedicated server administration application, this can be used on any machine and user that has server access
Installation
Regardless of the mode you run in the follow needs to be done initially:
- You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to FluenceXLWeb without any changes to the setup process
- Create a blank SQL Server database, and grant full access to it for the user doing the installation:
- Set the "Recovery Mode" to "Simple"
- Ensure the database is set to a case-insensitive locale and that the default locale for the server isn't case-sensitive
- On the FluenceXL Web Edition server, run the Configure FluenceXL Web Edition application
- Select the appropriate Web Edition instance (this will usually be done for you)
- Switch to the Sql Repository tab:
- Select the Use SQL for FluenceXL Repository box
- Enter the server and database names for the previously created blank database, and click Connect .
- The application will ask you if you want to populate the database. Click Yes :
- Select the mode to run in:
Then follow the rest of the setup for the mode you select.
Role Based
- Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the Application Pool Identity, then you can use IIS AppPool\FluenceXL as the Windows Authentication login name - see Creating default IIS application pool user in SQL)
- On the User Mappings screen map that user to the XLCUBED_USER role
- If any users should be able to administer the repository (see below), map those users to the XLCUBED_ADMIN role
No other setup is required, by default "Everyone" is granted access to the default role. You can edit and add additional roles in the Admin Application:
Active Directory Based
NB. We would always recommend Role-based setup
You will now need to import the active directory users:
- Check the Domain name and click the Load Domain Information button to load the users and groups from the domain, or see below for advanced options
- If desired, click the Import Repository button and enter the path to import an existing file system repository. Existing permissions will be transferred wherever possible but this should be verified once the system is available again as an initial test.
- Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the Application Pool Identity, then you can use IIS AppPool\FluenceXL as the Windows Authentication login name)
- On the User Mappings screen map that user to the XLCUBED_USER role
- If any users should be able to administer the repository (see below), map those users to the XLCUBED_ADMIN role
- If you need to restrict access to FluenceXLWeb:
- In the Configure FluenceXL Web Edition application, click the Administration button
- Select the Admin > System Settings menu item
- Select the group that should be allowed access to the Web Edition then click the Save toolbar button:
- Define which users will have "Root" folder access to create new folders and to manage permissions from in Excel, Select Admin > Folder Permissions:
Select SYS Reports Root Folder - for the standard report publication area or the required item for connections, templates etc.
Hybrid Mode
- Using SQL Server Management Studio, you should now add a Login for the FluenceXL application pool user (if the default FluenceXL application pool is used with the Application Pool Identity, then you can use IIS AppPool\FluenceXL as the Windows Authentication login name - see Creating default IIS application pool user in SQL)
- On the User Mappings screen map that user to the XLCUBED_USER role
- If any users should be able to administer the repository (see below), map those users to the XLCUBED_ADMIN role
If the file system is your chosen location for storing the reports, then no further configuration is necessary.
If SharePoint is your chosen location for storing the reports you need to add the following line to the ApplicationSettings of the FluenceXLWeb web.config file:
<add key="SharePointServerUrl" value="http://sharepointserver"/>
Where sharepointserver is the SharePoint web server address. Then to publish Excel users should connect directly to SharePoint, for scheduling and mobile app access they should connect to the FluenceXLWeb server
Continue settting up SharePoint with the SharePoint Integration guide.
Scheduling
If Scheduling is desired, a few additional steps are required to use it with the SQL Repository. The Scheduling Installation with the SQL Repository page has full details.
