Linked server security problems


I have a SProc that is set to run on Server - it calls and makes some changes to remote DB on another server.
I have created a SQL user account with basic priveleges and used it to set up a linked server connection between the 2 servers.
The account is called 'LinkedUser' for example.

The account has basic priveleges to the databases it needs to run against.

I am trying to call the SProc from a SQL Agent job.
But when I try and run the job get error message
"Access to the remote server is denied because no login-mapping exists. "

The job is set to run as SA..

How can I get around this ?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

chapmandewConnect With a Mentor Commented:
mooriginalAuthor Commented:
thanks i hadnt seen that - but it didnt help
its not helped
1.i dont use client OLE
2. also tried the option that the remote login i use for the connection is also a remote login on the target server...

If I could get SQL Agent to run the job as linkedsvruser [ my login ive created to make the linked connection] then it fix my problem
But when I try to add the SQL login Linkedsvruser - from the job properties I cant see this login to add it - ive given this login now SYSADMIN rights on the server to see if would show up in the list but it still doesnt

my connection string
/****** Object:  LinkedServer [CALLIOPE]    Script Date: 10/03/2008 10:45:08 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CALLIOPE', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo

Open in new window

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Ok...let me ask you thin.  when you created the linked server, did you do so through the interface (SSMS) or did you do it through a stored procedure?
mooriginalAuthor Commented:
erm interface

I picked SQL server - gave it that server name
then picked security - local login - remote user
made sure that both these accounts matched and existed on both source and target servers

for login not definded in above list - said connections will Not Be Made.
mooriginalAuthor Commented:
I have made sure that the 'test' login is valid sql login on Source server and the Target server [MINOS\SQL200564].

I have added the linked server using the following below.

I then open a query connection using sa account and try and run this :
select * from TEST_LINK.DBA.dbo.checkedout_mabel

I get error:
Login failed for user 'sa'.
OLE DB provider "SQLNCLI" for linked server "TEST_LINK" returned message "Invalid connection string attribute".

EXEC sp_addlinkedserver
@server = 'TEST_LINK',
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = 'DRIVER={SQL Server};SERVER=MINOS\SQL200564;UID=test;PWD=password;'

Open in new window

All Courses

From novice to tech pro — start learning today.