Linking or importing Active Directory objects to SQL Server tables

We're trying to figure out whether it's possible to link AD objects to SQL tables. For example, if we have a table of Users and a table of Companies, we'd like to enter the users into Active Directory, create the Companies as either OU's or Security Groups, then have those entities magically appear in the corresponding SQL Server tables. Is that possible? Thanks in advance.

There is a OLE DB Provider for MIcrosoft Directory Services http://msdn2.microsoft.com/en-us/library/aa172728(SQL.80).aspx.

Would that work for you?

Kar

sql

Linking Active Directory to SQL Server 2000

Hi,

everyone I'm trying to link an Active Directory to the SQL Server.
Unluckily I came across some problems and the information I found in the archive of this
forum didn't help me.
I got the link basically running by following code:

EXEC sp_addlinkedserver 'TEST', 'Active Directory Services 2.5', 'ADSDSOObject', 'LDAP://testserver.test.de'

select * from openquery(TEST, '<LDAP://testserver.test.de>;(&(objectClass=Person)(sn=Smith));adspath;subtree')

Unluckily I'm not able to query any other attributes then adspath. Trying the following code results in an error:

select * from openquery(TEST, '<LDAP://testserver.test.de>;(&(objectClass=Person)(sn=Smith));sn;subtree')

I receive the following error message:

OLE DB-Fehlertrace [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB-Fehlertrace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=sn STATUS=DBSTATUS_E_CANTCONVERTVALUE]].

The administrator of the Active directory service told me that my queries have successfully been executed and
correct values have been returned.

Does anyone have an idea what might cause my problems?

ThanksRefer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410) about performing query using ADSI.|||Thanks, but I know this article already. I thought that maybe the chapter about limitations might be of interest for me especially the point about multivalued properties.
However I cross checked all limitations with the admin. of my Active Directory Service. The second query which I posted as not running only returns one value. Also I checked the query with a Delphi application of my colleague and the application returned correctly a string value of the surname. Nevertheless my SQL Server always tells me that it cannot convert a value.|||What was the error during the conversion?|||Well I'm not doing an explicit conversion. The statement which causes the error message which indicates me that there is something wrong with a conversion is:

select * from openquery(TEST, '<LDAP://testserver.test.de>;(&(objectClass=Person)(sn=Smith));sn;subtree')

and the error message is:

Server: Nachr.-Nr. 7346, Schweregrad 16, Status 2, Zeile 1
Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=sn STATUS=DBSTATUS_E_CANTCONVERTVALUE]].

Unluckily I also don't know of any tool with which I might be able to monitor the OLE DB provider to see what values and type of values are transfered.

Is there any way to do explicit conversions of data types within the LDAP syntax of the OPENQUERY statement?

servers Fail

I have a Windows 2000 active/passive cluster. I have configured 6 linked servers using OpenLink Database Proxy Client.

I have had to repeatedly cycle the cluster (stop/restart SQL services) because the linked servers "fail". By failing, I mean that the linked servers stop responding to user queries. The user query simply spins with no activity.

The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
2. I configure SQL Server linked server to point to the DSN

Each node in the cluster has identical DSN entries (same name, same driver, same settings). We have tested failing the cluster from Node 1 to Node 2 and back again. We have tested the failover process with queries actively running (they fail). We are always able to reconnect and resume normal processing after the backup node recovers the SQL server.

The linked server failures don't appear to have any real pattern to them. There seems to be a weak correlation between a failover and failure in the linked servers (the linked servers fail 1 - 2 days later).

This is not making any sense to me at all. I am NOT a fan of linked servers, but I have no choice here: I inherited these and the developers are NOT interested in alternative solutions.

If you have any thoughts on the matter, please drop a line.

Regards,

hmscott

Additonal info:
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1Contacted MS PSS for answer. Did a stack dump and traced error to a modal messagebox that the client provider raised (!!!).

Back to the provider.

Was not related to cluster server, but it sure "felt" that way. Learned some great things about stack dumps and such.

Regards,

hmscott

servers Fail

I have a Windows 2000 active/passive cluster. I have
configured 6 linked servers using OpenLink Database Proxy
Client. See below for steps I took to create linked
server entries.
In two months of testing, I have had to cycle the SQL
cluster (stop/restart SQL services) three or four times
because the linked servers "fail". By failing, I mean that
the linked servers stop responding to user queries. The
user query simply spins with no activity. I have isolated
the failure to the interaction between SQL and the DSN
entry (ie, the DB proxy works, the DSN works, but SELECT *
FROM OPENQUERY(MyLink, 'SELECT * FROM db2Table.db2Column')
does not work).
The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
(www.openlinksw.com)
2. I configure SQL Server linked server to point to the DSN
Each node in the cluster has identical DSN entries (same
name, same driver, same settings). We have tested failing
the cluster from Node 1 to Node 2 and back again. We have
tested the failover process with queries actively running
(they fail). We are always able to reconnect and resume
normal processing after the backup node recovers the SQL
server.
The linked server failures don't appear to have any real
pattern to them. There seems to be a weak correlation
between a failover and failure in the linked servers (the
linked servers fail 1 - 2 days later).
This is not making any sense to me at all. I am NOT a fan
of linked servers, but I have no choice here: I inherited
these and the developers are NOT interested in alternative
solutions.
If you have any thoughts on the matter, please drop a line.
Regards,
hmscott
Additonal info:
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1Contact MS PSS for answer. Did a stack dump and traced
error to a modal messagebox that the client provider
raised (!!!).
Back to the provider.
Regards,
hmscott
>--Original Message--
>I have a Windows 2000 active/passive cluster. I have
>configured 6 linked servers using OpenLink Database Proxy
>Client. See below for steps I took to create linked
>server entries.
>In two months of testing, I have had to cycle the SQL
>cluster (stop/restart SQL services) three or four times
>because the linked servers "fail". By failing, I mean
that
>the linked servers stop responding to user queries. The
>user query simply spins with no activity. I have
isolated
>the failure to the interaction between SQL and the DSN
>entry (ie, the DB proxy works, the DSN works, but SELECT
*
>FROM OPENQUERY(MyLink, 'SELECT * FROM
db2Table.db2Column')
>does not work).
>The linked servers are configured in the following manner:
>1. I configure a DSN using the OpenLink client
>(www.openlinksw.com)
>2. I configure SQL Server linked server to point to the
DSN
>Each node in the cluster has identical DSN entries (same
>name, same driver, same settings). We have tested failing
>the cluster from Node 1 to Node 2 and back again. We have
>tested the failover process with queries actively running
>(they fail). We are always able to reconnect and resume
>normal processing after the backup node recovers the SQL
>server.
>The linked server failures don't appear to have any real
>pattern to them. There seems to be a weak correlation
>between a failover and failure in the linked servers (the
>linked servers fail 1 - 2 days later).
>This is not making any sense to me at all. I am NOT a fan
>of linked servers, but I have no choice here: I inherited
>these and the developers are NOT interested in
alternative
>solutions.
>If you have any thoughts on the matter, please drop a
line.
>Regards,
>hmscott
>Additonal info:
>Windows 2000 SP4
>SQL 2000 SP3 (hotfix 0818)
>MDAC 2.7 SP1
>OpenLink MultiTier 5.1
>.
>

servers and Cluster

I am having an issue using linked servers on
active/active cluster. I have two instances, and when
both instances are on the same machine, things work as
expected. When I move one of the instances to the other
machine, it does not. It appears to work correctly when I
link the named instance to the default instance. I am
having issues linking the default instance to the named
instance.
What is the text of the query you are issuing? What error is returned from
the Linked Server query? What are the SQL Server and operating system
versions?
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.