XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "SQL Repository/Version 9"
(→‎Active Directory Based)
 
(28 intermediate revisions by 3 users not shown)
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 ====
  
−
Role-based security requires Active-directory groups and users to be 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.
 +
 
 +
[[Image:rolesql.png|350px]]
  
 
==== Active Directory based ====
 
==== 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.
+
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.
 +
 
 +
[[Image:adsql.png|350px]]
  
 
===Hybrid SQL Database===
 
===Hybrid SQL Database===
  
−
Only report usage and commentary is store in the database, this gives you access to the following features:
+
Only report usage and commentary is stored in the database, this gives you access to the following features:
  
 
* Report commentary
 
* Report commentary
Line 42: Line 46:
 
==== File-based reports ====
 
==== File-based reports ====
  
 +
Excel users connect to XLCubedWeb to publish and the reports are stored in the file system - permissions are applied using standard NTFS security.
 +
 +
[[Image:ntfssql.png|350px]]
  
 
==== 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 XLCubedWeb which presents the SharePoint published reports.
  
−
 
+
[[Image:sharepointsql.png|350px]]
−
 
 
−
 
 
−
 
 
  
  
Line 58: Line 63:
  
 
==Installation==
 
==Installation==
−
# You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to XLCubedWeb 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: [[File:SQLRepository1.png|center|400px]]
 
−
#* Set the "Recovery Mode" to "Simple"
 
−
# On the XLCubed Web Edition server, run the Configure XLCubed Web Edition application
 
−
# Select the appropriate Web Edition instance (this will usually be done for you)
 
−
# Switch to the {{Menu|Sql Repository}} tab: [[File:SQLRepository2.png|center]]
 
−
# Select the {{Menu|Use SQL for XLCubed Repository}} box
 
−
# 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}}: [[File:SQLRepository3.png|center]]
 
−
# 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.
 
−
# Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\XLCubed}} as the Windows Authentication login name)
 
−
# 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 you need to restrict access to XLCubedWeb:
 
−
#* In the Configure XLCubed Web Edition application, click the {{Menu|Administration}} button
 
−
#* 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]]
 
−
# 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.]]
 
  
−
==Scheduling==
+
Regardless of the mode you run in the follow needs to be done initially:
−
If [[Scheduling]] is desired, a few additional steps are required to use it with the SQL Repository.
 
−
# On SQL Server Management Studio's {{Menu|User Mappings}} screen, map the scheduling service user to the {{Code|XLCUBED_SCHEDULER}} role
 
−
# In the Configure XLCubed Web Edition application, click the {{Menu|Administration}} button, then select the {{Menu|Admin|System Settings}} menu item. Choose the {{Menu|Scheduling user}} from the list then click the Save toolbar button
 
−
# Select the {{Menu|Admin|Scheduling|SMTP Servers}} menu item and add the details of the SMTP server
 
−
#* The {{Code|ID}} and {{Code|uniqueid}} columns will be filled in automatically
 
−
#* To use an SSL connection, enter {{Code|Y}} in the Use SSL column
 
−
#* To allow the scheduler to use the server to send emails, enter {{Code|Y}} in the Is Global column
 
−
# Select the {{Menu|Admin|Users}} or {{Menu|Admin|Groups}} menu item, and add the {{Menu|Can schedule reports}} and the {{Menu|Can edit schedule distribution groups}} where appropriate.
 
−
# Schedules must now be set up through the Excel Edition, select the {{Menu|XLCubed ribbon|Publish to Web dropdown|Scheduling...}} ribbon button. Once you have connected to the server, you can then add new scheduled reports and manage existing ones
 
  
−
===Running the Scheduler On Demand===
+
* You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to XLCubedWeb without any changes to the setup process
−
From [[Version 9]], the {{Code|XLCubed Scheduling Service}} Windows Service can be removed and the application can be run on demand, for example manually or by using Windows Task Scheduler, by using the {{Code|RUNPENDING}} switch. For example, using the standard installation path:
+
* Create a blank SQL Server database, and grant full access to it for the user doing the installation:
−
  C:\inetpub\wwwroot\XLCubedWeb\Scheduler\XLCubed.Scheduling.Service.exe /RUNPENDING
+
[[File:SQLRepository1.png|400px]]
 +
** 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 XLCubed Web Edition server, run the Configure XLCubed Web Edition application
 +
* Select the appropriate Web Edition instance (this will usually be done for you)
 +
* Switch to the {{Menu|Sql Repository}} tab:
 +
[[File:SQLRepository2v9.png|400px]]
 +
* Select the {{Menu|Use SQL for XLCubed Repository}} box
 +
* 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}}:
 +
[[File:SQLRepository3.png]]
 +
* Select the mode to run in:
 +
  [[File:SQLRepositorymode.png]]
  
−
The command should be run using a user that has been set up as described [[#Scheduling|above]], i.e. with the necessary permissions and SQL roles.
+
Then follow the rest of the setup for the mode you select.
  
−
Running it this way allows the use of the Scheduling feature even where multiple websites need to be set up on the same server. In this case, each website would have its own copy of the XLCubed.Scheduling.Service.exe and its own SQL repository, so each could have a scheduled task set up to run its pending schedules.
 
  
−
==Administration==
+
===Role Based===
−
There are two applications that can be used to administer the SQL repository:
 
−
* Configure XLCubed Web Edition (included in the XLCubed Web Edition setup, and installed on the server's start menu)
 
−
* Administer XLCubed Web Edition Repository (optionally installed separately on any machine)
 
  
−
Either of these will allow you to:
+
# Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\XLCubed}} as the Windows Authentication login name - see [[Creating default IIS application pool user in SQL]])
−
* Administer users, including giving them access to the XLCubed Web application, allow and deny them access to the repository, their personal folders, scheduling and scheduling distribution groups
+
# On the {{Menu|User Mappings}} screen map that user to the {{Code|XLCUBED_USER}} role
−
* Administer custom groups, including adding and removing users. You may also designate a group for XLCubed web access, as mentioned above
+
# If any users should be able to administer the repository (see [[#Administration|below]]), map those users to the {{Code|XLCUBED_ADMIN}} role
−
* Control folder access and permissions
 
−
* Control startup reports for users and groups
 
−
* Manage SMTP servers for scheduling
 
−
* View logs
 
  
−
==Using Domain Users and Groups==
+
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:
−
In order to use users and groups from your Domain, run the Configure XLCubed Web Edition application, then select the {{Menu|Sql Repository}} tab. By default, the Domain box will automatically be populated with the name of the local Domain, but it supports several formats:
 
−
; Domain name
 
−
: e.g. {{Code|example.com}}
 
−
: Load all the users and groups in the given domain
 
−
; {{Code|Localhost}}
 
−
: Load all the users and groups from the host machine
 
−
; A semi-colon delimited list of LDAP queries
 
−
: e.g. {{Code|1=LDAP://OU=Development,DC=somecom,DC=com; LDAP://OU=Consultants,DC=somecom,DC=com}}
 
−
: Allows you to load users and groups from the domain, but limit the import to the given Organisation Units, etc.
 
−
; A semi-colon delimited list of global catalog queries
 
−
: e.g. {{Code|1=GC://OU=Development,DC=somecom,DC=com; GC://OU=Consultants,DC=somecom,DC=com}}
 
−
: If your domain supports it, this may be faster than the corresponding LDAP queries
 
−
; Group filtering, for example
 
−
: e.g. {{Code|1=LDAP://DC=somecom,DC=com~(cn=group)}}
 
−
: When using LDAP or GC queries, you may also filter the groups returned by using this form, where {{Code|group}} can be a full group name or a wildcard (e.g. {{Code|Sales*}})
 
  
−
===Automatically keeping the Domain information up to date===
+
[[File:SQLRepositoryrole.png]]
−
Once this has been configured, the [[Scheduling Installation|XLCubed Scheduling Service]] can be used to keep the Domain information up to date on a regular basis. To set this:
 
−
# Run the Configure XLCubed Web Edition application
 
−
# Select the {{Menu|Scheduling}} tab
 
−
# Select the {{Menu|Schedule domain update}} box, and enter how frequently you would like to update the Domain information
 
−
# The update will occur at midnight, server time
 
  
−
Note that the [[Scheduling Installation|XLCubed Scheduling Service]] must be set up correctly in order for this to succeed.
+
===Active Directory Based===
  
−
===Manually running the Domain import process===
+
''NB. We would always recommend Role-based setup''
−
From XLCubed v8.1.39, the Scheduling Service domain import process can be triggered manually by passing an extra command line parameter:
 
−
C:\inetpub\wwwroot\XLCubedWeb\Scheduler\XLCubed.Scheduling.Service.exe /UPDATEDOMAIN
 
  
−
This allows it to be called manually or via an automated process, for example Task Scheduler. The user running the process must be a member of the XLCUBED_ADMIN role.
+
You will now need to import the active directory users:
  
−
===Viewing the Domain import logs===
+
# 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
−
In order to see the progress of an individual Domain import, or whether errors occurred on a previous occasion, run the Configure XLCubed Web Edition application, then select the {{Menu|Sql Repository}} tab. Click on the {{Menu|Administration}} button, then select the {{Menu|Admin|Logs|Active Directory}} menu.
+
# 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 XLCubed application pool user (if the default XLCubed application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\XLCubed}} as the Windows Authentication login name)
 +
# 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 you need to restrict access to XLCubedWeb:
 +
#* In the Configure XLCubed Web Edition application, click the {{Menu|Administration}} button
 +
#* 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]]
 +
# 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.]]
  
−
==Operational Reporting==
+
===Hybrid Mode===
−
The installation provides an operational reporting dashboard as shown below:
 
  
−
[[Image:zxzx.png|350px|centre]]
+
# Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the ''Application Pool Identity'', then you can use {{Code|IIS AppPool\XLCubed}} 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
  
−
The Excel file can be accessed through the "XLCubed Administration Reports" start menu shortcut, or directly in the \Reports folder within the XLCubed Web Website. Additional operational reports can be built using the oData datasource provided with XLCubed Web (accessed through Grids & Tables - Other Sources).
+
If the file system is your chosen location for storing the reports, then no further configuration is necessary.
  
−
The report will run against the last XLCubedWeb server connected to in the "Publishing" screen.
+
If SharePoint is your chosen location for storing the reports you need to add the following line to the ApplicationSettings of the XLCubedWeb web.config file:
  
−
You can enable at a group or user level who has access to these reports:
+
  <add key="SharePointServerUrl" value="http://sharepointserver"/>
  
−
[[Image:UserSystemDashboardPermission.png|centre]]
+
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 XLCubedWeb server
  
−
==Import and Export of Reports==
+
Continue settting up SharePoint with the [[SharePoint Integration]] guide.
−
Moving reports between repositories is no longer a case of copying files between file system folders. In order to provide a mechanism for this, XLCubed Excel Edition has two menu items, which can be found on the {{Menu|XLCubed Ribbon|Extras}} menu:
 
  
−
[[Image:ImportExportItemsMenu.png|centre]]
+
==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.
−
Note that these options will only be available to users who have the {{Menu|Can bulk import and export reports}} permission configured, as described [[#Administration|above]].
 
−
 
 
−
===Exporting Items===
 
−
When you select the {{Menu|XLCubed Ribbon|Extras|Export Items from Web...}} menu item, the {{Menu|Export Items}} form appears. Once you connect to the web server, the list of folders and reports available to you appears in the form:
 
−
 
 
−
[[Image:ExportItemsForm.png|centre]]
 
−
 
 
−
Select any items that need to be exported and click {{Menu|OK}} to save the folders and files as a repository archive file.
 
−
 
 
−
===Importing Items===
 
−
When you select the {{Menu|XLCubed Ribbon|Extras|Import Items into Web...}} menu item, the {{Menu|Import Items}} form appears. Connect to the web server, then select the repository archive file created [[#Exporting Items|above]]. The selected items are imported into the repository in the same folder location they were exported from, but on the current web server.
 
  
 
==See Also==
 
==See Also==
 +
* [[SQL Repository Administration]]
 
* [[Scheduling with the SQL Repository]]
 
* [[Scheduling with the SQL Repository]]
 +
* [[Using Domain Users and Groups with the SQL Repository]]
 +
* [[Running the Scheduling Service in non-Service mode]]
  
 
[[Category:Web Edition]]
 
[[Category:Web Edition]]
 
[[Category:Installation]]
 
[[Category:Installation]]

Latest revision as of 08:36, 6 June 2023

This article refers to XLCubed version 9. For other versions, see SQL Repository/Versions.



XLCubed 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

There are several options when you first setup XLCubedWeb 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.

Rolesql.png

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.

Adsql.png

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 XLCubedWeb to publish and the reports are stored in the file system - permissions are applied using standard NTFS security.

Ntfssql.png

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 XLCubedWeb which presents the SharePoint published reports.

Sharepointsql.png


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 XLCubedWeb 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:
SQLRepository1.png
    • 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 XLCubed Web Edition server, run the Configure XLCubed Web Edition application
  • Select the appropriate Web Edition instance (this will usually be done for you)
  • Switch to the Sql Repository tab:
SQLRepository2v9.png
  • Select the Use SQL for XLCubed 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 :
SQLRepository3.png
  • Select the mode to run in:
SQLRepositorymode.png

Then follow the rest of the setup for the mode you select.


Role Based

  1. Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the Application Pool Identity, then you can use IIS AppPool\XLCubed as the Windows Authentication login name - see Creating default IIS application pool user in SQL)
  2. On the User Mappings screen map that user to the XLCUBED_USER role
  3. 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:

SQLRepositoryrole.png

Active Directory Based

NB. We would always recommend Role-based setup

You will now need to import the active directory users:

  1. 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
  2. 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.
  3. Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the Application Pool Identity, then you can use IIS AppPool\XLCubed as the Windows Authentication login name)
  4. On the User Mappings screen map that user to the XLCUBED_USER role
  5. If any users should be able to administer the repository (see below), map those users to the XLCUBED_ADMIN role
  6. If you need to restrict access to XLCubedWeb:
    • In the Configure XLCubed 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:
      SQLRepository4.png
  7. 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

  1. Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the Application Pool Identity, then you can use IIS AppPool\XLCubed as the Windows Authentication login name - see Creating default IIS application pool user in SQL)
  2. On the User Mappings screen map that user to the XLCUBED_USER role
  3. 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 XLCubedWeb 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 XLCubedWeb 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.

See Also

Retrieved from ‘https://help.xlcubed.com/index.php?title=SQL_Repository/Version_9&oldid=11167’