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 and TLUSER 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 and TLUSER. 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.