November 20, 2013 at 2:09 pm
On SQL Server 2008R2, I created a linked server named differently than the remote server name. I can use it with my AD login, also I have sys admin permission. I need to have a SQL login called report use it though, without sys admin. This is the code I used:
EXEC master.dbo.sp_addlinkedserver @server = N'ANAPP', @srvproduct=N'MSSQL', @provider=N'SQLNCLI',
@provstr=N'PROVIDER=SQLOLEDB;SERVER=ANAPP1;UID=report;PWD=xxxxxx'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ANAPP',@useself=N'False',@locallogin=NULL,@rmtuser=N'report',@rmtpassword='xxxxxx'
The link works fine for me, but when report tries to use it we get error:
Access to the remote server is denied because no login-mapping exists.
This user, report does have permission on both the server that the link is on and on the remote server.
Also, I created a link using the SSMS wizard where the link's name is the same as the remote server name and report can use that one.
I must have something wrong in the code I used to create the link, but I can't figure out what I'm missing. Any ideas are greatly appreciated.
Thanks!
November 20, 2013 at 3:57 pm
This sounds familiar to me but I forget the specific issue.
I ended up scripting out the linked server created by the wizard and then modifying that script.
November 20, 2013 at 5:19 pm
The error that it gives you is correct. Lets say your report connects to the local database as reportuser1 on server1. In your linked server, on server1 which connects to server2, you need to have a login mapping of reportuser1, to another login on server2 that has access to query the data on server 2. Lets call him reportuser2 for this example.
So regardless of what your linked server is called, you need a login mapping of reportuser1 to reportuser2 for that report to work.
November 22, 2013 at 6:53 am
How do you make that mapping? Is it part of the script to create the link? report is a user login on both servers with the same password. Again thanks for the help with this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply