1

I can fetch data back from LDAPS (port 636/3269) by first creating a Linked Server using sp_addlinkedserver and sp_addlinkedsrvlogin.

However, what I really need to do is to authenticate a Username/Password pair against LDAPS, to determine if the credentials are Valid.

Can SQL Server be used in any way to authenticate Username/Password credentials against AD/LDAP? Is it possible to use an Extended Stored Procedure or the CLR to do this? I need to abstract this away from a .DLL because the same authentication needs to be used in a legacy application and on a website.

Improve this question
4
  • 1
    The best practice would be to not do this in a database at all.
    – LowlyDBA - John M
    Commented Feb 17, 2021 at 21:29
  • Agreed, however, we decided to investigate this because we needed to perform the same functionality from within an old windows application (written in Delphi 5, and not accessible to new libraries) as well as a website. SQL queries are one of the most accessible intersecting points between the two environments.
    – Daniel Bragg
    Commented Feb 17, 2021 at 21:39
  • I have found that sp_addlinkedsrvlogin does no authentication, it only sets up a login with the credentials you provide. I cannot use this for authentication.
    – Daniel Bragg
    Commented Feb 17, 2021 at 22:09
  • I've read in places the possibility of using an Extended Stored Procedure that could access IADsOpenDSObject:: OpenDSObject. Does anyone have any code on how that would looks like (I have no Extended Stored Procedure or CLR experience), and know whether that would be able to address the new LDAPS (port 636/3269) requirements?
    – Daniel Bragg
    Commented Feb 17, 2021 at 22:18

1 Answer 1

Reset to default
1

I'm not sure why you'd need a linked server to LDAP to validate credentials? SQL Server has Active Directory (Windows) authentication built into it, so you should be able to leverage that as long as you add the appropriate Login object in SQL Server for either the AD User or AD Group that user is a part of, so they have the ability to connect.

Unless I'm missing something like you're trying to connect to LDAP of a different domain than that of the SQL Server instance, but then we'd more details on your goal.

Improve this answer
5
  • The goal of using SQL Server to access LDAP is for the purposes of SSO (Single Sign-On) and allowing AD to manage user credentials for our application/website. Once they validate to AD/LDAP, we check to see if a matching user exists in our system. If they do, then the are allowed in, no second password required. If they don't, then LDAP is used to fetch user details so a user can be created in our system that matches. That way, all "User Management" is done via AD/LDAP, not our system.
    – Daniel Bragg
    Commented Feb 18, 2021 at 15:11
  • @Dan Sorry I still don't follow 100% but let summarize what I think you're saying and what is available out of the box. 1) It sounds like you're looking for a way to not require your users to input their password twice to access the application and the database behind it. This is already the default way SQL Server operates, so long as an appropriate Login object is configured in your SQL instance. (Depending on your app, you may need to use the Trusted Connection property in your connection string.) If you're experiencing behavior otherwise, then you should ask that question here....
    – J.D.
    Commented Feb 18, 2021 at 15:50
  • ...2) It sounds like you're also looking for a way to possibly automatically manage the Login objects I refer to in #1. This is where creating a Login for an AD Group will benefit you because you'll only need to setup the Login once and moving forward your users will automatically be given access to the database once they're AD account is created and they're added to that AD Group. Alternatively if you must come up with a more automatic way of managing the creation of Logins in SQL Server, then you'll likely want to create a stored procedure that scripts it out...
    – J.D.
    Commented Feb 18, 2021 at 15:52
  • ...and takes in the parameters for the AD User or AD Group you want it to create. Then you'll need to call that stored procedure from your application code after the application verifies the user is new. These are definitely the more simpler and recommended approaches, as opposed to trying to get SQL Server to verify a user's existence in LDAP, so I have to agree with John McCall here. The first solution to #2 with just using AD Groups the way they were intended is super simple.
    – J.D.
    Commented Feb 18, 2021 at 15:55
  • Let us continue this discussion in chat.
    – Daniel Bragg
    Commented Feb 18, 2021 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.