August 9, 2012 at 5:28 am
Hi All,
I have two production servers, Server A is connected to Server B through linked server LI_SERVER, security settings for this are selected as 'Be made using this security context' and login provided with this option is LI_LOGIN.
LI_LOGIN exists on both the servers, it has db_owner rights on Server A and db_datareader on Server B for all databases.
While running a select query from A to B using LI_LOGIN account, following error is returned:
'Access to the remote server is denied because no login-mapping exists'
however if I grant sysadmin to LI_LOGIN, query runs fine.
I cannot grant sysadmin since this is a production server.
Can anyone guide me thorugh this problem?
I would appreciate any help. Thanks in advance.
August 9, 2012 at 5:58 am
Can you login to Server B with LI_LOGIN using Management Studio, SQLCMD or other tools, or do you get the same error?
August 9, 2012 at 6:03 am
Yes, I can login and successfully issue select queries locally.
August 9, 2012 at 6:03 am
Check the SIDs of both users on each server, I suspect they are different.
If they are you need to add a mapping on the linked server configuration to mapp LL_LOGIN to LL_LOGIN, granted this sounds strange, but as the SIDs dont match they are not the same user, even if the usernames and passwords are the same.
August 9, 2012 at 6:06 am
MI_DBA (8/9/2012)
Yes, I can login and successfully issue select queries locally.
Can you login to Server B from Server A with the LI_LOGIN account?
Is LI_LOGIN a Windows och SQL Server account?
August 9, 2012 at 6:10 am
SIDs are exactly same:
0x3DA4C364F7CBF54DB2E25F6C18376DC8
0x3DA4C364F7CBF54DB2E25F6C18376DC8
August 9, 2012 at 6:11 am
LI_LOGIN is a sql account
August 9, 2012 at 9:35 am
Do you have any explicit login mappings on this linked server?
Have you tried an explicit mapping for LI_LOGIN to LI_LOGIN?
I'm able to recreate the problem you're experiencing by adding a login mapping for LI_LOGIN without specifying the remote login/password:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LI_SERVER', @locallogin = N'LI_Login', @useself = N'False', @rmtuser = N'', @rmtpassword = N''
So there's a login mapping for LI_LOGIN, therefore it won't use the default below, but the mapping isn't valid, hence the error. If I fix the mapping:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LI_SERVER', @locallogin = N'LI_Login', @useself = N'False', @rmtuser = N'LI_LOGIN', @rmtpassword = N'pass'
GO
The link works. I'm not sure if this is what you're running into, but it's what came to mind.
August 10, 2012 at 9:11 am
Under Linked Server properties, is Data Access, RPC and RPC Out set to true ??
Graeme
August 10, 2012 at 9:13 am
ALso, I assume you you don't have a login mapping as you are using a specific remote login correct ??
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply