Solved

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

Posted on 2007-11-24
23
1,759 Views
Last Modified: 2009-12-16
I am writing a program in C# attaching to a SQL 2005 server.  I am getting the following error message

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

I have given this user complete writes to the database I am accessing.  What reason would the server give this error message?

Thanks
Kenny


0
Comment
Question by:kwh3856
  • 13
  • 5
  • 2
  • +3
23 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 20344811
In Enterprise Manager, right click on the server and select Properties.  On the Security tab, select "SQL Server and Windows" for login types.  Click ok and restart the SQL service.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 20344816
^ "SQL Server and Windows" for Authentication
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20344823
Check whether the you have configured Mixed authentication, also try to connect from sql server management studio with windows authentication


Aneesh
0
 

Author Comment

by:kwh3856
ID: 20344843
I have set the server to SQL Server and Windows authentication but now I get Login failed for TestDomain\testuser.  I have logged in to the SQL server as the user to verify that my username and password is correct but still get this error message when I try to connect to it from my program.

Any ideas??

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 20344846
When you view the event log for SQL, here is what it says:

Date            11/24/2007 11:06:15 PM
Log            SQL Server (Current - 11/24/2007 11:06:00 PM)

Source            Logon

Message
Error: 18456, Severity: 14, State: 6.


0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 20344910
state 6 in the error message says means Attempt to use a Windows login name with SQL Authentication
you have to change the authentication mode to mixed mode.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20345207
can you please show the connection string? (with password cleared)
0
 

Author Comment

by:kwh3856
ID: 20345683
<add name="HTTConnectionString" connectionString="Data Source=FS;Initial Catalog=HPT;User ID=HDS\kenny; Password=notdisplayed"
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20345688
>User ID=HDS\kenny
you CANNOT use a windows user login in the connection string directly.
you can only use non-windows logins in the connection string, or request the current windows user account to be used:
remove the user ID and Password from the connection string, and place this instead:
Integrated Security=SSPI;

now, if this in the context of a web application, this will be a bit more complex, though...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20346176
<<Login failed for user 'TestDomain\testuser'. The user is not associated with a trusted SQL Server connection.>>Make sure that TestDomain\testuser has credentials on the db server...
0
 

Author Comment

by:kwh3856
ID: 20346301
AngelIII,
I set it to Integrated Security=SSPI and now I get

Server Error in '/HTT' Application.
--------------------------------------------------------------------------------

Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  


Any ideas?

Thanks
Kenny
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:kwh3856
ID: 20346316
Also,
The SQL log file now shows.

Date            11/25/2007 12:42:09 PM
Log            SQL Server (Current - 11/25/2007 12:42:00 PM)

Source            Logon

Message
Error: 18452, Severity: 14, State: 1.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20346422
as I said:
>if this in the context of a web application, this will be a bit more complex, though...

see this article:
http://www.codeguru.com/csharp/.net/net_security/authentication/article.php/c7725/
the parts "configuring IIS" and "configuring asp.net application" is what you need to look at.
0
 

Author Comment

by:kwh3856
ID: 20350107
Angel,
I went through and made those changes and now I get

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:


[SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]

 


Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 20350130
When I ran the application, the system did ask me to login this time.  I thought I logged in correctly but I am not sure why it saw me as an anonymous logon when I unchecked that box in IIS.  

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 20350181
Just as a test, I went back and checked the Anonymous login box in IIS and when I ran the program it did not ask me to login and then gave me the exact same error message.  I am not sure if it makes a difference but here is my basic configuration:

I am running Visual Studio 2005 on a Windows XP machine.  I am using IIS running on the XP machine.  This machine belongs to a domain.  SQL 2005 is running on a different server and that server is  also the domain controller.

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 20350201
Also, this application will ultimately be ran on the internet for an undertermined group of users.  With this type of configuration, will I have to setup a Windows Username for everyone of those users?

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20350310
>will I have to setup a Windows Username for everyone of those users?
yes.

now, please confirm that you also did the change in the web.config and restarted the IIS afterwards

<system.web>

<authentication mode = "Windows"/>

<identity impersonate="true"/>
 

<authorization>

   <allow users = "*"/>

</authorization>

<!--other settings-->
 

</system.web>

Open in new window

0
 

Author Comment

by:kwh3856
ID: 20350449
Here is my current web config:


----------------------------------------------------------------------------------------------------------


<?xml version="1.0"?>
<!--
    Note: As an alternative to hand editing this file you can use the
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in
    machine.config.comments usually located in
    \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
      <configSections>
            <section name="infragistics.web" type="System.Configuration.SingleTagSectionHandler,System, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      </configSections>
      <infragistics.web enableAppStyling="true" styleSetName="ElectricBlue" styleSetPath="~/ig_res/"/>
      <appSettings/>
      <connectionStrings>
  <add name="HTTConnectionString" connectionString="Data Source=TESTFS1;Initial Catalog=PTT;Integrated Security=SSPI; persist security info=False;Trusted_Connection=Yes"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
      <system.web>
            <!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.
        -->
            <compilation debug="true">
                  <assemblies>
                        <add assembly="Infragistics2.WebUI.Misc.v7.2, Version=7.2.20072.61, Culture=neutral, PublicKeyToken=7DD5C3163F2CD0CB"/>
                        <add assembly="Infragistics2.WebUI.Shared.v7.2, Version=7.2.20072.61, Culture=neutral, PublicKeyToken=7DD5C3163F2CD0CB"/>
                        <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                        <add assembly="Infragistics2.WebUI.WebDataInput.v7.2, Version=7.2.20072.61, Culture=neutral, PublicKeyToken=7DD5C3163F2CD0CB"/>
                        <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                        <add assembly="Infragistics2.WebUI.UltraWebGrid.v7.2, Version=7.2.20072.61, Culture=neutral, PublicKeyToken=7DD5C3163F2CD0CB"/></assemblies>
            </compilation>
            <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
            <authentication mode="Windows"/>-----------------------------------Here are the lines
    <identity impersonate="true"/>------------------------------------------------<<<<<<<<<<<-------------
    <authorization>
      <allow users="*"/>---------------------------------------<<<<<<<<<<<-----------------
    </authorization>
            <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
      </system.web>
</configuration>
0
 

Author Comment

by:kwh3856
ID: 20350465
Also,
If I do not want to have to setup a user for every user access the server.  Is there a different configuration that I need to use?  I do not want to have to worry about purchase an NT Client license for every user that accesses my application.

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 20350808
Additional Info:
SQL Log shows

Source            Logon

Message
Error: 18456, Severity: 14, State: 11.

State 11 is "Login is valid, but server access failed."  - Not sure what to do.

Thanks
Kenny

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20351553
>If I do not want to have to setup a user for every user access the server.
create a windows domain group, and create for that a login in sql server.
ensure that all windows account are part of that windows group, that will give them implicitly access in sql server also, via the nt group/sql login.

did you grant the login some permissions in the database?
0
 

Author Comment

by:kwh3856
ID: 20351767
For this particular database I have

Server Roles =
 bulkadmin
 dbcreator
 diskadmin
 processadmin
 securityadmin
 serveradmin
 setupadmin
 sysadmin


For this database if have a role membership set to
 db_accessadmin
 db_backupoperator
 db_datareader
 db_datawriter
 db_ddladmin
 db_owner
 db_securityadmin
 db_public

This user is granted permission to connect to the database engine
and the login is enabled


I just saw something intresting I am not sure how to interperate

Under the folder Security->Logins the user name is Domain\testuser
when I click on properties then status, under Authentication for the Username it shows Domain\Administrator

Does that mean I really need to login as the domain administrator to access this database?

Thanks
Kenny


0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now