SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
JohnI suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label server1. Show all posts
Showing posts with label server1. Show all posts
Friday, March 23, 2012
Linked SQL Server and Windows 2003 Firewall/SCWQL
SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
John
I suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>
|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
John
I suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>
|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***
Wednesday, March 21, 2012
servers and partitioned view?
Hi,
I have 2 database servers running W2K/SQL 2000 with Gigabit Ethernet between
them. Server1 contains 2003 and 2002 data in one database. Server2 contains
2001 data in one database.
I have an web application that points to Server1 all the time. So, I created
a linked server from Server1 to add Server2 in. I then created Server1.VIEWS
of all tables from Server2.database.dbo.table_name...
The problem is that accessing data via Server1.VIEWS from the web
application is very slow. Reports aren't performing for 2001 data
physicially stored on Server2. The reports aggregate millions of rows on
each table.
what are ways to improve query performance via linked servers? I still want
to keep one web application pointing to one physicially server, if possible.
Thanks!
HHIf you haven't done so already, run a trace against the database and try to
index the tables on server2 based on the trace.
For some of the more common reports, if the data is aggregated maybe you can
bypass some of the work SQL Server has to do by creating tables with the
aggregated data in it.
You could try a solution using Analysis Services (this solution would
require a lot of FTE hours)|||Our relational reporting systems works great when the data is local. Reports
usually run within seconds. Only the set of data physically stored on
another machine via linked server performs severely. I didn't expect it to
be this slow because the two machine are on the same Gigabit Ether LAN. It
could be because the query is running on Machine1, but it has to pull all
data from Machine2 over first' It'd be ideal if queries are being run on
Machine2, and only results get transferred over the wire.
Thanks!
HH
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:eZhKrsJmDHA.964@.TK2MSFTNGP10.phx.gbl...
> If you haven't done so already, run a trace against the database and try
to
> index the tables on server2 based on the trace.
> For some of the more common reports, if the data is aggregated maybe you
can
> bypass some of the work SQL Server has to do by creating tables with the
> aggregated data in it.
> You could try a solution using Analysis Services (this solution would
> require a lot of FTE hours)
>
I have 2 database servers running W2K/SQL 2000 with Gigabit Ethernet between
them. Server1 contains 2003 and 2002 data in one database. Server2 contains
2001 data in one database.
I have an web application that points to Server1 all the time. So, I created
a linked server from Server1 to add Server2 in. I then created Server1.VIEWS
of all tables from Server2.database.dbo.table_name...
The problem is that accessing data via Server1.VIEWS from the web
application is very slow. Reports aren't performing for 2001 data
physicially stored on Server2. The reports aggregate millions of rows on
each table.
what are ways to improve query performance via linked servers? I still want
to keep one web application pointing to one physicially server, if possible.
Thanks!
HHIf you haven't done so already, run a trace against the database and try to
index the tables on server2 based on the trace.
For some of the more common reports, if the data is aggregated maybe you can
bypass some of the work SQL Server has to do by creating tables with the
aggregated data in it.
You could try a solution using Analysis Services (this solution would
require a lot of FTE hours)|||Our relational reporting systems works great when the data is local. Reports
usually run within seconds. Only the set of data physically stored on
another machine via linked server performs severely. I didn't expect it to
be this slow because the two machine are on the same Gigabit Ether LAN. It
could be because the query is running on Machine1, but it has to pull all
data from Machine2 over first' It'd be ideal if queries are being run on
Machine2, and only results get transferred over the wire.
Thanks!
HH
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:eZhKrsJmDHA.964@.TK2MSFTNGP10.phx.gbl...
> If you haven't done so already, run a trace against the database and try
to
> index the tables on server2 based on the trace.
> For some of the more common reports, if the data is aggregated maybe you
can
> bypass some of the work SQL Server has to do by creating tables with the
> aggregated data in it.
> You could try a solution using Analysis Services (this solution would
> require a lot of FTE hours)
>
Monday, March 12, 2012
server: Can't create server but I can connect from one server to the other
I have 3 servers in this equation
server1 server2 and link1
I can create a linked server between server1->link1
I get an error when I try and do the exact same thing
between server2->link1
Error:
TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user reader
OLE DB provider "SQLNCLI" for linked server "link1" returned message
"Communication link failure". (Microsoft SQL Server, Error:10054)
So then I went through some troubleshooting on server2
Patch levels of windows and sql server seem to be exactly the same as
server1
I can remote to server2 and register link1 and it connects fine and
also allows
me to run queries.
Any ideas?On Jun 21, 7:26 pm, Mandible <elliottj...@.gmail.com> wrote:
> I have 3 servers in this equation
> server1 server2 and link1
> I can create a linked server between server1->link1
> I get an error when I try and do the exact same thing
> between server2->link1
> Error:
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Login failed for user reader
> OLE DB provider "SQLNCLI" for linked server "link1" returned message
> "Communication link failure". (Microsoft SQL Server, Error:10054)
> So then I went through some troubleshooting on server2
> Patch levels of windows and sql server seem to be exactly the same as
> server1
> I can remote to server2 and register link1 and it connects fine and
> also allows
> me to run queries.
> Any ideas?
Are you registerting with TCP IP protocol. Check whether this enabled
on server2 .
Check for any firewall settings which may not be allowing to connect
throgh link server . I hope you enabled remote access allowed on both
the systems
server1 server2 and link1
I can create a linked server between server1->link1
I get an error when I try and do the exact same thing
between server2->link1
Error:
TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user reader
OLE DB provider "SQLNCLI" for linked server "link1" returned message
"Communication link failure". (Microsoft SQL Server, Error:10054)
So then I went through some troubleshooting on server2
Patch levels of windows and sql server seem to be exactly the same as
server1
I can remote to server2 and register link1 and it connects fine and
also allows
me to run queries.
Any ideas?On Jun 21, 7:26 pm, Mandible <elliottj...@.gmail.com> wrote:
> I have 3 servers in this equation
> server1 server2 and link1
> I can create a linked server between server1->link1
> I get an error when I try and do the exact same thing
> between server2->link1
> Error:
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Login failed for user reader
> OLE DB provider "SQLNCLI" for linked server "link1" returned message
> "Communication link failure". (Microsoft SQL Server, Error:10054)
> So then I went through some troubleshooting on server2
> Patch levels of windows and sql server seem to be exactly the same as
> server1
> I can remote to server2 and register link1 and it connects fine and
> also allows
> me to run queries.
> Any ideas?
Are you registerting with TCP IP protocol. Check whether this enabled
on server2 .
Check for any firewall settings which may not be allowing to connect
throgh link server . I hope you enabled remote access allowed on both
the systems
Friday, March 9, 2012
server, DTC, SQLOLEDB, Access, linked tables = HEADACHE!
OK, welcome to my nightmare... I got 2 SQL Servers and an Access DB Front end
SQL Server1: SQL Server 7.0 (on our network)
SQL Server2: SQL Server 2000 (remote)
AccessDB: on our network
There is a Form in the Access DB that is used by Sales & Marketing to update data in both SQL Servers and there are subqueries involved. I tried just linking the tables in Access, but the connection to the remote SQL Server keeps getting dropped within seconds of opening it regardless of whatever timeouts I set anywhere. The linked tables to the local SQL Server stay connected just fine (both DSNs on the Access machine are using TCP/IP).
So, I tried adding SQL Server2 as a Linked Server to SQL Server1, then write a distributed view object (SELECT * FROM [linkedserver].[catalog].[dbo].[table]) the table I need. Then I create a linked table in Access to the new view. Well I can SEE the data fine (SELECT), but in order to update the data, it evidently requires DTC, but since it's SQLOLEDB (all together now) "does not support distributed transactions." Yup DTC is running just fone on both SQL Servers, nope we can't upgrade the SQL Server 7.0
If anyone has ANY insight into this mess, I'm all ears.Can you call an SP to do the update?
set transact abort on gets round some transaction incorporation problems but I doubt if it would solve this.|||no, I can't do an SP in this situation. It's just MS Access updating a linked table (view). I did try to find a way to do this with an SP to employ the trasact abort thing, but it didn't apply.|||I think I'm going to X-Post this in the usenet forums. Seems to be a lot of traffic in there. I'll be a nice x-poster though, if I get an answer in one forum, i'll post it in the other.
SQL Server1: SQL Server 7.0 (on our network)
SQL Server2: SQL Server 2000 (remote)
AccessDB: on our network
There is a Form in the Access DB that is used by Sales & Marketing to update data in both SQL Servers and there are subqueries involved. I tried just linking the tables in Access, but the connection to the remote SQL Server keeps getting dropped within seconds of opening it regardless of whatever timeouts I set anywhere. The linked tables to the local SQL Server stay connected just fine (both DSNs on the Access machine are using TCP/IP).
So, I tried adding SQL Server2 as a Linked Server to SQL Server1, then write a distributed view object (SELECT * FROM [linkedserver].[catalog].[dbo].[table]) the table I need. Then I create a linked table in Access to the new view. Well I can SEE the data fine (SELECT), but in order to update the data, it evidently requires DTC, but since it's SQLOLEDB (all together now) "does not support distributed transactions." Yup DTC is running just fone on both SQL Servers, nope we can't upgrade the SQL Server 7.0
If anyone has ANY insight into this mess, I'm all ears.Can you call an SP to do the update?
set transact abort on gets round some transaction incorporation problems but I doubt if it would solve this.|||no, I can't do an SP in this situation. It's just MS Access updating a linked table (view). I did try to find a way to do this with an SP to employ the trasact abort thing, but it didn't apply.|||I think I'm going to X-Post this in the usenet forums. Seems to be a lot of traffic in there. I'll be a nice x-poster though, if I get an answer in one forum, i'll post it in the other.
Subscribe to:
Posts (Atom)