locked
SQL server 2008 - Mirroring and connection string - SSPI error RRS feed

  • Question

  • Hi All !

    I have an old application (written in VB6) which "normally" connects to a mirroring solution using ADO (not ADO.NET).

    The mirroring chain: principal <--> witness <--> mirror. Authentication mode is SQL Server (with a local account)

    In normal mode, application connects ok. After failover on the mirror, application connection is KO

    In the connection string for SQL, I use all the parameters with failoverpartner, sspi, ...).

    When I do a failback, application connection is ok.

    Server resolution is ok (ping -a and ping @), I see instance and I can connect it through SSMS from another network place, browser is ok, ... ports 1433, 1434, ... are open, no firewall...

    But I have SSPI issue "can't generate a SSPI context ...". 

    I use a SQL server account (same name) on each server (with sufficient permissions) to connect the application and the instance name. 

    Is it due to application account (same name, but SID different, when reconnection) ?

    Do I transfer the account + its SID to the mirror ?

    Is it due to a bad connection string ? (user/pass instead of sspi ) ?

    If I put "bcp master..syslogins.dat" and after sp_resolve_logins, can reconnection will be ok ?


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

    Thursday, May 31, 2012 10:43 AM

Answers

  • SSPI context error represents that your  login is not authenticating from Active directory. Check the login should be  avalid domain account or If you moved your box from one domain to another then check the login and also look at your DSM setting in case you have moved it.

    Thanks & Regards Manvendra Deo Singh

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 12:05 PM
  • I would suggest to use a valid domain account in place of local account. Bec your application is trying to connect to server but the login ID is not authenticated by domain bec Its local account.

     Propose as answer If It helps.


    Thanks & Regards Manvendra Deo Singh My Blog:- http://manvendradeosingh.blogspot.com/

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 3:44 PM
  • In reading your initial post and response, I'm a bit confused about 'local account'.  On the one hand I'm reading that to be a Windows account in the server's SAM database, on the other hand, I'm reading it to be a SQL login.  I'll try to be clear when I talk about an 'account' so I don't further muddy the water.

    You're on the right track, I think, in suspecting the SID.  Because the mirror database has all the users and SIDs of the principle, you won't be able to use the same-named account (sql or local windows) and expect to login on the mirror; though the names are the same, the SIDs are different.

    One way I've gotten around this issue is to use a script to 'fix' the SID issue (for SQL logins only).  As you are trying to do this automagically in code, the script will be useless to you, except as a curiosity (provided below).

    I think the way to fix your issue is to change the authentication account to a domain user with appropriate rights on both servers.

    Here's the script I mentioned; it is not mine and I don't remember where I got it, but it works well enough for SQL logins so I've kept it:

    SET NOCOUNT ON
    
    DECLARE
    	@l_sysDBName		sysname
    	,@l_vSQL		varchar(5000) = space(0)
    	
    	-- if a login does not exist, one will be created with this user name / password combo
    	-- ##### this will only work with SQL Logins #####
    	,@l_vUser		sysname = 'SomeUser' -- this is the user to resolve
    	,@l_vPwd		sysname = 'SomeUser'
    	
    	
    CREATE TABLE #tblDBWithUser
    	(
    		[num]		int			identity(1,1)
    		,[DBName]	sysname			NOT NULL		DEFAULT(SPACE(0))
    		,[UserExist]	tinyint			NOT NULL		DEFAULT(0)
    	)
    	
    INSERT INTO #tblDBWithUser([DBName])
    	SELECT [name]
    	FROM [sys].[databases] AS [d]
    	WHERE
    		[d].[state] = 0 -- only online databases
    		AND [d].[name] NOT IN
    			(
    				'master'
    				,'msdb'
    				,'model'
    				,'tempdb'
    			)
    
    DECLARE curUserExists CURSOR
    READ_ONLY
    FOR 
    	SELECT [DBName]
    	FROM #tblDBWithUser
    	ORDER BY [DBName] ASC
    
    OPEN curUserExists
    
    FETCH NEXT FROM curUserExists INTO @l_sysDBName
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    	
    		SET @l_vSQL = '
    			IF EXISTS(SELECT * FROM ' + QUOTENAME(@l_sysDBName, '[]') + '.[sys].[database_principals] WHERE [name] = ' + 
    				QUOTENAME(@l_vUser, '''') + ')
    			BEGIN
    				UPDATE #tblDBWithUser SET [UserExist] = 1 WHERE [DBName] = ' + QUOTENAME(@l_sysDBName, '''') + '
    			END'
    
    		-- PRINT(@l_vSQL)
    		EXEC(@l_vSQL)
    		
    		SET @l_vSQL = SPACE(0)
    		
    	END
    	FETCH NEXT FROM curUserExists INTO @l_sysDBName
    END
    
    CLOSE curUserExists
    DEALLOCATE curUserExists
    
    --SELECT *
    --FROM #tblDBWithUser
    
    
    IF NOT EXISTS(select [name] FROM [sys].[server_principals] WHERE [name] = @l_vUser)
    BEGIN
    	SET @l_vSQL = '
    		CREATE LOGIN ' + QUOTENAME(@l_vUser, '[]') + '
    		WITH
    			PASSWORD = ' + QUOTENAME(@l_vPwd, '''') + '
    			,CHECK_POLICY = OFF
    			,DEFAULT_DATBASE = tempdb'
    	
    	-- PRINT(@l_vSQL)
    	EXEC(@l_vSQL)
    END
    
    
    DECLARE curResolveUser CURSOR
    READ_ONLY
    FOR 
    	SELECT [DBName]
    	FROM #tblDBWithUser
    	WHERE [UserExist] = 1
    	ORDER BY [DBName] ASC
    
    OPEN curResolveUser
    
    FETCH NEXT FROM curResolveUser INTO @l_sysDBName
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    	
    		SET @l_vSQL = '
    		USE ' + @l_sysDBName + ' 
    		ALTER USER ' + QUOTENAME(@l_vUser, '[]') + '
    		WITH 
    			LOGIN = ' + QUOTENAME(@l_vUser, '[]') + '
    			,DEFAULT_SCHEMA = [dbo]'
    			
    		-- PRINT(@l_vSQL)
    		EXEC(@l_vSQL)
    		
    		SET @l_vSQL = SPACE(0)
    		
    	END
    	FETCH NEXT FROM curResolveUser INTO @l_sysDBName
    END
    
    CLOSE curResolveUser
    DEALLOCATE curResolveUser
    
    
    
    DROP TABLE #tblDBWithUser

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 3:50 PM
  • The thing with SQL users is that if you just create them on two servers they will end up with different SIDs on each server. As the permissions are based on SIDs and not user names the permissions will no longer work in this scenario... And this is most likely what is happening here... There are two options to solve this:

    1) If you run SQL 2012 try using Contained Database Logins. With this you remove the limitations.

    2) Transfer the user from the primary including the SID. Here is how you do that: http://support.microsoft.com/kb/918992

    Hope that helps.

    Lucifer

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Friday, June 1, 2012 5:42 AM

All replies

  • SSPI context error represents that your  login is not authenticating from Active directory. Check the login should be  avalid domain account or If you moved your box from one domain to another then check the login and also look at your DSM setting in case you have moved it.

    Thanks & Regards Manvendra Deo Singh

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 12:05 PM
  • Hi Manvendra !

    Thanks for your response, but login account is not in a domain, it is a local account. Servers are in a domain.


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

    Thursday, May 31, 2012 2:05 PM
  • I would suggest to use a valid domain account in place of local account. Bec your application is trying to connect to server but the login ID is not authenticated by domain bec Its local account.

     Propose as answer If It helps.


    Thanks & Regards Manvendra Deo Singh My Blog:- http://manvendradeosingh.blogspot.com/

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 3:44 PM
  • In reading your initial post and response, I'm a bit confused about 'local account'.  On the one hand I'm reading that to be a Windows account in the server's SAM database, on the other hand, I'm reading it to be a SQL login.  I'll try to be clear when I talk about an 'account' so I don't further muddy the water.

    You're on the right track, I think, in suspecting the SID.  Because the mirror database has all the users and SIDs of the principle, you won't be able to use the same-named account (sql or local windows) and expect to login on the mirror; though the names are the same, the SIDs are different.

    One way I've gotten around this issue is to use a script to 'fix' the SID issue (for SQL logins only).  As you are trying to do this automagically in code, the script will be useless to you, except as a curiosity (provided below).

    I think the way to fix your issue is to change the authentication account to a domain user with appropriate rights on both servers.

    Here's the script I mentioned; it is not mine and I don't remember where I got it, but it works well enough for SQL logins so I've kept it:

    SET NOCOUNT ON
    
    DECLARE
    	@l_sysDBName		sysname
    	,@l_vSQL		varchar(5000) = space(0)
    	
    	-- if a login does not exist, one will be created with this user name / password combo
    	-- ##### this will only work with SQL Logins #####
    	,@l_vUser		sysname = 'SomeUser' -- this is the user to resolve
    	,@l_vPwd		sysname = 'SomeUser'
    	
    	
    CREATE TABLE #tblDBWithUser
    	(
    		[num]		int			identity(1,1)
    		,[DBName]	sysname			NOT NULL		DEFAULT(SPACE(0))
    		,[UserExist]	tinyint			NOT NULL		DEFAULT(0)
    	)
    	
    INSERT INTO #tblDBWithUser([DBName])
    	SELECT [name]
    	FROM [sys].[databases] AS [d]
    	WHERE
    		[d].[state] = 0 -- only online databases
    		AND [d].[name] NOT IN
    			(
    				'master'
    				,'msdb'
    				,'model'
    				,'tempdb'
    			)
    
    DECLARE curUserExists CURSOR
    READ_ONLY
    FOR 
    	SELECT [DBName]
    	FROM #tblDBWithUser
    	ORDER BY [DBName] ASC
    
    OPEN curUserExists
    
    FETCH NEXT FROM curUserExists INTO @l_sysDBName
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    	
    		SET @l_vSQL = '
    			IF EXISTS(SELECT * FROM ' + QUOTENAME(@l_sysDBName, '[]') + '.[sys].[database_principals] WHERE [name] = ' + 
    				QUOTENAME(@l_vUser, '''') + ')
    			BEGIN
    				UPDATE #tblDBWithUser SET [UserExist] = 1 WHERE [DBName] = ' + QUOTENAME(@l_sysDBName, '''') + '
    			END'
    
    		-- PRINT(@l_vSQL)
    		EXEC(@l_vSQL)
    		
    		SET @l_vSQL = SPACE(0)
    		
    	END
    	FETCH NEXT FROM curUserExists INTO @l_sysDBName
    END
    
    CLOSE curUserExists
    DEALLOCATE curUserExists
    
    --SELECT *
    --FROM #tblDBWithUser
    
    
    IF NOT EXISTS(select [name] FROM [sys].[server_principals] WHERE [name] = @l_vUser)
    BEGIN
    	SET @l_vSQL = '
    		CREATE LOGIN ' + QUOTENAME(@l_vUser, '[]') + '
    		WITH
    			PASSWORD = ' + QUOTENAME(@l_vPwd, '''') + '
    			,CHECK_POLICY = OFF
    			,DEFAULT_DATBASE = tempdb'
    	
    	-- PRINT(@l_vSQL)
    	EXEC(@l_vSQL)
    END
    
    
    DECLARE curResolveUser CURSOR
    READ_ONLY
    FOR 
    	SELECT [DBName]
    	FROM #tblDBWithUser
    	WHERE [UserExist] = 1
    	ORDER BY [DBName] ASC
    
    OPEN curResolveUser
    
    FETCH NEXT FROM curResolveUser INTO @l_sysDBName
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    	
    		SET @l_vSQL = '
    		USE ' + @l_sysDBName + ' 
    		ALTER USER ' + QUOTENAME(@l_vUser, '[]') + '
    		WITH 
    			LOGIN = ' + QUOTENAME(@l_vUser, '[]') + '
    			,DEFAULT_SCHEMA = [dbo]'
    			
    		-- PRINT(@l_vSQL)
    		EXEC(@l_vSQL)
    		
    		SET @l_vSQL = SPACE(0)
    		
    	END
    	FETCH NEXT FROM curResolveUser INTO @l_sysDBName
    END
    
    CLOSE curResolveUser
    DEALLOCATE curResolveUser
    
    
    
    DROP TABLE #tblDBWithUser

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Thursday, May 31, 2012 3:50 PM
  • The thing with SQL users is that if you just create them on two servers they will end up with different SIDs on each server. As the permissions are based on SIDs and not user names the permissions will no longer work in this scenario... And this is most likely what is happening here... There are two options to solve this:

    1) If you run SQL 2012 try using Contained Database Logins. With this you remove the limitations.

    2) Transfer the user from the primary including the SID. Here is how you do that: http://support.microsoft.com/kb/918992

    Hope that helps.

    Lucifer

    • Marked as answer by RHUM2 Monday, March 11, 2013 3:03 PM
    Friday, June 1, 2012 5:42 AM