Use these tasks and information to resolve problems with SQL server configuration.
Unable to connect to SQL Server
If Experience Analytics is unable to connect to the SQL Server database, try the following steps.
- Verify the SQL Server custom ports value through the SQL Server Configuration Manager.
By default, SQL Server listens on port 1433, which is also the default communication port for Experience Analytics, unless selected differently during installation.
- If your SQL Server implementation uses dynamic ports, you must sync your Experience Analytics solution with them.
- Verify that the SQL network protocols are enabled through the SQL Configuration
By default, SQL Server does not enable TCP and Named Pipes, which are used by Experience Analytics.
Failure to install due to model database size mismatch
During installation of the Experience Analytics databases, the Database Manager might report a failure because of a mismatch between the configured database sizes in Experience Analytics and the model database sizes that are configured in SQL Server Management Studio.
In rare cases, you might change the size of the model database size in your SQL Server installation from the default value of 50MB. SQL Server Management Studio does not allow databases to be installed below the model database size. For example, if the model database size is set to 250MB, when the Database Manager attempts to install the databases, the installation fails because it is smaller than this minimum limit.
The solution is to do one of the following:
- Reconfigure the model database size in SQL Server Management Studio to 50MB. For more information, consult the documentation that was provided with your SQL Server product.
- Reconfigure the database sizes in Database Manager to be slightly larger than the model
database size in SQL Server Management Studio. For the above example, you could set the database
size to 260MB.Note: Database sizes must be configured through individual filegroups for each database.
Configuring use of SQL Server dynamic ports
Experience Analytics does not natively support the use of dynamic ports when accessing SQL Server. If possible, disable the use of dynamic ports.
When the SQL Server starts, a dynamic port is selected. This port is used during operations. During restart, the configured port remains, unless a conflict emerges.
If the ports must remain dynamic, complete the following steps.
- Start all Experience Analytics services.
- Start the database.
- Through SQL Server Management Studio, locate the port that the SQL Server uses.
This port must be populated for each database in the Connection dialog of the Database Manager.
- In the Database Manager, set the ports for the databases.
- From the TDM menu, select Mode > Info/Config > Report Server Configuration.
- Populate the ports accordingly. When changes are applied, the registry keys are updated for you.
On the Report Server, database port numbers are in the following registry keys. You can update these through the Database Manager Connecting screen.
Product (Database) Registry Key cxImpact (Reports)
Tealeaf Technology > DataStore > Report Server > Port
Tealeaf Technology > DataStore > Database > Search > Database PortNote: If the SQL Server is forced to choose a new dynamic port, then the new port must be used whenever you access the databases through the Connecting screen in the Database Manager and repopulated in the appropriate registry key through the Database Manager using the above steps.
- Through the Database Manager, install or upgrade the databases as necessary.
Restore failed when MDF file name is claimed
When restoring databases to a new instance of SQL Server, you may encounter an error similar to the following:
Restore failed for Server <MyServer> Additional information:System.Data.SqlClient.SqlError: File: 'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RL_REPORTS.mdf' is claimed by 'DATA'(3) and 'PRIMARY'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
The above issue is caused by the SQL Server restore procedure that converts all of the Experience Analytics database names to
RL_REPORT.mdf. This situation is typically
caused when the new location has a different directory structure or when the databases are moved to
a new version of SQL Server.
The solution is to manually append the file name of each data file to include the Filegroup identifier for the file to the end of the file name.
SuperSocket information: (SpnRegister): error 1355 in Windows application event log
If the SQL Server is unable to register a Service Principal Name (SPN), a
SuperSocket information: (SpnRegister): error 1355 message is
See the Microsoft Support site for information about the
Info warning message.
This message is not an error message. This text is only a warning that SQL Server was not
able to register a Service Principal Name (SPN), which indicates that the security mechanism used is
Microsoft Windows NT Challenge\Response (NTLM) authentication instead of Kerberos.
The message usually appears because the SQL Server service account is running as a domain
user who does not have requisite permissions to register SPNs.
SQL Server blocks access to procedure sys.sp_OAGetProperty
In some environments, the following error may be displayed in the SQL Server error log:
SQL Server blocked access to procedure 'sys.sp_OAGetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
sys.sp_OAGetProperty is an automation procedure that allows access to system
details. Experience Analytics uses this stored procedure to acquire details on disk
sys.sp_OAGetPropertyprocedure is not enabled:
- The Database Filegroup Size and Database Table Size reports in the Portal do not contain free space and unused space information.
- Available disk space on SQL Server is not recorded and does not appear in the Event Log.
The error message is harmless. However, to avoid confusion, you might want to deny access to
pr_ServerDiskSpace, which uses this stored procedure. When access is denied, error
messages are reported from
pr_ServerDiskSpace, instead of
sys.sp_OAGetProperty. Error messages from the latter procedure may suggest possible
hacks or malicious software, so unnecessary messages should be suppressed.
To deny access, run the following through SQL Server Management Studio:
use TL_STATISTICS go Deny execute on pr_ServerDiskSpace to TLAdmin; Deny execute on pr_ServerDiskSpace to TLUser; use TL_visreport go Deny execute on pr_ServerDiskSpace to TLAdmin; Deny execute on pr_ServerDiskSpace to TLUser