Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Discussion in 'ASP .Net' started by eruth, Oct 26, 2007.

  1. eruth

    eruth Guest

    There are loads of post on this, but nothing that seems to cover my
    exact problem ;)

    I have an ASP.Net 1.1 web application running on my local machine. I
    want to connect to an SQL 2005 server running on Windows 2003.

    If I use SQL authentication, all is fine and dandy. If I want to use
    windows authentication it gives the above error. My SQL server is set
    to allow both methods (first thing I checked) and is part of the
    domain (2nd thing checked).

    If I create a UDL file on the local machine and connect to the SQL
    server with that, it reports that the test connection is successful,
    so I took the connection string from that and used it in my
    web.config, but got the above error.

    Here's my connection string using SQL Authentication

    "Password=RagUser;Persist Security Info=True;User ID=RagUser;Initial
    Catalog=KBDL_Config;Data Source=UKBOLDEV01"

    and the connection string using windows authentication

    "Integrated Security=SSPI;Persist Security Info=False;Initial
    Catalog=KBDL_Config;Data Source=ukboldev01"

    Even adding 'Trusted Connection=True' to the windows authentication
    doesn't help!

    SQL 2005 has errors in it's log, it shows.

    10/26/2007 16:35:54,Logon,Unknown,Login failed for user ''. The user
    is not associated with a trusted SQL Server connection. [CLIENT:
    192.168.41.75]
    10/26/2007 16:35:54,Logon,Unknown,Error: 18452<c/> Severity: 14<c/>
    State: 1.
    10/26/2007 16:35:54,Logon,Unknown,SSPI handshake failed with error
    code 0x8009030c while establishing a connection with integrated
    security; the connection has been closed. [CLIENT: 192.168.41.75]
    10/26/2007 16:35:54,Logon,Unknown,Error: 17806<c/> Severity: 20<c/>
    State: 2.


    I'm baffled by this, I've even tried connecting to the server using
    oSQL, that was successful, but with vb.net from a web app, it fails.
    There's a code snippet below

    <code>
    strSQL = "SELECT * FROM LOGINS WHERE Username='" & strUsername & "'
    AND Password='" & strPassword & "'"

    myconnection = New
    SqlConnection(ConfigurationSettings.AppSettings("ConfigCon")) 'get
    from web.config
    myCommand = New SqlCommand(strSQL, myconnection)

    Try
    If (myCommand.Connection.State <> ConnectionState.Open)
    Then 'check connection state
    myCommand.Connection.Open() ' open connection if
    needed
    End If
    </code>
    eruth, Oct 26, 2007
    #1
    1. Advertising

  2. eruth

    Patrice Guest

    Have you tried also something such as :
    http://msdn2.microsoft.com/en-us/library/ms998320.aspx


    --
    Patrice

    "eruth" <> a écrit dans le message de news:
    ...
    > There are loads of post on this, but nothing that seems to cover my
    > exact problem ;)
    >
    > I have an ASP.Net 1.1 web application running on my local machine. I
    > want to connect to an SQL 2005 server running on Windows 2003.
    >
    > If I use SQL authentication, all is fine and dandy. If I want to use
    > windows authentication it gives the above error. My SQL server is set
    > to allow both methods (first thing I checked) and is part of the
    > domain (2nd thing checked).
    >
    > If I create a UDL file on the local machine and connect to the SQL
    > server with that, it reports that the test connection is successful,
    > so I took the connection string from that and used it in my
    > web.config, but got the above error.
    >
    > Here's my connection string using SQL Authentication
    >
    > "Password=RagUser;Persist Security Info=True;User ID=RagUser;Initial
    > Catalog=KBDL_Config;Data Source=UKBOLDEV01"
    >
    > and the connection string using windows authentication
    >
    > "Integrated Security=SSPI;Persist Security Info=False;Initial
    > Catalog=KBDL_Config;Data Source=ukboldev01"
    >
    > Even adding 'Trusted Connection=True' to the windows authentication
    > doesn't help!
    >
    > SQL 2005 has errors in it's log, it shows.
    >
    > 10/26/2007 16:35:54,Logon,Unknown,Login failed for user ''. The user
    > is not associated with a trusted SQL Server connection. [CLIENT:
    > 192.168.41.75]
    > 10/26/2007 16:35:54,Logon,Unknown,Error: 18452<c/> Severity: 14<c/>
    > State: 1.
    > 10/26/2007 16:35:54,Logon,Unknown,SSPI handshake failed with error
    > code 0x8009030c while establishing a connection with integrated
    > security; the connection has been closed. [CLIENT: 192.168.41.75]
    > 10/26/2007 16:35:54,Logon,Unknown,Error: 17806<c/> Severity: 20<c/>
    > State: 2.
    >
    >
    > I'm baffled by this, I've even tried connecting to the server using
    > oSQL, that was successful, but with vb.net from a web app, it fails.
    > There's a code snippet below
    >
    > <code>
    > strSQL = "SELECT * FROM LOGINS WHERE Username='" & strUsername & "'
    > AND Password='" & strPassword & "'"
    >
    > myconnection = New
    > SqlConnection(ConfigurationSettings.AppSettings("ConfigCon")) 'get
    > from web.config
    > myCommand = New SqlCommand(strSQL, myconnection)
    >
    > Try
    > If (myCommand.Connection.State <> ConnectionState.Open)
    > Then 'check connection state
    > myCommand.Connection.Open() ' open connection if
    > needed
    > End If
    > </code>
    >
    Patrice, Oct 26, 2007
    #2
    1. Advertising

  3. eruth

    bruce barker Guest

    Re: Login failed for user ''. The user is not associated with a trustedSQL Server connection.

    if your local asp.net site is running under iis, then you need to set to
    run under a domain account with access the sqlserver.

    1) if you only hit the website locally just set impersonate=true in the
    web config and require authenication to ntlm in iis

    2) if your local box is vista or server2003 set the app pool for the
    asp.net site to the desired domain account.

    3) if none of above, in web.config set impersonate with a useName and
    password.

    -- bruce (sqlwork.com)

    eruth wrote:
    > There are loads of post on this, but nothing that seems to cover my
    > exact problem ;)
    >
    > I have an ASP.Net 1.1 web application running on my local machine. I
    > want to connect to an SQL 2005 server running on Windows 2003.
    >
    > If I use SQL authentication, all is fine and dandy. If I want to use
    > windows authentication it gives the above error. My SQL server is set
    > to allow both methods (first thing I checked) and is part of the
    > domain (2nd thing checked).
    >
    > If I create a UDL file on the local machine and connect to the SQL
    > server with that, it reports that the test connection is successful,
    > so I took the connection string from that and used it in my
    > web.config, but got the above error.
    >
    > Here's my connection string using SQL Authentication
    >
    > "Password=RagUser;Persist Security Info=True;User ID=RagUser;Initial
    > Catalog=KBDL_Config;Data Source=UKBOLDEV01"
    >
    > and the connection string using windows authentication
    >
    > "Integrated Security=SSPI;Persist Security Info=False;Initial
    > Catalog=KBDL_Config;Data Source=ukboldev01"
    >
    > Even adding 'Trusted Connection=True' to the windows authentication
    > doesn't help!
    >
    > SQL 2005 has errors in it's log, it shows.
    >
    > 10/26/2007 16:35:54,Logon,Unknown,Login failed for user ''. The user
    > is not associated with a trusted SQL Server connection. [CLIENT:
    > 192.168.41.75]
    > 10/26/2007 16:35:54,Logon,Unknown,Error: 18452<c/> Severity: 14<c/>
    > State: 1.
    > 10/26/2007 16:35:54,Logon,Unknown,SSPI handshake failed with error
    > code 0x8009030c while establishing a connection with integrated
    > security; the connection has been closed. [CLIENT: 192.168.41.75]
    > 10/26/2007 16:35:54,Logon,Unknown,Error: 17806<c/> Severity: 20<c/>
    > State: 2.
    >
    >
    > I'm baffled by this, I've even tried connecting to the server using
    > oSQL, that was successful, but with vb.net from a web app, it fails.
    > There's a code snippet below
    >
    > <code>
    > strSQL = "SELECT * FROM LOGINS WHERE Username='" & strUsername & "'
    > AND Password='" & strPassword & "'"
    >
    > myconnection = New
    > SqlConnection(ConfigurationSettings.AppSettings("ConfigCon")) 'get
    > from web.config
    > myCommand = New SqlCommand(strSQL, myconnection)
    >
    > Try
    > If (myCommand.Connection.State <> ConnectionState.Open)
    > Then 'check connection state
    > myCommand.Connection.Open() ' open connection if
    > needed
    > End If
    > </code>
    >
    bruce barker, Oct 26, 2007
    #3
  4. eruth

    eruth Guest

    Bruce,

    To address your suggestions.

    1) The problem with this solution is that once this site is deployed,
    the webserver will be in the DMZ, and the SQL server on the inside of
    the firewall.
    2) May be possible when deployed as the live servers will both be
    2003, however my development box (local pc) is XPsp2
    3) I'll try this and let you know.


    On 26 Oct, 17:29, bruce barker <> wrote:
    > if your local asp.net site is running under iis, then you need to set to
    > run under a domain account with access the sqlserver.
    >
    > 1) if you only hit the website locally just set impersonate=true in the
    > web config and require authenication to ntlm in iis
    >
    > 2) if your local box is vista or server2003 set the app pool for the
    > asp.net site to the desired domain account.
    >
    > 3) if none of above, in web.config set impersonate with a useName and
    > password.
    >
    > -- bruce (sqlwork.com)
    >
    > eruth wrote:
    > > There are loads of post on this, but nothing that seems to cover my
    > > exact problem ;)

    >
    > > I have an ASP.Net 1.1 web application running on my local machine. I
    > > want to connect to an SQL 2005 server running on Windows 2003.

    >
    > > If I use SQL authentication, all is fine and dandy. If I want to use
    > > windows authentication it gives the above error. My SQL server is set
    > > to allow both methods (first thing I checked) and is part of the
    > > domain (2nd thing checked).

    >
    > > If I create a UDL file on the local machine and connect to the SQL
    > > server with that, it reports that the test connection is successful,
    > > so I took the connection string from that and used it in my
    > > web.config, but got the above error.

    >
    > > Here's my connection string using SQL Authentication

    >
    > > "Password=RagUser;Persist Security Info=True;User ID=RagUser;Initial
    > > Catalog=KBDL_Config;Data Source=UKBOLDEV01"

    >
    > > and the connection string using windows authentication

    >
    > > "Integrated Security=SSPI;Persist Security Info=False;Initial
    > > Catalog=KBDL_Config;Data Source=ukboldev01"

    >
    > > Even adding 'Trusted Connection=True' to the windows authentication
    > > doesn't help!

    >
    > > SQL 2005 has errors in it's log, it shows.

    >
    > > 10/26/2007 16:35:54,Logon,Unknown,Login failed for user ''. The user
    > > is not associated with a trusted SQL Server connection. [CLIENT:
    > > 192.168.41.75]
    > > 10/26/2007 16:35:54,Logon,Unknown,Error: 18452<c/> Severity: 14<c/>
    > > State: 1.
    > > 10/26/2007 16:35:54,Logon,Unknown,SSPI handshake failed with error
    > > code 0x8009030c while establishing a connection with integrated
    > > security; the connection has been closed. [CLIENT: 192.168.41.75]
    > > 10/26/2007 16:35:54,Logon,Unknown,Error: 17806<c/> Severity: 20<c/>
    > > State: 2.

    >
    > > I'm baffled by this, I've even tried connecting to the server using
    > > oSQL, that was successful, but with vb.net from a web app, it fails.
    > > There's a code snippet below

    >
    > > <code>
    > > strSQL = "SELECT * FROM LOGINS WHERE Username='" & strUsername & "'
    > > AND Password='" & strPassword & "'"

    >
    > > myconnection = New
    > > SqlConnection(ConfigurationSettings.AppSettings("ConfigCon")) 'get
    > > from web.config
    > > myCommand = New SqlCommand(strSQL, myconnection)

    >
    > > Try
    > > If (myCommand.Connection.State <> ConnectionState.Open)
    > > Then 'check connection state
    > > myCommand.Connection.Open() ' open connection if
    > > needed
    > > End If
    > > </code>
    eruth, Oct 29, 2007
    #4
  5. eruth

    eruth Guest

    One final note - yes suggestion 3 did work, but on talking to the nice
    security people here, I was advised that using windows authentication
    might not be a good idea. So I'm going to stick with the SQL
    authentication and write a series of SQL jobs that will make sure the
    right users with the right permissions exist at the right time for my
    web pages to connect to the database.

    On 29 Oct, 09:15, eruth <> wrote:
    > Bruce,
    >
    > To address your suggestions.
    >
    > 1) The problem with this solution is that once this site is deployed,
    > the webserver will be in the DMZ, and theSQLserveron the inside of
    > the firewall.
    > 2) May be possible when deployed as the live servers will both be
    > 2003, however my development box (local pc) is XPsp2
    > 3) I'll try this and let you know.
    >
    > On 26 Oct, 17:29, bruce barker <> wrote:
    >
    > > if your local asp.net site is running under iis, then you need to set to
    > > run under a domain account with access the sqlserver.

    >
    > > 1) if you only hit the website locally just set impersonate=true in the
    > > web config and require authenication to ntlm in iis

    >
    > > 2) if your local box is vista or server2003 set the app pool for the
    > > asp.net site to the desired domain account.

    >
    > > 3) if none of above, in web.config set impersonate with a useName and
    > > password.

    >
    > > -- bruce (sqlwork.com)

    >
    > > eruth wrote:
    > > > There are loads of post on this, but nothing that seems to cover my
    > > > exact problem ;)

    >
    > > > I have an ASP.Net 1.1 web application running on my local machine. I
    > > > want to connect to anSQL2005serverrunning on Windows 2003.

    >
    > > > If I useSQLauthentication, all is fine and dandy. If I want to use
    > > > windows authentication it gives the above error. MySQLserveris set
    > > > to allow both methods (first thing I checked) and is part of the
    > > > domain (2nd thing checked).

    >
    > > > If I create a UDL file on the local machine and connect to theSQL
    > > >serverwith that, it reports that the testconnectionis successful,
    > > > so I took theconnectionstring from that and used it in my
    > > > web.config, but got the above error.

    >
    > > > Here's myconnectionstring usingSQLAuthentication

    >
    > > > "Password=RagUser;Persist Security Info=True;UserID=RagUser;Initial
    > > > Catalog=KBDL_Config;Data Source=UKBOLDEV01"

    >
    > > > and theconnectionstring using windows authentication

    >
    > > > "Integrated Security=SSPI;Persist Security Info=False;Initial
    > > > Catalog=KBDL_Config;Data Source=ukboldev01"

    >
    > > > Even adding 'TrustedConnection=True' to the windows authentication
    > > > doesn't help!

    >
    > > >SQL2005 has errors in it's log, it shows.

    >
    > > > 10/26/2007 16:35:54,Logon,Unknown,Loginfailedforuser''. Theuser
    > > > isnotassociatedwith atrustedSQLServerconnection. [CLIENT:
    > > > 192.168.41.75]
    > > > 10/26/2007 16:35:54,Logon,Unknown,Error: 18452<c/> Severity: 14<c/>
    > > > State: 1.
    > > > 10/26/2007 16:35:54,Logon,Unknown,SSPI handshakefailedwith error
    > > > code 0x8009030c while establishing aconnectionwith integrated
    > > > security; theconnectionhas been closed. [CLIENT: 192.168.41.75]
    > > > 10/26/2007 16:35:54,Logon,Unknown,Error: 17806<c/> Severity: 20<c/>
    > > > State: 2.

    >
    > > > I'm baffled by this, I've even tried connecting to theserverusing
    > > > oSQL, that was successful, but with vb.net from a web app, it fails.
    > > > There's a code snippet below

    >
    > > > <code>
    > > > strSQL = "SELECT * FROM LOGINS WHERE Username='" & strUsername & "'
    > > > AND Password='" & strPassword & "'"

    >
    > > > myconnection = New
    > > > SqlConnection(ConfigurationSettings.AppSettings("ConfigCon")) 'get
    > > > from web.config
    > > > myCommand = New SqlCommand(strSQL, myconnection)

    >
    > > > Try
    > > > If (myCommand.Connection.State <> ConnectionState.Open)
    > > > Then 'checkconnectionstate
    > > > myCommand.Connection.Open() ' openconnectionif
    > > > needed
    > > > End If
    > > > </code>
    eruth, Oct 30, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page