Search

Websense Log Server Service Starts and Stops post Upgrade to Version 7.7 - usp_user_permission_verify

rated by 0 users
This post has 4 Replies | 3 Followers

Not Ranked
Posts 1
Reply
Krishnag Posted: 6 Aug 2012 1:23 AM

Hi,

We just upgraded our websense infrasturcture to the new version 7.7.

Post upgrade we cannot get the websense Log Server Service to start.

I mean it starts and it stops immediately.

Note:  We had configured the Log Server service to connect via a Trusted User Authentication earlier.

We tried the websense article:

 

http://www.websense.com/support/article/kbarticle/Log-Server-not-running-after-upgrading-to-v7-7

 

The stored procedure which the article talks about  - usp_user_permission_verify_logserver was sucessfully created. However, we got a message that the object could still not be made effective or something due to some issue.

After debugging the Log server service using

http://www.websense.com/support/article/kbarticle/v7-Debugging-Websense-Logserver

Checked the debug log :

WsLogRecordset::Open() - FAILURE:  sqlCommand: {call usp_user_permission_verify_logserver} ERROR: The Procedure usp_user_permission_verify is not found.

We Uninstalled the log server component and reinstalled. Still same issue. After troubleshooting with Websense Support for a long time and got no where.

 

Now, If you could notice, you will find no document about this Stored proceedure anywhere.

I checked for the      usp_user_permission_verify_logserver  stored procedure which just resides above the  usp_user_permission_verify_logserver stored procedure in the sql server. (Refer to the picture in the Websense article we tried earlier)

Found the particular  usp_user_permission_verify_logserver be missing completely.

The usp_user_permission_verify_logserver is actually dependent on the usp_user_permission_verify_logserver stored procedure. So without it the logserver verify will fail.

Now how to you create the usp_user_permission_verify procedure.

If you try to manually create the procedure (which you can) (Right click on Stored procedures-->click on new Stored procedure). You can only create the blank procedure. But where do you get the code ?? or what the parameters to be supplied?? 

You run the debug again -- You get this in the debug.txt file.

 

 

 WsDBConnectionProperties comparison: != SSL: 0 != 0

WsODBCConnectionController::Initialize() - Initializing a new datasource for dsn: wslogdb70.

Returning Number of Logger Threads: 6

WsODBCConnectionController::Initialize() - Attempting to open datasource: wslogdb70.

 WsDBConnectionProperties::GetConnectionString() - using a Trusted Connection for the ODBC string

WsConnectionPool::initialize() - SUCCESS: Created 9 connections to db.

WsODBCConnectionController::Initialize() - Open datasource : wslogdb70 SUCCESS.

WsLogRecordset::Open() - FAILURE:  sqlCommand: {call usp_user_permission_verify_logserver} ERROR: Procedure usp_user_permission_verify has no parameters and arguments were supplied.

WsDataSource::Validate() - Permission test failed: .

WsODBCConnectionController::Initialize() - Validate datasource : wslogdb70 FAILURE: .

Waiting for LogServer to shutdown...

LogServerDoc shutdown cleanly...

So how to fix this. Well a couple of ways.
1. If you have another log server:
     Open the Stored procedure usp_user_permission_verify from the other log database and copy the query and paste in the affected database. Execute it. Then execute the usp_user_permission_verify_logserver script again. 
2. You can try to restore a old backup of the log database to another sql server or with a different name. attach it get the script work. i was not sure how to do this.
then I tried this.
1. You have to detach your websense database (wslogdb70) from the SQL server. 
2. Uninstall Log server, 
3. Then move the Websense database to a backup folder on the same location. (you may also try installing og server without moving the files - I didn't try that)
4. Install the log server feature again. 
5. Now a database is created with the new usp_user_permission_verify stored procedure.
6. Copy the query to a notepad file. 
7. Follow the procedure from step 1 in reverse to get the old database. 
8. Modify or create the usp_user_permission_verify SP and paste query you copied and run it.
Well I would say, You can copy the query you can  find here and recreate the usp_user_permission_verify SP like :
-------------------------------------------------------------------------------------------------------------------------------------------------
USE [wslogdb70]
GO
/****** Object:  StoredProcedure [dbo].[usp_user_permission_verify]    Script Date: 08/06/2012 09:46:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 create procedure [dbo].[usp_user_permission_verify] @reason nvarchar(1000) output, @debug bit = 0 as
 begin
     set nocount on;
     
     set @reason = N'OK';
     
     declare @productStr    varchar(20),
             @engineEdition int,
             @version_num   int;
             
     select  @productStr    = cast ( serverproperty(N'ProductVersion') as varchar(20) ),
             @engineEdition = cast ( serverproperty(N'EngineEdition') as int ),
             @version_num   = dbo.udf_dbserver_get_version();
  
     -- check version before creating jobs
     if (dbo.udf_dbserver_valid_version(@version_num) = 0)
     begin
         exec dbo.usp_event_log N'error', N'usp_user_permission_verify(): Specifiec SQL Server version is not supported';
         set @reason = N'Specifiec SQL Server version is not supported';
         print N'usp_user_permission_verify(): Specifiec SQL Server version ' + cast(@version_num as varchar) + N' is not supported.';
         return -1;
     end
         
     -- SQL Express
     if @engineEdition = 4 and IS_SRVROLEMEMBER(N'sysadmin') != 1
     begin
         set @reason = N'You must have sysadmin for SQL Express platform';
         return 1;
     end
     
     -- everything is OK here
     if IS_SRVROLEMEMBER(N'sysadmin') = 1
         return 0;
         
     -- standard or enterprise
     -- need db_creator
     if IS_SRVROLEMEMBER(N'dbcreator') != 1
     begin
         set @reason = N'You must have dbcreator server role';
         return 2;
     end
    
     declare @sid          varbinary(85),
             @db_var       sysname,
             @sql          nvarchar(4000),
             @total        int,
             @i            int;
     select @sid = sid from sys.server_principals where name = system_user; 
     print @sid
      
     declare @db_list table (
         table_id int identity(1,1),
         name     sysname primary key
     );
     
     create table #db_permission (
         id        int identity(1,1) primary key,
         dname     sysname,
         role_name sysname
     );
         
     insert into @db_list ( name )
         select [DB_NAME] from dbo.wse_partitions where offline=0 and deleted= 0
         union
         select [name] from dbo.amt_partitions where active = 1;
     set @total = @@ROWCOUNT;
     
     -- add msdb
     insert into @db_list (name) values (N'msdb');
     set @total = @total + 1;
     if @debug = 1 select * from @db_list;
 
     begin try
         select @i = 1;
         while @i <= @total
         begin
             select @db_var = name from @db_list where table_id = @i;
             
             set @sql = N'
             insert into #db_permission (dname, role_name)
             select ''' + @db_var + ''', role_principal.name role_principal_name
               from ' + @db_var + N'.sys.database_role_members join ' + @db_var + N'.sys.database_principals as role_principal on database_role_members.role_principal_id = role_principal.principal_id
               join ' + @db_var + N'.sys.database_principals as member_principal on database_role_members.member_principal_id = member_principal.principal_id
              where member_principal.sid = N''' + cast(@sid as nvarchar(100)) + N'''';
              
              if @debug = 1 print @sql;
              exec (@sql);
              set @i = @i + 1;
         end
         
         if @debug = 1 select * from #db_permission ;  
         
         if not exists (select 1 from #db_permission where dname = N'msdb' and role_name in ( N'db_datareader' , N'db_owner') )
         begin
             set @reason = N'You must have msdb.db_datareader database role';
             return 3;
         end
      
         if not exists (select 1 from #db_permission where dname = N'msdb' and role_name in ( N'SQLAgentUserRole', N'SQLAgentOperatorRole', N'SQLAgentReaderRole') )
         begin
             set @reason = N'You must have one of the msdb.SQLAgentxxxRole (User, Operator, Reader) database role ';
             return 4;
         end
         
         -- check partition ownership
         select @i = 1;
         while @i < @total
         begin
             select @db_var = min(name) from @db_list where name != N'msdb' and table_id >= @i;
             if @debug > 0 print  @db_var;
             if not exists (select 1 from #db_permission where dname = @db_var and role_name = N'db_owner')
             begin
                 set @reason = N'You must have ' + @db_var + N'.db_owner database role';
                 return 5;
             end
              
              set @i = @i + 1;
              select @db_var = min(name) from @db_list where name != N'msdb' and table_id >= @i;
         end
     end try
     begin catch
         set @reason = ERROR_MESSAGE();
         exec dbo.usp_error_log_info N'usp_user_permission_verify() failed to verify database permission ', @db_var;
         return 6;    
     end catch
 
     return 0;
 end  -- usp_user_permission_verify
 
 
GO
--------------------------------------------------------------------------------------------------------------------------------------------------
Now follow the steps in the Websense ariticle and check. 
You can start the log server fine.
Hope websense releases an article similar to the usp_user_permission_verify_logserver one for this too.
Thanks,
Krishna.

 

 

 

 

 

 

 

 

|
Top 10 Contributor
Posts 454
Moderator
Reply
phylliszh replied on 6 Aug 2012 11:10 PM

Hi,

Have you logged a ticket with support? You may need some engineers to remote in to fix the issu.

Phil

|
Not Ranked
Posts 1
Reply
pmirabile replied on 7 Aug 2012 10:35 AM

I have had the same issue since upgrading to 7.7. We first reported the problem a few weeks back and the engineer performed a work around where I had to copy all of the temp files back into the BCP folder. I have since been having problems again when the temp files are not writing to the DB. 

We've since encounter the IO Exception error. 

http://www.websense.com/support/article/kbarticle/Log-Server-not-running-after-upgrading-to-v7-7

Support has tried these resolutions and have not been able to resolve. I wait now over 24 hours to get someone to contact me. Websense support hasn't been what we expected neither has the product. 

|
Not Ranked
Posts 3
Reply
Biju Nair replied on 21 Sep 2012 12:06 AM

I am too facing the same problem after upgrade to 7.7, the log server service stops and even after starting it manually it agains stops.. any solution to it ?

|
Not Ranked
Posts 3
Reply
Biju Nair replied on 30 Sep 2012 10:33 PM

Hi

 

Do we have any solution to this ?

I already logged a case couple of week back, and the issue is not sorted yet.

 

 

Rgds,

BJU

|
Page 1 of 1 (5 items) | RSS