December 23, 2010 at 2:14 am
Can anyone tell me why this is happening:
- I have a mirrored database (with witness) called 'DataLog' with a user 'Server'
- Both Servers have a SQL login defined called 'Server'. On the principal server this login is mapped to a user 'Server'.
- Client app. connects using DSN which defines 'DataLog', the failover partner and UID=Server. This works fine.
- Failover the database
- Client cannot now connect; (SQLDriverConnect) [Microsoft][SQL Native Client][SQL Server]Cannot open database "DataLog" requested by the login. The login failed. 37000(4060)
- If the user 'Server' is deleted from the database, then the login 'Server' is mapped to DataLog again, then the Client once again connects, to what was the failover partner but now is the principal.
- Failover the database again and once again the connection breaks.
- The login 'Server' is no longer mapped to the user 'Server' on the principal server.
December 23, 2010 at 8:43 am
The database userid is becoming orphaned from the login.
the user and login are mapped together using a SID (Server ID) value. When you failover the database the SID value for the userid comes over with the database, this no longer matches the SID value for the login on the new server, so the user is orphaned as SQL cannot match it to a valid login.
This is happening because I would guess the logins were created separately by individual create login statements and each login has a different SID. The permanent fix for this, other than running sp_change_users_login after each failover, is to use sp_help_revlogin procedure (google it), this reverse engineers out the create login statement with the same SID value (and password).
So on the current live server where the SIDS match, run sp_help_revlogin to produce the create login statement, and create the login on the other server using this SQL. Then the SIDS will match on both servers and you won't get this problem. Make sure the default database and default language and any server level permissions granted to the login match as well (a one off manual task).
the above is only an issue with SQL Authenticated logins, if you were to use a windows authenticated domain user it would not occur. these use GUIDS (Global User IDs) instead.
---------------------------------------------------------------------
December 23, 2010 at 9:52 am
Spot on George. Thanks
December 23, 2010 at 1:09 pm
Pleasure. Its a common issue.
Thanks for the feedback, happy christmas. 🙂
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply