October 27, 2015 at 2:37 am
Yep the login is defaulted to master, I am guessing that the initial catalog in the connection string/connection manager is forcing it to ignore the default db and go straight to the user db.
October 27, 2015 at 4:04 am
anthony.green (10/27/2015)
Yep the login is defaulted to master, I am guessing that the initial catalog in the connection string/connection manager is forcing it to ignore the default db and go straight to the user db.
Which side is it failing on? The destination or the source?
October 27, 2015 at 4:07 am
The 18456 error is happening on the mirror node.
The connection strings / connection managers are all set to point to the principal node.
October 27, 2015 at 6:06 am
anthony.green (10/27/2015)
The 18456 error is happening on the mirror node.The connection strings / connection managers are all set to point to the principal node.
Okay. Just talking this through to myself to see if I can figure this out.
So the mirror logins can't connect to the primary? But the primary obviously isn't restoring. I'm assuming the default dbs were checked on both sides and that any linked servers were verified for their permissions.
So perhaps ... maybe try using a connection string that points to master but calls 4 dot notation to grab data off the user database?
But then you might not be able to do that.
.... Doing some brief research ....
Okay, back to silly questions. Have you run the sp_change_users_login against the databases in question?
Or have you disabled connection pooling (which doesn't actually have an answer to the last two posts)?
Hrm. Pinal Dave has the interesting solution of setting the login to db_owner in the database, but then he's referring to the NT Authority\Network Service account[/url]. I doubt that this is your issue. (I'm spit-balling at this point, BTW).
Oh, wait. Here's an answer by Microsoft regarding a very similiar (or even the same) issue.
Does any of this help?
October 27, 2015 at 6:14 am
Logins exists on both sides of the mirror.
Primary is active 24/7 unless we issue manual failover
Secondary is always restoring
Partner timeout is 60 seconds
No linked servers in the mix.
The jobs all run as the agent account, no proxies, account has sysadmin
The apps run as a generic user as its a web site, which has db_owner
Doing 3 part naming DB.Schema.Object will be do-able from the in house written issues, but from the application not so much as the 3rd party who writes them wont want to keep different branches of code per customer with different DB names, so that wont be an option for the apps.
That links looks exactly like the issue I am seeing, I will take a deep look into the content.
October 27, 2015 at 6:24 am
Sorry I was unable to help more. I hope the link gives you the info you need.
October 27, 2015 at 6:32 am
The link details 2005 not 2008R2 but it has raised something else to look at changing.
The remote login timeout is set to 15 seconds, going to add a change control in to change it to 30+ to coincide with the ODBC driver timeouts to see if the two being in sync resolves the issues.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply