SQL Query Throws Exception When Run From IIS Server?

Question

I built a simple ASP.NET web app using Web Forms. On the default page, a button click was used to run a SQL query, the output of which was added to three list boxes. the source code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace WebApplication3
{
public partial class _Default : Page
{

    protected void Button1_Click(object sender, EventArgs e)
    {


        SqlConnection myConnection = new SqlConnection("user id=WebApp;" +
                                    "password=[REMOVED];" + 
                                    "server=JC-SRV;" +
                                    "Trusted_Connection=yes;" +
                                    "database=Database; " +
                                    "connection timeout=30");

        string Command = ("SELECT *" + "FROM Table");


        try
        {
            TextBox4.Text = ("Attempting to connect to database...");
            myConnection.Open();
            TextBox4.Text = ("Success!");
            SqlCommand myCommand = new SqlCommand(Command, myConnection);
            try
            {
                SqlDataReader myReader = null;
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    ListBox1.Items.Add("Field 1 " + myReader["Test1"].ToString());
                    ListBox2.Items.Add("Field 2 " + myReader["Test2"].ToString());
                    ListBox3.Items.Add("Field 3 " + myReader["Test3"].ToString());

                }
            }

            catch (Exception ex)
            {
                TextBox4.Text = (ex.ToString());

            }

            finally
            {
                myConnection.Close();
            }


        }
        catch (Exception ex)
        {
            TextBox4.Text = (ex.ToString());

        }
    }
    }
}

This works fine when run from my development machine in Visual Studio 2013 and IIS Express, however, when I publish the project to my Server 2008 R2 box with IIS 7.0 installed the following exception is thrown:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'CHARMAN\JC-SRV$'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at WebApplication3._Default.Button1_Click(Object sender, EventArgs e)ClientConnectionId:0516c418-145d-4558-b9d7-ed2e55c74724

It may be worth noting that the server is a DC of a domain called CHARMAN and that JC-SRV is the machine name. The SQL 2014 server is running on this machine also.

Any help would be greatly appreciated.

The error is pretty strait forward..The machine in question in not allowed to login in the data base. — Ortiga, Jun 23 '15 at 19:13
When I use Trusted_Connection, I'm assuming windows authentication from the context my app is running. In my case it's usually the running user of the application pool of my site. In these cases, I've never had a username and password in the connection string. It looks like you may want sql authentication instead? — Jason Eades, Jun 23 '15 at 19:15
@Andre I can access the the database using those credentials from SMSS and the same machine... How would this be different when the app is in a web server? — Jake Charman, Jun 23 '15 at 19:21
@JasonEades I removed trusted_connection and still get the same error. The ID and password are for SQL authentication. What would you suggest? — Jake Charman, Jun 23 '15 at 19:22
Try something like this as your connection string: Data Source=server;Initial Catalog=MyDatabase;User Id=blah;Password=blah; — Jason Eades, Jun 23 '15 at 19:29
@JakeCharman Same exactly? The stack trace looks like login failed for a domain user rather than a sql account. — Jason Eades, Jun 23 '15 at 19:42
@JasonEades My apologies.. the new exception is too long for a comment but begins System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) --- – — Jake Charman, Jun 23 '15 at 19:54

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

Thanks to This Post I added

Integrated Security=False

To the connection string and it worked perfectly

edited May 23 '17 at 12:29

Community

1
1

answered Jun 23 '15 at 20:29

Jake Charman

111
2
2
11

score 0 · Answer 2 · answered Jun 23 '15 at 19:39

0

Contact your DBA and get the required permissions for "CHARMAN\JC-SRV$" on the SQL Server instance in question.

Note: I don't have privileges to add comments hence posting my comment as answer.

answered Jun 23 '15 at 19:39

AbdulM

1
7

Thanks for the suggestion but I am just playing around in my own network at home. Therefore there is nobody to contact. – Jake Charman Jun 23 '15 at 19:49
If you have SA role then provide Public role to this user on the databases you need to connect from security. (Atleast Public role. ) – AbdulM Jun 23 '15 at 20:42

SQL Query Throws Exception When Run From IIS Server?

2 Answers2