Can't log in with Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Can't log in with Linked Server

Author

Topic

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-07-05 : 12:41:53

I am trying to create a linked server connection. I have tried the following things:


exec master..sp_addlinkedserver 'AUDITCONN', ' ', 'MSDASQL', NULL, NULL,
'Driver={SQL Server};Database=AUDITDB;Server=AUDITSRV;UID=auditor;PWD=tattletale;'
go

and...


sp_addlinkedserver N'AUDITCONN', ' ', N'SQLOLEDB', N'AUDITSRV'
go
sp_addlinkedsrvlogin N'AUDITCONN', false, NULL, N'auditor', N'tattletale'
go

However, when I attempt this:


select top 1 * from [AUDITCONN].AUDITDB.dbo.sql_servers

With the first linked server creation method, I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

With the second method, I get:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

I read http://support.microsoft.com/kb/314530, but that seems more for timeout issues. (I tried adjusting the timeout anyway, with no luck.)

I know the password is correct and that the login exists on the linked server. I also tried creating the logon on the other server as well, but that had no effect either. This does work on other servers. The servers it works on seem to be running standard edition, and the servers it is failing on are running personal edition. Would that have anything to do with it?

Any advice or help is appreciated.

Thanks.

-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-05 : 12:46:57

Tried create linked server in em? Can you register server AUDITCONN in local em?

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-07-05 : 14:46:07

I have tried the following in EM:
- Registering the Audit server from the remote server: Same error.
- Registering another server from the remote server: Works fine.
- Registering the remote server from the audit server: Same error.
- Registering the remote server from another server: Works fine.

The audit server and the 'other' server are sitting right next to each other. The remote server is across the state. The audit server and 'other' server are communicating fine, as well.

I have also duplicated this issue and all the same results on two other remote servers as well.
____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-07-05 : 15:44:46

This has turned out to be a firewall issue. SQL traffic was not allowed through specifically to the audit server from the remote sites. The network admins have made the change and we should be OK in the morning.

rmiao, Thanks for your help.

-D.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources