Querying Active Directory from SQL Server 2005

Querying Active Directory from SQL Server 2005

Pretty general question but here are some pointers.

You need a linked server creating on the SQL Server that points to ADSI (Active Directory Service Interface) something like this will do it.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

Then you can use the following sort of query.

SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass = ''user''')

You'll need to set the LDAP:// line appropriately (ask your AD admin for the details) and be aware that distributed adhoc queries using OpenQuery are disabled by default in SQL Server. Once you have the above though it should be pretty easy to google for any particular variations.

Active Directory Groups With SQL Server 2005

Are you aware of the fact that you cannot query more objects than the AD server is willing to return in one reply?

The ADSI SQL provider does not support paging through the results. The AD server is usually configured to return the first 1000 results only.

If you query for virtually all AD objects at once you are very likely to hit that limit.

Can you clarify what you are trying to achieve?

Accessing Active Directory Role Membership through LDAP using SQL Server 2005

Looks like this is a limitation that cannot be directly overcome: - TSQL: How to get a list of groups that a user belongs to in Active Directory. OpenQuery cannot handle multi-valued attributes.

I ended up writing a .NET CLR job to handle this.

Is it possible to query the Active Directory from Azure SQL

Azure SQL Database only allows to query another Azure SQL Database (remote) or Azure Synapse Analytics databases using elastic queries. All other SQL and non-SQL sources are not allowed.

Azure Managed Instance uses a private VNET and supports linked servers to a limited number of targets. Supported targets: SQL Server and SQL Database. Not supported targets: Active Directory, files, Excel, Oracle, MySQL, Analysis Services, and other RDBMS.

Azure SQL Database would have no way to communicate with your on-premises servers since it does not uses private VNETs as Managed Instance does.

If you rely heavily on this you should discard all Azure SQL (PaaS) options and go for a SQL Server Azure VM.

Getting description field from Active Directory in SQL Server 2005 (LDAP)

I guess your problem is the fact that AD can store multiple values in a single field (quite contrary to the very first normalization rule in relational databases).

Not sure what your query looks like - but could you possibly try to add a "[0]" indexer to the description attribute, by any chance??

Marc

SQL Server 2005 query results differ for different AD accounts using same SQL login

The issue I had was a result of Microsoft Dynamics CRM, not SQL Server itself. I was using Filtered views, which return zero results to any user not using Windows authentication. I am not sure how I got the results I mentioned above, but trying again another day, I was never able to get results with SQL authentication, no matter what Windows account I was using. Likewise, I was always able to get results when logged in with Windows authentication.

How can I query an which Active Directory account is associated with a SQL Server user?

You can check that Windows groups you have defined on your system as login;

SELECT *
FROM sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'

This works on SQL Server 2005 and newer only.

But you won't get the actual AD group name - only the "SID" for that group ....

The whole security system was very different on SQL Server 2000 - I don't think there's a 1:1 equivalent query for that old dinosaur :-) The best I can think of would be:

SELECT *
FROM master.dbo.sysxlogins
WHERE password IS NULL 
  AND name IS NOT null

But unfortunately, there's no way I would be aware of to separate between Windows users and Windows security groups here....

query Active Directory for email address from SQL Server 2008 R2?

It looks like a permissions error. How have you defined the authentication settings for the linked server arrangement? Run it as a domain admin account and see if it works.

---

Related Topics