Question :
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'DOMAINUSER',
@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?
Answer :
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 'akilusGroupName', '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.
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.