March 28, 2011 at 10:52 am
Hello,
I'm configuring a linked server on a Sql Server 2005 server, to a mirrored database on a Sql Server 2008 database (composed by node31 primary, node32 secondary, node33 witness).
I would like to configure the linked server with node31 and failover partner=node32, so that in case of failover of node31 the linked server would switch automatically too.
I've created the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'node31', @srvproduct=N'Sofia', @provider=N'SQLNCLI', @provstr=N'Server=node31;FailoverPartner=node32;', @catalog=N'SOFIA'
in the security settings of linked server I've added the local login 'pinco' and 'sa' both with remote login 'si' with relative pwd.
connecting on my server as 'sa', i can query the linked server without problems, instead with 'pinco' I get the error:
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.
I've retyped the login mappings many times, and I'm sure the password was correct, but it doesn't work.
I've tried also to create the linked server without mappings, configuring the option 'Be made using this security option' and configuring the 'si' user for all logins. As before, it works only with 'sa'.
If instead of Sql Native Client I use Ole Db Provider for Sql Server, without FailoverPartner, it works with every login.
I wait for your kind suggestions.
sb
March 28, 2011 at 1:12 pm
I don't do much with clustering but 2 possiblities,
1) Is pinco a SQL login or an AD one? If its an AD login that may be part of your problem.
2) Is it possible that when you used the FailoverPartner flag on the Sql native Client that you can't map logins anymore? Try creating "pinco" on the remote server and see if it works then. If it does then mappings won't work. If it still doesn't work then I'm afraid your beyond me.
Another possiblity just occured to me which is that sa is a sysadmin on both boxes. Is si?, is pinco?
All of this is of course just looking at the differences between sa and the other logins. SA may not be your best test case since it is not only on every sql server (even ones that are NT Auth only, its just disabled) it is sysadmin on every sql server.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 29, 2011 at 12:47 am
I map 'sa' and 'pinco', which are both sql server logins, with the same remote login 'si'.
If instead of Sql Native Client I use Ole Db Provider for Sql Server, without FailoverPartner, it works with every login, and also with 'pinco'.
Can it depend on the driver used?
March 30, 2011 at 11:47 am
pixye.sb (3/29/2011)
I map 'sa' and 'pinco', which are both sql server logins, with the same remote login 'si'.If instead of Sql Native Client I use Ole Db Provider for Sql Server, without FailoverPartner, it works with every login, and also with 'pinco'.
Can it depend on the driver used?
My guess, and it is just a guess, is that the FailoverPartner causes it to completly ignore the mapping. SA will work just fine because it exists and is sysadmin on both sides. If pinco does not exist on the other server(s) then that may be why the error. And yes, things can be very different depending on driver. Does the server you are calling the failoverpartner have the same logins? Pinco as well as si?
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 31, 2011 at 8:38 am
Remote server has only the 'si' login for our use, I have to it for all local logins, also for 'sa', which, it's true, exists on remote server, but with different pwd.
It seems, from other forums I've read by now, that parameters in @provstr can create mapping problems for no-sysadmin users.
I'm thinking if I could solve the problem reinstalling Sql Servers (the principal and secondary mirrored) with named instance. But if node1 fails, and node2 becomes the active instance, would the linked server recognize the instance indipendently from the server?
Many thanks,
sb
March 31, 2011 at 6:56 pm
One of the requirements for setting up a mirrored database is that you transfer all the logins from the principal including their SIDS. The easiest way to do it -copy login from one server to another using SSIS package.
Jim:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply