Login failed connecting to new principal after failover

Hello,

I have setup database mirroring on two Windows 2003 R2 x64 servers using SQL Server 2005 SP1 Developer Edition. Our application is connecting to SQL Server using a SQL Server login. The application is using ADO and SQL Native Client to connect to the server. After a failover, our app attempts to reconnect to the database. The reconnect fails with the error:

Cannot open database "db1" requested by the login. The login failed.

The login is not associated with a user in the new principal database. I run sp_change_users_login to reconnect the user and login. sp_change_users_login says that it fixed 1 orphaned user. Our app then reconnects successfully.

I have tried several failovers, and each time I see the same behavior. The association between the login and user gets lost.

The issue is definitely with the login. I tried using sa to connect to the database, and then our app was able to reconnect after a failover.

Is this a known issue with database mirroring? Is it fixed in SP2?

Thanks,

Heather

Hi Heather. How are you creating the sql logins on the mirror server? If you create them with the same SID as they have on the principal server, that should address the issue. In Sql 2005, you specify the SID for a sql login using the new 'create login' syntax, so it would look something like follows:

create login <loginname> with password = <password>, sid = <sid for same login on principal server>,...

HTH,

|||

Thanks, Chad. That resolved the problem.

Heather

Login error after automatic failover

I have setup a mirror configuration with a witness to be able to use the automatic failover. The principal is DBSP01, the mirror is DBSP02 and the witness is DBSP03. I have an application running an DBCP01. When the mirroring is working, the application can connect to the database on DBSP01. I disconnect dbsp01 from the network, so that DBSP02 becomes the principal. When I try to connect the application to the database on DBSP02, the login fails. Whithout the mirroring I was able to logon to DBSP02, but as soon as it is part of the mirroring, I'm not able to connect to it anymore, whatever the state of the database is. What could be the problem? Can anybody help?

Remco

I hope you are running with safety FULL. Please confirm.

Before you disconnect DBSP01, what is the status of the DBM session? is it SYNCHRONIZED?

After you disconnect DBSP01, are you sure the failover is taking place, and the database DBSP02 has become principal? Please check the role and status.

|||

I found the problem. The services of SQL were not running under the same accounts on the different boxes. One service was running under local account, while the same service was running under the network service account on the other box. When I changed it, it works properly.

Thanks

Logical Drive Letters

I am running a 3active/1passive node cluster on W2K3 and SQL 2k. The passive
node has the ability to have any of the active nodes to failover to it. In
addition to it being the failover node it also has 3 instances that match the
active nodes.
Since all the drive letters must be shared between the 6 instances I have
quickly run out of drive letters. Each node currently has 2 logical drives
accounting for 12 of the letters, add in local drives and cd drive,A-F and
Z:. I have 5 letters left.
I would like to add a seperate drive on each instance for the system dbs but
do not have enough letters left. Is there a way around this restriction?
Hi
No. Volumes without drive letters are not supported on Clusters.
Having a match of a DB to a drive letter does not gain you much, unless you
know each drive letter is a separate LUN and separate drives on the SAN.
We operate our SANS with 3 drivers per instance, a Data, a backup drive and
a log drive. All RAID-10. Each one is at SAN (EMC) level on different
drives.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:FC39CDC8-F010-4541-B0BB-6CE66DC45395@.microsoft.com...
>I am running a 3active/1passive node cluster on W2K3 and SQL 2k. The
>passive
> node has the ability to have any of the active nodes to failover to it.
> In
> addition to it being the failover node it also has 3 instances that match
> the
> active nodes.
> Since all the drive letters must be shared between the 6 instances I have
> quickly run out of drive letters. Each node currently has 2 logical drives
> accounting for 12 of the letters, add in local drives and cd drive,A-F and
> Z:. I have 5 letters left.
> I would like to add a seperate drive on each instance for the system dbs
> but
> do not have enough letters left. Is there a way around this restriction?
|||It sounds like you are mapping SQL 7.0 behavior to a SQL 2000 4-node
cluster. There are a lot of differences. First, each instance no longer
has a designated home node. You can limit the nodes an instance is allowed
to run on, but you are far better off allowing all nodes to be hosts for all
instances. You can move instances around on nodes as necessary. For
example, SQL_Instance_1 normally runs on Node A but can run on any node.
SQL2 and SQL3 normally run on nodes B and C respectively but also can run on
any other node. The second choice of all instances is node D, the normally
empty node. Once you have a failover (or just an instance move for
maintenance) you will need to rearrange the failover order to keep from
accidentally stacking the instances on the same node.
Each LUN should also map to a single drive letter cluster-wide. For
example, SQL_Instance_1 should use data drive R: and Log Drive S: on all
nodes. With three instances, you will have plenty of drive letters even with
the cluster overhead.
Geoff N. Hiten
Microsoft SQL Server MVP.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uBukmxNVFHA.3532@.TK2MSFTNGP09.phx.gbl...
> Hi
> No. Volumes without drive letters are not supported on Clusters.
> Having a match of a DB to a drive letter does not gain you much, unless
> you know each drive letter is a separate LUN and separate drives on the
> SAN.
> We operate our SANS with 3 drivers per instance, a Data, a backup drive
> and a log drive. All RAID-10. Each one is at SAN (EMC) level on different
> drives.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Thom" <Thom@.discussions.microsoft.com> wrote in message
> news:FC39CDC8-F010-4541-B0BB-6CE66DC45395@.microsoft.com...
>
|||Just to clarify I have 6 instances on a 4 node cluster. 3 of those instances
reside on one server most of the time while the other 3 are spread across the
other servers?
If drive letter A-F and Z are used for local drives on each of the nodes
that only leave 18 drives for the instances, Q is for quorum. At this point
I can only add 1 more drive letter to each instance or 6 drives total. Since
currently each instance has two drives. After I use these 6 drive letters
will there be any way to add additional drives to the instances.
I am wanting to keep my data spread out as much as possible to avoid Disk
I/O bottle necks.
"Thom" wrote:

> I am running a 3active/1passive node cluster on W2K3 and SQL 2k. The passive
> node has the ability to have any of the active nodes to failover to it. In
> addition to it being the failover node it also has 3 instances that match the
> active nodes.
> Since all the drive letters must be shared between the 6 instances I have
> quickly run out of drive letters. Each node currently has 2 logical drives
> accounting for 12 of the letters, add in local drives and cd drive,A-F and
> Z:. I have 5 letters left.
> I would like to add a seperate drive on each instance for the system dbs but
> do not have enough letters left. Is there a way around this restriction?
|||You are limited to only named drives a-z with SQL Server 2000. There is no
supported way around it. Can you consolidate some of your local drives to
make those letters availble for shared drives?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.