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 boxes. Show all posts
Showing posts with label boxes. 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 ***
Monday, March 12, 2012
servers
I have 2 test boxes each running sql server 2000 standard
edition. For both boxes i've created a linked server and
specified the name of the system DSN of the ODBC data
source within the properties of the linked server created.
A drive has been mapped to the Access database i want to
see and the ODBC connections have been created. But
unfortunately only one of my sql boxes can see the tables
while the other generates a Error '7399' message. why,
please advise. thank you.
Error 7399 doesn't tell us much. We'd need more details
regarding the exact error message. Can you access the mapped
drive? Have you tried using a UNC path instead of a mapped
drive? Mapped drives are often unreliable for this type of
situation.
-Sue
On Wed, 15 Sep 2004 13:26:47 -0700, "vincent"
<vincec@.mail.com> wrote:
>I have 2 test boxes each running sql server 2000 standard
>edition. For both boxes i've created a linked server and
>specified the name of the system DSN of the ODBC data
>source within the properties of the linked server created.
>A drive has been mapped to the Access database i want to
>see and the ODBC connections have been created. But
>unfortunately only one of my sql boxes can see the tables
>while the other generates a Error '7399' message. why,
>please advise. thank you.
|||Thank you for responding. I can access the mapped drive and i've tried
using the absolute path, both attempts have been unsuccessful. Because
the access database i'm tring to access is of a relitively small size if
copied it locally and i still wasn't able to access the tables. So I
image the problem would have to be with the OLE DB provider? This is the
stored procedure that trys to execute.
select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
trans_table')
where invoice_num like 'OEPP%'
and (authorize_code is not null
or ltrim(rtrim(authorize_code)) <> '')
When i view my job history logs i receive all of these error messages.
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
(80040E14)
Error string: OLE DB provider 'MSDASQL' reported an error.
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14);
Provider Error: 7399 (1CE7)
Error: -2147217900 (80040E14);
Provider Error: 7300 (1C84)
Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBInitialize::Initialize returned 0x80004005: ].
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
Error string: [OLE/DB provider ret... Process Exit Code 1.
The step failed.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What happens when you use Query Analyzer and execute:
dbcc traceon(7300, 3604)
and then execute the query in Query Analyzer (not DTS)?
Do you have any additional error messages?
-Sue
On Wed, 22 Sep 2004 06:54:45 -0700, Vincent Cristiano
<vincec@.mail.com> wrote:
>Thank you for responding. I can access the mapped drive and i've tried
>using the absolute path, both attempts have been unsuccessful. Because
>the access database i'm tring to access is of a relitively small size if
>copied it locally and i still wasn't able to access the tables. So I
>image the problem would have to be with the OLE DB provider? This is the
>stored procedure that trys to execute.
>select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
>trans_table')
>where invoice_num like 'OEPP%'
>and (authorize_code is not null
>or ltrim(rtrim(authorize_code)) <> '')
>When i view my job history logs i receive all of these error messages.
>DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
>(80040E14)
>Error string: OLE DB provider 'MSDASQL' reported an error.
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error Detail Records: Error: -2147217900 (80040E14);
>Provider Error: 7399 (1CE7)
>Error: -2147217900 (80040E14);
>Provider Error: 7300 (1C84)
>Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
>IDBInitialize::Initialize returned 0x80004005: ].
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
>Error string: [OLE/DB provider ret... Process Exit Code 1.
>The step failed.
>Thanks in advance.
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Did anyone figure out this issue. I'm also getting the exact same error and would really appreciate if someone can help me with this.. I have to get this fixed within the next two days..
I've not yet tried this - dbcc traceon(7300, 3604)
I'll give this a try and let you know what happens
Thanks
edition. For both boxes i've created a linked server and
specified the name of the system DSN of the ODBC data
source within the properties of the linked server created.
A drive has been mapped to the Access database i want to
see and the ODBC connections have been created. But
unfortunately only one of my sql boxes can see the tables
while the other generates a Error '7399' message. why,
please advise. thank you.
Error 7399 doesn't tell us much. We'd need more details
regarding the exact error message. Can you access the mapped
drive? Have you tried using a UNC path instead of a mapped
drive? Mapped drives are often unreliable for this type of
situation.
-Sue
On Wed, 15 Sep 2004 13:26:47 -0700, "vincent"
<vincec@.mail.com> wrote:
>I have 2 test boxes each running sql server 2000 standard
>edition. For both boxes i've created a linked server and
>specified the name of the system DSN of the ODBC data
>source within the properties of the linked server created.
>A drive has been mapped to the Access database i want to
>see and the ODBC connections have been created. But
>unfortunately only one of my sql boxes can see the tables
>while the other generates a Error '7399' message. why,
>please advise. thank you.
|||Thank you for responding. I can access the mapped drive and i've tried
using the absolute path, both attempts have been unsuccessful. Because
the access database i'm tring to access is of a relitively small size if
copied it locally and i still wasn't able to access the tables. So I
image the problem would have to be with the OLE DB provider? This is the
stored procedure that trys to execute.
select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
trans_table')
where invoice_num like 'OEPP%'
and (authorize_code is not null
or ltrim(rtrim(authorize_code)) <> '')
When i view my job history logs i receive all of these error messages.
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
(80040E14)
Error string: OLE DB provider 'MSDASQL' reported an error.
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14);
Provider Error: 7399 (1CE7)
Error: -2147217900 (80040E14);
Provider Error: 7300 (1C84)
Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBInitialize::Initialize returned 0x80004005: ].
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
Error string: [OLE/DB provider ret... Process Exit Code 1.
The step failed.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||What happens when you use Query Analyzer and execute:
dbcc traceon(7300, 3604)
and then execute the query in Query Analyzer (not DTS)?
Do you have any additional error messages?
-Sue
On Wed, 22 Sep 2004 06:54:45 -0700, Vincent Cristiano
<vincec@.mail.com> wrote:
>Thank you for responding. I can access the mapped drive and i've tried
>using the absolute path, both attempts have been unsuccessful. Because
>the access database i'm tring to access is of a relitively small size if
>copied it locally and i still wasn't able to access the tables. So I
>image the problem would have to be with the OLE DB provider? This is the
>stored procedure that trys to execute.
>select invoice_num,authorize_code from OPENQUERY(idd3,'select * from
>trans_table')
>where invoice_num like 'OEPP%'
>and (authorize_code is not null
>or ltrim(rtrim(authorize_code)) <> '')
>When i view my job history logs i receive all of these error messages.
>DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217900
>(80040E14)
>Error string: OLE DB provider 'MSDASQL' reported an error.
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error Detail Records: Error: -2147217900 (80040E14);
>Provider Error: 7399 (1CE7)
>Error: -2147217900 (80040E14);
>Provider Error: 7300 (1C84)
>Error string: OLE DB error trace [OLE/DB Provider 'MSDASQL'
>IDBInitialize::Initialize returned 0x80004005: ].
>Error source: Microsoft OLE DB Provider for SQL Server
>Help file: Help context: 0
>Error: -2147217900 (80040E14); Provider Error: 7312 (1C90)
>Error string: [OLE/DB provider ret... Process Exit Code 1.
>The step failed.
>Thanks in advance.
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Did anyone figure out this issue. I'm also getting the exact same error and would really appreciate if someone can help me with this.. I have to get this fixed within the next two days..
I've not yet tried this - dbcc traceon(7300, 3604)
I'll give this a try and let you know what happens
Thanks
Wednesday, March 7, 2012
server to Visual FoxPro Not Work
I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
Here's some particulars:
--
EXEC sp_addlinkedserver
@.server='VFP',
@.provider='VFPOLEDB',
@.datasrc='\\hdmcpdctis1\tisrnddata\',
@.srvproduct='Visual FoxPro'
--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \\hdmcpdctis1\tisrnddata\ ',
'select * from [tislists.DBF]')
With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================
Any Help is greatly appreciated! Thanks,
peter :confused:Hi,
Do you still have this problem? If not, how did you get around it? I am experiencing the exact same thing and am quite confused at this point...
Thanks!|||At my last job I handled a few linked Visual FoxPro linked serves from a SQL 7 database and I did'nt encounter this but let me ask you 2 questions.
1. Are you able query any database in BOX B from BOX A?
2. Have you tried querying using the 4 part name? linked_server_name.catalog.schema.object_name|||Answers to the questions:
1) Yes I can query some DB's from BOX A while on BOX B, the success depends on what authentication type the SQL Server instance is registered with in my enterprise manager
2) I have not tried querying with the 4 part name, I have only gotten as far as trying to view the list of tables through enterprise manager when I get the error
Thanks|||Can you see the tables is enterprise manager on the local box?
If not you have problem with your linked server definition. I am leaving for the day but I can check back on this this evening if I am not totally dead to the world.|||I can see the tables in EM on the local box, just not from the remote box.
Good luck on the interview...
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
Here's some particulars:
--
EXEC sp_addlinkedserver
@.server='VFP',
@.provider='VFPOLEDB',
@.datasrc='\\hdmcpdctis1\tisrnddata\',
@.srvproduct='Visual FoxPro'
--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \\hdmcpdctis1\tisrnddata\ ',
'select * from [tislists.DBF]')
With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================
Any Help is greatly appreciated! Thanks,
peter :confused:Hi,
Do you still have this problem? If not, how did you get around it? I am experiencing the exact same thing and am quite confused at this point...
Thanks!|||At my last job I handled a few linked Visual FoxPro linked serves from a SQL 7 database and I did'nt encounter this but let me ask you 2 questions.
1. Are you able query any database in BOX B from BOX A?
2. Have you tried querying using the 4 part name? linked_server_name.catalog.schema.object_name|||Answers to the questions:
1) Yes I can query some DB's from BOX A while on BOX B, the success depends on what authentication type the SQL Server instance is registered with in my enterprise manager
2) I have not tried querying with the 4 part name, I have only gotten as far as trying to view the list of tables through enterprise manager when I get the error
Thanks|||Can you see the tables is enterprise manager on the local box?
If not you have problem with your linked server definition. I am leaving for the day but I can check back on this this evening if I am not totally dead to the world.|||I can see the tables in EM on the local box, just not from the remote box.
Good luck on the interview...
Subscribe to:
Posts (Atom)