Why is Visual Studio (VB) not reading my SQL connection string properly?

Question

I am writing a small windows tool to search a few SQL databases. I was able to connect and search the first database without issues but I keep getting the following login error when I try to search the second database (Database 2):

'System.Data.SqlClient.SqlException' occurred in System.Data.dll Login failed for user '\azahir'

You will find that <Domain>\azahir is not even specified in my connection string or anywhere in my few lines of code.

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1

Dim Conn As SqlConnection
Dim Conn2 As SqlConnection

Private Sub btSearch_Click(sender As Object, e As EventArgs) Handles btSearch.Click
    Conn = New SqlConnection("Data Source = <SERVER>;Initial Catalog=<DATABASE>;Integrated Security=SSPI;User ID = <Domain> \ axzahir;Password=<Password>;")
    Conn.Open()

    Dim cmd2 As SqlCommand = Conn.CreateCommand
    cmd2.CommandText = "select firstname, lastname
    from systemuserbase where firstname like" + "'%" + TxFirstName.Text + "%'" +
    " And lastname Like" + " '%" + TxLastname.Text + "%'"

    Dim dir As SqlDataReader = cmd2.ExecuteReader()
    If dir.HasRows Then

        Dim dtClient As New DataTable
        dtClient.Load(dir)
        dtOutput.DataSource = dtClient

    End If

    dir.Close()
    Conn.Close()
End Sub

....

Private Sub btnArgus_Click(sender As Object, e As EventArgs) Handles btnArgus.Click
    Conn2 = New SqlConnection("Data Source = <SERVER2>;Initial Catalog=<DATABASE 2>;Integrated Security=SSPI;User ID = <DOMAIN> \ axzahir;Password=<PASSWORD>;")
    Conn2.Open()

    Dim cmd3 As SqlCommand = Conn2.CreateCommand
    cmd3.CommandText = "select userID, Fullname
    from Users where FullName like" + "'%" + TxFirstName.Text + "%'" +
    " And Fullname Like" + " '%" + TxLastname.Text + "%'"

    Dim dir3 As SqlDataReader = cmd3.ExecuteReader()
    If dir3.HasRows Then

        Dim dtClient As New DataTable
        dtClient.Load(dir3)
        dtOutput.DataSource = dtClient


    End If

    dir3.Close()
    Conn2.Close()
End Sub
End Class

I have verified that my domain/username + password works for database 2. I am stumped as to why Visual Studio thinks my user is '\azahir' instead of the specified '\axzahir'. Any thoughts on how this can be fixed?

Thank you, Asif

Answer 1

That's not how integrated security works. When using integrated security, there is no way to specify a specific username or the password. Instead, you get the user authorization for whatever user account runs your program. The entire connection string looks like this, with no specific user information:

Data Source = <SERVER>;Initial Catalog=<DATABASE>;Integrated Security=SSPI;

If you want to specify a username and password, you must use SQL authentication. If you want to access the database as a specific domain account, you use integrated security, but you have to run your app as that user. There is no way to specify Active Directory credentials in a connection string and get that user's database access.

---

While I'm here, let me show you a better pattern for your database connection. (One that's not crazy vulnerable to sql injection! and will remember to close the connection even if an exception is thrown.)

Assuming a valid connection string:

Private ConnString As String = "connection string here"

Private Sub btSearch_Click(sender As Object, e As EventArgs) Handles btSearch.Click
    Dim SQL As String = _ 
      "SELECT firstname, lastname " & 
      "FROM systemuserbase " & 
      "WHERE firstname like '%' + @FirstName + '%' AND lastname Like '%' + @LastName + '%';"

    Using Conn As New SqlConnection(ConnString), _
          cmd As New SqlCommand(SQL, Conn)

        'Use actual database column types and lengths here
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 20).Value = TxFirstName.Text
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = TxLastName.Text

        Conn.Open()   
        Using dir As SqlDataReader = cmd2.ExecuteReader()
            dtOutput.DataSource = dir
            dir.Close()
        End Using
    End Using
End Sub

Private Sub btnArgus_Click(sender As Object, e As EventArgs) Handles btnArgus.Click
    Dim SQL As String = _ 
      "SELECT userID, Fullname " & 
      "FROM Users " &  
      "WHERE FullName like '%' + @FirstName + '%' AND Fullname Like '%' + @Lastname + '%';"

    'Note I can use the same variable names. 
    ' These are scoped to the method, not the class.
    ' Different scope, different variables, even though the names are the same
    Using Conn AS New SqlConnection(ConnString), _
          cmd As New SqlCommand(SQL, Conn)

        'Use actual database column types and lengths here
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 20).Value = TxFirstName.Text
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = TxLastName.Text

        Conn.Open()    
        Using dir As SqlDataReader = cmd.ExecuteReader()
            dtOutput.DataSource = dir 
            dir.Close()
        End Using
    End Using
End Sub