locked
Authenticate LDAP user via SQL Server 2008 R2 RRS feed

  • Question

  • Dear All,

    I need your valuable help for one of the issue i am facing today.

    It will help other also.

    Is it possible to authenticate a user from an Active Directory through SQL Server using LDAP ? Assuming that I enter the Windows domain username and password in an SQL Select query.

    I have created link LDAP link server to my domain with below script.

    declare @linkedServerSql nvarchar(4000),  
      @linkedServerName varchar(100),  
      @localSqlUsername varchar(100),  
      @domainUsername varchar(100),  
      @domainUserPassword varchar(100)  
     
    -- Set the local sql server user  
    set @localSqlUsername = 'xx'  
    -- format can be 'domainName\userName' or 'userName@domainName'  
    set @domainUsername = 'xxxxxx'  
    set @domainUserPassword = 'xxxxxx'  
     
    set @linkedServerName = 'ADSI'  
     
    set @linkedServerSql = '  
      exec sp_addlinkedserver  
      ''' + @linkedServerName + ''',  
      '''',  
      ''ADSDSOObject'',  
      ''''   
     
    exec sp_addlinkedsrvlogin  
      ''' + @linkedServerName + ''',  
      false,  
      ''' + @localSqlUsername + ''',  
      ''' + @domainUsername + ''',  
      ''' + @domainUserPassword + ''''  
     
    exec sp_executesql @linkedServerSql

    I can authenticate user with below query.

    select  *  
        from  openquery(adsi, '  
        select  givenName,  
            sn,  
            sAMAccountName,  
            displayName,  
            mail,  
            telephoneNumber,  
            mobile,  
            physicalDeliveryOfficeName,  
            department,  
            division  
        from    ''LDAP://xxxxxxxxxxxx''  
        where   objectCategory = ''Person''  
                and  
                objectClass = ''user'' and sAMAccountname = ''xxxxxxx''
        ') 

    Now i want to authenticate user with respective password.

    Thank you for your help in advance.

    • Moved by Tom Phillips Tuesday, June 26, 2012 1:28 PM Possibly better security question (From:SQL Server Database Engine)
    Tuesday, June 19, 2012 7:42 AM

Answers