Linked server error - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Linked server error

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-21 : 13:25:20

Hi,
I am working on sql server 8.0.818 workgroup edition..
I want to join a table from it to a different server having sql server 2005 enterprise edition.
I created a linked server on the workgroup edition ...
When i try to run hte query i get the following error:-

Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 13:35:09

How did you set up the linked server?

What security context are you using?

Can you connect to the SQL 2005 instance?

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-21 : 13:44:39

I set up linked server by SSMS gui..
I set the options Data access, rpc and rpc out as true ,rest are false..
Security as
local login: sa
remote user : sa
remote password : ....
The logins not mentioned in this list, connection will be made widout using the security context.

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 14:23:44

Login mapping must be from a domain account on the local server to a SQL account on the remote server.

Are you using Windows authentication to login to the local server? If so, just use the "Be made using the logins current security context"

You can always choose "Be made using this security context" and add a SQL login and password, but that is not best practice from a security standpoint.

See here

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-21 : 14:49:52

@ ruseell
I did that also, no effect same error

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 16:25:04

did you spell the server name correctly?

is it a named instance or a default instance?

is remote access enabled?

can you ping the remote server?

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-21 : 16:39:56

I spelled it correctly

Its a default instance so wat shld i write in the servername?

How to check if remote access enabled??

Yes, i can ping the remote server

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 16:47:30

Can you connect to it using SSMS?

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-21 : 17:33:45

yes

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 17:42:40

Right click it in the object explorer, click properties. Go to the connections tab, make sure Allow Remote Connections to this server is checked.

If that's not it, then go back to your linked server in the GUI, right-click it and script it out. Post that here.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-22 : 09:02:31

Allow remote connections is checked...
The script for linked server :--

/****** Object: LinkedServer [RTW-PASEA-MBS1] Script Date: 03/22/2011 09:01:05 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'RTW-PASEA-MBS1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RTW-PASEA-MBS1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RTW-PASEA-MBS1', @optname=N'use remote collation', @optvalue=N'false'

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 09:10:39

You're using 4 part naming convention in your query?

SELECT xxx FROM [RTW-PASEA-MBS1].[databaseName].[dbo].[tableName]

You can connect to the server using credentials used in the linked server properties?

Did you remove the custom mappings you tried to create?

You realize, that by using sa, that everyone who can connect to the local server at all, is now sa on the remote server, right?

By the way, these are on the same network, right? Can you ping the remote server logged into the local one?

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-22 : 10:07:39

ya im using 4 naming conventions in hte query
yes, i can connect.
remove the custom mappings? i dont know

they are not on the same network.

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 10:21:55

Ahaaa, now we're getting somehwere. Can the remote server be reached from the local server?

From the local server, start/run/cliconfg <enter>

Create an alias for the remote server using IP address. Then use that alias in your linked server configuration.

skybvi
Posting Yak Master

193 Posts

Posted - 2011-03-22 : 12:01:00

I cant make rdc on the local server. Although i can connect to its hard drive.

Regards,
Sushant
DBA
West Indies

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 12:07:36

Then have your admin do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources