Products
Webtrends Visitor Data Mart
Issue
When attempting to add a new database location in the user interface, password validation fails and displays an error message similar to the one below:
The login for the UI Service Account does not have appropriate rights on to create the login for the DB Loader Service.
The login for the UI Service Account does not have appropriate rights on to create the login for the DB Loader Service.
Cause
This can occur due to an issue in which the SQL user name used by the service account appears in \ format when the user name should only include the user name. To resolve this issue, access the database using Microsoft SQL Server Management Studio and expand “Databases” > “System Databases” > “msdb” > “Security” > “Users.” Rename the service account user name to only display the user name. After completing this, return to the Webtrends user interface and add the new database location, at which time the error should no longer display.
Resolution
To resolve this issue, access the database using Microsoft SQL Server Management Studio and expand “Databases” > “System Databases” > “msdb” > “Security” > “Users.” Rename the service account user name to only display the user name. After completing this, return to the Webtrends user interface and add the new database location, at which time the error should no longer display.
Please run the following script on the database server to setup the appropriate rights.
IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins WHERE Logins.type in (‘g’,’u’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN CREATE LOGIN [phx\_vdmtrail] FROM WINDOWS WITH DEFAULT_DATABASE = [tempdb] END ELSE BEGIN ALTER LOGIN [phx\_vdmtrail] ENABLE END IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in (‘r’) AND roles.name in (‘processadmin’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN EXEC sp_addsrvrolemember @loginame = N’phx\_vdmtrail’, @rolename = N’processadmin’ END IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in (‘r’) AND roles.name in (‘dbcreator’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN EXEC sp_addsrvrolemember @loginame = N’phx\_vdmtrail’, @rolename = N’dbcreator’ END
An additional error message displays after the script, similar to the following:
The login for the UI Service Account does not have appropriate rights on to create the ETL credential. Please replace the value PROVIDE_PASSWORD in the following script and then run it on the database server to setup the appropriate credentials:
USE msdb; IF NOT EXISTS(SELECT * from sys.credentials WHERE name = ‘wtEtlCredential’) CREATE CREDENTIAL wtEtlCredential WITH IDENTITY = ‘phx\_vdmtrail’, SECRET = N’PROVIDE_PASSWORD’; IF NOT EXISTS(SELECT * FROM sysproxies WHERE name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_add_proxy @proxy_name = ‘wtEtl application proxy’, @enabled = 1, @description = ‘WebTrends ETL execute SSIS package proxy.’, @credential_name = ‘wtEtlCredential’; DECLARE @LoginName NVARCHAR(100) SET @LoginName = ‘phx\_vdmtrail’ IF NOT EXISTS(SELECT * FROM sysproxysubsystem AS ss JOIN sysproxies AS p ON ss.proxy_id = p.proxy_id WHERE p.name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’wtEtl application proxy’, @subsystem_id=11 If NOT EXISTS ( SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in ( ‘r’) AND roles.name = ‘sysadmin’ AND Logins.name = @LoginName) BEGIN IF NOT EXISTS(SELECT * FROM sysproxylogin AS pl JOIN sysproxies AS p ON pl.proxy_id = p.proxy_id WHERE p.name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_grant_login_to_proxy @login_name=N’phx\_vdmtrail’, @proxy_name = ‘wtEtl application proxy’ END DECLARE @dbuser sysname DECLARE @sql nvarchar(100) DECLARE @msg nvarchar(100) SELECT @dbuser = IsNull(dbUsers.Name, ”) FROM sys.server_principals as Logins LEFT JOIN sys.database_principals as dbUsers on dbUsers.sid = Logins.sid WHERE Logins.name = ‘phx\_vdmtrail’ IF (@dbUser <> ‘dbo’) AND (@dbUser <> ‘_vdmtrail’) BEGIN IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ‘_vdmtrail’) BEGIN CREATE USER [_vdmtrail] FOR LOGIN [phx\_vdmtrail] WITH DEFAULT_SCHEMA = dbo EXEC sp_addrolemember @rolename = ‘SQLAgentUserRole’, @membername = ‘_vdmtrail’ END END
Note: Only the “user name” value should be modified. The “login name” value requires the \ format and is essential for functionality.
Please run the following script on the database server to setup the appropriate rights.
IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins WHERE Logins.type in (‘g’,’u’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN CREATE LOGIN [phx\_vdmtrail] FROM WINDOWS WITH DEFAULT_DATABASE = [tempdb] END ELSE BEGIN ALTER LOGIN [phx\_vdmtrail] ENABLE END IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in (‘r’) AND roles.name in (‘processadmin’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN EXEC sp_addsrvrolemember @loginame = N’phx\_vdmtrail’, @rolename = N’processadmin’ END IF NOT EXISTS (SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in (‘r’) AND roles.name in (‘dbcreator’) AND Logins.name = ‘phx\_vdmtrail’) BEGIN EXEC sp_addsrvrolemember @loginame = N’phx\_vdmtrail’, @rolename = N’dbcreator’ END
An additional error message displays after the script, similar to the following:
The login for the UI Service Account does not have appropriate rights on to create the ETL credential. Please replace the value PROVIDE_PASSWORD in the following script and then run it on the database server to setup the appropriate credentials:
USE msdb; IF NOT EXISTS(SELECT * from sys.credentials WHERE name = ‘wtEtlCredential’) CREATE CREDENTIAL wtEtlCredential WITH IDENTITY = ‘phx\_vdmtrail’, SECRET = N’PROVIDE_PASSWORD’; IF NOT EXISTS(SELECT * FROM sysproxies WHERE name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_add_proxy @proxy_name = ‘wtEtl application proxy’, @enabled = 1, @description = ‘WebTrends ETL execute SSIS package proxy.’, @credential_name = ‘wtEtlCredential’; DECLARE @LoginName NVARCHAR(100) SET @LoginName = ‘phx\_vdmtrail’ IF NOT EXISTS(SELECT * FROM sysproxysubsystem AS ss JOIN sysproxies AS p ON ss.proxy_id = p.proxy_id WHERE p.name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’wtEtl application proxy’, @subsystem_id=11 If NOT EXISTS ( SELECT * FROM sys.server_principals as Logins JOIN sys.server_role_members as members on Logins.principal_id = members.member_principal_id JOIN sys.server_principals as roles on roles.principal_id = members.role_principal_id WHERE Logins.type in (‘g’,’u’) AND roles.type in ( ‘r’) AND roles.name = ‘sysadmin’ AND Logins.name = @LoginName) BEGIN IF NOT EXISTS(SELECT * FROM sysproxylogin AS pl JOIN sysproxies AS p ON pl.proxy_id = p.proxy_id WHERE p.name = N’wtEtl application proxy’) EXEC msdb.dbo.sp_grant_login_to_proxy @login_name=N’phx\_vdmtrail’, @proxy_name = ‘wtEtl application proxy’ END DECLARE @dbuser sysname DECLARE @sql nvarchar(100) DECLARE @msg nvarchar(100) SELECT @dbuser = IsNull(dbUsers.Name, ”) FROM sys.server_principals as Logins LEFT JOIN sys.database_principals as dbUsers on dbUsers.sid = Logins.sid WHERE Logins.name = ‘phx\_vdmtrail’ IF (@dbUser <> ‘dbo’) AND (@dbUser <> ‘_vdmtrail’) BEGIN IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ‘_vdmtrail’) BEGIN CREATE USER [_vdmtrail] FOR LOGIN [phx\_vdmtrail] WITH DEFAULT_SCHEMA = dbo EXEC sp_addrolemember @rolename = ‘SQLAgentUserRole’, @membername = ‘_vdmtrail’ END END
Note: Only the “user name” value should be modified. The “login name” value requires the \ format and is essential for functionality.