Connect to SQL Server 2000 through an ASP.NET Web Application

Hi!
I am going to connect to "SQL Server 2000" through an "ASP.NET Web Application". My development environment is "Visual Studio .NET 2003". In my "Toolbox" -> "Data", I drag a "SqlConnection" and drop on the page. It would automatically be named "SqlConnection1". In its "Properties" -> "ConnectionString" -> "<New Connection...>", I would enter my Server Name (SAMIEI), select "Use Windows NT Integrated Security" and Select the Database on the Server (Northwind). Then such Connection String would automatically be generated:

"workstation id=SAMIEI;packet size=4096;integrated security=SSPI;data source=SAMIEI;persist security info=False;initial catalog=Northwind"

Now, I may see "SAMIEI.Northwind.dbo" in the "Data Connections" row of "Server Explorer" with its all "Database Diagrams", "Tables", "Views", "Stored Procedures" and "Functions".
Please take a look at the following code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try
SqlConnection1.Open()
Catch ex As SqlException
Response.Write(ex.Message)
End Try

End Sub

It would unexceptedly encounter such error:

"Login failed for user 'NT AUTHORITY\NETWORK SERVICE'"

Please tell me the reason. I am much obliged to you for your attention.

Regards!
M.Sadegh Samiei

Hi Sadegh Samiei,

The code looks fine but connecting to SQL will fail because by default ASP.NET application will run under ASP.NET default user security context or IIS Anonymus user context. So inorder to run in others or in your security context goto web.config file set the impersonate attribute of identity element to true to run under login user security context mode if you want to run under specific user security context mode then username and password attributes in identity element

<configuration>

<system.Web>

<identity impersonate="true"/>

</system.Web>

</configuration

Regards,

|||

Hi,

The 'NT AUTHORITY\NETWORK SERVICE' is the ASP.NET user process used under Win Server 2003 and IIS 6. When you check the database you are working with, "Northwind", you may see that a user named ASPNET (the "user" for pre Win Server 2003) already has the necessary permissions, but you need to provide the correct permissions for 'NT AUTHORITY\NETWORK SERVICE'. If you don't have the that user set up on the database with proper permissions, you'll get a login error message - as you did. You can use SQL Server 2000's Enterprise manager to do the setup. Or you can use method suggested in the other reply. BRN..