Showing posts with label background. Show all posts
Showing posts with label background. Show all posts

Friday, March 30, 2012

Linked Server Problems

All
Background: I have three SQL servers, SRV1, SRV2, and SRV3. SRV1 is running
SQL Server 2000, and SRV2 and SRV3 are running SQL Server 2005.
I have SRV3 set up as a linked server on SRV1. I have also set up SRV3 as a
linked server on SRV2. When I run a query against SRV3 on SRV1, I never have
any problems. When I try the same query on SRV2, it fails every time, but
inconsistently. Sometimes it will simply not return any records, with no
error messages. Sometimes it will return a few records (differing amounts
each time), but also supply the following error message:
OLE DB provider "SQLNCLI" for linked server "SRV3" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Sometimes there will be different error messages (sorry, I don't have them
to hand). I've googled the error messages, but not found anything specific
enough ot start me off on a strategy to address this issue.
Any assistance is very much appreciated.
--
Regards
Chris MarshHi Chris,
Per my understanding, you created two linked servers: LINK1: SRV1->SRV3 and
LINK2: SRV2->SRV3. SRV1 is running SQL Server 2000, and SRV2 and SRV3 are
running SQL Server 2005. The problem is that LINK1 always worked fine
while LINK2 always failed with inconsistent errors.
If I have misunderstood, please let me know.
For further research, I would like to check with you the following:
1. What are the differences between SRV1 and SRV2 on communication protocol
and SQL Server service startup account?
2. Has SQL Server 2005 SP1 been installed on SRV2 and SRV3?
You can first check the Communication Protocol via SQL Server Configuration
Manager on SRV3; if it supports TCP/IP and Named Pipes, please try
configuring the communication protocol as TCP/IP on LINK2. If TCP/IP
protocol is not enabled on SRV3, please enable it and set the TCP port
under IP Address tab. I recommend that you just set it under the IPAll and
set the Enable option of IP1 and IP2 to No; then restart your SQL Server.
1. On SRV3, run "netstat -nab >C:\portstat.log" from command prompt to see
which TCP port is being listened by your SQL Server;
2. Asssuming that it is listening on the TCP port 1433, run the command
"telnet SRV3 1433" from SRV2 to see if SRV2 can telnet to the SRV3:1433; if
the telnet command fails, please check if Firewall is enabled on SRV3 by
running firewall.cpl from command prompt and add the TCP port to the
Exception list;
3. If you can telnet to SRV3:1433 from SRV2, please run cliconfg.exe from
command prompt on SRV2; switch to the Alias tab, click Add button, select
TCP/IP, input "SRV3" to the server name field, cancel the selection of
"Dynamically determin port" and input your TCP Port number to the Port
number field, and then input an alias to the Server alias field. The alias
name can be customized but I recommended that you use your SQL Server
instance full name for easy memory.
You can check your SQL Server service startup account from Service list,
right click the SQL Server service or MSSQLSERVER service, click
Properties, and switch to the Log On tab. I recommend that you use a domain
account as your SQL Server service startup account.
If your SQL Server 2005 SP1 has not been installed on SRV2 and SRV3, I
recommend that you install it before further research. You can download it
from:
http://www.microsoft.com/sql/sp1.mspx
Also, I recommend that you check if Data Access option was enabled on your
linked server. On SRV2, expand the Server Objects from SQL Server
Management Studio, expand Linked Servers, right click your linked server,
click Properties, select Server Options, and set the Data Acess option to
True.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Chris,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

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 "symin" 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 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:
>

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