Linked server with mirrored database 2

As recommended by Mark Han, I am opening a new post for this issue. I am
trying to setup a linked server that works with both integrated logins and
sql logins and includes the information necessary to connect to a mirrored
database.

I have created a linked server that includes a database mirror using the
following statements:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName2',
@srvproduct=N'.', @provider=N'SQLNCLI', @datasrc=N'Server\Instance',
@catalog=N'Database'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkedServerName2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


This linked server only works with integrated logins. When it is used by a
SQL login, the following error is returned:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.


I have verified that the problem is not in the permissions of the sql login
by creating a linked server that connects to the same instance, but using
more basic parameters that do not include mirroring information. This linked
server works for the same sql login, but does not have the needed mirroring
information:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName2',
@srvproduct=N'.', @provider=N'SQLNCLI', @datasrc=N'Server\Instance',
@catalog=N'Database'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkedServerName2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

This is an example of a simple select that works with the second linked
server (no mirroring) but not the first (mirroring):

select * from LinkedServerName.Database.dbo.tableName
select * from LinkedServerName2.Database.dbo.tableName

Both of the above work if an integrated login is used.

How do I create a linked server that includes mirroring and works with both
integrated and sql logins?

Thanks,
Martin

HI Martin,

This is Mark, agian. I am glad to work with you on this issue.

Based on the current information, I would like to sum up the situation for
you,
1 we have created 2 linked server pointing to a same instance. One linked
server(named A) with mirroring database and another(named B)

2 scenario B works with SQL Authentication and Windows Authenticattion but
scenario can only works with Windows Authenticattion

in order to address your concern, I would like to explain the following
1 at first, the scenario A doesn't work with SQL Authentication and Windows
Authenticattion. Based on my suggestion, now the scenario A works with
Windows Authenticattion.

2 For your convenience, I make a sample here.
For example, to explicitly connect using TCP/IP to the AdventureWorks
database on either Partner_A or Partner_B, a client application could
supply the following connection string
"Server=Partner_A; Failover Partner=Partner_B; Database=AdventureWorks;
Network=dbmssocn"

3 Based on the error message, please take the following.
1 confirm if the SQL Authentication is accepted by the mirroring server
instance.(ServerB\Instance)
2 verify if we can use the SQl login to connect to the mirroring server
instance.(ServerB\Instance)

Besides, there is an article to share with you:
http://msdn.microsoft.com/en-us/library/ms366348(SQL.90).aspx

If there is anything unclear, please do not hesitate to let me know.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg.DeleteThis@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================