Deploy Problem with ASP.NET SQL Server connection issues

I have been working on an asp.net/sql server project for the last month and finally got to the point where I need to deploy it to IIS(port80)/ inetpub/wwwroot and run it from localhost instead of from within Visual Studio. The project deploys fine but when I go to the page with the first database connection, I get an error that basically says my table in the datatable can not be found. Since the project works great from Visual Studio 2005 I can only assume that there is a problem connecting to sql server. My sql server is set up to allow remote connections. I have tried it using windows authentication and sql server authentication. Is there another configuration setting I'm missing or what?

Thanks,
Ted

re: Deploy Problem with ASP.NET SQL Server connection issues

What's your connection string? How did you connect to your db? Could you issue a generic t-sql just to check if you are connected? You mentioned that the error showed that your table is missing. I'm not sure if you are connected or you just need to specify the full qualifier in accessing objects (ie dbname.owner.object).

-- CK

re: Deploy Problem with ASP.NET SQL Server connection issues

How did I connect:

public DataSet GetUID(string uid)
{ DataSet ds = new DataSet();
sqlComm = new SqlCommand();
sqlComm.Connection = connectionString;
sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "Client_GetUID"; sqlComm.Parameters.Add("@uid", SqlDbType.Text, 16).Value = uid;
try
{
Helper.FillDataSet(sqlComm, ds, "Users"); sqlComm.CommandText, ds, tableNames, SearchValue);
if (!EventLog.SourceExists(sSource))
{
EventLog.CreateEventSource(sSource, sLog);
}
sEvent = "App_Code/DataTier.GetUID Normal Operation"; EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Information, 301);
}
catch (SqlException ae)
{
if (!EventLog.SourceExists(sSource))
{
EventLog.CreateEventSource(sSource, sLog);
}
sEvent = "App_Code/DataTier.GetUID:" + ae.ToString(); EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Warning, 101);
}
return ds;
}

connection string:

Data Source=TedsLaptop;Initial Catalog=VoterTest;Integrated Security=True;Uid=teddarr;Password=xxxxxxxx" providerName="System.Data.SqlClient"

Also tried this using windows authentication
Data Source=TedsLaptop;Initial Catalog=VoterTest;Integrated Security=True;" providerName="System.Data.SqlClient"

I've just finished putting some event logging code into the program, but the data I got back so far is useless because I haven't put it in early enough in the workflow.

Everything is telling me I am not getting a connection with the database, I just don't know why, nor have I proven 100% that this is what is happening.

Any help is appreciated.

Ted

re: Deploy Problem with ASP.NET SQL Server connection issues

I don't see any problem with your connection string (or maybe am just not seeing it). Try creating a sample apps that uses your connection string and connect to your server. Then do a simple t-sql.You have to isolate the problem first. Since you're not sure yet if it's really a connection problem, might as well find out first.

-- CK

re: Deploy Problem with ASP.NET SQL Server connection issues

I know the connection string works from the app when I run it in Visual studio. I use the same connection string from windows apps that I have currently in production.

The problem occurs only after I publish the project to the inetpub/wwwroot folder and run http://localhost from a browser.

I think it is a sql server configuration problem.

I've just littered the login.aspx page with event logging statements. Hopefully there will be a clue in there. I'll put in a method that checks for an open connection and call it from the Page_Load method to see what it says.

I'll run it tonight and report the results.

re: Deploy Problem with ASP.NET SQL Server connection issues

The only message that I am getting is "Cannot open Database xxxxx requested by the login. The login failed. Login failed for user 'ComputerName\ASPNET'. at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owingingObject.

This is the text of the SqlException that is recorded in the event viewer.

Everything works great until I publish to inetpub/wwwroot. I'm stumped.

Could it have something to do with database ownership?

re: Deploy Problem with ASP.NET SQL Server connection issues

I think your website is using ComputerName\ASPNET to connect to your sql server. Did you configure your IIS to use Integrated Windows Authentication?

-- CK

re: Deploy Problem with ASP.NET SQL Server connection issues

I think you may be hitting on part of the problem.

I have found a website that walks through the configuring of both sql server and the asp.net application. It addresses different scenarios and should get me on the right track.

Here is the link for anyone else who has this problem:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395

re: Deploy Problem with ASP.NET SQL Server connection issues

The steps in the article listed above did lead me to a successful conclusion. It all had to do with 2 missing steps in the security configuration.

Thanks for your help.