Hi!
I have written a Perl Program, which works on a MS SQL SERVER 2000 database. It should import some data from an Oracle8i database. so i created a linked server and a linked server login. There were no problems when i created these things. But if i execute a "select ..."-Statement at the linked server(Oracle), i get the following message:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Die Operation konnte nicht ausgefhrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]. (SQL-01000)(DBD: st_execute/SQLExecute err=-1),ERROR = -1"
But the select statement, i used is ok. i started it in the Query Analyzer and everything was fine! This is the statement:
"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"
Does anyone know , what the problem is?
NickWell...I know very little German (it is German isn't it), but what login did you use to connect with?
Is it a remote login in Oracle?
This is purely a shot in the dark...|||Strange SELECT you use,
why
"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"
instead of
SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
Linked Server Connection Problem
Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
ThanksHello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/...7D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-[/ur
l]
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
----
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwin
d','northwind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a. Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
----
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
----
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a. Start FileMon to monitor the file system events.
b. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c. Stop FileMon and save the log to a file named filemon.log.
d. Start RegMon to monitor the registry events.
e. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f. Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
----
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
ThanksHello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/...7D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-[/ur
l]
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
----
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwin
d','northwind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a. Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
----
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
----
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a. Start FileMon to monitor the file system events.
b. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c. Stop FileMon and save the log to a file named filemon.log.
d. Start RegMon to monitor the registry events.
e. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f. Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
----
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
Linked Server Connection Problem
Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
Thanks
Hello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/d...D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> http://www.microsoft.com/downloads/d...D8F-2D58-491F-
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwind','northw ind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a.Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a.Start FileMon to monitor the file system events.
b.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c.Stop FileMon and save the log to a file named filemon.log.
d.Start RegMon to monitor the registry events.
e.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f.Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
Thanks
Hello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/d...D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> http://www.microsoft.com/downloads/d...D8F-2D58-491F-
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwind','northw ind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a.Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a.Start FileMon to monitor the file system events.
b.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c.Stop FileMon and save the log to a file named filemon.log.
d.Start RegMon to monitor the registry events.
e.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f.Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
Wednesday, March 7, 2012
Linked server 7302 error
Hi
I have a strange connection error that I believe is related to security. But
I need some advice.
I need to know what are exactly the permissions needed to establish a linked
server connection through network using integrated security.
My setup is the following for my test environment before I implement a
linked server in production servers.
I have installed MSDE on my Windows XP FR machine. The exact version is SQL
Server Desktop Engine 8.00.194 on Windows NT5.1(2600) Francais(France). MDAC
2.7 is installed. MSDAORA.dll version is 2.71.9030. MSDAORAR.dll version is
2.70.7713.
I have installed Oracle 9i SQL*Net Client 9.0.1.1.1. All Oracle tools are
configured and work properly and allow me to access the Oracle database
without any problem. I conclude there is no Oracle connectivity problem.
I am a local admin. I add my user A to the Security/Connections tab to allow
a connection to this DB. I add a linked server pointing to my Oracle DB wher
e
the oracle user is readonly. Still on the same machine, I open the Query
analyser using local server and integrated security. Works like a charm.
I enter a query using the linked server. select * from
openquery(ORACLE,'sql') where sql is executed on the oracle DB. I receive
results correctly. This works perfectly. I go to another computer and log
with user A. I use the QA to connect to my SQLserver DB with integrated
security. It works and I can query the SQLServer tables. I run the same
linked server query as if I was on my first machine and it works.
Now my problem. In my SQLServer, I add a second Security/Connection user B.
On the second computer using QA and user B, I can connect to my SQLServer
with integrated security under user B. But when I try to run the linked
server query, it does not work. I get an error logged in the registry
whenever I attempt a connection. The error is DCOMM 10002 Access denied
attempting to launch a DCOM server. The server is
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
This corresponds to MSDAINITIALIZE DCOM component. In QA, my query returns
with error 7302, Unable to create a MSDAORA OLEDB provider instance.
What is the problem ?
I have tried so far:
- giving admin rights on to user B on SQL Server computer. No success.
- the linked server works when I use a SQLServer authentication.
Other thoughts ?
Stephane
Sorry for the third post. I had trouble with Microsoft managed newsgroup
stuff...Hi Stephane,
Before we go further, I'd like to get a better
understanding of the following points to ensure that I
understand the problem clearly:
1. "I add my user A to the Security/Connections tab to
allow a connection to this DB"
How did you do this? Where is the Security/Connections
tab? Is "this DB" a SQL Server DB or a Oracle DB?
2. What is the difference between User A and User B? If
you create a new user, say User C, do you still have
this problem?
You may want to refer to this article for more basic
troubleshooting steps regarding this error:
280106 How to set up and troubleshoot a linked server to
Oracle in SQL Server
http://support.microsoft.com/?id=280106
Feel free to post back if you have any further updates.
Sincerely,
William Wang
Microsoft Online Partner Support
=============================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=============================
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUDvo+royZwDPf5QV+3AtJyHzQhaA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>Subject: Linked server 7302 error
>Date: Wed, 26 Jan 2005 07:49:03 -0800
>Lines: 52
>Message-ID:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44257
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>I have a strange connection error that I believe is
related to security. But
>I need some advice.
>I need to know what are exactly the permissions needed
to establish a linked
>server connection through network using integrated
security.
>My setup is the following for my test environment
before I implement a
>linked server in production servers.
>I have installed MSDE on my Windows XP FR machine. The
exact version is SQL
>Server Desktop Engine 8.00.194 on Windows NT5.1(2600)
Francais(France). MDAC
>2.7 is installed. MSDAORA.dll version is 2.71.9030.
MSDAORAR.dll version is
>2.70.7713.
>I have installed Oracle 9i SQL*Net Client 9.0.1.1.1.
All Oracle tools are
>configured and work properly and allow me to access the
Oracle database
>without any problem. I conclude there is no Oracle
connectivity problem.
>I am a local admin. I add my user A to the
Security/Connections tab to allow
>a connection to this DB. I add a linked server pointing
to my Oracle DB where
>the oracle user is readonly. Still on the same machine,
I open the Query
>analyser using local server and integrated security.
Works like a charm.
>I enter a query using the linked server. select * from
>openquery(ORACLE,'sql') where sql is executed on the
oracle DB. I receive
>results correctly. This works perfectly. I go to
another computer and log
>with user A. I use the QA to connect to my SQLserver DB
with integrated
>security. It works and I can query the SQLServer
tables. I run the same
>linked server query as if I was on my first machine and
it works.
>Now my problem. In my SQLServer, I add a second
Security/Connection user B.
>On the second computer using QA and user B, I can
connect to my SQLServer
>with integrated security under user B. But when I try
to run the linked
>server query, it does not work. I get an error logged
in the registry
>whenever I attempt a connection. The error is DCOMM
10002 Access denied
>attempting to launch a DCOM server. The server is
>{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
>This corresponds to MSDAINITIALIZE DCOM component. In
QA, my query returns
>with error 7302, Unable to create a MSDAORA OLEDB
provider instance.
>What is the problem ?
>I have tried so far:
>- giving admin rights on to user B on SQL Server
computer. No success.
>- the linked server works when I use a SQLServer
authentication.
>Other thoughts ?
>Stephane
>Sorry for the third post. I had trouble with Microsoft
managed newsgroup
>stuff...
>|||Hi William
Here are answers to your questions.
1. I use SQL Server Enterprise Manager to add a user to the Security/Logins.
Sorry, my Security/Connections is a bad translation from my French
installation.
2. I had already tried a third user before I posted. It shows the same
behavior.
Computer A Computer B
Windows XP Fr SP1 Windows XP Fr
MSDE installation SQL Server tools
Oracle network drivers No oracle drivers
User A is local admin User a logs in
this computer.
In QA, login to DB with integrated QA login integrated
security
security.
Queries to linked server work. Queries to linked
server work
Login with user B Login with user
B on computer B
Open QA, login to DB with integrated Open QA, login to DB with
integrated
security. security
Run linked server query. Does not Run linked server query.
Error 7302
work(7302 error) but no DCOM but no DCOM error anymore
in
error in eventlog. eventlog.
On SQLServer error log, I see the following message:
Unable to load OLE/DB initialization service.
On all computers, if I log to SQLServer DB with SQLServer security,
everything works perfectly.
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Before we go further, I'd like to get a better
> understanding of the following points to ensure that I
> understand the problem clearly:
> 1. "I add my user A to the Security/Connections tab to
> allow a connection to this DB"
> How did you do this? Where is the Security/Connections
> tab? Is "this DB" a SQL Server DB or a Oracle DB?
> 2. What is the difference between User A and User B? If
> you create a new user, say User C, do you still have
> this problem?
> You may want to refer to this article for more basic
> troubleshooting steps regarding this error:
> 280106 How to set up and troubleshoot a linked server to
> Oracle in SQL Server
> http://support.microsoft.com/?id=280106
> Feel free to post back if you have any further updates.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> =============================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =============================
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --|||Hi Stephane,
Your explaination is great. Now my understanding of this
issue is: On both Computer A and Computer B, if you
logon the SQL Server using User A and run the Openquery
statement, the query works fine; but if you logon as
User B, the query fails regardless whether you are on
Computer A or Computer B. If this is not correct,
please let me know.
This issue looks strange because the error message
should not be caused by a permission problem.
Nevertheless, let's try the following steps to see what
happens:
To isolate the problem we don't need to involve Computer
B in our troubleshooting step.
1. Register MSDAORA.dll using RegSvr32.exe and then test
the problem.
2. Check the Security tab of the Linked Server
Properties dialog box to see if there is anything
related to User A. What do you see from the tab? You may
want to remove the linked server and then set up a
linked server with a simliar script to the one mentioned
in KB280106 and then test the problem.
3. If the issue persists, reinstall MDAC to see if it
helps. You can download the latest MDAC from the
following link:
http://www.microsoft.com/downloads/...aspx?displayla
ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
If the issue still occurs, I suggest that you post this
issue in the <microsoft.public.fr.sqlserver> newsgroup
as you are using Franch version.
Hope it helps.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with
technical phone support at no charge during critical LAN
outages or "business down" situations. This benefit is
available 24 hours a day, 7 days a week to all Microsoft
technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...support/support
overview/40010469
Others:
https://partner.microsoft.com/US/te...support/support
overview/
If you are outside the United States, please visit our
International Support page:
http://support.microsoft.com/defaul...scid=%2finterna
tional.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUEeBMq2g7sFC82QQGsoRNJgct6CA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Thu, 27 Jan 2005 05:57:01 -0800
>Lines: 83
>Message-ID:
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44266
>X-Tomcat-NG: microsoft.public.sqlserver.connect
> Hi William
> Here are answers to your questions.
>1. I use SQL Server Enterprise Manager to add a user to
the Security/Logins.
>Sorry, my Security/Connections is a bad translation
from my French
>installation.
>2. I had already tried a third user before I posted. It
shows the same
>behavior.
>Computer A
Computer B
>Windows XP Fr SP1
Windows XP Fr
>MSDE installation
SQL Server tools
>Oracle network drivers
No oracle drivers
>User A is local admin
User a logs in
>this computer.
>In QA, login to DB with integrated QA
login integrated
>security
>security.
>Queries to linked server work.
Queries to linked
>server work
>Login with user B
Login with user
>B on computer B
>Open QA, login to DB with integrated Open
QA, login to DB with
>integrated
>security.
security
>Run linked server query. Does not Run
linked server query.
>Error 7302
>work(7302 error) but no DCOM but
no DCOM error anymore
>in
>error in eventlog.
eventlog.
>On SQLServer error log, I see the following message:
> Unable to load OLE/DB initialization service.
>On all computers, if I log to SQLServer DB with
SQLServer security,
>everything works perfectly.
>Stephane
>
>"William Wang[MSFT]" wrote:
>
I[vbcol=seagreen]
to[vbcol=seagreen]
Security/Connections[vbcol=seagreen]
If[vbcol=seagreen]
to[vbcol=seagreen]
updates.[vbcol=seagreen]
and[vbcol=seagreen]
>|||Hi
Your understanding is correct. User A can use the linked server from any
computer. User B(or any other) can not. Error message is related to
initializing the connection.
1. The MSDAORA.dll is already registered since it works perfectly under user
A.
2. Under the linked server security tab, nothing is entered except the last
item is selected: Be made with this security context : username/password.
Nothing related to user A or B. I will look at KB article.
3. MDAC 2.7 is installed and works. Again, it works perfectly under user A.
Post to microsoft.public.fr.sqlserver. Bad suggestion. I have a problem and
you suggest that I go somewhere else. No thanks.
I still have a problem with my linked server and I still need some support.
I believe it is related to a security issue using integrated security login
because I can access the linked server with a SQLServer login from any user
or computer.
What other suggestions do you have ? Surely, you can find a SQL Server /
linked server / integrated security specialist somewhere in Microsoft ?
Thanks anyway
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Your explaination is great. Now my understanding of this
> issue is: On both Computer A and Computer B, if you
> logon the SQL Server using User A and run the Openquery
> statement, the query works fine; but if you logon as
> User B, the query fails regardless whether you are on
> Computer A or Computer B. If this is not correct,
> please let me know.
> This issue looks strange because the error message
> should not be caused by a permission problem.
> Nevertheless, let's try the following steps to see what
> happens:
> To isolate the problem we don't need to involve Computer
> B in our troubleshooting step.
> 1. Register MSDAORA.dll using RegSvr32.exe and then test
> the problem.
> 2. Check the Security tab of the Linked Server
> Properties dialog box to see if there is anything
> related to User A. What do you see from the tab? You may
> want to remove the linked server and then set up a
> linked server with a simliar script to the one mentioned
> in KB280106 and then test the problem.
> 3. If the issue persists, reinstall MDAC to see if it
> helps. You can download the latest MDAC from the
> following link:
> http://www.microsoft.com/downloads/...aspx?displayla
> ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
> If the issue still occurs, I suggest that you post this
> issue in the <microsoft.public.fr.sqlserver> newsgroup
> as you are using Franch version.
> Hope it helps.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with
> technical phone support at no charge during critical LAN
> outages or "business down" situations. This benefit is
> available 24 hours a day, 7 days a week to all Microsoft
> technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...support/support
> overview/40010469
> Others:
> https://partner.microsoft.com/US/te...support/support
> overview/
> If you are outside the United States, please visit our
> International Support page:
> http://support.microsoft.com/defaul...scid=%2finterna
> tional.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --|||Hi Stephane,
I truely understand your concern, but by asking you to
post this issue in the <microsoft.public.fr.sqlserver>
newsgroup, I did not mean to bounce you between support
professionals. We always try our best to assist
customers whether they are using English version or
non-English version of products, but for those issues
that occur in non-English version of products, it is
best to troubleshoot them in the newsgroup setup
specifically for the relevant language. That way the
issues can be resolved in a more efficient manner. In
your case where the problem is not common, we may need
to check some logs such as Windows Event logs and SQL
Error logs to isolate the problem, we would also like to
get the exact error message, but we have difficulties
doing this with a non-English product. Your
understanding on this would be much appreciated.
I agree that the issue relates to the login account used
to connect to SQL Server. Let's perform the following
steps to see if the issue can be resolved (before making
any changes, note down the original settings in order to
roll back):
1. Run dcomcnfg to open the Component Services MMC.
Expand Component Services, expand Computers. Right-click
My Computer.
2. On the 'Default Properties' tab, verify that
- Enable Distributed COM on this computer is
checked
- Default Authentication Level is set to Connect,
- Default Impersonation Level = Impersonate.
3. On the 'COM Security' tab, click on the 'Edit
Default...' button for each of the 'Access Permissions'
and the 'Default Launch and Activation Permissions', if
you see a list of names in the dialog box then make sure
the startup account for the SQL Server service is in the
list or belongs to a group that is in the list.
4. Expand 'My Computer' and click the 'DCOM config'
folder, find out 'MSDAORA' on the right pane and then
right-click 'MSDAORA' and click 'Properties', in general
here are the settings that are known to work:
- On the 'General' tab the Authentication Level is
set to Default
- On the 'Security' tab, 'Launch and Activation
Permissions', 'Access Permissions' and 'Configuration
Permissions' should have 'Use Default' selected. If not,
click on the 'Edit...' button and if you see a list of
names in the dialog box then make sure the account used
to start the SQL Server service is in the list or
belongs to a group that is in the list.
- On the 'Identity' tab, select 'This user' and
type the SQL Server Service startup account and password
in the text boxes.
5. Perform step4 on MSDAINITIALIZE.
6. Restart the computer and then test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUFQ3p3veomzo4ARHuHhI/d7yYwWg==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
<H0FSQ1RBFHA.764@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Fri, 28 Jan 2005 06:13:02 -0800
>Lines: 106
>Message-ID:
<27C18AF3-63D8-430C-BE26-E064C736B756@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44286
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>Your understanding is correct. User A can use the
linked server from any
>computer. User B(or any other) can not. Error message
is related to
>initializing the connection.
>1. The MSDAORA.dll is already registered since it works
perfectly under user
>A.
>2. Under the linked server security tab, nothing is
entered except the last
>item is selected: Be made with this security context :
username/password.
>Nothing related to user A or B. I will look at KB
article.
>3. MDAC 2.7 is installed and works. Again, it works
perfectly under user A.
>Post to microsoft.public.fr.sqlserver. Bad suggestion.
I have a problem and
>you suggest that I go somewhere else. No thanks.
>I still have a problem with my linked server and I
still need some support.
>I believe it is related to a security issue using
integrated security login
>because I can access the linked server with a SQLServer
login from any user
>or computer.
>What other suggestions do you have ? Surely, you can
find a SQL Server /
>linked server / integrated security specialist
somewhere in Microsoft ?
>Thanks anyway
>Stephane
>
>"William Wang[MSFT]" wrote:
>
this[vbcol=seagreen]
Openquery[vbcol=seagreen]
what[vbcol=seagreen]
Computer[vbcol=seagreen]
test[vbcol=seagreen]
may[vbcol=seagreen]
mentioned[vbcol=seagreen]
http://www.microsoft.com/downloads/...aspx?displayla[vbcol=seagreen]
this[vbcol=seagreen]
newsgroup[vbcol=seagreen]
with[vbcol=seagreen]
LAN[vbcol=seagreen]
is[vbcol=seagreen]
Microsoft[vbcol=seagreen]
https://partner.microsoft.com/US/te...support/support[vbcol=seagreen]
https://partner.microsoft.com/US/te...support/support[vbcol=seagreen]
our[vbcol=seagreen]
http://support.microsoft.com/defaul...scid=%2finterna[vbcol=seagreen]
and[vbcol=seagreen]
>
I have a strange connection error that I believe is related to security. But
I need some advice.
I need to know what are exactly the permissions needed to establish a linked
server connection through network using integrated security.
My setup is the following for my test environment before I implement a
linked server in production servers.
I have installed MSDE on my Windows XP FR machine. The exact version is SQL
Server Desktop Engine 8.00.194 on Windows NT5.1(2600) Francais(France). MDAC
2.7 is installed. MSDAORA.dll version is 2.71.9030. MSDAORAR.dll version is
2.70.7713.
I have installed Oracle 9i SQL*Net Client 9.0.1.1.1. All Oracle tools are
configured and work properly and allow me to access the Oracle database
without any problem. I conclude there is no Oracle connectivity problem.
I am a local admin. I add my user A to the Security/Connections tab to allow
a connection to this DB. I add a linked server pointing to my Oracle DB wher
e
the oracle user is readonly. Still on the same machine, I open the Query
analyser using local server and integrated security. Works like a charm.
I enter a query using the linked server. select * from
openquery(ORACLE,'sql') where sql is executed on the oracle DB. I receive
results correctly. This works perfectly. I go to another computer and log
with user A. I use the QA to connect to my SQLserver DB with integrated
security. It works and I can query the SQLServer tables. I run the same
linked server query as if I was on my first machine and it works.
Now my problem. In my SQLServer, I add a second Security/Connection user B.
On the second computer using QA and user B, I can connect to my SQLServer
with integrated security under user B. But when I try to run the linked
server query, it does not work. I get an error logged in the registry
whenever I attempt a connection. The error is DCOMM 10002 Access denied
attempting to launch a DCOM server. The server is
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
This corresponds to MSDAINITIALIZE DCOM component. In QA, my query returns
with error 7302, Unable to create a MSDAORA OLEDB provider instance.
What is the problem ?
I have tried so far:
- giving admin rights on to user B on SQL Server computer. No success.
- the linked server works when I use a SQLServer authentication.
Other thoughts ?
Stephane
Sorry for the third post. I had trouble with Microsoft managed newsgroup
stuff...Hi Stephane,
Before we go further, I'd like to get a better
understanding of the following points to ensure that I
understand the problem clearly:
1. "I add my user A to the Security/Connections tab to
allow a connection to this DB"
How did you do this? Where is the Security/Connections
tab? Is "this DB" a SQL Server DB or a Oracle DB?
2. What is the difference between User A and User B? If
you create a new user, say User C, do you still have
this problem?
You may want to refer to this article for more basic
troubleshooting steps regarding this error:
280106 How to set up and troubleshoot a linked server to
Oracle in SQL Server
http://support.microsoft.com/?id=280106
Feel free to post back if you have any further updates.
Sincerely,
William Wang
Microsoft Online Partner Support
=============================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=============================
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUDvo+royZwDPf5QV+3AtJyHzQhaA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>Subject: Linked server 7302 error
>Date: Wed, 26 Jan 2005 07:49:03 -0800
>Lines: 52
>Message-ID:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44257
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>I have a strange connection error that I believe is
related to security. But
>I need some advice.
>I need to know what are exactly the permissions needed
to establish a linked
>server connection through network using integrated
security.
>My setup is the following for my test environment
before I implement a
>linked server in production servers.
>I have installed MSDE on my Windows XP FR machine. The
exact version is SQL
>Server Desktop Engine 8.00.194 on Windows NT5.1(2600)
Francais(France). MDAC
>2.7 is installed. MSDAORA.dll version is 2.71.9030.
MSDAORAR.dll version is
>2.70.7713.
>I have installed Oracle 9i SQL*Net Client 9.0.1.1.1.
All Oracle tools are
>configured and work properly and allow me to access the
Oracle database
>without any problem. I conclude there is no Oracle
connectivity problem.
>I am a local admin. I add my user A to the
Security/Connections tab to allow
>a connection to this DB. I add a linked server pointing
to my Oracle DB where
>the oracle user is readonly. Still on the same machine,
I open the Query
>analyser using local server and integrated security.
Works like a charm.
>I enter a query using the linked server. select * from
>openquery(ORACLE,'sql') where sql is executed on the
oracle DB. I receive
>results correctly. This works perfectly. I go to
another computer and log
>with user A. I use the QA to connect to my SQLserver DB
with integrated
>security. It works and I can query the SQLServer
tables. I run the same
>linked server query as if I was on my first machine and
it works.
>Now my problem. In my SQLServer, I add a second
Security/Connection user B.
>On the second computer using QA and user B, I can
connect to my SQLServer
>with integrated security under user B. But when I try
to run the linked
>server query, it does not work. I get an error logged
in the registry
>whenever I attempt a connection. The error is DCOMM
10002 Access denied
>attempting to launch a DCOM server. The server is
>{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
>This corresponds to MSDAINITIALIZE DCOM component. In
QA, my query returns
>with error 7302, Unable to create a MSDAORA OLEDB
provider instance.
>What is the problem ?
>I have tried so far:
>- giving admin rights on to user B on SQL Server
computer. No success.
>- the linked server works when I use a SQLServer
authentication.
>Other thoughts ?
>Stephane
>Sorry for the third post. I had trouble with Microsoft
managed newsgroup
>stuff...
>|||Hi William
Here are answers to your questions.
1. I use SQL Server Enterprise Manager to add a user to the Security/Logins.
Sorry, my Security/Connections is a bad translation from my French
installation.
2. I had already tried a third user before I posted. It shows the same
behavior.
Computer A Computer B
Windows XP Fr SP1 Windows XP Fr
MSDE installation SQL Server tools
Oracle network drivers No oracle drivers
User A is local admin User a logs in
this computer.
In QA, login to DB with integrated QA login integrated
security
security.
Queries to linked server work. Queries to linked
server work
Login with user B Login with user
B on computer B
Open QA, login to DB with integrated Open QA, login to DB with
integrated
security. security
Run linked server query. Does not Run linked server query.
Error 7302
work(7302 error) but no DCOM but no DCOM error anymore
in
error in eventlog. eventlog.
On SQLServer error log, I see the following message:
Unable to load OLE/DB initialization service.
On all computers, if I log to SQLServer DB with SQLServer security,
everything works perfectly.
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Before we go further, I'd like to get a better
> understanding of the following points to ensure that I
> understand the problem clearly:
> 1. "I add my user A to the Security/Connections tab to
> allow a connection to this DB"
> How did you do this? Where is the Security/Connections
> tab? Is "this DB" a SQL Server DB or a Oracle DB?
> 2. What is the difference between User A and User B? If
> you create a new user, say User C, do you still have
> this problem?
> You may want to refer to this article for more basic
> troubleshooting steps regarding this error:
> 280106 How to set up and troubleshoot a linked server to
> Oracle in SQL Server
> http://support.microsoft.com/?id=280106
> Feel free to post back if you have any further updates.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> =============================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =============================
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --|||Hi Stephane,
Your explaination is great. Now my understanding of this
issue is: On both Computer A and Computer B, if you
logon the SQL Server using User A and run the Openquery
statement, the query works fine; but if you logon as
User B, the query fails regardless whether you are on
Computer A or Computer B. If this is not correct,
please let me know.
This issue looks strange because the error message
should not be caused by a permission problem.
Nevertheless, let's try the following steps to see what
happens:
To isolate the problem we don't need to involve Computer
B in our troubleshooting step.
1. Register MSDAORA.dll using RegSvr32.exe and then test
the problem.
2. Check the Security tab of the Linked Server
Properties dialog box to see if there is anything
related to User A. What do you see from the tab? You may
want to remove the linked server and then set up a
linked server with a simliar script to the one mentioned
in KB280106 and then test the problem.
3. If the issue persists, reinstall MDAC to see if it
helps. You can download the latest MDAC from the
following link:
http://www.microsoft.com/downloads/...aspx?displayla
ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
If the issue still occurs, I suggest that you post this
issue in the <microsoft.public.fr.sqlserver> newsgroup
as you are using Franch version.
Hope it helps.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with
technical phone support at no charge during critical LAN
outages or "business down" situations. This benefit is
available 24 hours a day, 7 days a week to all Microsoft
technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...support/support
overview/40010469
Others:
https://partner.microsoft.com/US/te...support/support
overview/
If you are outside the United States, please visit our
International Support page:
http://support.microsoft.com/defaul...scid=%2finterna
tional.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUEeBMq2g7sFC82QQGsoRNJgct6CA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Thu, 27 Jan 2005 05:57:01 -0800
>Lines: 83
>Message-ID:
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44266
>X-Tomcat-NG: microsoft.public.sqlserver.connect
> Hi William
> Here are answers to your questions.
>1. I use SQL Server Enterprise Manager to add a user to
the Security/Logins.
>Sorry, my Security/Connections is a bad translation
from my French
>installation.
>2. I had already tried a third user before I posted. It
shows the same
>behavior.
>Computer A
Computer B
>Windows XP Fr SP1
Windows XP Fr
>MSDE installation
SQL Server tools
>Oracle network drivers
No oracle drivers
>User A is local admin
User a logs in
>this computer.
>In QA, login to DB with integrated QA
login integrated
>security
>security.
>Queries to linked server work.
Queries to linked
>server work
>Login with user B
Login with user
>B on computer B
>Open QA, login to DB with integrated Open
QA, login to DB with
>integrated
>security.
security
>Run linked server query. Does not Run
linked server query.
>Error 7302
>work(7302 error) but no DCOM but
no DCOM error anymore
>in
>error in eventlog.
eventlog.
>On SQLServer error log, I see the following message:
> Unable to load OLE/DB initialization service.
>On all computers, if I log to SQLServer DB with
SQLServer security,
>everything works perfectly.
>Stephane
>
>"William Wang[MSFT]" wrote:
>
I[vbcol=seagreen]
to[vbcol=seagreen]
Security/Connections[vbcol=seagreen]
If[vbcol=seagreen]
to[vbcol=seagreen]
updates.[vbcol=seagreen]
and[vbcol=seagreen]
>|||Hi
Your understanding is correct. User A can use the linked server from any
computer. User B(or any other) can not. Error message is related to
initializing the connection.
1. The MSDAORA.dll is already registered since it works perfectly under user
A.
2. Under the linked server security tab, nothing is entered except the last
item is selected: Be made with this security context : username/password.
Nothing related to user A or B. I will look at KB article.
3. MDAC 2.7 is installed and works. Again, it works perfectly under user A.
Post to microsoft.public.fr.sqlserver. Bad suggestion. I have a problem and
you suggest that I go somewhere else. No thanks.
I still have a problem with my linked server and I still need some support.
I believe it is related to a security issue using integrated security login
because I can access the linked server with a SQLServer login from any user
or computer.
What other suggestions do you have ? Surely, you can find a SQL Server /
linked server / integrated security specialist somewhere in Microsoft ?
Thanks anyway
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Your explaination is great. Now my understanding of this
> issue is: On both Computer A and Computer B, if you
> logon the SQL Server using User A and run the Openquery
> statement, the query works fine; but if you logon as
> User B, the query fails regardless whether you are on
> Computer A or Computer B. If this is not correct,
> please let me know.
> This issue looks strange because the error message
> should not be caused by a permission problem.
> Nevertheless, let's try the following steps to see what
> happens:
> To isolate the problem we don't need to involve Computer
> B in our troubleshooting step.
> 1. Register MSDAORA.dll using RegSvr32.exe and then test
> the problem.
> 2. Check the Security tab of the Linked Server
> Properties dialog box to see if there is anything
> related to User A. What do you see from the tab? You may
> want to remove the linked server and then set up a
> linked server with a simliar script to the one mentioned
> in KB280106 and then test the problem.
> 3. If the issue persists, reinstall MDAC to see if it
> helps. You can download the latest MDAC from the
> following link:
> http://www.microsoft.com/downloads/...aspx?displayla
> ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
> If the issue still occurs, I suggest that you post this
> issue in the <microsoft.public.fr.sqlserver> newsgroup
> as you are using Franch version.
> Hope it helps.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with
> technical phone support at no charge during critical LAN
> outages or "business down" situations. This benefit is
> available 24 hours a day, 7 days a week to all Microsoft
> technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...support/support
> overview/40010469
> Others:
> https://partner.microsoft.com/US/te...support/support
> overview/
> If you are outside the United States, please visit our
> International Support page:
> http://support.microsoft.com/defaul...scid=%2finterna
> tional.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --|||Hi Stephane,
I truely understand your concern, but by asking you to
post this issue in the <microsoft.public.fr.sqlserver>
newsgroup, I did not mean to bounce you between support
professionals. We always try our best to assist
customers whether they are using English version or
non-English version of products, but for those issues
that occur in non-English version of products, it is
best to troubleshoot them in the newsgroup setup
specifically for the relevant language. That way the
issues can be resolved in a more efficient manner. In
your case where the problem is not common, we may need
to check some logs such as Windows Event logs and SQL
Error logs to isolate the problem, we would also like to
get the exact error message, but we have difficulties
doing this with a non-English product. Your
understanding on this would be much appreciated.
I agree that the issue relates to the login account used
to connect to SQL Server. Let's perform the following
steps to see if the issue can be resolved (before making
any changes, note down the original settings in order to
roll back):
1. Run dcomcnfg to open the Component Services MMC.
Expand Component Services, expand Computers. Right-click
My Computer.
2. On the 'Default Properties' tab, verify that
- Enable Distributed COM on this computer is
checked
- Default Authentication Level is set to Connect,
- Default Impersonation Level = Impersonate.
3. On the 'COM Security' tab, click on the 'Edit
Default...' button for each of the 'Access Permissions'
and the 'Default Launch and Activation Permissions', if
you see a list of names in the dialog box then make sure
the startup account for the SQL Server service is in the
list or belongs to a group that is in the list.
4. Expand 'My Computer' and click the 'DCOM config'
folder, find out 'MSDAORA' on the right pane and then
right-click 'MSDAORA' and click 'Properties', in general
here are the settings that are known to work:
- On the 'General' tab the Authentication Level is
set to Default
- On the 'Security' tab, 'Launch and Activation
Permissions', 'Access Permissions' and 'Configuration
Permissions' should have 'Use Default' selected. If not,
click on the 'Edit...' button and if you see a list of
names in the dialog box then make sure the account used
to start the SQL Server service is in the list or
belongs to a group that is in the list.
- On the 'Identity' tab, select 'This user' and
type the SQL Server Service startup account and password
in the text boxes.
5. Perform step4 on MSDAINITIALIZE.
6. Restart the computer and then test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUFQ3p3veomzo4ARHuHhI/d7yYwWg==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "examnotes"
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
<H0FSQ1RBFHA.764@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Fri, 28 Jan 2005 06:13:02 -0800
>Lines: 106
>Message-ID:
<27C18AF3-63D8-430C-BE26-E064C736B756@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44286
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>Your understanding is correct. User A can use the
linked server from any
>computer. User B(or any other) can not. Error message
is related to
>initializing the connection.
>1. The MSDAORA.dll is already registered since it works
perfectly under user
>A.
>2. Under the linked server security tab, nothing is
entered except the last
>item is selected: Be made with this security context :
username/password.
>Nothing related to user A or B. I will look at KB
article.
>3. MDAC 2.7 is installed and works. Again, it works
perfectly under user A.
>Post to microsoft.public.fr.sqlserver. Bad suggestion.
I have a problem and
>you suggest that I go somewhere else. No thanks.
>I still have a problem with my linked server and I
still need some support.
>I believe it is related to a security issue using
integrated security login
>because I can access the linked server with a SQLServer
login from any user
>or computer.
>What other suggestions do you have ? Surely, you can
find a SQL Server /
>linked server / integrated security specialist
somewhere in Microsoft ?
>Thanks anyway
>Stephane
>
>"William Wang[MSFT]" wrote:
>
this[vbcol=seagreen]
Openquery[vbcol=seagreen]
what[vbcol=seagreen]
Computer[vbcol=seagreen]
test[vbcol=seagreen]
may[vbcol=seagreen]
mentioned[vbcol=seagreen]
http://www.microsoft.com/downloads/...aspx?displayla[vbcol=seagreen]
this[vbcol=seagreen]
newsgroup[vbcol=seagreen]
with[vbcol=seagreen]
LAN[vbcol=seagreen]
is[vbcol=seagreen]
Microsoft[vbcol=seagreen]
https://partner.microsoft.com/US/te...support/support[vbcol=seagreen]
https://partner.microsoft.com/US/te...support/support[vbcol=seagreen]
our[vbcol=seagreen]
http://support.microsoft.com/defaul...scid=%2finterna[vbcol=seagreen]
and[vbcol=seagreen]
>
Linked server 7302 error
Hi
I have a strange connection error that I believe is related to security. But
I need some advice.
I need to know what are exactly the permissions needed to establish a linked
server connection through network using integrated security.
My setup is the following for my test environment before I implement a
linked server in production servers.
I have installed MSDE on my Windows XP FR machine. The exact version is SQL
Server Desktop Engine 8.00.194 on Windows NT5.1(2600) Francais(France). MDAC
2.7 is installed. MSDAORA.dll version is 2.71.9030. MSDAORAR.dll version is
2.70.7713.
I have installed Oracle 9i SQL*Net Client 9.0.1.1.1. All Oracle tools are
configured and work properly and allow me to access the Oracle database
without any problem. I conclude there is no Oracle connectivity problem.
I am a local admin. I add my user A to the Security/Connections tab to allow
a connection to this DB. I add a linked server pointing to my Oracle DB where
the oracle user is readonly. Still on the same machine, I open the Query
analyser using local server and integrated security. Works like a charm.
I enter a query using the linked server. select * from
openquery(ORACLE,'sql') where sql is executed on the oracle DB. I receive
results correctly. This works perfectly. I go to another computer and log
with user A. I use the QA to connect to my SQLserver DB with integrated
security. It works and I can query the SQLServer tables. I run the same
linked server query as if I was on my first machine and it works.
Now my problem. In my SQLServer, I add a second Security/Connection user B.
On the second computer using QA and user B, I can connect to my SQLServer
with integrated security under user B. But when I try to run the linked
server query, it does not work. I get an error logged in the registry
whenever I attempt a connection. The error is DCOMM 10002 Access denied
attempting to launch a DCOM server. The server is
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
This corresponds to MSDAINITIALIZE DCOM component. In QA, my query returns
with error 7302, Unable to create a MSDAORA OLEDB provider instance.
What is the problem ?
I have tried so far:
- giving admin rights on to user B on SQL Server computer. No success.
- the linked server works when I use a SQLServer authentication.
Other thoughts ?
Stephane
Sorry for the third post. I had trouble with Microsoft managed newsgroup
stuff...
Hi Stephane,
Before we go further, I'd like to get a better
understanding of the following points to ensure that I
understand the problem clearly:
1. "I add my user A to the Security/Connections tab to
allow a connection to this DB"
How did you do this? Where is the Security/Connections
tab? Is "this DB" a SQL Server DB or a Oracle DB?
2. What is the difference between User A and User B? If
you create a new user, say User C, do you still have
this problem?
You may want to refer to this article for more basic
troubleshooting steps regarding this error:
280106 How to set up and troubleshoot a linked server to
Oracle in SQL Server
http://support.microsoft.com/?id=280106
Feel free to post back if you have any further updates.
Sincerely,
William Wang
Microsoft Online Partner Support
=============================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=============================
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUDvo+royZwDPf5QV+3AtJyHzQhaA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>Subject: Linked server 7302 error
>Date: Wed, 26 Jan 2005 07:49:03 -0800
>Lines: 52
>Message-ID:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44257
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>I have a strange connection error that I believe is
related to security. But
>I need some advice.
>I need to know what are exactly the permissions needed
to establish a linked
>server connection through network using integrated
security.
>My setup is the following for my test environment
before I implement a
>linked server in production servers.
>I have installed MSDE on my Windows XP FR machine. The
exact version is SQL
>Server Desktop Engine 8.00.194 on Windows NT5.1(2600)
Francais(France). MDAC
>2.7 is installed. MSDAORA.dll version is 2.71.9030.
MSDAORAR.dll version is
>2.70.7713.
>I have installed Oracle 9i SQL*Net Client 9.0.1.1.1.
All Oracle tools are
>configured and work properly and allow me to access the
Oracle database
>without any problem. I conclude there is no Oracle
connectivity problem.
>I am a local admin. I add my user A to the
Security/Connections tab to allow
>a connection to this DB. I add a linked server pointing
to my Oracle DB where
>the oracle user is readonly. Still on the same machine,
I open the Query
>analyser using local server and integrated security.
Works like a charm.
>I enter a query using the linked server. select * from
>openquery(ORACLE,'sql') where sql is executed on the
oracle DB. I receive
>results correctly. This works perfectly. I go to
another computer and log
>with user A. I use the QA to connect to my SQLserver DB
with integrated
>security. It works and I can query the SQLServer
tables. I run the same
>linked server query as if I was on my first machine and
it works.
>Now my problem. In my SQLServer, I add a second
Security/Connection user B.
>On the second computer using QA and user B, I can
connect to my SQLServer
>with integrated security under user B. But when I try
to run the linked
>server query, it does not work. I get an error logged
in the registry
>whenever I attempt a connection. The error is DCOMM
10002 Access denied
>attempting to launch a DCOM server. The server is
>{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
>This corresponds to MSDAINITIALIZE DCOM component. In
QA, my query returns
>with error 7302, Unable to create a MSDAORA OLEDB
provider instance.
>What is the problem ?
>I have tried so far:
>- giving admin rights on to user B on SQL Server
computer. No success.
>- the linked server works when I use a SQLServer
authentication.
>Other thoughts ?
>Stephane
>Sorry for the third post. I had trouble with Microsoft
managed newsgroup
>stuff...
>
|||Hi William
Here are answers to your questions.
1. I use SQL Server Enterprise Manager to add a user to the Security/Logins.
Sorry, my Security/Connections is a bad translation from my French
installation.
2. I had already tried a third user before I posted. It shows the same
behavior.
Computer A Computer B
Windows XP Fr SP1 Windows XP Fr
MSDE installation SQL Server tools
Oracle network drivers No oracle drivers
User A is local admin User a logs in
this computer.
In QA, login to DB with integrated QA login integrated
security
security.
Queries to linked server work. Queries to linked
server work
Login with user B Login with user
B on computer B
Open QA, login to DB with integrated Open QA, login to DB with
integrated
security. security
Run linked server query. Does not Run linked server query.
Error 7302
work(7302 error) but no DCOM but no DCOM error anymore
in
error in eventlog. eventlog.
On SQLServer error log, I see the following message:
Unable to load OLE/DB initialization service.
On all computers, if I log to SQLServer DB with SQLServer security,
everything works perfectly.
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Before we go further, I'd like to get a better
> understanding of the following points to ensure that I
> understand the problem clearly:
> 1. "I add my user A to the Security/Connections tab to
> allow a connection to this DB"
> How did you do this? Where is the Security/Connections
> tab? Is "this DB" a SQL Server DB or a Oracle DB?
> 2. What is the difference between User A and User B? If
> you create a new user, say User C, do you still have
> this problem?
> You may want to refer to this article for more basic
> troubleshooting steps regarding this error:
> 280106 How to set up and troubleshoot a linked server to
> Oracle in SQL Server
> http://support.microsoft.com/?id=280106
> Feel free to post back if you have any further updates.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> =============================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =============================
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
|||Hi Stephane,
Your explaination is great. Now my understanding of this
issue is: On both Computer A and Computer B, if you
logon the SQL Server using User A and run the Openquery
statement, the query works fine; but if you logon as
User B, the query fails regardless whether you are on
Computer A or Computer B. If this is not correct,
please let me know.
This issue looks strange because the error message
should not be caused by a permission problem.
Nevertheless, let's try the following steps to see what
happens:
To isolate the problem we don't need to involve Computer
B in our troubleshooting step.
1. Register MSDAORA.dll using RegSvr32.exe and then test
the problem.
2. Check the Security tab of the Linked Server
Properties dialog box to see if there is anything
related to User A. What do you see from the tab? You may
want to remove the linked server and then set up a
linked server with a simliar script to the one mentioned
in KB280106 and then test the problem.
3. If the issue persists, reinstall MDAC to see if it
helps. You can download the latest MDAC from the
following link:
http://www.microsoft.com/downloads/d...aspx?displayla
ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
If the issue still occurs, I suggest that you post this
issue in the <microsoft.public.fr.sqlserver> newsgroup
as you are using Franch version.
Hope it helps.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with
technical phone support at no charge during critical LAN
outages or "business down" situations. This benefit is
available 24 hours a day, 7 days a week to all Microsoft
technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...upport/support
overview/40010469
Others:
https://partner.microsoft.com/US/tec...upport/support
overview/
If you are outside the United States, please visit our
International Support page:
http://support.microsoft.com/default...cid=%2finterna
tional.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUEeBMq2g7sFC82QQGsoRNJgct6CA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Thu, 27 Jan 2005 05:57:01 -0800
>Lines: 83
>Message-ID:
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44266
>X-Tomcat-NG: microsoft.public.sqlserver.connect
> Hi William
> Here are answers to your questions.
>1. I use SQL Server Enterprise Manager to add a user to
the Security/Logins.
>Sorry, my Security/Connections is a bad translation
from my French
>installation.
>2. I had already tried a third user before I posted. It
shows the same
>behavior.
>Computer A
Computer B
>Windows XP Fr SP1
Windows XP Fr
>MSDE installation
SQL Server tools
>Oracle network drivers
No oracle drivers
>User A is local admin
User a logs in
>this computer.
>In QA, login to DB with integrated QA
login integrated
>security
>security.
>Queries to linked server work.
Queries to linked
>server work
>Login with user B
Login with user
>B on computer B
>Open QA, login to DB with integrated Open
QA, login to DB with
>integrated
>security.
security
>Run linked server query. Does not Run
linked server query.
>Error 7302
>work(7302 error) but no DCOM but
no DCOM error anymore
>in
>error in eventlog.
eventlog.
>On SQLServer error log, I see the following message:
> Unable to load OLE/DB initialization service.
>On all computers, if I log to SQLServer DB with
SQLServer security,[vbcol=seagreen]
>everything works perfectly.
>Stephane
>
>"William Wang[MSFT]" wrote:
I[vbcol=seagreen]
to[vbcol=seagreen]
Security/Connections[vbcol=seagreen]
If[vbcol=seagreen]
to[vbcol=seagreen]
updates.[vbcol=seagreen]
and
>
|||Hi
Your understanding is correct. User A can use the linked server from any
computer. User B(or any other) can not. Error message is related to
initializing the connection.
1. The MSDAORA.dll is already registered since it works perfectly under user
A.
2. Under the linked server security tab, nothing is entered except the last
item is selected: Be made with this security context : username/password.
Nothing related to user A or B. I will look at KB article.
3. MDAC 2.7 is installed and works. Again, it works perfectly under user A.
Post to microsoft.public.fr.sqlserver. Bad suggestion. I have a problem and
you suggest that I go somewhere else. No thanks.
I still have a problem with my linked server and I still need some support.
I believe it is related to a security issue using integrated security login
because I can access the linked server with a SQLServer login from any user
or computer.
What other suggestions do you have ? Surely, you can find a SQL Server /
linked server / integrated security specialist somewhere in Microsoft ?
Thanks anyway
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Your explaination is great. Now my understanding of this
> issue is: On both Computer A and Computer B, if you
> logon the SQL Server using User A and run the Openquery
> statement, the query works fine; but if you logon as
> User B, the query fails regardless whether you are on
> Computer A or Computer B. If this is not correct,
> please let me know.
> This issue looks strange because the error message
> should not be caused by a permission problem.
> Nevertheless, let's try the following steps to see what
> happens:
> To isolate the problem we don't need to involve Computer
> B in our troubleshooting step.
> 1. Register MSDAORA.dll using RegSvr32.exe and then test
> the problem.
> 2. Check the Security tab of the Linked Server
> Properties dialog box to see if there is anything
> related to User A. What do you see from the tab? You may
> want to remove the linked server and then set up a
> linked server with a simliar script to the one mentioned
> in KB280106 and then test the problem.
> 3. If the issue persists, reinstall MDAC to see if it
> helps. You can download the latest MDAC from the
> following link:
> http://www.microsoft.com/downloads/d...aspx?displayla
> ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
> If the issue still occurs, I suggest that you post this
> issue in the <microsoft.public.fr.sqlserver> newsgroup
> as you are using Franch version.
> Hope it helps.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with
> technical phone support at no charge during critical LAN
> outages or "business down" situations. This benefit is
> available 24 hours a day, 7 days a week to all Microsoft
> technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...upport/support
> overview/40010469
> Others:
> https://partner.microsoft.com/US/tec...upport/support
> overview/
> If you are outside the United States, please visit our
> International Support page:
> http://support.microsoft.com/default...cid=%2finterna
> tional.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
|||Hi Stephane,
I truely understand your concern, but by asking you to
post this issue in the <microsoft.public.fr.sqlserver>
newsgroup, I did not mean to bounce you between support
professionals. We always try our best to assist
customers whether they are using English version or
non-English version of products, but for those issues
that occur in non-English version of products, it is
best to troubleshoot them in the newsgroup setup
specifically for the relevant language. That way the
issues can be resolved in a more efficient manner. In
your case where the problem is not common, we may need
to check some logs such as Windows Event logs and SQL
Error logs to isolate the problem, we would also like to
get the exact error message, but we have difficulties
doing this with a non-English product. Your
understanding on this would be much appreciated.
I agree that the issue relates to the login account used
to connect to SQL Server. Let's perform the following
steps to see if the issue can be resolved (before making
any changes, note down the original settings in order to
roll back):
1. Run dcomcnfg to open the Component Services MMC.
Expand Component Services, expand Computers. Right-click
My Computer.
2. On the 'Default Properties' tab, verify that
- Enable Distributed COM on this computer is
checked
- Default Authentication Level is set to Connect,
- Default Impersonation Level = Impersonate.
3. On the 'COM Security' tab, click on the 'Edit
Default...' button for each of the 'Access Permissions'
and the 'Default Launch and Activation Permissions', if
you see a list of names in the dialog box then make sure
the startup account for the SQL Server service is in the
list or belongs to a group that is in the list.
4. Expand 'My Computer' and click the 'DCOM config'
folder, find out 'MSDAORA' on the right pane and then
right-click 'MSDAORA' and click 'Properties', in general
here are the settings that are known to work:
- On the 'General' tab the Authentication Level is
set to Default
- On the 'Security' tab, 'Launch and Activation
Permissions', 'Access Permissions' and 'Configuration
Permissions' should have 'Use Default' selected. If not,
click on the 'Edit...' button and if you see a list of
names in the dialog box then make sure the account used
to start the SQL Server service is in the list or
belongs to a group that is in the list.
- On the 'Identity' tab, select 'This user' and
type the SQL Server Service startup account and password
in the text boxes.
5. Perform step4 on MSDAINITIALIZE.
6. Restart the computer and then test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUFQ3p3veomzo4ARHuHhI/d7yYwWg==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
<H0FSQ1RBFHA.764@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Fri, 28 Jan 2005 06:13:02 -0800
>Lines: 106
>Message-ID:
<27C18AF3-63D8-430C-BE26-E064C736B756@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44286
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>Your understanding is correct. User A can use the
linked server from any
>computer. User B(or any other) can not. Error message
is related to
>initializing the connection.
>1. The MSDAORA.dll is already registered since it works
perfectly under user
>A.
>2. Under the linked server security tab, nothing is
entered except the last
>item is selected: Be made with this security context :
username/password.
>Nothing related to user A or B. I will look at KB
article.
>3. MDAC 2.7 is installed and works. Again, it works
perfectly under user A.
>Post to microsoft.public.fr.sqlserver. Bad suggestion.
I have a problem and
>you suggest that I go somewhere else. No thanks.
>I still have a problem with my linked server and I
still need some support.
>I believe it is related to a security issue using
integrated security login
>because I can access the linked server with a SQLServer
login from any user
>or computer.
>What other suggestions do you have ? Surely, you can
find a SQL Server /
>linked server / integrated security specialist
somewhere in Microsoft ?[vbcol=seagreen]
>Thanks anyway
>Stephane
>
>"William Wang[MSFT]" wrote:
this[vbcol=seagreen]
Openquery[vbcol=seagreen]
what[vbcol=seagreen]
Computer[vbcol=seagreen]
test[vbcol=seagreen]
may[vbcol=seagreen]
mentioned[vbcol=seagreen]
http://www.microsoft.com/downloads/d...aspx?displayla[vbcol=seagreen]
this[vbcol=seagreen]
newsgroup[vbcol=seagreen]
with[vbcol=seagreen]
LAN[vbcol=seagreen]
is[vbcol=seagreen]
Microsoft[vbcol=seagreen]
https://partner.microsoft.com/US/tec...upport/support[vbcol=seagreen]
https://partner.microsoft.com/US/tec...upport/support[vbcol=seagreen]
our[vbcol=seagreen]
http://support.microsoft.com/default...cid=%2finterna[vbcol=seagreen]
and
>
I have a strange connection error that I believe is related to security. But
I need some advice.
I need to know what are exactly the permissions needed to establish a linked
server connection through network using integrated security.
My setup is the following for my test environment before I implement a
linked server in production servers.
I have installed MSDE on my Windows XP FR machine. The exact version is SQL
Server Desktop Engine 8.00.194 on Windows NT5.1(2600) Francais(France). MDAC
2.7 is installed. MSDAORA.dll version is 2.71.9030. MSDAORAR.dll version is
2.70.7713.
I have installed Oracle 9i SQL*Net Client 9.0.1.1.1. All Oracle tools are
configured and work properly and allow me to access the Oracle database
without any problem. I conclude there is no Oracle connectivity problem.
I am a local admin. I add my user A to the Security/Connections tab to allow
a connection to this DB. I add a linked server pointing to my Oracle DB where
the oracle user is readonly. Still on the same machine, I open the Query
analyser using local server and integrated security. Works like a charm.
I enter a query using the linked server. select * from
openquery(ORACLE,'sql') where sql is executed on the oracle DB. I receive
results correctly. This works perfectly. I go to another computer and log
with user A. I use the QA to connect to my SQLserver DB with integrated
security. It works and I can query the SQLServer tables. I run the same
linked server query as if I was on my first machine and it works.
Now my problem. In my SQLServer, I add a second Security/Connection user B.
On the second computer using QA and user B, I can connect to my SQLServer
with integrated security under user B. But when I try to run the linked
server query, it does not work. I get an error logged in the registry
whenever I attempt a connection. The error is DCOMM 10002 Access denied
attempting to launch a DCOM server. The server is
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
This corresponds to MSDAINITIALIZE DCOM component. In QA, my query returns
with error 7302, Unable to create a MSDAORA OLEDB provider instance.
What is the problem ?
I have tried so far:
- giving admin rights on to user B on SQL Server computer. No success.
- the linked server works when I use a SQLServer authentication.
Other thoughts ?
Stephane
Sorry for the third post. I had trouble with Microsoft managed newsgroup
stuff...
Hi Stephane,
Before we go further, I'd like to get a better
understanding of the following points to ensure that I
understand the problem clearly:
1. "I add my user A to the Security/Connections tab to
allow a connection to this DB"
How did you do this? Where is the Security/Connections
tab? Is "this DB" a SQL Server DB or a Oracle DB?
2. What is the difference between User A and User B? If
you create a new user, say User C, do you still have
this problem?
You may want to refer to this article for more basic
troubleshooting steps regarding this error:
280106 How to set up and troubleshoot a linked server to
Oracle in SQL Server
http://support.microsoft.com/?id=280106
Feel free to post back if you have any further updates.
Sincerely,
William Wang
Microsoft Online Partner Support
=============================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=============================
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUDvo+royZwDPf5QV+3AtJyHzQhaA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>Subject: Linked server 7302 error
>Date: Wed, 26 Jan 2005 07:49:03 -0800
>Lines: 52
>Message-ID:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44257
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>I have a strange connection error that I believe is
related to security. But
>I need some advice.
>I need to know what are exactly the permissions needed
to establish a linked
>server connection through network using integrated
security.
>My setup is the following for my test environment
before I implement a
>linked server in production servers.
>I have installed MSDE on my Windows XP FR machine. The
exact version is SQL
>Server Desktop Engine 8.00.194 on Windows NT5.1(2600)
Francais(France). MDAC
>2.7 is installed. MSDAORA.dll version is 2.71.9030.
MSDAORAR.dll version is
>2.70.7713.
>I have installed Oracle 9i SQL*Net Client 9.0.1.1.1.
All Oracle tools are
>configured and work properly and allow me to access the
Oracle database
>without any problem. I conclude there is no Oracle
connectivity problem.
>I am a local admin. I add my user A to the
Security/Connections tab to allow
>a connection to this DB. I add a linked server pointing
to my Oracle DB where
>the oracle user is readonly. Still on the same machine,
I open the Query
>analyser using local server and integrated security.
Works like a charm.
>I enter a query using the linked server. select * from
>openquery(ORACLE,'sql') where sql is executed on the
oracle DB. I receive
>results correctly. This works perfectly. I go to
another computer and log
>with user A. I use the QA to connect to my SQLserver DB
with integrated
>security. It works and I can query the SQLServer
tables. I run the same
>linked server query as if I was on my first machine and
it works.
>Now my problem. In my SQLServer, I add a second
Security/Connection user B.
>On the second computer using QA and user B, I can
connect to my SQLServer
>with integrated security under user B. But when I try
to run the linked
>server query, it does not work. I get an error logged
in the registry
>whenever I attempt a connection. The error is DCOMM
10002 Access denied
>attempting to launch a DCOM server. The server is
>{2206CDB0-19C1-11D1-89E0-00C04FD7A829}. The user is...
>This corresponds to MSDAINITIALIZE DCOM component. In
QA, my query returns
>with error 7302, Unable to create a MSDAORA OLEDB
provider instance.
>What is the problem ?
>I have tried so far:
>- giving admin rights on to user B on SQL Server
computer. No success.
>- the linked server works when I use a SQLServer
authentication.
>Other thoughts ?
>Stephane
>Sorry for the third post. I had trouble with Microsoft
managed newsgroup
>stuff...
>
|||Hi William
Here are answers to your questions.
1. I use SQL Server Enterprise Manager to add a user to the Security/Logins.
Sorry, my Security/Connections is a bad translation from my French
installation.
2. I had already tried a third user before I posted. It shows the same
behavior.
Computer A Computer B
Windows XP Fr SP1 Windows XP Fr
MSDE installation SQL Server tools
Oracle network drivers No oracle drivers
User A is local admin User a logs in
this computer.
In QA, login to DB with integrated QA login integrated
security
security.
Queries to linked server work. Queries to linked
server work
Login with user B Login with user
B on computer B
Open QA, login to DB with integrated Open QA, login to DB with
integrated
security. security
Run linked server query. Does not Run linked server query.
Error 7302
work(7302 error) but no DCOM but no DCOM error anymore
in
error in eventlog. eventlog.
On SQLServer error log, I see the following message:
Unable to load OLE/DB initialization service.
On all computers, if I log to SQLServer DB with SQLServer security,
everything works perfectly.
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Before we go further, I'd like to get a better
> understanding of the following points to ensure that I
> understand the problem clearly:
> 1. "I add my user A to the Security/Connections tab to
> allow a connection to this DB"
> How did you do this? Where is the Security/Connections
> tab? Is "this DB" a SQL Server DB or a Oracle DB?
> 2. What is the difference between User A and User B? If
> you create a new user, say User C, do you still have
> this problem?
> You may want to refer to this article for more basic
> troubleshooting steps regarding this error:
> 280106 How to set up and troubleshoot a linked server to
> Oracle in SQL Server
> http://support.microsoft.com/?id=280106
> Feel free to post back if you have any further updates.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> =============================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =============================
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
|||Hi Stephane,
Your explaination is great. Now my understanding of this
issue is: On both Computer A and Computer B, if you
logon the SQL Server using User A and run the Openquery
statement, the query works fine; but if you logon as
User B, the query fails regardless whether you are on
Computer A or Computer B. If this is not correct,
please let me know.
This issue looks strange because the error message
should not be caused by a permission problem.
Nevertheless, let's try the following steps to see what
happens:
To isolate the problem we don't need to involve Computer
B in our troubleshooting step.
1. Register MSDAORA.dll using RegSvr32.exe and then test
the problem.
2. Check the Security tab of the Linked Server
Properties dialog box to see if there is anything
related to User A. What do you see from the tab? You may
want to remove the linked server and then set up a
linked server with a simliar script to the one mentioned
in KB280106 and then test the problem.
3. If the issue persists, reinstall MDAC to see if it
helps. You can download the latest MDAC from the
following link:
http://www.microsoft.com/downloads/d...aspx?displayla
ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
If the issue still occurs, I suggest that you post this
issue in the <microsoft.public.fr.sqlserver> newsgroup
as you are using Franch version.
Hope it helps.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with
technical phone support at no charge during critical LAN
outages or "business down" situations. This benefit is
available 24 hours a day, 7 days a week to all Microsoft
technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...upport/support
overview/40010469
Others:
https://partner.microsoft.com/US/tec...upport/support
overview/
If you are outside the United States, please visit our
International Support page:
http://support.microsoft.com/default...cid=%2finterna
tional.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUEeBMq2g7sFC82QQGsoRNJgct6CA==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Thu, 27 Jan 2005 05:57:01 -0800
>Lines: 83
>Message-ID:
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44266
>X-Tomcat-NG: microsoft.public.sqlserver.connect
> Hi William
> Here are answers to your questions.
>1. I use SQL Server Enterprise Manager to add a user to
the Security/Logins.
>Sorry, my Security/Connections is a bad translation
from my French
>installation.
>2. I had already tried a third user before I posted. It
shows the same
>behavior.
>Computer A
Computer B
>Windows XP Fr SP1
Windows XP Fr
>MSDE installation
SQL Server tools
>Oracle network drivers
No oracle drivers
>User A is local admin
User a logs in
>this computer.
>In QA, login to DB with integrated QA
login integrated
>security
>security.
>Queries to linked server work.
Queries to linked
>server work
>Login with user B
Login with user
>B on computer B
>Open QA, login to DB with integrated Open
QA, login to DB with
>integrated
>security.
security
>Run linked server query. Does not Run
linked server query.
>Error 7302
>work(7302 error) but no DCOM but
no DCOM error anymore
>in
>error in eventlog.
eventlog.
>On SQLServer error log, I see the following message:
> Unable to load OLE/DB initialization service.
>On all computers, if I log to SQLServer DB with
SQLServer security,[vbcol=seagreen]
>everything works perfectly.
>Stephane
>
>"William Wang[MSFT]" wrote:
I[vbcol=seagreen]
to[vbcol=seagreen]
Security/Connections[vbcol=seagreen]
If[vbcol=seagreen]
to[vbcol=seagreen]
updates.[vbcol=seagreen]
and
>
|||Hi
Your understanding is correct. User A can use the linked server from any
computer. User B(or any other) can not. Error message is related to
initializing the connection.
1. The MSDAORA.dll is already registered since it works perfectly under user
A.
2. Under the linked server security tab, nothing is entered except the last
item is selected: Be made with this security context : username/password.
Nothing related to user A or B. I will look at KB article.
3. MDAC 2.7 is installed and works. Again, it works perfectly under user A.
Post to microsoft.public.fr.sqlserver. Bad suggestion. I have a problem and
you suggest that I go somewhere else. No thanks.
I still have a problem with my linked server and I still need some support.
I believe it is related to a security issue using integrated security login
because I can access the linked server with a SQLServer login from any user
or computer.
What other suggestions do you have ? Surely, you can find a SQL Server /
linked server / integrated security specialist somewhere in Microsoft ?
Thanks anyway
Stephane
"William Wang[MSFT]" wrote:
> Hi Stephane,
> Your explaination is great. Now my understanding of this
> issue is: On both Computer A and Computer B, if you
> logon the SQL Server using User A and run the Openquery
> statement, the query works fine; but if you logon as
> User B, the query fails regardless whether you are on
> Computer A or Computer B. If this is not correct,
> please let me know.
> This issue looks strange because the error message
> should not be caused by a permission problem.
> Nevertheless, let's try the following steps to see what
> happens:
> To isolate the problem we don't need to involve Computer
> B in our troubleshooting step.
> 1. Register MSDAORA.dll using RegSvr32.exe and then test
> the problem.
> 2. Check the Security tab of the Linked Server
> Properties dialog box to see if there is anything
> related to User A. What do you see from the tab? You may
> want to remove the linked server and then set up a
> linked server with a simliar script to the one mentioned
> in KB280106 and then test the problem.
> 3. If the issue persists, reinstall MDAC to see if it
> helps. You can download the latest MDAC from the
> following link:
> http://www.microsoft.com/downloads/d...aspx?displayla
> ng=fr&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
> If the issue still occurs, I suggest that you post this
> issue in the <microsoft.public.fr.sqlserver> newsgroup
> as you are using Franch version.
> Hope it helps.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with
> technical phone support at no charge during critical LAN
> outages or "business down" situations. This benefit is
> available 24 hours a day, 7 days a week to all Microsoft
> technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...upport/support
> overview/40010469
> Others:
> https://partner.microsoft.com/US/tec...upport/support
> overview/
> If you are outside the United States, please visit our
> International Support page:
> http://support.microsoft.com/default...cid=%2finterna
> tional.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
|||Hi Stephane,
I truely understand your concern, but by asking you to
post this issue in the <microsoft.public.fr.sqlserver>
newsgroup, I did not mean to bounce you between support
professionals. We always try our best to assist
customers whether they are using English version or
non-English version of products, but for those issues
that occur in non-English version of products, it is
best to troubleshoot them in the newsgroup setup
specifically for the relevant language. That way the
issues can be resolved in a more efficient manner. In
your case where the problem is not common, we may need
to check some logs such as Windows Event logs and SQL
Error logs to isolate the problem, we would also like to
get the exact error message, but we have difficulties
doing this with a non-English product. Your
understanding on this would be much appreciated.
I agree that the issue relates to the login account used
to connect to SQL Server. Let's perform the following
steps to see if the issue can be resolved (before making
any changes, note down the original settings in order to
roll back):
1. Run dcomcnfg to open the Component Services MMC.
Expand Component Services, expand Computers. Right-click
My Computer.
2. On the 'Default Properties' tab, verify that
- Enable Distributed COM on this computer is
checked
- Default Authentication Level is set to Connect,
- Default Impersonation Level = Impersonate.
3. On the 'COM Security' tab, click on the 'Edit
Default...' button for each of the 'Access Permissions'
and the 'Default Launch and Activation Permissions', if
you see a list of names in the dialog box then make sure
the startup account for the SQL Server service is in the
list or belongs to a group that is in the list.
4. Expand 'My Computer' and click the 'DCOM config'
folder, find out 'MSDAORA' on the right pane and then
right-click 'MSDAORA' and click 'Properties', in general
here are the settings that are known to work:
- On the 'General' tab the Authentication Level is
set to Default
- On the 'Security' tab, 'Launch and Activation
Permissions', 'Access Permissions' and 'Configuration
Permissions' should have 'Use Default' selected. If not,
click on the 'Edit...' button and if you see a list of
names in the dialog box then make sure the account used
to start the SQL Server service is in the list or
belongs to a group that is in the list.
- On the 'Identity' tab, select 'This user' and
type the SQL Server Service startup account and password
in the text boxes.
5. Perform step4 on MSDAINITIALIZE.
6. Restart the computer and then test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Linked server 7302 error
>thread-index: AcUFQ3p3veomzo4ARHuHhI/d7yYwWg==
>X-WBNR-Posting-Host: 205.151.229.14
>From: "=?Utf-8?B?U1BhcXVpbg==?="
<spaquin@.newsgroup.nospam>
>References:
<0245D03E-DA88-49FA-86AC-65FCE40910E8@.microsoft.com>
<CV5HjoFBFHA.644@.cpmsftngxa10.phx.gbl>
<FF352DAC-0676-4328-818B-2C57D7F563EA@.microsoft.com>
<H0FSQ1RBFHA.764@.cpmsftngxa10.phx.gbl>
>Subject: RE: Linked server 7302 error
>Date: Fri, 28 Jan 2005 06:13:02 -0800
>Lines: 106
>Message-ID:
<27C18AF3-63D8-430C-BE26-E064C736B756@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.connect
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.connect:44286
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi
>Your understanding is correct. User A can use the
linked server from any
>computer. User B(or any other) can not. Error message
is related to
>initializing the connection.
>1. The MSDAORA.dll is already registered since it works
perfectly under user
>A.
>2. Under the linked server security tab, nothing is
entered except the last
>item is selected: Be made with this security context :
username/password.
>Nothing related to user A or B. I will look at KB
article.
>3. MDAC 2.7 is installed and works. Again, it works
perfectly under user A.
>Post to microsoft.public.fr.sqlserver. Bad suggestion.
I have a problem and
>you suggest that I go somewhere else. No thanks.
>I still have a problem with my linked server and I
still need some support.
>I believe it is related to a security issue using
integrated security login
>because I can access the linked server with a SQLServer
login from any user
>or computer.
>What other suggestions do you have ? Surely, you can
find a SQL Server /
>linked server / integrated security specialist
somewhere in Microsoft ?[vbcol=seagreen]
>Thanks anyway
>Stephane
>
>"William Wang[MSFT]" wrote:
this[vbcol=seagreen]
Openquery[vbcol=seagreen]
what[vbcol=seagreen]
Computer[vbcol=seagreen]
test[vbcol=seagreen]
may[vbcol=seagreen]
mentioned[vbcol=seagreen]
http://www.microsoft.com/downloads/d...aspx?displayla[vbcol=seagreen]
this[vbcol=seagreen]
newsgroup[vbcol=seagreen]
with[vbcol=seagreen]
LAN[vbcol=seagreen]
is[vbcol=seagreen]
Microsoft[vbcol=seagreen]
https://partner.microsoft.com/US/tec...upport/support[vbcol=seagreen]
https://partner.microsoft.com/US/tec...upport/support[vbcol=seagreen]
our[vbcol=seagreen]
http://support.microsoft.com/default...cid=%2finterna[vbcol=seagreen]
and
>
Friday, February 24, 2012
Linked Server -> Interbase
Hi!!!
I have a Interbase database and I would like to make a linked server.
I don't have Interbase driver in the Windows 98's ODBC Data Source Administrator
What I need to do?
Thank you.
ByeDownload from web.|||You can get the
Easysoft ODBC for Interbase at
www.easysoft.com
I have a Interbase database and I would like to make a linked server.
I don't have Interbase driver in the Windows 98's ODBC Data Source Administrator
What I need to do?
Thank you.
ByeDownload from web.|||You can get the
Easysoft ODBC for Interbase at
www.easysoft.com
Monday, February 20, 2012
Linked Server
Hi
I would like to now how to create a linked server on SQL 2005, not using a provider SQLNCLI but using a provider SQLOLEDB by T-SQL Commands.
Thanks
This might work for you
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
Subscribe to:
Posts (Atom)