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.