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]
>