SQL Server 2008 Login failure when mirroring

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
psychotic_savage
Registered User
Posts: 439
Joined: 04 Jun 2004, 02:00
Contact:

SQL Server 2008 Login failure when mirroring

Post by psychotic_savage »

Hi All,

I've finally got my foot in the door as a SQL admin so bear with me I'm very n00bish still.
Ok so here's the situation: I've setup Mirroring for all our DB's including a witness. This all works 100%.
The problem is now that, the developers need to alter their code to include a failover partner. And there are some discrepancies in the log file viewer.

My first question: If a DB has failed over to the Mirror will there be entries in the original principle's (Now the Mirror) log files that say there was a failed login.
I ask this because surely the connection first attempts to connect to the first server in the connection string and then on failure it moves to the failover partner part of the connection string.

Second Question: If I'm using SQL auth do I need to have the SID the same for the logins on both servers. If so how do I go about this.

Third Question: Our DB's weigh in at around 30gig's each, when I do an integrity check the DB's seem to failover. Is it wise for me to remove the integrity check from my backup schedule.

Thanks for the assist
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: SQL Server 2008 Login failure when mirroring

Post by hamin_aus »

Question 1 - There will be a login failed error... look in the SQL log file for Error: 18456, Severity: 14, State: 16. (this is a SQL2005 error code, but it should be the same for 2008)

Question 2

Code: Select all

/*
Script Function: This script is designed to help move logins from one SQL Server 2005 system
                            to another.  For example, if you wish to move an SQL Server and 
                            its databases from one server to another.  It creates a Transact-SQL 
                            script of both Windows and SQL Server Logins.  It copies the 
                            SID and Password for the SQL Server Logins.  The script also sets 
                            the appropriate Server Roles for all logins.  
Output:                The output is a file of Transact-SQL statements to add logins,
                             grant logins or deny logins based on settings in the source
                             SQL Server Master database. This script will not disable the SA account
Developed by Jeff Jones 
             JBJ Group. 
             
Disclaimer: This script is offered with no implied support nor has it been extensively tested.  
You should thoroughly review the script generated before applying it to your system.  
You can use this script for the intended purpose and also as a model for how you can use SQL 
to write scripts using a database table as the source. 

*/
USE MASTER
GO
SET NOCOUNT ON    -- Turn off Rows Affected Message in output script

SELECT  -- Create Windows Logins
		CHAR(13) + CHAR(10) + '-- ADD WINDOWS LOGIN ' + name  
		+ CHAR(13) + CHAR(10) + 'CREATE LOGIN ['
		+ name + '] FROM WINDOWS'
		+ CHAR(13) + CHAR(10) + CHAR(9) + 'WITH DEFAULT_DATABASE = [' 
		+ default_database_name + ']'
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_LANGUAGE = [' 
		+ default_language_name + ']'

FROM sys.server_principals
WHERE   name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authority\network service') 
	AND TYPE_DESC IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
	AND name NOT LIKE '%\SQLServerFDHostUser$%'
	AND name NOT LIKE '%\SQLServerMSSQLUser$%'
	AND name NOT LIKE '%\SQLServerSQLAgentUser$%'
UNION ALL

SELECT 
-- Create SQL Server logins
		CHAR(13) + CHAR(10) + '-- ADD SQL LOGIN ' + name  
		+ CHAR(13) + CHAR(10) + 'CREATE LOGIN ['
		+ name + ']'
		+ CHAR(13) + CHAR(10) + CHAR(9) + 'WITH PASSWORD = ' 
		+ coalesce(dbo.fn_varbintohexstr(password_hash), 'NULL') + ' HASHED' 
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_DATABASE = ['
		+ default_database_name + ']'
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_LANGUAGE = [' 
		+ default_language_name + ']'
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', SID = '
		+ dbo.fn_varbintohexstr(sid) 
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', CHECK_EXPIRATION = ' 
		+ CASE is_expiration_checked 
			WHEN 1 THEN 'ON' 
			WHEN 0 THEN 'OFF'
			ELSE ''
		  END	
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', CHECK_POLICY = ' 
		+ CASE is_policy_checked 
			WHEN 1 THEN 'ON' 
			WHEN 0 THEN 'OFF'
			ELSE ''
		  END	
FROM sys.sql_logins
WHERE  name <> 'sa'
UNION ALL
-- Create sp_denylogin for Windows Login
SELECT  CHAR(13) + CHAR(10) + '-- Disable LOGIN ' + name  
		+ CHAR(13) + CHAR(10)  + 'ALTER LOGIN ['
		+ name + '] DISABLE'
FROM sys.server_principals
WHERE is_disabled = 1
  AND name <> 'sa' 
  AND name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authority\network service')
	AND name NOT LIKE '%\SQLServerFDHostUser$%'
	AND name NOT LIKE '%\SQLServerMSSQLUser$%'
	AND name NOT LIKE '%\SQLServerSQLAgentUser$%'
UNION ALL
-- Create sp_addsrvrolemember 
SELECT  CHAR(13) + CHAR(10) + '-- ADD SERVER ROLE TO LOGIN ' + sp1.name  
		+ CHAR(13) + CHAR(10)  + 'exec sp_addsrvrolemember @loginame = ['
		+ sp1.name + ']'
		+ CHAR(13) + CHAR(10) + CHAR(9) + ', @rolename = ' + sp2.name
FROM sys.server_principals sp1
	INNER JOIN sys.server_role_members rm
		ON sp1.principal_id = rm.member_principal_id
	INNER JOIN sys.server_principals sp2
		ON rm.role_principal_id = sp2.principal_id
WHERE sp1.name <> 'sa' 
  AND sp1.name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authority\network service')
  AND sp1.name NOT LIKE '%\SQLServerFDHostUser$%'
  AND sp1.name NOT LIKE '%\SQLServerMSSQLUser$%'
  AND sp1.name NOT LIKE '%\SQLServerSQLAgentUser$%'
Edit: This code is a once-off thing... if the SQL accounts passwords change constantly you'll have to come up with another plan.

Question 3 - Find out why the integrity check is failing, there is probably something corrupt or missing somewhere in the db. But if nothing is actually breaking, you can leave it out of the maintenance plan until you find out why.
Image
psychotic_savage
Registered User
Posts: 439
Joined: 04 Jun 2004, 02:00
Contact:

Re: SQL Server 2008 Login failure when mirroring

Post by psychotic_savage »

Shot Jamin,
So the Error: 18456, Severity: 14, State: 38. is a failed login even though the connection was then made to the mirror.
Thanks for the code I'll try that shortly.
As for the Integrity Check when I break the Mirror and run the Integ Check it passes with no errors. Would you know if the Integ Check locks the DB's at all?

Thanks Though
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: SQL Server 2008 Login failure when mirroring

Post by hamin_aus »

psychotic_savage wrote:when I break the Mirror and run the Integ Check it passes with no errors. Would you know if the Integ Check locks the DB's at all?
running the check will create an snapshot of the DB that will be checked for integrity, so no, it does no create any locks on the DB itself, unless you use the TABLOC argument when running the check, which I doubt you are doing.

Check out this article, right at the bottom - it seems you can sometimes have an error creating a DB snapshot for DBCC CHECKDB to run on in a mirrored environment... maybe that's what is happening to you?

Integrity checks are not something you need to run daily if your DB has page protection. What you could do is schedule a weekly restore of your DB backup to a DR or test machine and then run the integrity check on the restored DB... same difference, really!
Image
psychotic_savage
Registered User
Posts: 439
Joined: 04 Jun 2004, 02:00
Contact:

Re: SQL Server 2008 Login failure when mirroring

Post by psychotic_savage »

Thanks Jamin
I have removed the Integrity check from the larger DB's and the maintenance tasks go off without a hitch now. I'll just have to setup a T-SQL statement that will backup then restore the DB on a temp server and run the Integ Check Then drop the DB if the result is positive. Oh my today is going to be a long day :)
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
psychotic_savage
Registered User
Posts: 439
Joined: 04 Jun 2004, 02:00
Contact:

Re: SQL Server 2008 Login failure when mirroring

Post by psychotic_savage »

My fix for the failing over was actually increasing the timeout period to 20 seconds instead of the default 10
Intel Core i5 750 2.66GHz LGA1156 \\ XFX Radeon HD5870XXX Edition \\ Asus P7P55D IP55 Motherboard \\ Corsair DDR3-1600 4GB (2x2GB) XMS3 DHX CL9 Memory Module Kit \\ 2x 250gig Sata 2x 160gig Sata\\ Win 7 Ent 64bit \\ Samsung 26" wide-screen LCD
Post Reply