2

My boss sent to request me that: I want to a SQL query, that lists all domain users of a specific group. I began to search query. I found several solutions using Linked Server.

EXEC master.dbo.sp_addlinkedserver 
@server = N'ADSI', 
@srvproduct=N'Active Directory Service Interfaces', 
@provider=N'ADSDSOObject', 
@datasrc=N'adsdatasource'

then

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'ADSI',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'DOMAIN\USER',
@rmtpassword='*********'

I replaced username and password with a domain admin user's information but I got this error

Cannot create an instance of OLE DB provider "ADsDSOObject" for linked server "ADSI".

Our domain name is akilus.local, but I didn't replace the data source.

What could the problem be?

1

I would suggest using xp_logininfo before I tried anything else more drastic. You can find more information from BOL on it.

As long as the permissions are there for the SQL Server service (being a member server in the domain) you should be able to execute the following command:

EXEC xp_logininfo 'akilus\GroupName', 'all'

If that did not work I would simply opt for doing this in PowerShell as there is much more control, and less things to configure to get it setup.

  • I added domain group to SQL Server logins then I executed query. The Users list is retrieved.Thanks – sabandurna Feb 16 '15 at 15:17
  • Now if you did that just to get the list results, that is not the purpose of using this method. If you are being asked to just pull in the user list of any random AD group use PowerShell. – Shawn Melton Feb 16 '15 at 15:43
5

Why is SQL Server required to do this task? This isn't the objective of an RDBMS. You would have to go through hoops to get SQL Server to find this data.

The quickest/easiest/most natural way to do this? The Active Directory PowerShell module. It's a one-liner:

Get-ADGroupMember "administrators" | Select-Object Name, DistinguishedName, ObjectClass

In short, keep logic like this away from SQL Server and put it in the OS where it belongs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.