|
|
You're in the
sql server connect
group:Linked server 7302 error
sql server connect:
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
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 [quoted text, click to view] "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,
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. -------------------- [quoted text, click to view] >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
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 [quoted text, click to view] "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/details.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/technicalsupport/support > overview/40010469 > Others: > https://partner.microsoft.com/US/technicalsupport/support > overview/ > > If you are outside the United States, please visit our > International Support page: > http://support.microsoft.com/default.aspx?scid=%2finterna > tional.aspx. > > ===================================================== > > 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/details.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/technicalsupport/support overview/40010469 Others: https://partner.microsoft.com/US/technicalsupport/support overview/ If you are outside the United States, please visit our International Support page: http://support.microsoft.com/default.aspx?scid=%2finterna tional.aspx. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- [quoted text, click to view] >Thread-Topic: Linked server 7302 error <0245D03E-DA88-49FA-86AC-65FCE40910E8@microsoft.com> >thread-index: AcUEeBMq2g7sFC82QQGsoRNJgct6CA== >X-WBNR-Posting-Host: 205.151.229.14 >From: "=?Utf-8?B?U1BhcXVpbg==?=" <spaquin@newsgroup.nospam> >References: <CV5HjoFBFHA.644@cpmsftngxa10.phx.gbl> [quoted text, click to view] >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: > >> 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,
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. -------------------- [quoted text, click to view] >Thread-Topic: Linked server 7302 error <0245D03E-DA88-49FA-86AC-65FCE40910E8@microsoft.com> >thread-index: AcUFQ3p3veomzo4ARHuHhI/d7yYwWg== >X-WBNR-Posting-Host: 205.151.229.14 >From: "=?Utf-8?B?U1BhcXVpbg==?=" <spaquin@newsgroup.nospam> >References: <CV5HjoFBFHA.644@cpmsftngxa10.phx.gbl> <FF352DAC-0676-4328-818B-2C57D7F563EA@microsoft.com> <H0FSQ1RBFHA.764@cpmsftngxa10.phx.gbl> [quoted text, click to view] >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: > >> 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/details.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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |