Validating Active Directory password from within a stored-procedure

[MarnickTelenet]

We want to deploy a ASP.NET web-application that is secured by a login-page.

We don't want to have a seperate password-system. We want to use the existing Active Directory system.

The web-application runs on a IIS-server which is OUTSIDE the domain. So it is not possible to reach Active Directory "directly" from the IIS-server.

The only solution we see is the following:

The web-application connects to a database on a SQL-server. That SQL-server is INSIDE the domain and can reach the Active Directory.

If we can write a stored-procedure on the SQL-server that receives the parameter "user" and the parameter "password" and returns the value "OK" or "NOT OK", we can validate the Active Directory user/password through the SQL-server.

We found SQL-code that can be used in a stored-procedure to query the Active Directory, buth with that SQL-code you can only query the AD users and their attributes, buth you can't validate the AD password.

What we need is the possibility to validate both from within a stored-procedure: the AD user and the AD password.

The question we want to be answered by the stored-procedure is: Does this user exists in Active Directory with this password?

Is this possible and how???

 

[mrdenny]

I've come up with a couple of options, but neither one of them are very clean.

1. Write a small command line app which accepts the users username and password. The app simply trys to impersonate the domain account which has passed to it and returns a return value of 0 if it works and something else if it doesn't. Call the app via xp_cmdshell.

2. If you have SQL 2005 use a .NET CLR which does the same basic thing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MarnickTelenet]

MrDenny,

Thank you... but:

1. In SQL2005 you must allow to run operating system commands in the SQL2005 Surface Area Configuration. If you allow this, you have a security risc our customers don't want.

2. If you try it by CLR, you can't use the Shell-command from the standard Microsoft.VisualBasic object to launch the command line app. You get a security exception error.

Any suggestion?

 

[mrdenny]

You didn't specify what version of SQL so I gave options for both SQL 2000 and SQL 2005.

Your client is correct, xp_cmdshell shouldn't ever be enabled.

Within the .NET procedure you don't need to shell out. Within the .NET code impersonate a windows account using the domain credentials provided. By trying to impersonate the users account you will call the domain controller and validate the users account. You shouldn't actually have to do anything to validate the account. If you do for some reason, connect back to the local SQL Server and select getdate() or something else which won't put any strain on the SQL Server. This will be enough to validate that the users domain credientials are working correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MarnickTelenet]

We started without a try to shell out. But then we had a problem with system.directoryservices.
This library isn't supported by CLR in SQL2005.

What do you mean by "you shouldn't actually have to do anything to validate the account"?
Isn't it necessary to use the directorySearcher to validate?

 

[mrdenny]

I do not know. You would have to check with a .NET programmer.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com