LDAP-Authentification with SQL-Server

  • wittke

    SSC Enthusiast

    Points: 156

    I have different Applications using VB 6.0, VB.NET and Compact Framework 3.5. Now my client demands LDAP-Authentification (Active Directory) for all of it.

    All Applications using the SQL-2014-Server and I would like to solve this problem with a Stored Procedure that does all the magic.

    Is there allready a solution that returns a "True" when I pass a UserID and Password to it ?

    That would be very helpful. I tried allready to get answers from the web, but I wasn´t successfull...

  • benjamin.reyes

    SSCertifiable

    Points: 5249

    Is this a purely windows domain?

    If so you could probably use something like sp_validatelogins.

  • Lowell

    SSC Guru

    Points: 323459

    wittke (5/16/2016)


    I have different Applications using VB 6.0, VB.NET and Compact Framework 3.5. Now my client demands LDAP-Authentification (Active Directory) for all of it.

    All Applications using the SQL-2014-Server and I would like to solve this problem with a Stored Procedure that does all the magic.

    Is there allready a solution that returns a "True" when I pass a UserID and Password to it ?

    That would be very helpful. I tried allready to get answers from the web, but I wasn´t successfull...

    you will need to change the way your application connects to SQL;

    if it currently connects via SQL username and password, you have to change the application to use integrated security instead.

    then SQL will automatically check if the windows token passed by the application is valid, and what it has access to.

    The classic way would be to add an Active directory group, say named MyAppAccessGroup, and add the five or five hundred people that should have acces sot that active directory group.

    then you add that group as a login to SQL,and then as a user in in the right database(s), and add the right permissions(data reader, writer, execute, etc.. that will allow them to have your permissions for the application.

    so to be clear, AFTER someone connects to SQL with a SQL username and password SQL Server CANNOT discover their username or whether they are allowed access or not.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • wittke

    SSC Enthusiast

    Points: 156

    Thank your for your help, but that seems to me too complicated.

    Actually I am able to read LDAP-Data with SQL and Connected Servers, like names, groups, department, etc. There are many samples in the web for that. I was wondering if there would be an advanced function to validate a user account.

    When there is no such (easy) way, I am thinking about creating a CLR Stored Procedures that will do the trick...

    Lowell (5/16/2016)


    wittke (5/16/2016)


    I have different Applications using VB 6.0, VB.NET and Compact Framework 3.5. Now my client demands LDAP-Authentification (Active Directory) for all of it.

    All Applications using the SQL-2014-Server and I would like to solve this problem with a Stored Procedure that does all the magic.

    Is there allready a solution that returns a "True" when I pass a UserID and Password to it ?

    That would be very helpful. I tried allready to get answers from the web, but I wasn´t successfull...

    you will need to change the way your application connects to SQL;

    if it currently connects via SQL username and password, you have to change the application to use integrated security instead.

    then SQL will automatically check if the windows token passed by the application is valid, and what it has access to.

    The classic way would be to add an Active directory group, say named MyAppAccessGroup, and add the five or five hundred people that should have acces sot that active directory group.

    then you add that group as a login to SQL,and then as a user in in the right database(s), and add the right permissions(data reader, writer, execute, etc.. that will allow them to have your permissions for the application.

    so to be clear, AFTER someone connects to SQL with a SQL username and password SQL Server CANNOT discover their username or whether they are allowed access or not.

  • konrad.wicek

    Valued Member

    Points: 59

    asd

Viewing 5 posts - 1 through 5 (of 5 total)

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