Sql Linked Server: domain

Showing posts with label domain. 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 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...
>
>

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

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

Wednesday, March 28, 2012

Linked server problem

Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:

> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>

Linked server problem

Hi,
I have two sql servers A and B linked together as LINKED SERVERS. A is
within the domain and B is outside the domain. They were working perfectly
fine a week back but now I can access A from B but I can't access B from A,
although B comes up under the Linked Servers list on A. What can be wrong?
Thanks in advance.
Well
Something has changed since last week. Perhaps try to re-create a linked
server on server A to server B
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
> Hi,
> I have two sql servers A and B linked together as LINKED SERVERS. A is
> within the domain and B is outside the domain. They were working perfectly
> fine a week back but now I can access A from B but I can't access B from
> A,
> although B comes up under the Linked Servers list on A. What can be wrong?
> Thanks in advance.
|||I have already done that. It does come up on the list of linked servers but
when I click on the tables icon it says "Server Not Accessible".
"Uri Dimant" wrote:

> Well
> Something has changed since last week. Perhaps try to re-create a linked
> server on server A to server B
>
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:BA9289B4-6028-41CA-88B2-E7968DD725AC@.microsoft.com...
>
>
sql

Monday, March 26, 2012

Linked server on a different domain

Hello,
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> >
> > I use SQL authentication, and both are working on TCPIP port 1433.
> >
> > It doesn't work, it says "Access denied or not existant Sql"...but the
> > credentials are right.
> >
> > I'm connected through a VPN channel (CISCO client vpn connector) with
> > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > connected to 2005 and it works fine.
> >
> > What it might be? The port is fixed. Do I need to open something else?
> >
> > Thanx,
> > Bige
> >|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
> > Have you tried pass through authentication? This is where you have two NT
> > accounts with the same account names and passwords in both domains? If you
> > use the security context of the one account it should work against the
> > server in the other domain.
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Bige" <ivan.pololi@.interah.com> wrote in message
> > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > >
> > > I use SQL authentication, and both are working on TCPIP port 1433.
> > >
> > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > credentials are right.
> > >
> > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > connected to 2005 and it works fine.
> > >
> > > What it might be? The port is fixed. Do I need to open something else?
> > >
> > > Thanx,
> > > Bige
> > >
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
> > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> >
> > I don't want to use NT accounts, I need to use SQL authentication.
> >
> > About the ports are the same and both use protocol TCPIP...if I
> > connect a SQL 2000 to 2005
> > in the same domain with my SQL credentials it works.
> >
> > The problem is when I try to connect from another network in VPN
> > mode...
> >
> > Thanks for your help, I appreciate it.
> > Bige
> >
> > Hilary Cotter ha scritto:
> >
> > > Have you tried pass through authentication? This is where you have two NT
> > > accounts with the same account names and passwords in both domains? If you
> > > use the security context of the one account it should work against the
> > > server in the other domain.
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > Hello,
> > > >
> > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > >
> > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > >
> > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > credentials are right.
> > > >
> > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > connected to 2005 and it works fine.
> > > >
> > > > What it might be? The port is fixed. Do I need to open something else?
> > > >
> > > > Thanx,
> > > > Bige
> > > >
> >
> >|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
> > Hi
> >
> > Connecting with a SQL connection should not matter about the servers being
> > on different domains. Can you connect with Query Analyser, if you can, then
> > it would tend to imply that it was configuration of the linked server is the
> > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > check out http://support.microsoft.com/kb/287932 and
> > http://support.microsoft.com/kb/306199/
> >
> > With a VPN there may be delays introduced in the connection, so increasing
> > timeouts may be necessary, also check that you can access the server via an
> > IP address, if this works there may be a DNS issue.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > >
> > > I don't want to use NT accounts, I need to use SQL authentication.
> > >
> > > About the ports are the same and both use protocol TCPIP...if I
> > > connect a SQL 2000 to 2005
> > > in the same domain with my SQL credentials it works.
> > >
> > > The problem is when I try to connect from another network in VPN
> > > mode...
> > >
> > > Thanks for your help, I appreciate it.
> > > Bige
> > >
> > > Hilary Cotter ha scritto:
> > >
> > > > Have you tried pass through authentication? This is where you have two NT
> > > > accounts with the same account names and passwords in both domains? If you
> > > > use the security context of the one account it should work against the
> > > > server in the other domain.
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Director of Text Mining and Database Strategy
> > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > >
> > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > positions, strategies or opinions.
> > > >
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > http://www.indexserverfaq.com
> > > >
> > > >
> > > >
> > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > Hello,
> > > > >
> > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > >
> > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > >
> > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > credentials are right.
> > > > >
> > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > connected to 2005 and it works fine.
> > > > >
> > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > >
> > > > > Thanx,
> > > > > Bige
> > > > >
> > >
> > >
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
> > Yes, I can connect using Query Analyser.
> >
> > I created the linked server using EM; here it returns "SQL server not
> > existant" when I try to list tables or views...
> >
> > I tried to change the timeout but didn't worked...and it is not a DNS
> > problem
> >
> > About the microsoft kbs were not really helpful...any clues?
> >
> > Thanx,
> > Bige
> >
> > John Bell ha scritto:
> >
> > > Hi
> > >
> > > Connecting with a SQL connection should not matter about the servers being
> > > on different domains. Can you connect with Query Analyser, if you can, then
> > > it would tend to imply that it was configuration of the linked server is the
> > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > check out http://support.microsoft.com/kb/287932 and
> > > http://support.microsoft.com/kb/306199/
> > >
> > > With a VPN there may be delays introduced in the connection, so increasing
> > > timeouts may be necessary, also check that you can access the server via an
> > > IP address, if this works there may be a DNS issue.
> > >
> > > John
> > >
> > > "Bige" wrote:
> > >
> > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > >
> > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > >
> > > > About the ports are the same and both use protocol TCPIP...if I
> > > > connect a SQL 2000 to 2005
> > > > in the same domain with my SQL credentials it works.
> > > >
> > > > The problem is when I try to connect from another network in VPN
> > > > mode...
> > > >
> > > > Thanks for your help, I appreciate it.
> > > > Bige
> > > >
> > > > Hilary Cotter ha scritto:
> > > >
> > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > accounts with the same account names and passwords in both domains? If you
> > > > > use the security context of the one account it should work against the
> > > > > server in the other domain.
> > > > >
> > > > > --
> > > > > Hilary Cotter
> > > > > Director of Text Mining and Database Strategy
> > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > >
> > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > positions, strategies or opinions.
> > > > >
> > > > > Looking for a SQL Server replication book?
> > > > > http://www.nwsu.com/0974973602.html
> > > > >
> > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > http://www.indexserverfaq.com
> > > > >
> > > > >
> > > > >
> > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > >
> > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > >
> > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > credentials are right.
> > > > > >
> > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > connected to 2005 and it works fine.
> > > > > >
> > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > >
> > > > > > Thanx,
> > > > > > Bige
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
> > Hi
> >
> > That implies it is the configuration of the linked server that may not be
> > working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> > Analyser and if they don't give an error see if you try run a query using 4
> > part naming.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > Yes, I can connect using Query Analyser.
> > >
> > > I created the linked server using EM; here it returns "SQL server not
> > > existant" when I try to list tables or views...
> > >
> > > I tried to change the timeout but didn't worked...and it is not a DNS
> > > problem
> > >
> > > About the microsoft kbs were not really helpful...any clues?
> > >
> > > Thanx,
> > > Bige
> > >
> > > John Bell ha scritto:
> > >
> > > > Hi
> > > >
> > > > Connecting with a SQL connection should not matter about the servers being
> > > > on different domains. Can you connect with Query Analyser, if you can, then
> > > > it would tend to imply that it was configuration of the linked server is the
> > > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > > check out http://support.microsoft.com/kb/287932 and
> > > > http://support.microsoft.com/kb/306199/
> > > >
> > > > With a VPN there may be delays introduced in the connection, so increasing
> > > > timeouts may be necessary, also check that you can access the server via an
> > > > IP address, if this works there may be a DNS issue.
> > > >
> > > > John
> > > >
> > > > "Bige" wrote:
> > > >
> > > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > > >
> > > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > > >
> > > > > About the ports are the same and both use protocol TCPIP...if I
> > > > > connect a SQL 2000 to 2005
> > > > > in the same domain with my SQL credentials it works.
> > > > >
> > > > > The problem is when I try to connect from another network in VPN
> > > > > mode...
> > > > >
> > > > > Thanks for your help, I appreciate it.
> > > > > Bige
> > > > >
> > > > > Hilary Cotter ha scritto:
> > > > >
> > > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > > accounts with the same account names and passwords in both domains? If you
> > > > > > use the security context of the one account it should work against the
> > > > > > server in the other domain.
> > > > > >
> > > > > > --
> > > > > > Hilary Cotter
> > > > > > Director of Text Mining and Database Strategy
> > > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > > >
> > > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > > positions, strategies or opinions.
> > > > > >
> > > > > > Looking for a SQL Server replication book?
> > > > > > http://www.nwsu.com/0974973602.html
> > > > > >
> > > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > > http://www.indexserverfaq.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > > >
> > > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > > >
> > > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > > credentials are right.
> > > > > > >
> > > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > > connected to 2005 and it works fine.
> > > > > > >
> > > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > > >
> > > > > > > Thanx,
> > > > > > > Bige
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Linked server on a different domain

Friday, March 9, 2012

Linked server and Windows domain user.

I logon to SQL Server using windows user and I created a linked server to
another SQL Server in the same domain (My windows user can logon both server
with db_owner role). I tried to use "Be made using the login's current
security context"
However, I cannot connect to remote server
Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I tried other options too either got the same error or the mapping windows
user says "User Domain\user cannot login". The only way I can access is to
create a log SQL Server account and mapping to it.
I also tried openrowset:
SELECT a.*
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
and I got the following error:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server D
river][SQL
Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
Did I miss any steps to make remote linked server?The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000 and above, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation (2000)
or in the topic Configuring Linked Servers for
Delegation(2005)
-Sue
On Mon, 23 Jan 2006 11:46:03 -0800, "nick"
<nick@.discussions.microsoft.com> wrote:

>I logon to SQL Server using windows user and I created a linked server to
>another SQL Server in the same domain (My windows user can logon both serve
r
>with db_owner role). I tried to use "Be made using the login's current
>security context"
>However, I cannot connect to remote server
>Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>I tried other options too either got the same error or the mapping windows
>user says "User Domain\user cannot login". The only way I can access is to
>create a log SQL Server account and mapping to it.
>I also tried openrowset:
>SELECT a.*
>FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
> pubs.dbo.authors) AS a
>ORDER BY a.au_lname, a.au_fname
>and I got the following error:
>Server: Msg 7303, Level 16, State 2, Line 1
>Could not initialize data source object of OLE DB provider 'MSDASQL'.
>[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver][SQL
>Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
>
>Did I miss any steps to make remote linked server?

Linked server and delegation

Hi to everybody,
I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
SQL2.
Both SQL Servers:
- are hosted on DC.
- use Windows authentication only.
- run under a domain user account, SQL1User and SQL2User.
Both SQL server domain user accounts:
- are "trusted for delegation" in AD.
- have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
User Admin1 can connect successfully to both SQL Servers.
User Admin1 have not "user cannot be delegated" in AD.
Now I setup a linked server from SQL1 to SQL2.
Linked server security is set to "be made using the login's current security
context"
Trying to access the linked server cause an error 18456 "Login failed for
user NT AUTHORITY/ANONIMOUS ACCESS" error.
What am I missing'
Seems Kerberos delegation does not work and fall back to NTLM which does not
support delegation, but AFAIK my Kerberos delegation setup should be fine.
Thanks,
CorradoYou need to specify the port probably. Sounds like you've pretty much got
it. I have copied and pasted my stock response below and apart from the port
looks like you are ok. Although I suggest it in my stock reply, I probably
wouldn't enable kerberos logging on a DC unless this is a test environment
(it needs a reboot for a start)
It can be a serious PITA and yes the various docs are confusing :-)
First of all I wouldn't use setspn, I tend to use ADSI Edit
It's in the 2k support tools on any 2k server CD. However if you
want to use setspn then the syntax is :
setspn -A MSSQLSvc/SQLNLB02.DOMSQL.COM:1433 NLBSQL02Svc
This is for a server called SQLNLB02 in the domain DOMSQL.COM
with a SQL Service account of DOMSQL\NLBSQL02Svc
You must also be able from the client to resolve the FQDN of the servers
involved using ping -a servername i.e. it must return
Pinging SQLNLB02.DOMSQL.COM [xxx.xxx.xxx.xxx]
and not
Pinging SQLNLB02 [xxx.xxx.xxx.xxx]
Regardless of what anything else says, you just need to set up SPN's
for the service accounts of the two SQL Servers involved. e.g.
I have 2 servers and 2 service accounts as below
Server1 : SQLNLB01
ServiceAccount : NLBSQL01Svc
Server2 : SQLNLB02
ServiceAccount : NLBSQL02Svc
Using ADSI Edit right click on the NLBSQL01Svc in the Users
container and choose Properties.In the select a property to view
listbox choose servicePrincipalName and then add a SPN like so
MSSQLSvc/SQLNLB01.DOMSQL.COM:1433
(where the FQDN of the server is the server that uses the account
I'm editing as it's SQL Service account). Do the same for the second
server and you should be up and running.
What I find really useful is enabling Kerberos logging on all the
computers involved. This will write to the event log and you'll be
able to see exactly why it's failing. .
To enable Kerberos logging look at
http://support.microsoft.com/defaul...kb;en-us;262177
If you've got AD set up then it's generally a malformed SPN or
poor name resolution (make sure you can ping -a the server IP
addresses and get back a FQDN and not just a server name)
This article also has some good stuff about Kerberos and SSPI
http://support.microsoft.com/defaul...kb;en-us;811889
and this one lists some of the kerberos errors you might see
http://support.microsoft.com/defaul...kb;EN-US;230476
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:%23Qwx%236k5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

> Hi to everybody,
> I've a Windows 2000 native mode domain, with 2 SQL Server 2000, SQL1 and
> SQL2.
> Both SQL Servers:
> - are hosted on DC.
> - use Windows authentication only.
> - run under a domain user account, SQL1User and SQL2User.
> Both SQL server domain user accounts:
> - are "trusted for delegation" in AD.
> - have a SPN (i.e. setspn -A MSSQLSvc/SQL1.mydomain.com SQL1User)
> User Admin1 can connect successfully to both SQL Servers.
> User Admin1 have not "user cannot be delegated" in AD.
> Now I setup a linked server from SQL1 to SQL2.
> Linked server security is set to "be made using the login's current

security

quote:

> context"
> Trying to access the linked server cause an error 18456 "Login failed for
> user NT AUTHORITY/ANONIMOUS ACCESS" error.
> What am I missing'
> Seems Kerberos delegation does not work and fall back to NTLM which does

not

quote:

> support delegation, but AFAIK my Kerberos delegation setup should be fine.
> Thanks,
> Corrado
>

Linked Server and ADSI

Does anyone one know how to create a linked server to active directory
on a sql server that is not a domain controller?
I have tried the following to add the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
but I can not issue any querys without getting the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
This is running on a windows 2000 sql 2000 box.
Thanks in advance.
That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,
exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query.
Cheers,
Mike
"sfibich" <sfibich@.pfgc.com> wrote in message
news:u2w7zDOzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on
> a sql server that is not a domain controller?
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
> but I can not issue any querys without getting the following error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e14].
>
> This is running on a windows 2000 sql 2000 box.
> Thanks in advance.
|||Mike Hodgson wrote:
> That's fine but you'll need some remote login credentials with which to
> query AD (otherwise what security context is it going to use?). I've set up
> links to AD from my SQL servers by adding the linked server (as you've done)
> and then adding a remote login mapping so that all local logins use a
> specific set of credentials to connect via ADSI (I'm not overly concerned
> about oppressive security in this case because any user that connects to my
> SQL box can also query AD anyway - this just makes multi-source queries a
> little more transparent/seamless). Specifically I've set up the mapping so
> that all local logins use the service account under which SQL is running
> (ie. Windows account). That is,
> exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> So any SQL client session that issues an ADSI query through that linked
> server will query AD using the service account that SQL server is running
> under (this assumes you're running your SQL server under a domain account,
> not as System or a local SAM account). Works pretty well for me - the only
> thing you have to bear in mind is you can only query the bits of AD that the
> SQL service account has been granted permissions to query.
>
Thanks that does it.
|||I'm hoping you are still checking this newsgroup.
I'm experiencing the same problem and I am using a domain account to start
the services as well as in the security context of the linked server but I
still am having problems executing a query.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
It is a Windows 2000 Server OS, SQL Server 2000. The AD is 2003 Server. Any
ideas?
"sfibich" wrote:

> Mike Hodgson wrote:
> Thanks that does it.
>

Linked Server and ADSI

Does anyone one know how to create a linked server to active directory
on a sql server that is not a domain controller?
I have tried the following to add the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
but I can not issue any querys without getting the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
This is running on a windows 2000 sql 2000 box.
Thanks in advance.That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,
exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query.
--
Cheers,
Mike
"sfibich" <sfibich@.pfgc.com> wrote in message
news:u2w7zDOzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on
> a sql server that is not a domain controller?
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
> but I can not issue any querys without getting the following error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e14].
>
> This is running on a windows 2000 sql 2000 box.
> Thanks in advance.|||Mike Hodgson wrote:
> That's fine but you'll need some remote login credentials with which to
> query AD (otherwise what security context is it going to use?). I've set up
> links to AD from my SQL servers by adding the linked server (as you've done)
> and then adding a remote login mapping so that all local logins use a
> specific set of credentials to connect via ADSI (I'm not overly concerned
> about oppressive security in this case because any user that connects to my
> SQL box can also query AD anyway - this just makes multi-source queries a
> little more transparent/seamless). Specifically I've set up the mapping so
> that all local logins use the service account under which SQL is running
> (ie. Windows account). That is,
> exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> So any SQL client session that issues an ADSI query through that linked
> server will query AD using the service account that SQL server is running
> under (this assumes you're running your SQL server under a domain account,
> not as System or a local SAM account). Works pretty well for me - the only
> thing you have to bear in mind is you can only query the bits of AD that the
> SQL service account has been granted permissions to query.
>
Thanks that does it.|||I'm hoping you are still checking this newsgroup.
I'm experiencing the same problem and I am using a domain account to start
the services as well as in the security context of the linked server but I
still am having problems executing a query.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
It is a Windows 2000 Server OS, SQL Server 2000. The AD is 2003 Server. Any
ideas?
"sfibich" wrote:
> Mike Hodgson wrote:
> > That's fine but you'll need some remote login credentials with which to
> > query AD (otherwise what security context is it going to use?). I've set up
> > links to AD from my SQL servers by adding the linked server (as you've done)
> > and then adding a remote login mapping so that all local logins use a
> > specific set of credentials to connect via ADSI (I'm not overly concerned
> > about oppressive security in this case because any user that connects to my
> > SQL box can also query AD anyway - this just makes multi-source queries a
> > little more transparent/seamless). Specifically I've set up the mapping so
> > that all local logins use the service account under which SQL is running
> > (ie. Windows account). That is,
> >
> > exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> > 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> >
> > So any SQL client session that issues an ADSI query through that linked
> > server will query AD using the service account that SQL server is running
> > under (this assumes you're running your SQL server under a domain account,
> > not as System or a local SAM account). Works pretty well for me - the only
> > thing you have to bear in mind is you can only query the bits of AD that the
> > SQL service account has been granted permissions to query.
> >
> Thanks that does it.
>

Linked Server and ADSI

Does anyone one know how to create a linked server to active directory
on a sql server that is not a domain controller?
I have tried the following to add the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
but I can not issue any querys without getting the following error
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
ICommandPrepare::Prepare returned 0x80040e14].
This is running on a Windows 2000 sql 2000 box.
Thanks in advance.That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,
exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query.
Cheers,
Mike
"sfibich" <sfibich@.pfgc.com> wrote in message
news:u2w7zDOzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on
> a sql server that is not a domain controller?
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
> but I can not issue any querys without getting the following error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e14].
>
> This is running on a Windows 2000 sql 2000 box.
> Thanks in advance.|||Mike Hodgson wrote:
> That's fine but you'll need some remote login credentials with which to
> query AD (otherwise what security context is it going to use?). I've set
up
> links to AD from my SQL servers by adding the linked server (as you've don
e)
> and then adding a remote login mapping so that all local logins use a
> specific set of credentials to connect via ADSI (I'm not overly concerned
> about oppressive security in this case because any user that connects to m
y
> SQL box can also query AD anyway - this just makes multi-source queries a
> little more transparent/seamless). Specifically I've set up the mapping s
o
> that all local logins use the service account under which SQL is running
> (ie. Windows account). That is,
> exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
> 'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'
> So any SQL client session that issues an ADSI query through that linked
> server will query AD using the service account that SQL server is running
> under (this assumes you're running your SQL server under a domain account,
> not as System or a local SAM account). Works pretty well for me - the onl
y
> thing you have to bear in mind is you can only query the bits of AD that t
he
> SQL service account has been granted permissions to query.
>
Thanks that does it.|||I'm hoping you are still checking this newsgroup.
I'm experiencing the same problem and I am using a domain account to start
the services as well as in the security context of the linked server but I
still am having problems executing a query.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
It is a Windows 2000 Server OS, SQL Server 2000. The AD is 2003 Server. Any
ideas?
"sfibich" wrote:

> Mike Hodgson wrote:
> Thanks that does it.
>

Wednesday, March 7, 2012

Linked Server Active Directory (ADSI) Error

Background: MS SQL 2000 SP3 Member Server in Active Directory Domain. Added
Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services
2.5',
'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this security
context' and set it to the domain administrator account. The linked server
object is created successfully howeve:
1. When I click on either the Tables or Views I get this error: ' Error
7301: Could not obtain a required interface from OLE DB Provider
'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
AND
2. When I try to run and OpenQuery in the Query Analyzer I get this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
The query I am running is formatted as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE objectCategory
= "person" AND objectClass = "user"')
Go
Thanks for any and all help.
the error may be caused by syntax errors in your query. Try this:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Richard
"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> Added
> Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> Services
> 2.5',
> 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> security
> context' and set it to the domain administrator account. The linked
> server
> object is created successfully howeve:
> 1. When I click on either the Tables or Views I get this error: ' Error
> 7301: Could not obtain a required interface from OLE DB Provider
> 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> AND
> 2. When I try to run and OpenQuery in the Query Analyzer I get this
> error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare
> returned 0x80040e14].
> The query I am running is formatted as:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> objectCategory
> = "person" AND objectClass = "user"')
> Go
>
> Thanks for any and all help.
>
|||Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:

> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
>
>
|||I do not believe you can use both a named server (the IP, in this case) and
the DC arguements. One or the other should suffice.
Here is a query we run all the time, with or without a linked server.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://DC=CBSH,DC=COM''
WHERE objectClass = ''organizationalPerson''
AND mail = ''*''
AND extensionAttribute3 <> ''9*''
AND extensionAttribute3 > ''1''
AND extensionAttribute3 <> ''***-*''
AND extensionAttribute3 <> ''n*''
'
)
Sincerely,
Anthony Thomas

"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:

> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
>
>
|||I received this error when I tried your suggestion:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'AdsDsoObject'.
Any ideas on what I am missing?
"AnthonyThomas" wrote:

> I do not believe you can use both a named server (the IP, in this case) and
> the DC arguements. One or the other should suffice.
> Here is a query we run all the time, with or without a linked server.
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://DC=CBSH,DC=COM''
> WHERE objectClass = ''organizationalPerson''
> AND mail = ''*''
> AND extensionAttribute3 <> ''9*''
> AND extensionAttribute3 > ''1''
> AND extensionAttribute3 <> ''***-*''
> AND extensionAttribute3 <> ''n*''
> '
> )
>
> Sincerely,
>
> Anthony Thomas
>
> --
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
> Thanks Richard I used the query you provided unfortunately I received the
> same Msg 7621 error I listed below.
> Regards.
> TCALL
> "Richard Ding" wrote:
>
>
|||Have you found a solution to your problem?
I am having similar issues. I try to add a linked server using
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','username','password'
go
but when I try to run a query from QA or view tables from EM i get similar
error messages.
"Sonya" wrote:
[vbcol=seagreen]
> I received this error when I tried your suggestion:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'AdsDsoObject'.
> Any ideas on what I am missing?
> "AnthonyThomas" wrote:

Linked Server Active Directory (ADSI) Error

Background: MS SQL 2000 SP3 Member Server in Active Directory Domain. Added
Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service
s
2.5',
'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this security
context' and set it to the domain administrator account. The linked server
object is created successfully howeve:
1. When I click on either the Tables or Views I get this error: ' Error
7301: Could not obtain a required interface from OLE DB Provider
'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
AND
2. When I try to run and OpenQuery in the Query Analyzer I get this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e14].
The query I am running is formatted as:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE objectCategor
y
= "person" AND objectClass = "user"')
Go
Thanks for any and all help.the error may be caused by syntax errors in your query. Try this:
SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
objectCategory
= ''person'' AND objectClass = ''user''')
Richard
"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
> Background: MS SQL 2000 SP3 Member Server in Active Directory Domain.
> Added
> Linked server with EXEC sp_addlinkedserver 'ADSI', 'Active Directory
> Services
> 2.5',
> 'ADSDSOObject', 'adsdatasource' Set Security to 'Be made using this
> security
> context' and set it to the domain administrator account. The linked
> server
> object is created successfully howeve:
> 1. When I click on either the Tables or Views I get this error: ' Error
> 7301: Could not obtain a required interface from OLE DB Provider
> 'ADSDSOBJECT'. OLE DB error trace[OLE/DB Provider 'ADSDSOBJECT'
> IUnknown::QueryInterface returned .0x80004002:IDBSchemaRowset].
> AND
> 2. When I try to run and OpenQuery in the Query Analyzer I get this
> error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare
> returned 0x80040e14].
> The query I am running is formatted as:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org WHERE
> objectCategory
> = "person" AND objectClass = "user"')
> Go
>
> Thanks for any and all help.
>|||Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:

> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
>
>|||I do not believe you can use both a named server (the IP, in this case) and
the DC arguements. One or the other should suffice.
Here is a query we run all the time, with or without a linked server.
SELECT *
FROM OPENROWSET(
'AdsDsoObject'
,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
,'SELECT mail
,ExtensionAttribute3
,SamAccountName
FROM ''LDAP://DC=CBSH,DC=COM''
WHERE objectClass = ''organizationalPerson''
AND mail = ''*''
AND extensionAttribute3 <> ''9*''
AND extensionAttribute3 > ''1''
AND extensionAttribute3 <> ''***-*''
AND extensionAttribute3 <> ''n*''
'
)
Sincerely,
Anthony Thomas
"TCALL" <TCALL@.discussions.microsoft.com> wrote in message
news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
Thanks Richard I used the query you provided unfortunately I received the
same Msg 7621 error I listed below.
Regards.
TCALL
"Richard Ding" wrote:

> the error may be caused by syntax errors in your query. Try this:
> SELECT * FROM Openquery(ADSI, 'SELECT givenName FROM
> ''LDAP://192.168.0.10/OU=Aurora,DC=sales,DC=company,DC=org'' WHERE
> objectCategory
> = ''person'' AND objectClass = ''user''')
>
> Richard
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:CA08517D-9EB6-49E8-81EB-A14091F46ECF@.microsoft.com...
>
>|||I received this error when I tried your suggestion:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'AdsDsoObject'.
Any ideas on what I am missing?
"AnthonyThomas" wrote:

> I do not believe you can use both a named server (the IP, in this case) an
d
> the DC arguements. One or the other should suffice.
> Here is a query we run all the time, with or without a linked server.
> SELECT *
> FROM OPENROWSET(
> 'AdsDsoObject'
> ,'User ID=;Password=;ADSI Flag=0x11;Page Size=10000'
> ,'SELECT mail
> ,ExtensionAttribute3
> ,SamAccountName
> FROM ''LDAP://DC=CBSH,DC=COM''
> WHERE objectClass = ''organizationalPerson''
> AND mail = ''*''
> AND extensionAttribute3 <> ''9*''
> AND extensionAttribute3 > ''1''
> AND extensionAttribute3 <> ''***-*''
> AND extensionAttribute3 <> ''n*''
> '
> )
>
> Sincerely,
>
> Anthony Thomas
>
> --
> "TCALL" <TCALL@.discussions.microsoft.com> wrote in message
> news:3B83DA10-E3C9-45F8-9F0C-2111F003EA82@.microsoft.com...
> Thanks Richard I used the query you provided unfortunately I received the
> same Msg 7621 error I listed below.
> Regards.
> TCALL
> "Richard Ding" wrote:
>
>
>|||Have you found a solution to your problem?
I am having similar issues. I try to add a linked server using
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
GO
go
sp_addlinkedSrvlogin 'ADSI',false, 'sa','username','password'
go
but when I try to run a query from QA or view tables from EM i get similar
error messages.
"Sonya" wrote:
[vbcol=seagreen]
> I received this error when I tried your suggestion:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'AdsDsoObject'.
> Any ideas on what I am missing?
> "AnthonyThomas" wrote:
>

Friday, March 30, 2012

Linked Server Properties - Security - SQL Server 2000

Linked Server Properties - Security - SQL Server 2000

Linked Server Properties - Security - SQL Server 2000

Wednesday, March 28, 2012

Linked server problem

Linked server problem

Monday, March 26, 2012

Linked server on a different domain

Linked server on a different domain

Linked server on a different domain

Friday, March 9, 2012

Linked server and Windows domain user.

Linked server and delegation

Linked Server and ADSI

Linked Server and ADSI

Linked Server and ADSI

Wednesday, March 7, 2012

Linked Server Active Directory (ADSI) Error

Linked Server Active Directory (ADSI) Error

Sql Linked Server

Blog Archive

About Me