3

I'm getting the following error in my ASP.NET web application in IIS when running in Windows XP. I'm using Windows Authentication within the web application. I would normally use SQL Server Authentication (with mixed mode), but I do not have access to this database server, except read permissions. They only allow Trusted Connections (or Integrated Security=SSPI;) meaning using our Windows network authentication user.

Error:

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

.NET Code:

SqlConnection conn = new SqlConnection(
    "server=server_name;" +
    "Integrated Security=SSPI;" +
    "database=db_name;" +
    "connection timeout=30;");
SqlDataReader rdr = null;
List<Ticket> list = new List<Ticket>();

try
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT TOP 10 [HEATSeq],[AssignedBy] FROM [dbo].[Asgnmnt]", conn);
    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Ticket record = new Ticket();

        record.ticket_id = rdr[0].ToString();
        record.assigned_by = rdr[1].ToString();
    }
}
catch (Exception e)
{
    string x = "";
}
finally
{
    if (rdr != null)
    {
        rdr.Close();
    }
    if (conn != null)
    {
        conn.Close();
    }
}

It's the same type of issue that I answered in Jon Skeet's first question here. In Windows 7, you'd normally set the application pool to run using LOCAL MACHINE or NETWORK SERVICE (if you choose not to use SQL Server Authentication). Except I don't know where to change the user of the application pool in IIS 6.

User permission error when accessing "user instance" database from ASP.NET

I read this article, but this applies to IIS in Windows 7, not Windows XP. How can I resolve this in Windows XP. Note that when I run this code in a console application, it connects fine.

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

Community
  • 1
  • 1
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245

3 Answers3

1

Found the answer in MSDN.

http://msdn.microsoft.com/en-us/library/ab4e6cky(v=vs.80).aspx

In case the URL changes in the future, I'm pasting the text below from the documentation above. Fickle's answer is correct if dealing with Windows 7. From what I'm reading, you must give privileges to your computer's machine (running IIS in Windows XP) on the remote machine running SQL Server. I found it easier to just ask the DBA of SQL Server to create an account for me to log in via SQL Server authentication.

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

This error can take a variety of forms, including:

Login failed for user '(null)'

Login failed for user domain\username

Login failed for user computer\ASPNET

The likely cause is that you are working with a Web application and are trying to access a computer running Microsoft SQL Server. The specific error or exception that is raised depends on whether the Web server is also running SQL Server. This problem arises because the Web application is not passing valid credentials to SQL Server. In general, you can resolve this issue by doing the one of the following:

  • If the Web server is also running SQL Server, give the local ASPNET user login privileges (Windows 2000 and Windows XP) or give the NT AUTHORITY\NETWORK SERVICE login privileges (Windows Server 2003) on the server.

  • If SQL Server is running on a remote computer and the Web server is running IIS 6.0, give the Web server's machine account login privileges on the remote computer. The machine account is referenced as DOMAIN\MACHINENAME$.

  • Set up ASP.NET to run as a Windows domain user who has login privileges on the computer running SQL Server.

  • Use a connection string to use SQL Server standard security. To do this, you must configure the connection string with an explicit user ID and password.

For more information, see Accessing SQL Server from a Web Application.

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
0

Under ApplicationPools right click on the pool that is used by your site. Choose properties, then go to the "Identity" tab. Click on the configurable radio button and type in the credentials. If you do not have a user to use then go to the link I provided it will give you steps to create your user.

Custom Identity

Fickle Panther
  • 221
  • 1
  • 4
0

I was facing same issue while running my MVC application. I was getting error while connecting entity database on remote machine from MVC application as i was logged in by my window credentials.

After using below batch script i got logged in by remote machine credentials so that issue resolved and application successfully connected to database.

runas /user:corp\username /netonly "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe"

Please replace username with your corp username. It will prompt you command prompt, insert your password (Password will not visible). so that new instance will open for .net application which will be connected you by corp credentials

Sayali R
  • 1