Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Linked server security problems

Hi

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 ?

0
mooriginal
Asked:
mooriginal
  • 3
  • 3
1 Solution
 
chapmandewCommented:
0
 
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'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CALLIOPE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo

Open in new window

0
 
chapmandewCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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.
0
 
chapmandewCommented:
0
 
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

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now