p2p.wrox.com: Archives - aspx

aspx_beginners thread: Re: Connection to the Northwind database in SQL Server 2000

Message #1 by "Charles Lun" <charleslun@h...> on Tue, 04 Jun 2002 11:47:57 -0700

I cannot get the connection to my Northwind database in SQL Server 2000.  My 
SQL Server 2000 is installed locally in my PC and I am using the Windows 
authentication for the connection. I have followed the book example in 
"Beginning ASP.NET using VB.NET" and it returns Connection failed to open 
error.

Below is my code:
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

<script language="VB" runat="server">
  Sub Page_Load()
    Dim strConnection as String = "user id=sa;password=;"
    strConnection += "database=northwind;server=charlesL;"
    strConnection += "Connect Timeout=30"
    data_src.text = strConnection

    Dim objConnection as New SqlConnection(strConnection)

    try
      objConnection.Open()
      con_open.text="Connection opened successfully.<br />"
      objConnection.Close()
      con_close.text="Connection closed.<br />"
    catch e as Exception
      con_open.text="Connection failed to open.<br />"
      con_close.text=e.ToString()
    end try
  end Sub
</script>

<html>
  <body>
  <h4>Testing the data connection
  <asp:label id="data_src" runat="server"/></h4>
  <asp:label id="con_open" runat="server"/><br />
  <asp:label id="con_close" runat="server"/><br />
  </body>
</html>

It returns the following error:

Testing the data connection user 
id=sa;password=;database=northwind;server=charlesL;Connect Timeout=30
Connection failed to open.

System.Data.SqlClient.SqlException: Login failed for user 'sa'. Reason: Not 
associated with a trusted SQL Server connection. at 
System.Data.SqlClient.SqlConnection.Open() at 
ASP.sql_connection_aspx.Page_Load()

Please let me know how to resolve it.

Thanks,
  Charles




_________________________________________________________________
Join the world?s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com

Message #2 by Imar Spaanjaars <Imar@S...> on Tue, 04 Jun 2002 22:03:05 +0200

Apparently, your SQL Server runs in "Windows Authentication" mode. This 
means you can't pass a user ID and a password to it, but that it uses the 
user context of the calling application. SQL Server supports two security 
mechanisms: Trusted Connections / Integrated Security and SQL Server 
authentication. The connection string you use uses SQL Authentication, but 
the SQL Server is not set up to allow it.

In normal situations (not .NET), the anonymous account that IIS uses is 
USR_MachineName so you should give that one access to the SQL Server.
Depending how you have set up your .NET installation, you also need to 
grant the user ASPNET access to SQL server. This account is created when 
you installed the .NET framework.

If you have .NET installed on a PDC, stuff is even a little more different. 
Numerous post have been made on these lists about this topic, so check the 
archives.

You definitely want to check out the following article (might wrap):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/authaspdotnet.asp?frame=true

This describes how security in ASP.NET works in great detail.

HtH

Imar

At 11:47 AM 6/4/2002 -0700, you wrote:

>I cannot get the connection to my Northwind database in SQL Server 
>2000.  My SQL Server 2000 is installed locally in my PC and I am using the 
>Windows authentication for the connection. I have followed the book 
>example in "Beginning ASP.NET using VB.NET" and it returns Connection 
>failed to open error.
>
>Below is my code:
><%@ import Namespace="System.Data" %>
><%@ import Namespace="System.Data.SqlClient" %>
>
><script language="VB" runat="server">
>  Sub Page_Load()
>    Dim strConnection as String = "user id=sa;password=;"
>    strConnection += "database=northwind;server=charlesL;"
>    strConnection += "Connect Timeout=30"
>    data_src.text = strConnection
>
>    Dim objConnection as New SqlConnection(strConnection)
>
>    try
>      objConnection.Open()
>      con_open.text="Connection opened successfully.<br />"
>      objConnection.Close()
>      con_close.text="Connection closed.<br />"
>    catch e as Exception
>      con_open.text="Connection failed to open.<br />"
>      con_close.text=e.ToString()
>    end try
>  end Sub
></script>
>
>
>
>Testing the data connection
>
>
>
>It returns the following error:
>
>Testing the data connection user
>id=sa;password=;database=northwind;server=charlesL;Connect Timeout=30
>Connection failed to open.
>
>System.Data.SqlClient.SqlException: Login failed for user 'sa'. Reason: Not
>associated with a trusted SQL Server connection. at
>System.Data.SqlClient.SqlConnection.Open() at
>ASP.sql_connection_aspx.Page_Load()
>
>Please let me know how to resolve it.
>
>Thanks,
>   Charles

Return to Index

P2P Archives