creating linked server

Question

Post reply

creating linked server

V.Rajagopalan

Valued Member

Points: 60
More actions
August 27, 2007 at 6:44 am

#121266

In my system i have one local server, there is one more remote server which is connected through lan i want to connect these two server and retrieve some details by using sql query.when i try to link the server i face this error
"There is no security context that can be used to verify if the specified SQL server exists. Would you like to continue to create the linked server?"
Can any one help me on this problem.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

How did you create the linked server? Did you specify the login mappings on the security tab?

Regards,Yelena Varsha

V.Rajagopalan Valued Member Points: 60 More actions · Answer 2

Before creating the Linked server, I linked the other server name

'Prod' which is connected in lan by using Server Registeration option available in SQL Enterprise manager . So when i open the Sql Server Enterprise manager it shows both the server

ie., local server name '

MIS' and newly registered server 'Prod'. By pressing security option, under 'Prod' server i select linked server option and under server type i mentioned Sql server type and mention the name of newly registered server 'Prod'. In security option i mentioned nothing and select the option 'Be made without using a security context'. After this it shows the error 'OLE DB provider 'SQLOLEDB' reported an error.

I tried another way ie.,

enter the Remote login : after select the option in security tab 'Be made using the security context'. after this method it shows some of the tables which is no way connected with the server 'Prod'.

what should i do?

My ultimate aim is, I want to retrieve the data from different tables available in both local server and new registered server. Is it really possible. If it is possible, pls let me know the method.

Thanks.

V.Rajagopalan Valued Member Points: 60 More actions · Answer 3

Before creating the Linked server, I linked the other server name

'Prod' which is connected in lan by using Server Registeration option available in SQL Enterprise manager . So when i open the Sql Server Enterprise manager it shows both the server

ie., local server name '

MIS' and newly registered server 'Prod'. By pressing security option, under 'Prod' server i select linked server option and under server type i mentioned Sql server type and mention the name of newly registered server 'Prod'. In security option i mentioned nothing and select the option 'Be made without using a security context'. After this it shows the error 'OLE DB provider 'SQLOLEDB' reported an error.

I tried another way ie.,

enter the Remote login : after select the option in security tab 'Be made using the security context'. after this method it shows some of the tables which is no way connected with the server 'Prod'.

what should i do?

My ultimate aim is, I want to retrieve the data from different tables available in both local server and new registered server. Is it really possible. If it is possible, pls let me know the method.

Thanks.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Did you see the today's article by Greg Larsen? There are several steps for the linked servers that can help

http://www.databasejournal.com/features/mssql/article.php/3696506

Setting Up Delegation for Linked Servers

Regards,Yelena Varsha

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 5

create a new login/password on the target server that you want to link to. Make this login a user on the databases that you want to query, and ensure that the user has (at least) datareader access to the required tables. Check by connecting to the target database via query analyzer using the login/password that you just created. If you can retrieve data, proceeed to the next step:

on the host server, add a new linked server (the target server) using the new login/password as authentication. No need for user mapping or delegation etc - not in my experience anyway. Once the new linked server has been added, try querying the target database from the host server. You will need to use four part naming in the query - e.g. Select * from server.northwind.dbo.orders.

Mal D SSC Enthusiast Points: 196 More actions · Answer 6

Try the following on your local server, remember you will need to set the security context.

--SCRIPT START

Create Procedure usp_AddlinkedServer (

@a_Server_name varchar (50),

@a_errorText varchar(200)OUTPUT,

@a_return int OUTPUT

)

as

Declare

@v_error int

set @v_error = 0 -- Assume no Error

set @a_errorText = '' -- Assume no Error Text.

EXEC master.dbo.sp_addlinkedserver @server = @a_Server_name, @srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'collation compatible', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'use remote collation', @optvalue=N'true'

set @v_error = @@error

if @v_error = 0

begin

set @a_return = 0

end

else

set @a_return = @v_error

--SCRIPT END

CodeOn

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

keep in mind that to be able to create a linked server (from SQL2005) to SQL2000, you need to run an extra script at SQL2000 side.

KB ref at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 8

re: "need to run an extra script"

I haven't had to do this - perhaps its only required for 64 bit installations of SQL 2005 where linked to SQL 2000?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

can you script and post your "create linked server" DDL (and the details) ?