0

When myself and our SA are testing our AO group, we are running a script that flows data into a table so we can monitor proper data. We are pointing this at our Listener: TestDBAG01, and it flows into our primary DB (TestDB0A1, using just 0A1 from here) and mirrored into our read-only secondary (TestDBOD1 - 0D1 from now on) when I do a select from the same table. So far so good.

So what's the problem?

No matter the way I do a failover, either manual or automatic, the script begins flagging an error IMMEDIATELY:

System.Exception: GetAccountToProcess failed: Cannot open database "Sitewit" requested by the login. The login failed. Login failed for user 'SWService'. at 
SiteWit.SessionProcess.GetAccountToProcess() in 
C:\Users\chris\Documents\Sitewit\LocalSource\Services\Development\SessionProcessing\SiteWit\SessionProcess.cs:line 1204 at 
SiteWit.SessionProcess.ProcessSessions() in 
C:\Users\chris\Documents\Sitewit\LocalSource\Services\Development\SessionProcessing\SiteWit\SessionProcess.cs:line 257 at 
SiteWit_Session_Processing_Service.SiteWitSessionProcessor._svcTimer_Elapsed(Object sender, ElapsedEventArgs e) in C:\Users\chris\Documents\Sitewit\Local 
Source\Services\Development\SessionProcessing\SiteWit Session Processing Service\SiteWitSessionProcessor.cs:line 100

and when I run a EXEC xp_readerrorlog I see:

Login failed for user 'SWService'. Reason: Failed to open the explicitly specified database 'Sitewit'. [CLIENT: 192.168.66.57]

Until I run a orphaned login script:

SET NOCOUNT ON
USE SiteWit
GO
DECLARE @loop INT
DECLARE @USER sysname
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL
BEGIN
    DROP TABLE #orphaned
END
CREATE TABLE #Orphaned (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1))
INSERT INTO #Orphaned
EXEC SP_CHANGE_USERS_LOGIN 'report';
IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
    SET @loop = 1
    WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
    BEGIN
        SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
        IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
        BEGIN
            EXEC SP_ADDLOGIN @USER,'password'
        END
        EXEC SP_CHANGE_USERS_LOGIN 'update_one',@USER,@USER
        PRINT @USER + ' link to DB user reset';
        SET @loop = @loop + 1
    END
END
SET NOCOUNT OFF
--End SQL Account Linking Script

I have been beating my head against a wall for a week about this. I am not sure if it's on the script side or the DB side. This is the first time I have been working with setting up a HA-AOG and am not sure what I can do to resolve this.

2
  • 1
    Look in the SQL ERRORLOG for both instances to validate which instance client is trying to connect to. It looks like you're trying to connect to the secondary, and it's not a read-only secondary, so the connection fails. May 17, 2021 at 21:39
  • Make sure the SQL login SIDs match for all replica instances. See this documentation topic.
    – Dan Guzman
    May 17, 2021 at 22:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.