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.