Linked Server B/w two SQL SERVER 05 instances (on differnt pc;s) VERY URGENT

I created a linked server named CONFUSION (as the machine name with perticular IP say a.b.c.d)

TESTED the connection by

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

CREATE TABLE #foo
(
pingResult SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'ping CONFUSION';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

DROP TABLE #foo;

I executed the above code and the result was :::

(13 row(s) affected)
Feel free to use linked server.

THAT means my linked server is perfect :::

but when I write the query

select * from CONFUSION.coindb.dbo.test

output is

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'pss2600'.
- INFO --
pss2600 is a user in both sql servers (with all previlages) and I am loged in as root on both machines, while creating a linked server I choosed the option "be made using the security context" user : pss2600 and pwd: XXXX.

What am I doing wrong ?
in both the servers I have the coindb database with the table test.

ITS VERY VERY URGENT

Are you using SQL Server authentication?

...on both servers?

If so does the user have the same password on both servers?

|||You haven't mentioned the SQL server version being used here, I believe there was some issue previous version of SQL.|||I am using SQL Server 2005 on both servers and also authentication mode is ( both ) that is window + SQL Server authentication mode.

user:

I can login into the machine in two ways:

1. As admin (of that machine - local)
2. user of the whole network (which includes UNIX machines + Windows + Mac)

In this perticular case, I logged in into the machies (both ) as admin and when I created a linked server I used the user name and password by which I can login into any machine (network user)

I also created the same user in sql server too (with same password I use to loggin into the network).

Thanks for your replies please let me know if you want any other details too

Pardeep|||

Sorry this isn't helping...

If pss2600 is a Login (servers have logins, databases have users) on both servers

and

pss2600 is created as a Login with SQL Server Authentication on both servers.

and

pss2600 has the same SQL Server Authentication password on both servers

and

you use the "Be made using this security context " option as shown in this quote from Books Online:

Be made using this security context

Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

You should not see the error.

The login you used to create the Linked Server is unimportant.

|||Even I have seen many articles and issues like these, but this is weired.

Can you tell me any way by which I can check (with or without liked server) if I can connect to say server A (SQL Server 2005) from machine say server B.

kinda of remote login (right that what we say). There could be the problem that, server A does not accept remote connection ? how to check this ? and what could be the solution if that what the case is.

Thanks for your response, but its still not working !!|||

I would expect to see a no remote connections allowed error before a Login failed error.

You could try this adhoc alternative to a linked server:
OPENROWSET (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190312.aspx

The Ping test you show should work with any server, linked or not.

Can you use your linked server with any account, say sysadmin or Windows account that is a Login on both servers.

Can you login to both servers from SSMS on both servers?

|||Dear LKH

YOU WROTE

"pss2600 is created as a Login with SQL Server Authentication on both servers."

Though, I selected the option "both SQL Server Authentication and Window Authentication" as previously it was on Window Authentication mode, I actually did not made a separate user with the same login ID and PWD in SQL Server Authentication mode.

THANK A LOTS !!!

now the query

select * from CONFUSION.coindb.dbo.test UNION select * from test

WORKS perfectly !!!!!
-
NOW I have to build an C# application over it (just to demonstrate my senior that it works)
I will get back to you, If you had any problem .. THANKS a lots again !!!

Pardeep
|||Hello lkh

NOW, we have a third server in the picture that is Oracle 10g XE in the same LAN.
I created a same table test(name varchar(10)) in Oracle 10g XE by login pss2600 in windows as well as in 10g. (machine name on which 10 g XE is 'don'

I created a linked server to 10g as follow

1. Right click on linked server, create new linked server
Linked Server : SERVER2
other data source : Microsoft OLEDB DB Provider for Oracle
Data sourse : don
provider string : OraOLEDB.Oracle

security :
be made using security context
username : pss2600 , pwd : same every where
Server options : rcp true, rpc out true, data access true

and if I run this now :

CREATE TABLE #foo
(
pingResult SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'ping SERVER2';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

it shows :

(2 row(s) affected)
Linked server not available.

every thing else is same, like firewall is turned off ..

I will really appreciate if you can get me out of this too.

Pardeep|||

For testing the linked server connectivity you can use the following stored procedure

sp_testlinkedserver [ @.servername ] = servername.

If this command works then there should not be any problem in accessing the data from linked server.

|||Hi ,

sp_testlinkedserver @.server = SERVER2

doest work. What I think is its because I am not setting up the Linked Server as it is supposed to be with Oracle 10g XE.

if you know any good articles or anyone got into same problem, that will aslo be help

I have already seen http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1040216&SiteID=1

but I was unable to make it working !!!!

Thanks for replying Rajesh|||

I copied your ping test to my machine and I can successfully ping any server in my domain even though it is not a linked server. So, if you get Linked server not available that tells me that the two servers can't talk regarless of their being linked.

Here is a link on Oracle linked servers in case you haven't already found it:

http://support.microsoft.com/kb/280106

My next check would be correct premissions in Oracle but it has been a few years since I worked with 9g

Rajesh is correct about sp_testlinkedserver [ @.servername ] = servername, with this caution in Books Online:

"No permissions are checked; however, the caller must have the appropriate login mapping."