none
VB.NET to Access MS SQL 2000 using Windows Active Directory Authentication RRS feed

  • Question

  •  I have a VB.NET application that originally sent data to a MySQL database. I am in the process of moving it to MS SQL 2000 server. I'm looking for the follwoing help:

     

    1. Is it possible for my application to access the SQL DB using a Windows Active Directory Authentication? If so How? I need to be able to submit a username and password to Authenticate on my domain. The machines running this application will be both registered and not registered on the domain.

     

    2. I use the following snippet of code to connect to the MySQL database... How different is it to query the MS SQL database?

     

    Dim mySelectQuery As String = "SELECT * FROM TABLE"

    Dim ConnectionString As String

     

    ConnectionString"DRIVER={MySQL ODBC 3.51 Driver};"

    ConnectionString += " SERVER="+Server+"; DATABASE="+Database+"; UID="+User+"; PWD="+Password

     

    'The MySQL system was using database Authentication but the MS SQL has to use Domain Authentication

    Dim myConnection As New OdbcConnection(MyVariables.ConnectionString)

    Dim myCommand As New OdbcCommand(mySelectQuery, myConnection)

    myConnection.Open() 'Connects to Database

    myReader = myCommand.ExecuteReader() 'Retrieves Query from Database

     

     

    I've looked up some resources like Impersonate and Intgrated Security= sspi;

    I seem to only be able to impersonate other users on my local machine which is not registered on the domain.

    Using the Integrated Security assumes the Identity of the user who is logged into the machine and not the domain user nor the "Specific" Domain user that has rights to the database.

     

    If this seems confusing what I'm trying to do:

    1. IT downloads my app onto any machine on our network (Domain registered and non-registered)

    2. The application will request their Active Directory username and password

    3. The application will use this username and password to Access a MicroSoft SQL 2000 database

    4. The Database uses Windows Authentication which only allows the Active Directory IT users access to read and write to the Database.

     

    Thanks for any assistance.

     

    Monday, August 20, 2007 9:16 PM

Answers

  • You can use Windows integrated or trusted security with SQL Server. In this instance the user does not supply the user ID or password but must be logged into the domain to handle the authentication process:

     

    How To Open a SQL Server Database by Using the SQL Server .NET Data Provider with Visual Basic .NET

     

    If the machine is not logged into the domain then you cannot use integrated security directly with SQL Server. You would have to use SQL Server security instead.

    Tuesday, August 21, 2007 1:35 PM
  • Finally after a long drawn out process of research and development.

     

    1. My applications takes the username and password for your domain account.

    2. Using a known domain account it logs into the LDAP server and locates where your account is stored.

    3. It verifies that you are a memberof a specific group

    4. Then knowing where to autheticate your account to on the LDAP server it attemtps to login with your credentials.

     

    I was unsuccessful in simply logging into the LDAP server because our cracker jack admin team has created users all over the place and not in someplace simple like CN=Users

     

    Once my app has confirmed that you are who you say you are it connects to the SQL server using an SQL account.

     

    So I'm not connecting to the SQL server using Windows Authentication directly but for now this is a nice work around.

     

    Thanks to everyone who helped me figure this out. Your patience and suggestions were most valuable.

     

    Monday, November 26, 2007 5:59 PM

All replies

  • You can use Windows integrated or trusted security with SQL Server. In this instance the user does not supply the user ID or password but must be logged into the domain to handle the authentication process:

     

    How To Open a SQL Server Database by Using the SQL Server .NET Data Provider with Visual Basic .NET

     

    If the machine is not logged into the domain then you cannot use integrated security directly with SQL Server. You would have to use SQL Server security instead.

    Tuesday, August 21, 2007 1:35 PM
  • That's what I figured but needed to confirm. So if there is no direct way into the Database is it possible that my application can:

     

    1. Verify that the user is in the Active Directory and a member of a specific group. (This part I am unfamiliar with)

    2. Then use an SQL Authentication to write to the Database. (This part I know)

     

     

    Tuesday, August 21, 2007 2:59 PM
  • You can check to see of a specific user is in the Active Directory listing using the System.DirectoryServices namespace

     

    I used to have the code somewhere but cannot seem to locate however I did find this and hope it helps:

    http://channel9.msdn.com/Showpost.aspx?postid=132740

     

     

    Not sure if this code works for you or not....

     

    private function Authenticate(byval userName as string, byval password as string, byval domain as string) as boolean

        dim authentic as boolean = false
        try
        
            Dim entry as new DirectoryEntry("LDAP://" & domain, userName, password)
            Dim nativeObject as object = entry.NativeObject
            authentic = true
        
        catch ex as DirectoryServicesCOMException
          

        end try

      

       return authentic
    end function

    Tuesday, August 21, 2007 3:26 PM
  • Yes, you can authenticate with AD using System.DirectoryServices but Microsoft doesn't recommend using this method. Checking role or group membership is possible via AD. You still need to authentication with SQL Server though. Are you planning on using a single login ID for all users? If not you will need to add an ID in SQL Server for each user.

     

    Tuesday, August 21, 2007 3:48 PM
  • Yeah I was thinking that I would enable SQL Authentication for one Read/Write user. However my application would contact our AD server to verify that the AD username and password are correct and that the user belongs to the SQL_MyApp group or something to the effect.

     

    This way specific users on any system can submit their AD credientials and if the machine is a member of the domain or not the application will verify that the correct AD user is accessing this application before using it's internal SQL username and password to access the database.

     

    Does this make sense?

     

    No all I need to know is how my application can verify that "User" is a member of the SQL_MyApp group...???

    Tuesday, August 21, 2007 4:02 PM
  • Close... This should let me know that the user is on our AD list but it doesn't verify that the "USER" is a member of the SQL_MyApp group. The other samples and refrences I've found also validate that the "USER" exists but not that they are a member of any specific group.

     

    If I find one I'll post it.. if anyone else finds one please let me know where you found it.

     

    Thanks

    Tuesday, August 21, 2007 4:04 PM
  • There should be code in the following newsgroup thread:

     

    http://www.dotnet247.com/247reference/msgs/38/193462.aspx

     

     

    Tuesday, August 21, 2007 5:22 PM
  • So far I've tried the following Functions:

     

    Public Function AuthenticateUser()

    Dim domainAndUsername As String

    Dim LDAPPATH As String

    LDAPPATH = Me.ldap.Text

    Dim entry As New DirectoryEntry(Me.ldap.Text, Me.UserName.Text, Me.Password.Text)

    AuthenticateUser = True

    Try

    'Bind to the native AdsObject to force authentication.

    Dim obj = entry.NativeObject()

    Dim search = New DirectorySearcher(entry)

    search.Filter = "(SAMAccountName=" + Me.UserName.Text + ")"

    search.PropertiesToLoad.Add("cn")

    Dim result = search.FindOne()

    If (result) Then

    AuthenticateUser = False

    End If

    'Update the new path to the user in the directory

    LDAPPATH = result.Path

    Catch ex As System.Exception

    MsgBox("Error authenticating user." + vbNewLine + ex.ToString(), MsgBoxStyle.OKOnly, "Caught Error")

    AuthenticateUser = False

    End Try

    End Function

     

    AND

     

    Public Function UserExists(ByVal UserName As String, ByVal Password As String) As Boolean

    Try

    Dim de As DirectoryEntry = New DirectoryEntry

    de.Path = Me.ldap.Text

    de.Username = Me.UserName.Text

    de.Password = Me.Password.Text

    Dim deSearch As DirectorySearcher = New DirectorySearcher

    deSearch.SearchRoot = de

    deSearch.Filter = "(&(objectClass=user) (cn=" & UserName & "))"

    Dim results As SearchResultCollection = deSearch.FindAll()

    If results.Count = 0 Then

    Return False

    Else

    Return True

    End If

    Catch ex As System.Exception

    MsgBox("Error authenticating user." + vbNewLine + ex.ToString(), MsgBoxStyle.OKOnly, "Caught Error")

    Return False

    End Try

    End Function

     

    Both are giving me an Unknown Error (0x80005000) on the lines Marked in Red above.

     

    Right now I'm just trying to verify the user on our domain with a simple form that takes username, password and LDAP:// information.

     

    I'll worry about their assigned groups later.

     

     

     

    Tuesday, August 21, 2007 7:53 PM
  • Are you specifying a valid LDAP path? It should be something like the following:


    LDAP://DC=xxx,DC=yyy,DC=zzz

     

     

    Wednesday, August 22, 2007 3:29 PM
  • Yes I believe so... I still get the Unknown error. I am supplying the following as it appears above with:

    Dim entry As New DirectoryEntry(Me.ldap.Text, Me.UserName.Text, Me.Password.Text)

    LDAP:

    1. Just the server name
    2. ServernameStick out tongueort#
    3. Servername/DC=xxx,DC=yyy,DC=zzz (With and Without Port#)
    4. Servername/CN=UserName,OU=GroupI'mAssociatedWith,DC=xxx,DC=yyy,DC=zzz  (With and Without Port#)

    And nothing seems to work  I've also prepended LDAP:// to all of the above attempts.

    The sites I've looked at make this so simple. I found an LDAP browser from

    http://www-unix.mcs.anl.gov/~gawor/ldap/ which I am able to connect with by punching in my server name, port number then fetching the Base DN then putting in my UserDN: CN=UserName,OU=MyGroup then checking the append Base DN and supplying my password. So I know I can get in, and that I have all of the information to get it.

    So I'm starting to think it's the VB code that I've submitted above that I've sampled form other sites. Yet it still does not work. Currently I'm creating this application in VB.NET 2003 which uses .NET 1.1 I'll look into VB.NET 2005 express and see if I can get it to work on .NET 2.0 I've attached below my User Authentication App which if I get running I'll adapt into my application which will communicate with the SQl server using SQL Authentication to access data from the database.

     

    Thanks to all who have contributed to this thread.  If anyone can get the code to work below please tell me what you did.

    =======================================================

     

    Imports System.Data
    Imports System.DirectoryServices


    Public Class Form1
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

        Public Sub New()
            MyBase.New()

            'This call is required by the Windows Form Designer.
            InitializeComponent()

            'Add any initialization after the InitializeComponent() call

        End Sub

        'Form overrides dispose to clean up the component list.
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer. 
        'Do not modify it using the code editor.
        Friend WithEvents UserName As System.Windows.Forms.TextBox
        Friend WithEvents Password As System.Windows.Forms.TextBox
        Friend WithEvents Button1 As System.Windows.Forms.Button
        Friend WithEvents ldap As System.Windows.Forms.TextBox
        Friend WithEvents Label1 As System.Windows.Forms.Label
        Friend WithEvents Label2 As System.Windows.Forms.Label
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.UserName = New System.Windows.Forms.TextBox
            Me.Password = New System.Windows.Forms.TextBox
            Me.Button1 = New System.Windows.Forms.Button
            Me.ldap = New System.Windows.Forms.TextBox
            Me.Label1 = New System.Windows.Forms.Label
            Me.Label2 = New System.Windows.Forms.Label
            Me.SuspendLayout()
            '
            'UserName
            '
            Me.UserName.Location = New System.Drawing.Point(80, 32)
            Me.UserName.Name = "UserName"
            Me.UserName.Size = New System.Drawing.Size(160, 20)
            Me.UserName.TabIndex = 0
            Me.UserName.Text = ""
            '
            'Password
            '
            Me.Password.Location = New System.Drawing.Point(96, 96)
            Me.Password.Name = "Password"
            Me.Password.PasswordChar = Microsoft.VisualBasic.ChrW(42)
            Me.Password.Size = New System.Drawing.Size(152, 20)
            Me.Password.TabIndex = 1
            Me.Password.Text = ""
            '
            'Button1
            '
            Me.Button1.Location = New System.Drawing.Point(104, 184)
            Me.Button1.Name = "Button1"
            Me.Button1.TabIndex = 2
            Me.Button1.Text = "Login"
            '
            'ldap
            '
            Me.ldap.Location = New System.Drawing.Point(16, 128)
            Me.ldap.Name = "ldap"
            Me.ldap.Size = New System.Drawing.Size(264, 20)
            Me.ldap.TabIndex = 3
            Me.ldap.Text = ""
            '
            'Label1
            '
            Me.Label1.Location = New System.Drawing.Point(8, 32)
            Me.Label1.Name = "Label1"
            Me.Label1.Size = New System.Drawing.Size(64, 16)
            Me.Label1.TabIndex = 4
            Me.Label1.Text = "UserName"
            '
            'Label2
            '
            Me.Label2.Location = New System.Drawing.Point(16, 96)
            Me.Label2.Name = "Label2"
            Me.Label2.Size = New System.Drawing.Size(56, 16)
            Me.Label2.TabIndex = 5
            Me.Label2.Text = "Password"
            '
            'Form1
            '
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(292, 266)
            Me.Controls.Add(Me.Label2)
            Me.Controls.Add(Me.Label1)
            Me.Controls.Add(Me.ldap)
            Me.Controls.Add(Me.Button1)
            Me.Controls.Add(Me.Password)
            Me.Controls.Add(Me.UserName)
            Me.Name = "Form1"
            Me.Text = "Form1"
            Me.ResumeLayout(False)

        End Sub

    #End Region

        Public Function AuthenticateUser()
            Dim domainAndUsername As String
            Dim LDAPPATH As String
            LDAPPATH = Me.ldap.Text
            Dim entry As New DirectoryEntry(Me.ldap.Text, Me.UserName.Text, Me.Password.Text)
            AuthenticateUser = True
            Try
                'Bind to the native AdsObject to force authentication.
                Dim obj = entry.NativeObject()
                Dim search = New DirectorySearcher(entry)
                search.Filter = "(SAMAccountName=" + Me.UserName.Text + ")"
                search.PropertiesToLoad.Add("cn")
                Dim result = search.FindOne()
                If (result) Then
                    AuthenticateUser = False
                End If
                'Update the new path to the user in the directory
                LDAPPATH = result.Path
            Catch ex As System.Exception
                MsgBox("Error authenticating user." + vbNewLine + ex.ToString(), MsgBoxStyle.OKOnly, "Caught Error")
                AuthenticateUser = False
            End Try

        End Function
        Public Function UserExists(ByVal UserName As String, ByVal Password As String) As Boolean
            Try
                Dim de As DirectoryEntry = New DirectoryEntry
                de.Path = Me.ldap.Text
                de.Username = Me.UserName.Text
                de.Password = Me.Password.Text
                Dim deSearch As DirectorySearcher = New DirectorySearcher
                deSearch.SearchRoot = de
                deSearch.Filter = "(&(objectClass=user) (cn=" & UserName & "))"
                Dim results As SearchResultCollection = deSearch.FindAll()
                If results.Count = 0 Then
                    Return False
                Else
                    Return True
                End If
            Catch ex As System.Exception
                MsgBox("Error authenticating user." + vbNewLine + ex.ToString(), MsgBoxStyle.OKOnly, "Caught Error")
                Return False
            End Try
        End Function

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If (AuthenticateUser()) Then
                MsgBox("Success", MsgBoxStyle.OKOnly, "Success")
            Else
                MsgBox("Damn No luck yet", MsgBoxStyle.OKOnly, "FAILURE")
            End If
            If (UserExists(Me.UserName.Text, Me.Password.Text)) Then
                MsgBox("Success", MsgBoxStyle.OKOnly, "Success")
            Else
                MsgBox("Damn No luck yet", MsgBoxStyle.OKOnly, "FAILURE")
            End If
        End Sub
    End Class

    Thursday, August 23, 2007 10:01 PM
  • Try the following to verify that you're using the correct path:

     

    Code Snippet

    Dim RootDSE As New DirectoryServices.DirectoryEntry("LDAP://RootDSE")

    MsgBox(RootDSE.Properties("DefaultNamingContext").Value)

     

     

    Monday, August 27, 2007 12:28 PM
  • Forgive me... do I have to change the RootDSE in LDAP://RootDSE to my LDAP server?

    Otherwise it tells me that it cannot locate my domain...

     

    Tuesday, August 28, 2007 5:23 AM
  • No, RootDSE will return information about the data directory tree on the directory server, such as the DefaultNamingContext (your domain distinguished name). This command shouldn't fail.

     

    You can then prefix the DefaultNamingContext with "LDAP://" and then use that in your active directory operations.

     

    Tuesday, August 28, 2007 12:55 PM
  • Sadly this battle continues....

    Does the machine I am running this on have to be connected to the Domain?

    When I execute the following:

     

    Dim RootDSE As New DirectoryServices.DirectoryEntry("LDAP://RootDSE")
            Try
                MsgBox(RootDSE.Properties("DefaultNamingContext").Value)
            Catch ex As Exception
                MsgBox(ex.ToString, MsgBoxStyle.OKOnly, "ERROR")
            End Try

    I get the following error message:

    The specified domain either does not exist or could not be contacted.

     

    Every site I've been to makes this seem like it's somethin gsimple to do but I seem to be having a lot of problems...

    Thursday, September 13, 2007 3:52 PM
  • Well if you're not connected to the domain then you're not going to have much luck authenticating against it. ;-)

     

    Thursday, September 13, 2007 4:21 PM
  • I believe it does need to be dropped into the domain for it to work.

     

    Thursday, September 13, 2007 4:21 PM
  • So is there any way I can verify a submitted username and password on an LDAP server from a machine that is not a member of the domain? This is important.

     

    Thursday, September 13, 2007 4:25 PM
  • Not if the machine cannot communicated with the domain, no. The only authentication available would be through the credentials stored locally on your machine.

     

    Friday, September 14, 2007 11:52 AM
  • My machine although not registered on the domain can map drives using my domain account. I have an LDAP browser I downloaded that will also connect to our LDAP server and show me all of the accounts and credentials...

     

    But for some reason I can't get VB to connect to the LDAP services to verify accounts.

     

    I continue to scour the internet for solutions but I appear to be the first to do something like this.

    Friday, September 14, 2007 5:01 PM
  • The LDAP browser could be using data access methods to query Active Directory. For example there is OLEDB Active Directory provider that will allow you to query Active Directory using ADO (ActiveX Data Objects). 

     

    Friday, September 14, 2007 5:42 PM
  • Finally after a long drawn out process of research and development.

     

    1. My applications takes the username and password for your domain account.

    2. Using a known domain account it logs into the LDAP server and locates where your account is stored.

    3. It verifies that you are a memberof a specific group

    4. Then knowing where to autheticate your account to on the LDAP server it attemtps to login with your credentials.

     

    I was unsuccessful in simply logging into the LDAP server because our cracker jack admin team has created users all over the place and not in someplace simple like CN=Users

     

    Once my app has confirmed that you are who you say you are it connects to the SQL server using an SQL account.

     

    So I'm not connecting to the SQL server using Windows Authentication directly but for now this is a nice work around.

     

    Thanks to everyone who helped me figure this out. Your patience and suggestions were most valuable.

     

    Monday, November 26, 2007 5:59 PM
  •  

    If you still access this account, would it be possible for you to share your final code?  I am experiencing the same issues.
    Monday, February 11, 2008 3:56 PM