I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group ar
e
connected. I have been forced to specify each group memebr individually. I
s
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>|||The login connecting to my sqlserver has no credentials on the remote server
.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with sa
y
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was lookin
g
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server o
r
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of t
he
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
Friday, March 30, 2012
Linked Server Properties - Security - SQL Server 2000
I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?
I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?
|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>
|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?
I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?
|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>
|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>
Linked Server Properties - Security - SQL Server 2000
I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
--
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>>I would like to specify a locallogin that is a domain(not local) group.
>>It
>> allows me to specify it, but does not recognize when memebrs of the group
>> are
>> connected. I have been forced to specify each group memebr individually.
>> Is
>> there a way to make the domain groupt option work?
>|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
> >I do not think this is possible based on looking at the help file for
> >sp_addlinkedserverlogin.
> >
> > --
> > Jason Massie
> > http://statisticsio.com
> >
> > "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> > message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
> >>I would like to specify a locallogin that is a domain(not local) group.
> >>It
> >> allows me to specify it, but does not recognize when memebrs of the group
> >> are
> >> connected. I have been forced to specify each group memebr individually.
> >> Is
> >> there a way to make the domain groupt option work?
> >
>
>
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
--
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>>I would like to specify a locallogin that is a domain(not local) group.
>>It
>> allows me to specify it, but does not recognize when memebrs of the group
>> are
>> connected. I have been forced to specify each group memebr individually.
>> Is
>> there a way to make the domain groupt option work?
>|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
> >I do not think this is possible based on looking at the help file for
> >sp_addlinkedserverlogin.
> >
> > --
> > Jason Massie
> > http://statisticsio.com
> >
> > "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> > message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
> >>I would like to specify a locallogin that is a domain(not local) group.
> >>It
> >> allows me to specify it, but does not recognize when memebrs of the group
> >> are
> >> connected. I have been forced to specify each group memebr individually.
> >> Is
> >> there a way to make the domain groupt option work?
> >
>
>
Linked Server properties
If I make changes to the linked server properties in the
Security tab, do I need to reboot for them to take affect?
What is the best practice for this?
This is on a SQL Server 2000 instance.
Thanks.They take effect immediately.(or at leat the next time the linked server =is used) if a query happened to be running while youmade the change =isuspect it would not pick up the change, but the next query certainly =should.
Reboot - No
Mike John
"phil" <pjconrad@.icontrans.com> wrote in message =news:09e901c3941f$a54d9d10$a001280a@.phx.gbl...
> If I make changes to the linked server properties in the > Security tab, do I need to reboot for them to take affect?
> What is the best practice for this?
> > This is on a SQL Server 2000 instance. > > Thanks.sql
Security tab, do I need to reboot for them to take affect?
What is the best practice for this?
This is on a SQL Server 2000 instance.
Thanks.They take effect immediately.(or at leat the next time the linked server =is used) if a query happened to be running while youmade the change =isuspect it would not pick up the change, but the next query certainly =should.
Reboot - No
Mike John
"phil" <pjconrad@.icontrans.com> wrote in message =news:09e901c3941f$a54d9d10$a001280a@.phx.gbl...
> If I make changes to the linked server properties in the > Security tab, do I need to reboot for them to take affect?
> What is the best practice for this?
> > This is on a SQL Server 2000 instance. > > Thanks.sql
LINKED server problem..
Hi All,
Here is the problem:
I want to link my sql server 2000 with oracle server and i used Linked
Servers feature under Security in SQL Server Enterprise Manager. I succeed t
o
configure and can view all the table name (not content) whihc belong in
Oracle database.
So, i try to use Query Analyser to pull some data from the linked server.
After i run, here is my error message:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Any ideas? Please advice.
thank you in advance.
regards,
Ocurnos.Any idea expert?Help needed.
thanks
"Daniel" wrote:
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||Any idea expert?Help needed.
thanks
"Daniel" wrote:
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.
Here is the problem:
I want to link my sql server 2000 with oracle server and i used Linked
Servers feature under Security in SQL Server Enterprise Manager. I succeed t
o
configure and can view all the table name (not content) whihc belong in
Oracle database.
So, i try to use Query Analyser to pull some data from the linked server.
After i run, here is my error message:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Any ideas? Please advice.
thank you in advance.
regards,
Ocurnos.Any idea expert?Help needed.
thanks
"Daniel" wrote:
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||Any idea expert?Help needed.
thanks
"Daniel" wrote:
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.
Wednesday, March 28, 2012
linked server problem after few mins
Dear All,
Before i get crazy please help me out, i have creatd a linked server between sql 2005 and sql 2000.
The security of linked server is how,
that i have used a window authitcation accout like domain\user and i m using this account as impersonate and with the option that "be made with the login's current security context".
Now the problem is when i used the application it is working fine, but after if login the applciation with a break of 15 mins or half an hour, i get the error
TCP Provider: An existing connection was forcibly closed by the remote host.\r\n\r\nLogin failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
I am unbale to undersatnd that why it so, y it fails after few mins, and if i check the particular Sp from backend it is working fine.
Moreover if a again create the link server with same setting as mentioned above it will work fine, but again after half an hour it will come up with same problem.
But if i create linked server with sql account that is available on both server using option "be made using this security context" every thing is working fine.
please help why problem in 1st case, and if my concept regarding linked server security is confusing do let me know any good link
Thnx
l
Sound like you're experiencing SynAttackProtect protection.
http://msdn2.microsoft.com/en-us/library/ms189083.aspx
Monday, March 26, 2012
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Friday, March 23, 2012
Linked Server loses login-mapping
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Any help is apprecaited...
Thanks
Please refer to BOL. A couple of links that hopefully will help you to get started:
· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx
· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx
If you have additional questions we will be glad to help.
-Raul Garcia
SDE/T
SQL Server Engine
Linked Server loses login-mapping
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Any help is apprecaited...
Thanks
Please refer to BOL. A couple of links that hopefully will help you to get started:
· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx
· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx
If you have additional questions we will be glad to help.
-Raul Garcia
SDE/T
SQL Server Engine
Friday, March 9, 2012
linked server and windows authentication
background sql2k on win2k3 server
can i still use the linked server if the sql server security was set up
as 'windows only'? if so, how? (i've been using mixed mode (sql and
windows) in the past and was able to establish linked server from
another server by mapping an host account to sa account on the target
server. but that didn't seem working under the 'windows only' mode.
also, does that mean 'sa' account is useless under the 'windows only'
mode?
pls advise. thank you!> also, does that mean 'sa' account is useless under the 'windows only'
> mode?
yes
> can i still use the linked server if the sql server security was set up
> as 'windows only'? if so, how? (i've been using mixed mode (sql and
> windows) in the past and was able to establish linked server from
> another server by mapping an host account to sa account on the target
> server. but that didn't seem working under the 'windows only' mode.
Implementing Delegation.
Troubleshooting Kerberos Delegation
http://www.microsoft.com/technet/pr...rbdel.mspx#EHAA
AMB
"=== Steve L ===" wrote:
> background sql2k on win2k3 server
> can i still use the linked server if the sql server security was set up
> as 'windows only'? if so, how? (i've been using mixed mode (sql and
> windows) in the past and was able to establish linked server from
> another server by mapping an host account to sa account on the target
> server. but that didn't seem working under the 'windows only' mode.
> also, does that mean 'sa' account is useless under the 'windows only'
> mode?
> pls advise. thank you!
>|||thank you. that (delegation) sounds complicated.
my company purchased a vendor application and the vendor consultant
instructed us to install sql backend (mostly default settings), but
with 'windows only' security mode. he saide that's the only way the
application will work. I'm not convinced why it has to be that way for
an application to work or that's the best practice. would anyone share
their views on this? thank you.
w/o the mix mode, obviosuly it's more difficult to implement the linked
server. also i'm wondering what would happen when a master database is
corrupted? i thought it requires sa account and password to restore a
master database. but if 'sa' account becomes useless, how would one
restore a master database if needed? thank you.|||> thank you. that (delegation) sounds complicated.
> my company purchased a vendor application and the vendor consultant
> instructed us to install sql backend (mostly default settings), but
> with 'windows only' security mode. he saide that's the only way the
> application will work. I'm not convinced why it has to be that way for
> an application to work or that's the best practice. would anyone share
> their views on this? thank you.
Because of the integration with windows security, which provides more
features.
See "Authentication Modes" in BOL, for more info.
> w/o the mix mode, obviosuly it's more difficult to implement the linked
> server. also i'm wondering what would happen when a master database is
> corrupted? i thought it requires sa account and password to restore a
> master database. but if 'sa' account becomes useless, how would one
> restore a master database if needed? thank you.
Any login that belong to the fixed server role "sy
min" can do it.
AMB
"=== Steve L ===" wrote:
> thank you. that (delegation) sounds complicated.
> my company purchased a vendor application and the vendor consultant
> instructed us to install sql backend (mostly default settings), but
> with 'windows only' security mode. he saide that's the only way the
> application will work. I'm not convinced why it has to be that way for
> an application to work or that's the best practice. would anyone share
> their views on this? thank you.
> w/o the mix mode, obviosuly it's more difficult to implement the linked
> server. also i'm wondering what would happen when a master database is
> corrupted? i thought it requires sa account and password to restore a
> master database. but if 'sa' account becomes useless, how would one
> restore a master database if needed? thank you.
>
can i still use the linked server if the sql server security was set up
as 'windows only'? if so, how? (i've been using mixed mode (sql and
windows) in the past and was able to establish linked server from
another server by mapping an host account to sa account on the target
server. but that didn't seem working under the 'windows only' mode.
also, does that mean 'sa' account is useless under the 'windows only'
mode?
pls advise. thank you!> also, does that mean 'sa' account is useless under the 'windows only'
> mode?
yes
> can i still use the linked server if the sql server security was set up
> as 'windows only'? if so, how? (i've been using mixed mode (sql and
> windows) in the past and was able to establish linked server from
> another server by mapping an host account to sa account on the target
> server. but that didn't seem working under the 'windows only' mode.
Implementing Delegation.
Troubleshooting Kerberos Delegation
http://www.microsoft.com/technet/pr...rbdel.mspx#EHAA
AMB
"=== Steve L ===" wrote:
> background sql2k on win2k3 server
> can i still use the linked server if the sql server security was set up
> as 'windows only'? if so, how? (i've been using mixed mode (sql and
> windows) in the past and was able to establish linked server from
> another server by mapping an host account to sa account on the target
> server. but that didn't seem working under the 'windows only' mode.
> also, does that mean 'sa' account is useless under the 'windows only'
> mode?
> pls advise. thank you!
>|||thank you. that (delegation) sounds complicated.
my company purchased a vendor application and the vendor consultant
instructed us to install sql backend (mostly default settings), but
with 'windows only' security mode. he saide that's the only way the
application will work. I'm not convinced why it has to be that way for
an application to work or that's the best practice. would anyone share
their views on this? thank you.
w/o the mix mode, obviosuly it's more difficult to implement the linked
server. also i'm wondering what would happen when a master database is
corrupted? i thought it requires sa account and password to restore a
master database. but if 'sa' account becomes useless, how would one
restore a master database if needed? thank you.|||> thank you. that (delegation) sounds complicated.
> my company purchased a vendor application and the vendor consultant
> instructed us to install sql backend (mostly default settings), but
> with 'windows only' security mode. he saide that's the only way the
> application will work. I'm not convinced why it has to be that way for
> an application to work or that's the best practice. would anyone share
> their views on this? thank you.
Because of the integration with windows security, which provides more
features.
See "Authentication Modes" in BOL, for more info.
> w/o the mix mode, obviosuly it's more difficult to implement the linked
> server. also i'm wondering what would happen when a master database is
> corrupted? i thought it requires sa account and password to restore a
> master database. but if 'sa' account becomes useless, how would one
> restore a master database if needed? thank you.
Any login that belong to the fixed server role "sy
min" can do it.AMB
"=== Steve L ===" wrote:
> thank you. that (delegation) sounds complicated.
> my company purchased a vendor application and the vendor consultant
> instructed us to install sql backend (mostly default settings), but
> with 'windows only' security mode. he saide that's the only way the
> application will work. I'm not convinced why it has to be that way for
> an application to work or that's the best practice. would anyone share
> their views on this? thank you.
> w/o the mix mode, obviosuly it's more difficult to implement the linked
> server. also i'm wondering what would happen when a master database is
> corrupted? i thought it requires sa account and password to restore a
> master database. but if 'sa' account becomes useless, how would one
> restore a master database if needed? thank you.
>
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
>
Linked Server (Oracle 9i)
Hello all,
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
Windows Server 2003, Standard edition
MS SQL/Server 2000
Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
Microsoft ODBC for Oracle
Oracle OLEDB
MDAC 2.8 RTM
Server2 (Oracle)
Windows 2000 - Advanced Server
Oracle 9i database (v9.2.0.1.0)
Two nodes clustered using Microsoft cluster manager. (Nodes are
DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
When I try to connect to the linked server in Enterprise Manager I get
the following error messages.
Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
From within Query analyzer I get a slightly different message
reporting that the username/password are incorrect.
dbcc traceon(7399)
select * from TURLIVE..SONICA.INV_LOC
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid
username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
I know the username/password combination is correct and I can use
these from with Oracle enterprise Manager with sucess.
TURLIVE is the name I've given the linked server, SONICA is the name
of the schema on the Oracle database and INV_LOC is a valid table.
TURLIVE is also the name of the database instance on Server2.
Steps taken so far
Install Oracle client tools (Enterprise Manager, Net manager etc) on
Server1.
Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
database. e.g.
TURLIVE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TURLIVE)
)
)
This works fine, I can connect via Oracle Enterprise manager and I can
TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
Configured an ODBC source to TURLIVE.
On Server1 I've configured the linked server using the following SQL.
sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
(password blanked)
I then rebooted Server1
The properties of the new linked server are:
Product name = Oracle
Data Source = TURLIVE
Provider String = blank
I've modifed the registry on Server1 as instructed by a Microsoft KB
article.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI \
OracleXaLib = "oracleclient8.dll"
OracleSqlLib = "orasql8.dll"
OracleOciLib = "oci.dll"
Still no luck. Can anyone please point out he bleeding obvious? :-)
Thanks in advance
As an aside, has anyone ever configured a linked server to an Oracle
Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
Are there any HOWTO guides for this type of connectivity?
Cheers
Dave.
Are you sure you are connecting to sql server with sa username? (check in
enterprise manager registration properties if you are using sa or windows
autentication)
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
Excuse me for my bad english.
"David Gray" <police@.spamcop.net> ha scritto nel messaggio
news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.4ax.com...
> Hello all,
> Having problems connecting to an Oracle 9i database from within
> SQL/Server 2000 using the Security/Linked Servers feature.
> Server1 (SQL/Server)
> --
> Windows Server 2003, Standard edition
> MS SQL/Server 2000
> Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
> Microsoft ODBC for Oracle
> Oracle OLEDB
> MDAC 2.8 RTM
>
> Server2 (Oracle)
> --
> Windows 2000 - Advanced Server
> Oracle 9i database (v9.2.0.1.0)
> Two nodes clustered using Microsoft cluster manager. (Nodes are
> DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
>
> When I try to connect to the linked server in Enterprise Manager I get
> the following error messages.
> Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
> failed.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
>
> From within Query analyzer I get a slightly different message
> reporting that the username/password are incorrect.
> dbcc traceon(7399)
> select * from TURLIVE..SONICA.INV_LOC
>
> Server: Msg 7399, Level 16, State 1, Line 3
> OLE DB provider 'MSDAORA' reported an error. Authentication failed.
> [OLE/DB provider returned message: ORA-01017: invalid
> username/password; logon denied
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
> I know the username/password combination is correct and I can use
> these from with Oracle enterprise Manager with sucess.
> TURLIVE is the name I've given the linked server, SONICA is the name
> of the schema on the Oracle database and INV_LOC is a valid table.
> TURLIVE is also the name of the database instance on Server2.
>
> Steps taken so far
> Install Oracle client tools (Enterprise Manager, Net manager etc) on
> Server1.
> Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
> database. e.g.
> TURLIVE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TURLIVE)
> )
> )
> This works fine, I can connect via Oracle Enterprise manager and I can
> TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
> Configured an ODBC source to TURLIVE.
> On Server1 I've configured the linked server using the following SQL.
> sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
> sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
> (password blanked)
> I then rebooted Server1
> The properties of the new linked server are:
> Product name = Oracle
> Data Source = TURLIVE
> Provider String = blank
> I've modifed the registry on Server1 as instructed by a Microsoft KB
> article.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI \
> OracleXaLib = "oracleclient8.dll"
> OracleSqlLib = "orasql8.dll"
> OracleOciLib = "oci.dll"
>
> Still no luck. Can anyone please point out he bleeding obvious? :-)
> Thanks in advance
>
> As an aside, has anyone ever configured a linked server to an Oracle
> Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
> Are there any HOWTO guides for this type of connectivity?
>
>
> Cheers
> Dave.
>
>
>
>
>
>
>
|||Thats the one, all works fine now. Thanks very much
Dave.
On Wed, 3 Nov 2004 00:45:23 +0100, "Cirrosi"
<CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote:
>Are you sure you are connecting to sql server with sa username? (check in
>enterprise manager registration properties if you are using sa or windows
>autentication)
>try to use
>sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
>Excuse me for my bad english.
>
>"David Gray" <police@.spamcop.net> ha scritto nel messaggio
>news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.4ax.com.. .
>
|||Hi David,
Make sure you are using the login/password. Since error says it is a
"Anthentication error". And also make sure you are using the right
Provider.
-- Use MS driver to Oracle
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
Windows Server 2003, Standard edition
MS SQL/Server 2000
Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
Microsoft ODBC for Oracle
Oracle OLEDB
MDAC 2.8 RTM
Server2 (Oracle)
Windows 2000 - Advanced Server
Oracle 9i database (v9.2.0.1.0)
Two nodes clustered using Microsoft cluster manager. (Nodes are
DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
When I try to connect to the linked server in Enterprise Manager I get
the following error messages.
Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
From within Query analyzer I get a slightly different message
reporting that the username/password are incorrect.
dbcc traceon(7399)
select * from TURLIVE..SONICA.INV_LOC
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid
username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
I know the username/password combination is correct and I can use
these from with Oracle enterprise Manager with sucess.
TURLIVE is the name I've given the linked server, SONICA is the name
of the schema on the Oracle database and INV_LOC is a valid table.
TURLIVE is also the name of the database instance on Server2.
Steps taken so far
Install Oracle client tools (Enterprise Manager, Net manager etc) on
Server1.
Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
database. e.g.
TURLIVE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TURLIVE)
)
)
This works fine, I can connect via Oracle Enterprise manager and I can
TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
Configured an ODBC source to TURLIVE.
On Server1 I've configured the linked server using the following SQL.
sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
(password blanked)
I then rebooted Server1
The properties of the new linked server are:
Product name = Oracle
Data Source = TURLIVE
Provider String = blank
I've modifed the registry on Server1 as instructed by a Microsoft KB
article.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI \
OracleXaLib = "oracleclient8.dll"
OracleSqlLib = "orasql8.dll"
OracleOciLib = "oci.dll"
Still no luck. Can anyone please point out he bleeding obvious? :-)
Thanks in advance
As an aside, has anyone ever configured a linked server to an Oracle
Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
Are there any HOWTO guides for this type of connectivity?
Cheers
Dave.
Are you sure you are connecting to sql server with sa username? (check in
enterprise manager registration properties if you are using sa or windows
autentication)
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
Excuse me for my bad english.
"David Gray" <police@.spamcop.net> ha scritto nel messaggio
news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.4ax.com...
> Hello all,
> Having problems connecting to an Oracle 9i database from within
> SQL/Server 2000 using the Security/Linked Servers feature.
> Server1 (SQL/Server)
> --
> Windows Server 2003, Standard edition
> MS SQL/Server 2000
> Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
> Microsoft ODBC for Oracle
> Oracle OLEDB
> MDAC 2.8 RTM
>
> Server2 (Oracle)
> --
> Windows 2000 - Advanced Server
> Oracle 9i database (v9.2.0.1.0)
> Two nodes clustered using Microsoft cluster manager. (Nodes are
> DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
>
> When I try to connect to the linked server in Enterprise Manager I get
> the following error messages.
> Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
> failed.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
>
> From within Query analyzer I get a slightly different message
> reporting that the username/password are incorrect.
> dbcc traceon(7399)
> select * from TURLIVE..SONICA.INV_LOC
>
> Server: Msg 7399, Level 16, State 1, Line 3
> OLE DB provider 'MSDAORA' reported an error. Authentication failed.
> [OLE/DB provider returned message: ORA-01017: invalid
> username/password; logon denied
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
> I know the username/password combination is correct and I can use
> these from with Oracle enterprise Manager with sucess.
> TURLIVE is the name I've given the linked server, SONICA is the name
> of the schema on the Oracle database and INV_LOC is a valid table.
> TURLIVE is also the name of the database instance on Server2.
>
> Steps taken so far
> Install Oracle client tools (Enterprise Manager, Net manager etc) on
> Server1.
> Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
> database. e.g.
> TURLIVE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TURLIVE)
> )
> )
> This works fine, I can connect via Oracle Enterprise manager and I can
> TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
> Configured an ODBC source to TURLIVE.
> On Server1 I've configured the linked server using the following SQL.
> sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
> sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
> (password blanked)
> I then rebooted Server1
> The properties of the new linked server are:
> Product name = Oracle
> Data Source = TURLIVE
> Provider String = blank
> I've modifed the registry on Server1 as instructed by a Microsoft KB
> article.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI \
> OracleXaLib = "oracleclient8.dll"
> OracleSqlLib = "orasql8.dll"
> OracleOciLib = "oci.dll"
>
> Still no luck. Can anyone please point out he bleeding obvious? :-)
> Thanks in advance
>
> As an aside, has anyone ever configured a linked server to an Oracle
> Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
> Are there any HOWTO guides for this type of connectivity?
>
>
> Cheers
> Dave.
>
>
>
>
>
>
>
|||Thats the one, all works fine now. Thanks very much
Dave.
On Wed, 3 Nov 2004 00:45:23 +0100, "Cirrosi"
<CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote:
>Are you sure you are connecting to sql server with sa username? (check in
>enterprise manager registration properties if you are using sa or windows
>autentication)
>try to use
>sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
>Excuse me for my bad english.
>
>"David Gray" <police@.spamcop.net> ha scritto nel messaggio
>news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.4ax.com.. .
>
|||Hi David,
Make sure you are using the login/password. Since error says it is a
"Anthentication error". And also make sure you are using the right
Provider.
-- Use MS driver to Oracle
Linked Server (Oracle 9i)
Hello all,
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
--
Windows Server 2003, Standard edition
MS SQL/Server 2000
Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
Microsoft ODBC for Oracle
Oracle OLEDB
MDAC 2.8 RTM
Server2 (Oracle)
--
Windows 2000 - Advanced Server
Oracle 9i database (v9.2.0.1.0)
Two nodes clustered using Microsoft cluster manager. (Nodes are
DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
When I try to connect to the linked server in Enterprise Manager I get
the following error messages.
Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
From within Query analyzer I get a slightly different message
reporting that the username/password are incorrect.
dbcc traceon(7399)
select * from TURLIVE..SONICA.INV_LOC
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid
username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
I know the username/password combination is correct and I can use
these from with Oracle enterprise Manager with sucess.
TURLIVE is the name I've given the linked server, SONICA is the name
of the schema on the Oracle database and INV_LOC is a valid table.
TURLIVE is also the name of the database instance on Server2.
Steps taken so far
Install Oracle client tools (Enterprise Manager, Net manager etc) on
Server1.
Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
database. e.g.
TURLIVE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TURLIVE)
)
)
This works fine, I can connect via Oracle Enterprise manager and I can
TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
Configured an ODBC source to TURLIVE.
On Server1 I've configured the linked server using the following SQL.
sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
(password blanked)
I then rebooted Server1
The properties of the new linked server are:
Product name = Oracle
Data Source = TURLIVE
Provider String = blank
I've modifed the registry on Server1 as instructed by a Microsoft KB
article.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI\
OracleXaLib = "oracleclient8.dll"
OracleSqlLib = "orasql8.dll"
OracleOciLib = "oci.dll"
Still no luck. Can anyone please point out he bleeding obvious? :-)
Thanks in advance
As an aside, has anyone ever configured a linked server to an Oracle
Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
Are there any HOWTO guides for this type of connectivity?
Cheers
Dave.Are you sure you are connecting to sql server with sa username? (check in
enterprise manager registration properties if you are using sa or windows
autentication)
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
Excuse me for my bad english.
"David Gray" <police@.spamcop.net> ha scritto nel messaggio
news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.
4ax.com...
> Hello all,
> Having problems connecting to an Oracle 9i database from within
> SQL/Server 2000 using the Security/Linked Servers feature.
> Server1 (SQL/Server)
> --
> Windows Server 2003, Standard edition
> MS SQL/Server 2000
> Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
> Microsoft ODBC for Oracle
> Oracle OLEDB
> MDAC 2.8 RTM
>
> Server2 (Oracle)
> --
> Windows 2000 - Advanced Server
> Oracle 9i database (v9.2.0.1.0)
> Two nodes clustered using Microsoft cluster manager. (Nodes are
> DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
>
> When I try to connect to the linked server in Enterprise Manager I get
> the following error messages.
> Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
> failed.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
>
> From within Query analyzer I get a slightly different message
> reporting that the username/password are incorrect.
> dbcc traceon(7399)
> select * from TURLIVE..SONICA.INV_LOC
>
> Server: Msg 7399, Level 16, State 1, Line 3
> OLE DB provider 'MSDAORA' reported an error. Authentication failed.
> [OLE/DB provider returned message: ORA-01017: invalid
> username/password; logon denied
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
> I know the username/password combination is correct and I can use
> these from with Oracle enterprise Manager with sucess.
> TURLIVE is the name I've given the linked server, SONICA is the name
> of the schema on the Oracle database and INV_LOC is a valid table.
> TURLIVE is also the name of the database instance on Server2.
>
> Steps taken so far
> Install Oracle client tools (Enterprise Manager, Net manager etc) on
> Server1.
> Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
> database. e.g.
> TURLIVE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TURLIVE)
> )
> )
> This works fine, I can connect via Oracle Enterprise manager and I can
> TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
> Configured an ODBC source to TURLIVE.
> On Server1 I've configured the linked server using the following SQL.
> sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
> sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
> (password blanked)
> I then rebooted Server1
> The properties of the new linked server are:
> Product name = Oracle
> Data Source = TURLIVE
> Provider String = blank
> I've modifed the registry on Server1 as instructed by a Microsoft KB
> article.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI\
> OracleXaLib = "oracleclient8.dll"
> OracleSqlLib = "orasql8.dll"
> OracleOciLib = "oci.dll"
>
> Still no luck. Can anyone please point out he bleeding obvious? :-)
> Thanks in advance
>
> As an aside, has anyone ever configured a linked server to an Oracle
> Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
> Are there any HOWTO guides for this type of connectivity?
>
>
> Cheers
> Dave.
>
>
>
>
>
>
>|||Thats the one, all works fine now. Thanks very much
Dave.
On Wed, 3 Nov 2004 00:45:23 +0100, "Cirrosi"
<CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote:
>Are you sure you are connecting to sql server with sa username? (check in
>enterprise manager registration properties if you are using sa or windows
>autentication)
>try to use
>sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
>Excuse me for my bad english.
>
>"David Gray" <police@.spamcop.net> ha scritto nel messaggio
> news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.
4ax.com...
>|||Hi David,
Make sure you are using the login/password. Since error says it is a
"Anthentication error". And also make sure you are using the right
Provider.
-- Use MS driver to Oracle
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
--
Windows Server 2003, Standard edition
MS SQL/Server 2000
Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
Microsoft ODBC for Oracle
Oracle OLEDB
MDAC 2.8 RTM
Server2 (Oracle)
--
Windows 2000 - Advanced Server
Oracle 9i database (v9.2.0.1.0)
Two nodes clustered using Microsoft cluster manager. (Nodes are
DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
When I try to connect to the linked server in Enterprise Manager I get
the following error messages.
Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
From within Query analyzer I get a slightly different message
reporting that the username/password are incorrect.
dbcc traceon(7399)
select * from TURLIVE..SONICA.INV_LOC
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid
username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
I know the username/password combination is correct and I can use
these from with Oracle enterprise Manager with sucess.
TURLIVE is the name I've given the linked server, SONICA is the name
of the schema on the Oracle database and INV_LOC is a valid table.
TURLIVE is also the name of the database instance on Server2.
Steps taken so far
Install Oracle client tools (Enterprise Manager, Net manager etc) on
Server1.
Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
database. e.g.
TURLIVE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TURLIVE)
)
)
This works fine, I can connect via Oracle Enterprise manager and I can
TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
Configured an ODBC source to TURLIVE.
On Server1 I've configured the linked server using the following SQL.
sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
(password blanked)
I then rebooted Server1
The properties of the new linked server are:
Product name = Oracle
Data Source = TURLIVE
Provider String = blank
I've modifed the registry on Server1 as instructed by a Microsoft KB
article.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI\
OracleXaLib = "oracleclient8.dll"
OracleSqlLib = "orasql8.dll"
OracleOciLib = "oci.dll"
Still no luck. Can anyone please point out he bleeding obvious? :-)
Thanks in advance
As an aside, has anyone ever configured a linked server to an Oracle
Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
Are there any HOWTO guides for this type of connectivity?
Cheers
Dave.Are you sure you are connecting to sql server with sa username? (check in
enterprise manager registration properties if you are using sa or windows
autentication)
try to use
sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
Excuse me for my bad english.
"David Gray" <police@.spamcop.net> ha scritto nel messaggio
news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.
4ax.com...
> Hello all,
> Having problems connecting to an Oracle 9i database from within
> SQL/Server 2000 using the Security/Linked Servers feature.
> Server1 (SQL/Server)
> --
> Windows Server 2003, Standard edition
> MS SQL/Server 2000
> Oracle 9i Client kit (OLEDB & ODBC) & Enterprise management tools
> Microsoft ODBC for Oracle
> Oracle OLEDB
> MDAC 2.8 RTM
>
> Server2 (Oracle)
> --
> Windows 2000 - Advanced Server
> Oracle 9i database (v9.2.0.1.0)
> Two nodes clustered using Microsoft cluster manager. (Nodes are
> DATABASE01 & DATABASE02 - Cluster is WMCLUSTER)
>
> When I try to connect to the linked server in Enterprise Manager I get
> the following error messages.
> Error 7399 OLE DB provider 'MSDAORA' reported an error. Authentication
> failed.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
>
> From within Query analyzer I get a slightly different message
> reporting that the username/password are incorrect.
> dbcc traceon(7399)
> select * from TURLIVE..SONICA.INV_LOC
>
> Server: Msg 7399, Level 16, State 1, Line 3
> OLE DB provider 'MSDAORA' reported an error. Authentication failed.
> [OLE/DB provider returned message: ORA-01017: invalid
> username/password; logon denied
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> IDBInitialize::Initialize returned 0x80040e4d: Authentication
> failed.].
> I know the username/password combination is correct and I can use
> these from with Oracle enterprise Manager with sucess.
> TURLIVE is the name I've given the linked server, SONICA is the name
> of the schema on the Oracle database and INV_LOC is a valid table.
> TURLIVE is also the name of the database instance on Server2.
>
> Steps taken so far
> Install Oracle client tools (Enterprise Manager, Net manager etc) on
> Server1.
> Setup an entry in TNSNAMES.ORA to the cluster that has the Oracle
> database. e.g.
> TURLIVE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = WMCLUSTER)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TURLIVE)
> )
> )
> This works fine, I can connect via Oracle Enterprise manager and I can
> TNSPING WMCLUSTER, DATABASE01 & DATABASE02.
> Configured an ODBC source to TURLIVE.
> On Server1 I've configured the linked server using the following SQL.
> sp_addlinkedserver 'TURLIVE', 'Oracle', 'MSDAORA', 'TURLIVE'
> sp_addlinkedsrvlogin 'TURLIVE', false, 'sa', 'sonica','******'
> (password blanked)
> I then rebooted Server1
> The properties of the new linked server are:
> Product name = Oracle
> Data Source = TURLIVE
> Provider String = blank
> I've modifed the registry on Server1 as instructed by a Microsoft KB
> article.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
DTC\MTxOCI\
> OracleXaLib = "oracleclient8.dll"
> OracleSqlLib = "orasql8.dll"
> OracleOciLib = "oci.dll"
>
> Still no luck. Can anyone please point out he bleeding obvious? :-)
> Thanks in advance
>
> As an aside, has anyone ever configured a linked server to an Oracle
> Rdb (Previously DEC Rdb running on OpenVMS and DEC UNIX) database?
> Are there any HOWTO guides for this type of connectivity?
>
>
> Cheers
> Dave.
>
>
>
>
>
>
>|||Thats the one, all works fine now. Thanks very much
Dave.
On Wed, 3 Nov 2004 00:45:23 +0100, "Cirrosi"
<CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote:
>Are you sure you are connecting to sql server with sa username? (check in
>enterprise manager registration properties if you are using sa or windows
>autentication)
>try to use
>sp_addlinkedsrvlogin 'TURLIVE', false, null, 'sonica','******'
>Excuse me for my bad english.
>
>"David Gray" <police@.spamcop.net> ha scritto nel messaggio
> news:0n5fo0pldddgt35repsc11d2s5jdp1rfe0@.
4ax.com...
>|||Hi David,
Make sure you are using the login/password. Since error says it is a
"Anthentication error". And also make sure you are using the right
Provider.
-- Use MS driver to Oracle
Subscribe to:
Posts (Atom)