Active Directory and T-SQL

Question

Active Directory and T-SQL

D. van Braak

SSC Veteran

Points: 270
More actions
November 29, 2011 at 2:04 am

#263751

With AD as linked server and usage from openquery i can connect to the AD. Hower, i can’t edit the AD.
What is the best method for edit users and membership in the AD from t-sql?
CLR with usage from C# or VB? Or is it possible to use powershel from t-sql? Or is ther any other method?
Regards,
Dick

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Usman Butt Hall of Fame Points: 3186 More actions · Answer 1

I am not able to understand why you want to manage Active Directory from SQL server? It is not built for that. As far as managing it by using powershell, yes you can. Following is a good link to that

http://technet.microsoft.com/en-us/library/dd378937%28WS.10%29.aspx

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

I'd agree with Usman, SQL wasnt designed to manage AD.

MS provide the MSC console applications to modify AD objects and they work and do the jobs you need.

Powershell can be used to manage AD but not through T-SQL

D. van Braak SSC Veteran Points: 270 More actions · Answer 3

Thanks for your replies.

However, MSC is not made to maintain 5000 users :-). I'd agree that SQL not is designed to manage AD, but there are too many classes for C# or VB for editing the AD-objects. Since SQL2005, i mean, it's possible to use C#or VB classes from SQL.

http://msdn.microsoft.com/en-US/library/ms254498(v=VS.80).aspx

Because of this, i think it's possible to use SQL for accountmanagement.

Regards,

Dick

Usman Butt Hall of Fame Points: 3186 More actions · Answer 4

I cannot understand your point. If you are unable to handle it from MMC (Do not know MSC but do know .msc extensions for different consoles), then why are you planning to do it from SQL Server? Do you know why the WINDOWS AUTHENTICATION is recommended in SQL Server? It is just to secure the environment as the users/ user credentials are hidden from SQL Server and the authentication is done by the Windows. Now you are trying to bring that thing into SQL Server. It is just insane to even think of this. The long list of consequences could be so dreadful that you cannot even imagine.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

D. van Braak (11/30/2011)
Thanks for your replies.
However, MSC is not made to maintain 5000 users :-). I'd agree that SQL not is designed to manage AD, but there are too many classes for C# or VB for editing the AD-objects. Since SQL2005, i mean, it's possible to use C#or VB classes from SQL.
http://msdn.microsoft.com/en-US/library/ms254498(v=VS.80).aspx
Because of this, i think it's possible to use SQL for accountmanagement.
Regards,
Dick

I would have to disagree on that, I have worked in environments with over 15,000 users and around 5,000 computers in one domain and no one ever said that dsa.msc wasnt good enough, it works, and it works a treat

If you want to use the classes then you need to go down the CLR route but again your opening up a whole set of security wholes across your entire domain here and I would strongly recommend against it.

It defies the whole principle of least access nessesary but if you want to do it then we cant stop you, just need to be aware of the consequencies.

D. van Braak SSC Veteran Points: 270 More actions · Answer 6

I think we understand each other. There are many tools which can customize objects in the AD (such as: http://www.tools4ever.com/products/user-management-resource-administrator/).

Why not SQL? All of our users (students and teachers) already exist in SQL. This way you can automate your process around user management, right? We call this 'User Account management'. Authentication remains, of course!, the role of Windows.

You can give from the long list there even 5?

D. van Braak SSC Veteran Points: 270 More actions · Answer 7

Hi Anthony,

Thank you for your constructive answer.

My question remains for me: call for me a lot of securityleaks. In this, my knowledge is not sufficient.

Usman Butt Hall of Fame Points: 3186 More actions · Answer 8

You are missing the point that how would you authenticate to the AD Server? Obviously, with kind of a domain admin user, right? Then what if your SQL Server got hacked? You are making your entire environment vulnerable, just to get some usefulness (seems only just for you) from SQL Server. This is unbelievable.