0

Hi. I'm new to ASP.net and I'm having a bit of trouble with loading information in from web.config. I've stored my database connection in the web.config and the rest of the code is in my code behind file. Here is the code behind file followed by the web.config extract. The error is caused on this line SqlConnection conn = new SqlConnection(strConnection); :

By the way, the database connection loads successfully and information is brought back when I hard code the string into the connection method.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class testDB : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
      try
      {
        String strConnection = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();

        //newLabel.Text = "Connection = " + strConnection;
        SqlConnection conn = new SqlConnection(strConnection);
        SqlCommand comm = new SqlCommand("select * from test_tab",conn);

        conn.Open();
        System.Diagnostics.Debug.WriteLine("ok");
      
        SqlDataReader reader = comm.ExecuteReader();

        while (reader.Read())
        {
          newLabel.Text += reader["name"] + "<br />\n";
        }

        reader.Close();
        conn.Close();
      }
      catch (InvalidOperationException eve)
      {
        System.Diagnostics.Debug.WriteLine("Test"+eve.Message);
        newLabel.Text = "Test" + eve.Message + "<br />" + eve.ToString();
      }
    }
}
<connectionStrings>
    <add name="DBConnection" connectionString="Server=localhost\\SQLExpress;Database=test;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

The error I'm getting from the exception is:

TestInstance failure.
System.InvalidOperationException: Instance failure. at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, ......

Edited by Ninjabear: Clarifying the problem &quot;By the way..&quot;.

4
Contributors
10
Replies
21
Views
7 Years
Discussion Span
Last Post by Ninjabear
0

Try with this connection string in the web.config file

<connectionStrings>
    <add name="DBConnection" connectionString="Data Source=localhost\SQLExpress;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Hi. I'm new to ASP.net and I'm having a bit of trouble with loading information in from web.config. I've stored my database connection in the web.config and the rest of the code is in my code behind file. Here is the code behind file followed by the web.config extract. The error is caused on this line SqlConnection conn = new SqlConnection(strConnection); :

By the way, the database connection loads successfully and information is brought back when I hard code the string into the connection method.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class testDB : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
      try
      {
        String strConnection = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();

        //newLabel.Text = "Connection = " + strConnection;
        SqlConnection conn = new SqlConnection(strConnection);
        SqlCommand comm = new SqlCommand("select * from test_tab",conn);

        conn.Open();
        System.Diagnostics.Debug.WriteLine("ok");
      
        SqlDataReader reader = comm.ExecuteReader();

        while (reader.Read())
        {
          newLabel.Text += reader["name"] + "<br />\n";
        }

        reader.Close();
        conn.Close();
      }
      catch (InvalidOperationException eve)
      {
        System.Diagnostics.Debug.WriteLine("Test"+eve.Message);
        newLabel.Text = "Test" + eve.Message + "<br />" + eve.ToString();
      }
    }
}
<connectionStrings>
    <add name="DBConnection" connectionString="Server=localhost\\SQLExpress;Database=test;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

The error I'm getting from the exception is:

TestInstance failure.
System.InvalidOperationException: Instance failure. at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, ......
0

Try with this connection string in the web.config file

<connectionStrings>
    <add name="DBConnection" connectionString="Data Source=localhost\SQLExpress;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

You were right Vimalrasa, it worked with the altered xml. What I don't understand is why the same connection string does work when hard coded but when placed into an external file it causes the exception error?

(The other suggestion about the connection string was accurate, but I changed it to toString just to see if I could get it to work that way.)

0

For some reason my db connection is no longer working again. I'm using the same connection string but now I get the error:

Cannot open database "test" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

The connection works if I load the file using the ASP.net web server. For example:

http://localhost:53830/test2/testDB.aspx

But if I load the file using the correct port for localhost then I get the error:

http://localhost:81/test2/testDB.aspx

What are these user accounts such as NT AUTHORITY\NETWORK SERVICE and what can I do to make the connection work?

I have checked the services used for this in windows and set them to manual from disabled but it makes no difference. I might try automatic but I don't know if it will make a difference.

0

Right click on your service in the Service Control Manager and you will notice a "Log on" tab that probably is using "Local System Account". This means your ASP.NET webserver is running under your user account (thus integrated authentication works) but IIS is running under another account, and integrated authentication breaks.

You should set Integrated Security=False in your connection string and supply an sql username/pass. I would not recommend using identitity impersonation or changing the service logon to fix this problem.

Connection string format:

public static string BuildSqlNativeConnStr(string server, string database, string username, string password)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", 
        server, 
        database,
        username,
        password);
    }
0

Right click on your service in the Service Control Manager and you will notice a "Log on" tab that probably is using "Local System Account". This means your ASP.NET webserver is running under your user account (thus integrated authentication works) but IIS is running under another account, and integrated authentication breaks.

Hi sknake. I went into the service and set it to sql authentication instead but I couldn't log on with any account (where is this account set?) I went into sql management studio and created an account but it doesn't seem to recognise this account. I also tried hitting advanced in the service's properties screen and then clicking find all. Selecting any of these user accounts also results in "Error 1069, the service did not start due to a logon failure" when I try to restart the service.

Edited
I also should mention that I've been trying to attach the database as part of getting my setup to work. I've been into "sql management studio" > right click databases > attach > add > selected my mdf file (with new name) > ok. The error I get is:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Edited by Ninjabear: n/a

0

You shouldn't change anything about the service, you should change the connection string to include a user defined on the SQL server. Undo your changes, add a user to SQL server, update your connection string, then it should be working.

1

You shouldn't change anything about the service, you should change the connection string to include a user defined on the SQL server. Undo your changes, add a user to SQL server, update your connection string, then it should be working.

After many hours work and a lot of reading, I managed to get it working with sql authentication. I had to alter settings in visual studio, sql server management studio, the sql server service and my web.config and code behind files.

Just in case anyone is reading this and is in the same position as me, I've written up my own instructions for making SQL Server work with ASP.net. These instructions will help you get a database connection working with "SQL Server Authentication":

--

Configuring VS and SQL Server to work together

SQL Server Management Studio Part

To set the general settings for management studio right click the computer name at the top of the tree and go to "properties".

Security

Set the "server authentication" method to "sql server and windows authentiation mode".
Set "failed logins only".

Open security > logins > right click logins and select "new login".

General

Select SQL Server Authentication

Uncheck "Enforce password policy".

Set the default database.

User Mapping

Select the required databases from the list marked "users mapped.."

Check the box which says "db_owner" from "database role membership for: master".

Securables
(taken from http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395)..

There's a fair chance that only reading and writing to tables is not enough. For instance, you could have a number of stored procedures that the user account must be able to execute as well. To grant the account execute permissions on your procedures, click Securables in the left hand pane and click the Add button. In the dialog that follows, choose All objects of the types and then click OK. Then check Stored Procedures and click OK. The list with Securables will be filled with a number of stored procedures, including your own. Locate your stored procedure, and then make sure at least Execute is selected in the Grant column of the Explicit permissions box.

Visual Studio Part

Under "data connections" in the "server explorer", right click the database and click "modify connection".

Set the authentication method to "use sql server authentication" and enter the username and password for the account configured in "sql server management studio".

Select the required database in this screen also.

Click test database to check it works.

Click "advanced" and copy the database string used in the connection code.

Services Part

Visit windows+r > type services.msc and go to properties for "SQL Server".
Go to "log on".
Enter "Network Service" and no password. To do this:

Go to "Browse" > "Advanced" > "Find Now" > Select "Network Service" > Ok > Enter no password > Ok > Restart the "SQL Server" service.

IMPORTANT: The "network service" account is equivalent to the ASPNET account often mentioned for windows XP. Aparently network service is used on "windows server 2003", but also on Vista/win7 it seems.

Edited by Ninjabear: n/a

Votes + Comments
great post
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.