Sql – Forms authentication against multiple providers (SQl and AD)

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

If you work on .NET 3.5 or newer, you can use the System.DirectoryServices.AccountManagement namespace and easily verify your credentials:

// create a "principal context" - e.g. your domain (could be machine, too)
using(PrincipalContext pc = new PrincipalContext(ContextType.Domain, "YOURDOMAIN"))
{
    // validate the credentials
    bool isValid = pc.ValidateCredentials("myuser", "mypassword");
}

It's simple, it's reliable, it's 100% C# managed code on your end - what more can you ask for? :-)

Read all about it here:

• Managing Directory Security Principals in the .NET Framework 3.5
• MSDN docs on System.DirectoryServices.AccountManagement

Update:

As outlined in this other SO question (and its answers), there is an issue with this call possibly returning True for old passwords of a user. Just be aware of this behavior and don't be too surprised if this happens :-) (thanks to @MikeGledhill for pointing this out!)