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.

Daniel Bragg
  • 173
  • 8
  • 1
    The best practice would be to not do this in a database at all. – LowlyDBA - John M Feb 17 '21 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 Feb 17 '21 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 Feb 17 '21 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 Feb 17 '21 at 22:18

1 Answers1

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.

J.D.
  • 22,196
  • 3
  • 34
  • 78
  • 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 Feb 18 '21 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. Feb 18 '21 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. Feb 18 '21 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. Feb 18 '21 at 15:55
  • Let us [continue this discussion in chat](https://chat.stackexchange.com/rooms/119903/discussion-between-dan-and-j-d). – Daniel Bragg Feb 18 '21 at 16:38