The Experience Analytics CX datastore runs on top of a set of four Microsoft™ SQL Server databases.
Experience Analytics components access these databases using one of two SQL logins created automatically during installation. These accounts are granted membership to a select set of database roles on each of the Experience Analytics databases. They do not have access to any other databases or to the SQL Server instance itself.
Experience Analytics databases can be installed independent of the main Experience Analytics installer.
Authentication
During the installation process for SQL Server, you are prompted for the type of authentication in use for the install account. The account in use should have both Windows™ and SQL Server authentication.
Required SQL Server login permissions
The Experience Analytics application requires three SQL Server logins to operate correctly.
- During installation, a SQL Server login is required with sufficient permissions to create and modify the Experience Analytics databases. This login is used only during installation.
- An Administrative login enables background processes to modify the database schema during normal operation.
- A User login is used by all customer-facing portions of the application and only permits read and write access to the databases.
Installation login
The SQL Server login that is used during installation needs the following permissions to install and modify the schemas of each Experience Analytics database.
This login can either be the NT account of the user running the Experience Analytics installation or a separate SQL Server login that is provided to the Experience Analytics Database Manager as a secondary step after the main installation completes.
The simplest option is to use an NT Account that has SQL Server admin privileges or provide a SQL login with that level of access. If that is not possible a login with the following roles and permissions is required to successfully complete the Experience Analytics database installation:
- Required Database Roles:
db_ddladmin
db_datareader
db_datawriter
- Required Database Permissions:
ALTER ANY LOGIN
ALTER ANY USER
CREATE DATABASE
CREATE SCHEMA
Administrative login
This login is used by Experience Analytics applications that must be able to modify the schemas of one or more of the Experience Analytics databases. The applications that use this account that is run in the background and do not allow user interaction.
- Default Account Name -
TLADMIN
- Required Database Roles:
db_ddladmin
db_datareader
db_datawriter
- Optional Database Permissions:
VIEW SERVER STATE
is useful for performance monitoring.
Extra admin login permissions
Through the Portal, database administrators can access useful reports on the state of the
Experience Analyticsdatabases. To acquire all of the information to populate the Database
Filegroup Size report, the TLADMIN
account requires extra permissions. Below are
the permissions to enable:
USE MASTER
GO
EXEC sp_grantdbaccess 'TLADMIN';
GRANT EXECUTE ON sys.sp_OACreate TO TLADMIN;
GRANT EXECUTE ON sys.sp_OADestroy TO TLADMIN;
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO TLADMIN;
GRANT EXECUTE ON sys.sp_OAGetProperty TO TLADMIN;
If these permissions are not enabled or are not possible, the Database Filegroup Size contains a significant number of zeros in the data.
User login
This login is used by the Portal and any other Experience Analytics applications that require database access and allow user interaction. The login is limited to reading and writing data from the Experience Analytics databases.
- Default Account Name -
TLUSER
- Required Database Roles:
db_datareader
db_datawriter
Database security
Follow these guidelines when setting up database security for Experience Analytics.
- You can set the default database for the
TLADMIN
andTLUSER
logins to any valid value. - No stored procedures in any Experience Analytics databases should have access that is
granted to public. All access should all be limited explicitly to
TLADMIN
andTLUSER
. Revoking permissions from public does not cause an issue. - Many system stored procedures in
TL_SYSTEM
database have run privileges that are granted to public. Revoking these permissions should not cause problems. However, since revoking these permissions requires changes to the MS SQL Server system procedures, Experience Analytics cannot guarantee that the changes does not result in system issues. The Experience Analytics SQL Server logins are explicitly granted the permissions that they need to access any system-stored procedures.
Comments
0 comments
Please sign in to leave a comment.