September 15, 2011 at 9:14 am
(Note: This is a duplicate posting of the original found in Backups, where it was posted by mistake. Please reply HERE!)
Scenario: Windows 2003 Server, SQL Server 2005, high availability configuration for mirroring (witness, use of quorum, etc). Database consists of one application database (xxx_prod), user id = yyy_prod. User id's default database is xxx_prod. In primary database, user id has dd_datareader and dd_datawriter privileges granted (NOT owner). Mixed authentication (no AD, all servers in same workgroup and in same sub-vlan (no firewall or other extraneous to muddy the waters)
When failover occurs, the mirror database becomes principal, but user id yyy_prod no longer has dd_datareader and dd_datawriter privileges. Consequently, application fails and high availability fails.
After failover, I must DELETE user_id (cannot merely change privileges-I think this is a hint) and recreate with appropriate attributes (default db is xxx_prod, grant datareader and datawriter for xxx_prod only, set password to never expire (because this is an application id, not a live user's id). Once recreated, application works fine. But, when I bring the original principal up as the mirror, and then failover, application can no longer access original principal. So, once again, I must DELETE user_id and recreate with proper configuration, and then application can access database and all is good.
When I failover again, the user id has lost its privileges and the application fails.
1. I'm in process of literature search, but having no initial luck finding meaningful items
2. There may be (hopefully) something obvious I'm overlooking. This is our only mirrored environment, so can't comparise to other setups.
3. The servers have not been kept current with updates (will be running tool shortly to find out just how out of date).
4. Hardware has been credibly stable with no known outstanding hardware issues at this time (just to rule out the obvious).
5. The scenario is repeatable.
6. One additional 'aberration': initially I did not have to contend with a schema name matching the user id name. However, after the first time, the schema appeared and required removal for me to be able to add the user id back in. I think this is also a clue, but don't know what it means.
TIA (that's both advance and arrears) for all your support and wonderful posts!
Steve (part-time DBA, chief cook and bottle washer)
September 16, 2011 at 2:08 pm
is this for all users or just a handfull
It is possible that a handful of users may have different SID so it is conflicting.
which can be fixed with sp_change_users_login 'report'
September 16, 2011 at 4:06 pm
There is only a single user other than 'sa', so it is both a single user and all of them....
the 'report' returns null set.
However, for our next test, I've added minimal access to the 'master' database which may actually resolve the problem. Doesn't explain root cause, but at this point, in SS2005, I'm more interested in keeping production up than 'fixing' an out of date system. If in fact added master to the login solves the issue, then I can research the literature and if I still come up empty write a little blurb to share with Microsoft / other DBAs with legacy implementations that could take advantage of the same resolution until such time as they migrate into a more modern tool set.
September 17, 2011 at 12:43 am
Is this single user id a windows login or a SQL login? If a SQL Login, the problem you are encountering is that the SID of the user id is different between the two systems. You have to do one of two things.
One, figure out how to get the sids between both servers to be the same. It is doable if I remember correctly but I don't know how you would do it.
Two, fix the sid on failover. This is what I did at a previous employer. I used event notification to run a set of processes on failover that fixed the sid on the SQL logins, and also disabled or enabled backup jobs depending on which direction the failover was going.
The procedure sp_change_users_login is a part of that process so be sure to read about it.
September 20, 2011 at 9:41 am
Lynn,
The user id in question is SQL Server only, so your points are very useful indeed! Of course, it would be too easy to be allowed to simply 'force' the change of the SID value to match through the replication process. Next easiest would be to capture the SID value from the primary, force a failover to the original mirror, force the SID change by a simple update table command, and then force the failover back to the primary. At least I now have a direction to move forward in.
Thanks for the help! Will keep posted, and perhaps describe the requisite steps for the next time (if ever) this problem arises. I do wonder, though, how many environments are not using AD but use mirroring. I suspect the population is small.
Steve
October 4, 2011 at 9:37 am
Lynn Pettis (9/17/2011)
Is this single user id a windows login or a SQL login? If a SQL Login, the problem you are encountering is that the SID of the user id is different between the two systems. You have to do one of two things.One, figure out how to get the sids between both servers to be the same. It is doable if I remember correctly but I don't know how you would do it.
Two, fix the sid on failover. This is what I did at a previous employer. I used event notification to run a set of processes on failover that fixed the sid on the SQL logins, and also disabled or enabled backup jobs depending on which direction the failover was going.
The procedure sp_change_users_login is a part of that process so be sure to read about it.
Lynn,
Your guidance was right on the money! Process (in summary):
1. copied the code in the kb article on the sp_change_users_login
2. created two new stored procedures in both principal (and then mirror)
2. executed the sp and saved the output in notepad
3. failed over to mirrored instance
4. deleted the sql server login id in question (used to run customer's application)
5. executed the code generated by sp_change_users_login to recreate the login and synchronize the sid
6. verified successful user connectivity
7. failed back over to principal
DONE!
Thank you so much!
Steve
October 4, 2011 at 9:45 am
Glad I was able to help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply