linked server problem on SQL Server 2000 sp3

Hello
im trying to connect to a remote server over an internet vpn connection
i asked admin of the remote location to open port 1433 in his firewall, he
did and im able to register the server successfully by IP address on
enterprise manager
i can also make a connection with query analyzer with no problem and select
from tables normally
however when i try to establish a linked server using OLEDB for SQL server ,
connection fails , properties of the connection is ,
product name: empty
data source: the IP address of the server
provider string and catalog: empty
security: no rows in local server login to remote server login mapping but
chose last option in
for a login not defined in the list above connection will: i chose
be made using this security context and i wrote sa and password i used
before in registering server as a test
what is wrong , does linking a server require opening additional ports in
firewall ' which query analyzer does not need if server is registered
normally not linked?
any help very appreciated
BassamBassam (bassam@.nptco.com.eg) writes:
> im trying to connect to a remote server over an internet vpn connection
> i asked admin of the remote location to open port 1433 in his firewall,
> he did and im able to register the server successfully by IP address on
> enterprise manager i can also make a connection with query analyzer with
> no problem and select from tables normally
> however when i try to establish a linked server using OLEDB for SQL
> server , connection fails , properties of the connection is ,
> product name: empty
> data source: the IP address of the server
> provider string and catalog: empty
And the error message is?
You could consider defining a provider string for the linked server. That's
all I can suggest without knowledge about the error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Error msg is :
SQL Server does not exist or access denied.
i tried to define a provider string also by format
srever=ip address;database=xx;user id=xx ; password=xx
but did not work either
question is do i have to open port UDP 1434 also in the firewall ?
Regards
Bassam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760ACD558EC9Yazorman@.127.0.0.1...
> Bassam (bassam@.nptco.com.eg) writes:
> And the error message is?
> You could consider defining a provider string for the linked server.
> That's
> all I can suggest without knowledge about the error.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Bassam (bassam@.nptco.com.eg) writes:
> Error msg is :
> SQL Server does not exist or access denied.
> i tried to define a provider string also by format
> srever=ip address;database=xx;user id=xx ; password=xx
> but did not work either
> question is do i have to open port UDP 1434 also in the firewall ?
I don't think that would matter.
Is SQL Server running on the same machine as you run QA/EM from? If not,
maybe the remote DBA only opened the firewall for one IP address?
Under which Windows account do SQL Server run? You can check this in
Computer Management/Services. If SQL Server is running under LocalSystem
try changing it to domain account, and restart SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Linked Server Port 1433 Alt

I have a Sql Server 2k that needs a linked server set up to connect to a
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?
You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:

> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>

Linked Server Port 1433 Alt

I have a Sql Server 2k that needs a linked server set up to connect to a
remote SQL Server 2k. The remote server is behind a firewall, and I don't
want to open up port 1433 to the whole world.
What other options are there to set up a linked server that is secure?
What is the recommended solution?You could run SQL Server on a different port, and open that up in your
firewall instead.
Jamie Clancy
Ulysses Systems, London
"Dan" wrote:

> I have a Sql Server 2k that needs a linked server set up to connect to a
> remote SQL Server 2k. The remote server is behind a firewall, and I don't
> want to open up port 1433 to the whole world.
> What other options are there to set up a linked server that is secure?
> What is the recommended solution?
>

Linked server performance

SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe

Linked Server not wanting to connect

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB19', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB19',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation compatible', @.optvalue=N'false'

GO

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

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'sub', @.optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB21', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB21',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation compatible', @.optvalue=N'false'

GO

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

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'sub', @.optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

=========== CODE END ==========

Now I have masked the real values in this post of the @.provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.

@.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

Is the remote server AUS02DB21 a 2005 server? Does it enable remote access?

Can you access the server from your utility server by SSMS?|||

Yes ... the instance I am creating the linked server to is a SQL Server 2005 SP2 instance.

It has remote access enabled.

I am also able, and have for quite some time, a registered instance in SSMS on the server I am creating the linked server on.

What is interesting is that DB19 and DB21 are identical servers in our farm. Same hardware and software.

I am now getting this error message when I try to view the Catalogs of the newly created linked server:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

TCP Provider: The specified network name is no longer available.
(Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

BUTTONS:

OK

Any clue with this added error message?

Thanks for all the help!!

|||

I have found something rather interesting.

In the Windows Application event log I am seeing error about a connection coming from the server I have the linked server created on trying to access the DAC.

Here is the event:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 10.100.10.7]

So why in the heck is the DAC being accessed?|||

I got the same problem on SQL server 2005 SP2.

It creates the link but when I try to test it: "The test connection to the linked server failed."An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "server_name" returned message "Invalid server name was specified.". (.Net SqlClient Data Provider)

I'm trying to establish a linked server between SQL server 2005 SP2 and a Sybase server (ASE)

Thanks

|||

Hey, I got it working:

I had to put a provider string:

Server Name=server_name,port_no

(server_name would be what ever your servername would be

port_no: 5000 for sybase ASE)

now I need to know of a way to replicate the server w/out having the replication agent on the server (since the admin of the ASe server doesn'T want to unable this option agrrr!!!)

sql

Linked Server not wanting to connect

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB19', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB19',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation compatible', @.optvalue=N'false'

GO

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

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'sub', @.optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB21', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB21',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation compatible', @.optvalue=N'false'

GO

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

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'sub', @.optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

=========== CODE END ==========

Now I have masked the real values in this post of the @.provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.

@.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

Is the remote server AUS02DB21 a 2005 server? Does it enable remote access?

Can you access the server from your utility server by SSMS?|||

Yes ... the instance I am creating the linked server to is a SQL Server 2005 SP2 instance.

It has remote access enabled.

I am also able, and have for quite some time, a registered instance in SSMS on the server I am creating the linked server on.

What is interesting is that DB19 and DB21 are identical servers in our farm. Same hardware and software.

I am now getting this error message when I try to view the Catalogs of the newly created linked server:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

TCP Provider: The specified network name is no longer available.
(Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

BUTTONS:

OK

Any clue with this added error message?

Thanks for all the help!!

|||

I have found something rather interesting.

In the Windows Application event log I am seeing error about a connection coming from the server I have the linked server created on trying to access the DAC.

Here is the event:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 10.100.10.7]

So why in the heck is the DAC being accessed?|||

I got the same problem on SQL server 2005 SP2.

It creates the link but when I try to test it: "The test connection to the linked server failed."An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "server_name" returned message "Invalid server name was specified.". (.Net SqlClient Data Provider)

I'm trying to establish a linked server between SQL server 2005 SP2 and a Sybase server (ASE)

Thanks

|||

Hey, I got it working:

I had to put a provider string:

Server Name=server_name,port_no

(server_name would be what ever your servername would be

port_no: 5000 for sybase ASE)

now I need to know of a way to replicate the server w/out having the replication agent on the server (since the admin of the ASe server doesn'T want to unable this option agrrr!!!)

Linked Server loses login-mapping

I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.

Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...

"Access to the remote server is denied because no login-mapping exists."

I am confused with the error message as it happens only after some successful insertions....

Any help is apprecaited...

Thanks

Please refer to BOL. A couple of links that hopefully will help you to get started:

· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx

· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx

If you have additional questions we will be glad to help.

-Raul Garcia

SDE/T

SQL Server Engine

Linked Server loses login-mapping

I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.

Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...

"Access to the remote server is denied because no login-mapping exists."

I am confused with the error message as it happens only after some successful insertions....

Any help is apprecaited...

Thanks

Please refer to BOL. A couple of links that hopefully will help you to get started:

· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx

· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx

If you have additional questions we will be glad to help.

-Raul Garcia

SDE/T

SQL Server Engine

Linked server DTS issue

Hi need help, I am using Linked server to connect to a oracle server and transferring the data to the local tables, it is working fine if the join transaction is not selected, if we select it it fails with the following error: "MSDASQL was unable to run distributed transaction", the join transaction unit is required as I need to rollback the deletion of data from the tables in the first step of package. Any help will be appreciated. Transaction coordinator is running.My Oracle knowledge is pretty thin but I am betting the MSDTC does not work with Oracle. I would google it.

Linked server connection intermittent

I have some SQL server jobs that connect to a linked DB2 on an AS400 and
fetches some data and inserts into SQL server. The AS400 is set up as a
linked server. The jobs fail at times and work at others. The error messag
e
I get when trying to look at the tables through Enterprise manager is
Error 7399: OLE DB provider 'MSDASQL' reproted an error.
Driver's SQLSetConnectAttr failed]
communication link failure. comm rc=12 - CWB0999 - Unexpected error:
unexpected return code 12]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize;;Initialize
returned 0x80004005: ].
The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
If I reboot SQL server then jobs run fine, and at times just doing nothing
(no reboot or stop/start of SQL server) also sometimes works.Intermittent problems are pretty difficult to troubleshoot
on newsgroups. In your case, I've generally seen the issues
due to old drivers, issues on the remote data source. But
other things can be causing the problems as well.
A couple of things you can try to stabilize the connectivity
would be to try the Client Access drivers or providers
instead of using the OLE DB provider for ODBC. That provider
is now considered obsolete by Microsoft.
In addition, when using the Client Access components, you
need to make sure to keep the patch level of the server
where CA is installed up to date with the server. I've run
into issues with linked servers to the 400 when the 400 is
patched and CA isn't updated.
-Sue
On Fri, 14 Jan 2005 14:11:03 -0800, "jddotman"
<jddotman@.discussions.microsoft.com> wrote:

>I have some SQL server jobs that connect to a linked DB2 on an AS400 and
>fetches some data and inserts into SQL server. The AS400 is set up as a
>linked server. The jobs fail at times and work at others. The error messa
ge
>I get when trying to look at the tables through Enterprise manager is
>Error 7399: OLE DB provider 'MSDASQL' reproted an error.
>Driver's SQLSetConnectAttr failed]
>communication link failure. comm rc=12 - CWB0999 - Unexpected error:
>unexpected return code 12]
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize;;Initialize
>returned 0x80004005: ].
>The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
>If I reboot SQL server then jobs run fine, and at times just doing nothing
>(no reboot or stop/start of SQL server) also sometimes works.
>

Linked server connection intermittent

I have some SQL server jobs that connect to a linked DB2 on an AS400 and
fetches some data and inserts into SQL server. The AS400 is set up as a
linked server. The jobs fail at times and work at others. The error message
I get when trying to look at the tables through Enterprise manager is
Error 7399: OLE DB provider 'MSDASQL' reproted an error.
Driver's SQLSetConnectAttr failed]
communication link failure. comm rc=12 - CWB0999 - Unexpected error:
unexpected return code 12]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize;;Initialize
returned 0x80004005: ].
The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
If I reboot SQL server then jobs run fine, and at times just doing nothing
(no reboot or stop/start of SQL server) also sometimes works.
Intermittent problems are pretty difficult to troubleshoot
on newsgroups. In your case, I've generally seen the issues
due to old drivers, issues on the remote data source. But
other things can be causing the problems as well.
A couple of things you can try to stabilize the connectivity
would be to try the Client Access drivers or providers
instead of using the OLE DB provider for ODBC. That provider
is now considered obsolete by Microsoft.
In addition, when using the Client Access components, you
need to make sure to keep the patch level of the server
where CA is installed up to date with the server. I've run
into issues with linked servers to the 400 when the 400 is
patched and CA isn't updated.
-Sue
On Fri, 14 Jan 2005 14:11:03 -0800, "jddotman"
<jddotman@.discussions.microsoft.com> wrote:

>I have some SQL server jobs that connect to a linked DB2 on an AS400 and
>fetches some data and inserts into SQL server. The AS400 is set up as a
>linked server. The jobs fail at times and work at others. The error message
>I get when trying to look at the tables through Enterprise manager is
>Error 7399: OLE DB provider 'MSDASQL' reproted an error.
>Driver's SQLSetConnectAttr failed]
>communication link failure. comm rc=12 - CWB0999 - Unexpected error:
>unexpected return code 12]
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize;;Initialize
>returned 0x80004005: ].
>The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
>If I reboot SQL server then jobs run fine, and at times just doing nothing
>(no reboot or stop/start of SQL server) also sometimes works.
>

linked server can not connect using Named Pipes

Push Transactional Replication

Replication was running fine, we switched one of the servers in the local lan to named pipes.

We can still connect to the database with Query Analyzer (QA) and Enterprise Manager (EM) but replication failed.

We realized that the linked server on the distributer for that subscriber could not connect.

We switched back to TCP and all is well again.

We can issue a Net Use \\server\ipc$ with success so it seems the pipe is ok, and we can still connect with QA and EM.

Why would the protocol make a difference. Why can't we connect a linked server with Named Pipes when it connects fine with TCP, and why even though the linked server can not connect can swe till connect with QA or EM.

More info please:

What agent is failing, distribution agent? Is logreader/snapshot agent functioning properly?

Which machine was configured to use named pipes, distributor or subscriber?

Did you change this at the client level or server level?

When you use QA/EM, are you connecting locally or remotely?

What error are you getting?

Linked server against DB2 using DirectLink driver.

Hi,
does anyone know if the DataDirect Connect driver for DB2 driver support
four-part-names?
I'm testing this driver and have been able to set up a linked server and
query the database using the openquery syntax, but when I use syntax with
four-part-names or try to view the DB2 tables in EM I just get error
messages.
( I get the following when trying to view the tables:
"Error 7399: OLE DB provider 'UNKNOWN' reported an error.
String data translation caused data truncation.]
OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchmaRowset::GetRowset
returned 0x80004005: ]. " )
Tia, Kjell.Kjell,
I suggest you try with another driver to see if the problem is related
to the driver (ie StarSQL, or the IBM's driver).
Bob
"Kjell Nilsen" <xyzzyxnilsen_kjell@.hotmail.com> wrote in message news:<epENJJkJEHA.2244@.tk2m
sftngp13.phx.gbl>...
> Hi,
> does anyone know if the DataDirect Connect driver for DB2 driver support
> four-part-names?
> I'm testing this driver and have been able to set up a linked server and
> query the database using the openquery syntax, but when I use syntax with
> four-part-names or try to view the DB2 tables in EM I just get error
> messages.
> ( I get the following when trying to view the tables:
> "Error 7399: OLE DB provider 'UNKNOWN' reported an error.
> String data translation caused data truncation.]
> OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchmaRowset::GetRowse
t
> returned 0x80004005: ]. " )
> Tia, Kjell.

Linked server against DB2 using DirectLink driver.

Hi,
does anyone know if the DataDirect Connect driver for DB2 driver support
four-part-names?
I'm testing this driver and have been able to set up a linked server and
query the database using the openquery syntax, but when I use syntax with
four-part-names or try to view the db2 tables in EM I just get error
messages.
( I get the following when trying to view the tables:
"Error 7399: OLE DB provider 'UNKNOWN' reported an error.
String data translation caused data truncation.]
OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchmaRowset::GetRowset
returned 0x80004005: ]. " )
Tia, Kjell.
Kjell,
I suggest you try with another driver to see if the problem is related
to the driver (ie StarSQL, or the IBM's driver).
Bob
"Kjell Nilsen" <xyzzyxnilsen_kjell@.hotmail.com> wrote in message news:<epENJJkJEHA.2244@.tk2msftngp13.phx.gbl>...
> Hi,
> does anyone know if the DataDirect Connect driver for DB2 driver support
> four-part-names?
> I'm testing this driver and have been able to set up a linked server and
> query the database using the openquery syntax, but when I use syntax with
> four-part-names or try to view the db2 tables in EM I just get error
> messages.
> ( I get the following when trying to view the tables:
> "Error 7399: OLE DB provider 'UNKNOWN' reported an error.
> String data translation caused data truncation.]
> OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchmaRowset::GetRowset
> returned 0x80004005: ]. " )
> Tia, Kjell.

linked server 2005 problems

Hi,
I've got problem with configuring a linked server within SQL server 2005. I
am trying to connect to SQL 2000. The link from 2000 to my 2005 server has
worked fine. But not the other way. Also, the 2000 is linkable from other
2000 servers so seems to be specific to 2005 not a problem with the 200
sercer. My version is developer edition could that be the issue? didn't see
something speficic for that.
This is the error message
OLE DB provider "SQLNCLI" for linked server "svr30" returned message "Login
timeout expired".
OLE DB provider "SQLNCLI" for linked server "svr30" returned message "An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].
The other server is configured to allow remote connectins that's why other
2000 can see it.
I've tried different options on the link but none worked... This is what I
have at the moment...
/****** Object: LinkedServer [SVR30] Script Date: 05/30/2006 18:11:36
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'SVR30', @.srvproduct=N'SVR30',
@.provider=N'SQLNCLI', @.datasrc=N'SVR30', @.catalog=N'lso'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'data access',
@.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'rpc',
@.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'rpc out',
@.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'query timeout',
@.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'SVR30', @.optname=N'use remote
collation', @.optvalue=N'true'
Does anybody know if something is wrong? Thanks.
Panos.Maybe you want to check this link..
http://www.aspfaq.com/show.asp?id=2544|||Nah, nothing useful in the link. I've also tried to set-up the same linked
server on another sql server 2005 which I've installed on my PC. Linked
servers work fine on my local sql 2005. So there is either something
different on the network for that particular server or some setting is
different. I've checked both 2005 servers local and problematic and they see
m
to have the same settings. Any other ideas? Thanks.
Panos.
"Omnibuzz" wrote:

> Maybe you want to check this link..
> http://www.aspfaq.com/show.asp?id=2544
>|||Well...
Then it looks like a network issue.
Check this link.. A discussion on an issue very similar to what you face :)
http://forums.microsoft.com/MSDN/Sh...327775&SiteID=1
Let me know if you resolve it.

Linked server

I'm trying to connect to a DB2/400 v4r creating a linked server on the
Enterprise Manager of my MSSQL 2000 box, but i receive the following message
when i try to expand the TABLES node on the tree:
Error 7302: Could not create an instance of OLE DB Provide 'IBMDA400'.
OLE DB error trace [non-interface error: CoCreate of DSO for IBMDA400
returned 0x80040154]
The version of CA400 installed on the MSSQL box is V4R5M0.
ThanksGenerally the error is caused by the provider not being
registered on the machine or from not having the in process
option set for the provider.
For the provider options, if you go to the linked server in
enterprise manager, view the properties and select Provider
Options. Then make sure the Allow InProcess option is
selected.
-Sue
On Mon, 15 Mar 2004 10:55:36 GMT, "Fabio Benedini"
<f.benedini@.magconsulting.it> wrote:

>I'm trying to connect to a DB2/400 v4r creating a linked server on the
>Enterprise Manager of my MSSQL 2000 box, but i receive the following messag
e
>when i try to expand the TABLES node on the tree:
>
>Error 7302: Could not create an instance of OLE DB Provide 'IBMDA400'.
>OLE DB error trace [non-interface error: CoCreate of DSO for IBMDA400
>returned 0x80040154]
>The version of CA400 installed on the MSSQL box is V4R5M0.
>Thanks
>|||Thank you Sue,
I've applied your tip and now I don't get that message, but now when I try
to expand the Tables node, my Enterprise Manager seems to hang. I tried to
wait for over 20 mins but i had to close it from the Task Manager.
Could you give me an example of a working configuration of a linked server
to DB2/400?
Thanks
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:l8eb50dfufaqvf68ugecj8aagokm5pop0h@.
4ax.com...
> Generally the error is caused by the provider not being
> registered on the machine or from not having the in process
> option set for the provider.
> For the provider options, if you go to the linked server in
> enterprise manager, view the properties and select Provider
> Options. Then make sure the Allow InProcess option is
> selected.
> -Sue
> On Mon, 15 Mar 2004 10:55:36 GMT, "Fabio Benedini"
> <f.benedini@.magconsulting.it> wrote:
>
message
>|||If you install Client Access on the server, it will install
and configure the drivers and providers for the AS400 on the
SQL Server box so that you can use linked servers. You
should also make sure you have the client updated with what
whatever service pack level based on updates to the AS400.
There is a link to update this in the Client Access program
group - I think that's where it is, can't remember off the
top of my head. For data source, use the IP address of the
AS400. For provider string, you need to include the library
you are using, connect timeout setting and code page. There
is some documentation for the settings in the Client Access
help files. You'd set the provider string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Mon, 15 Mar 2004 15:22:36 GMT, "Fabio Benedini"
<f.benedini@.magconsulting.it> wrote:

>Thank you Sue,
>I've applied your tip and now I don't get that message, but now when I try
>to expand the Tables node, my Enterprise Manager seems to hang. I tried to
>wait for over 20 mins but i had to close it from the Task Manager.
>Could you give me an example of a working configuration of a linked server
>to DB2/400?
>Thanks
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:l8eb50dfufaqvf68ugecj8aagokm5pop0h@.
4ax.com...
>message
>