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

is this the actual code or have you anonymised ]the connection string — Simon Price, Jun 17 '18 at 21:32
Don't know if this is a problem but at least it looks odd to me: Why are there spaces around the backslash in ` \ axzahir`? — sticky bit, Jun 17 '18 at 21:34
@SimonPrice Hey there, I have anonymised the connection string. I only left my username there. — Asif Bin Zahir, Jun 17 '18 at 21:34
@stickybit I have the space in my first connection string and it works. Removing the space works as well but I still get the same error for my second connection string unfortunately. — Asif Bin Zahir, Jun 17 '18 at 21:39
I think Integrate Security might be ignoring your user name/password. — Xedni, Jun 17 '18 at 21:42
Unrelated tips: `SqlConnection`, `SqlCommand` and `SqlDataReader` are all `IDisposable` so should be in `using` blocks. And use parameters to remove the SQL injection vulnerability. — Richardissimo, Jun 17 '18 at 21:45
@Xedni is right here, if you're using integrated Security, then the password and username values will be ignored; they arent required as the credentials have already been verified when the user logged in. As youre logged in as `azahir`, those are the credentials they will be used. You either need to add a login for `azahir` (and anyone else that would use the app), set up impersonation for a trusted connection, or use SQL Login Authentication. — Thom A, Jun 17 '18 at 21:57
If you look into SQL Server Management logs, does the failed login show up there ? — SQLApostle, Jun 17 '18 at 22:27
Thank you @Xedni Richardissmi, Larnu and everyone! I really appreciate this know-how. — Asif Bin Zahir, Jun 18 '18 at 02:20

Joel Coehoorn · Accepted Answer · 2018-06-18T17:47:41.560

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

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

1 Answers1