"ASPNET user" needs access to Database

[johnmcTemp]

2 Posts on my first day....I need to slow down.

Note: I have an ASP.NET Web app using ADO.NET.

Ok, I finally debugged my web app running on remote server enough to find out that when it creates an ODBC.NET connection it uses this new user account named, oddly enough, "ASPNET user."

What do I need to do to allow my web app to access a server local to the application AS WELL AS access a server on other machines in the domain?

Is it something like adding ASPNET user to the SQL SERVER database or giving ASPNET more user rights(which i dont like)?

- john

[pjpark]

The first one: add ASPNET as a user to your database.

A good security plan would be to give ASPNET only login rights to your database server, then use an Application Context to confer rights to different ASPNET applications or pages.

Hope this helps,

pjp

[johnmcTemp]

Hi pjp,
thanks for replying.

I figured the first would be the best choice. Can you explain a little more when you say "use an Application Context to confer rights to different ASPNET applications."

When I read your quote, is it related to what .Net docs refer to "Forms Authentication with cookies," which is what I am doing right now?

- john

[pjpark]

Actually it is related to SQL Server's security. I should have said use application roles instead of context. In the SQL Server Books Online go to Administering SQL Server/Managing Security/Advanced Security Topics/Establishing Application Security and Application Roles for details about application roles.

Good luck!

pjp

[johnmcTemp]

I will take a look at roles.

The problem I have now is this:
Machine A - the .NET Web app is on this machine, which uses the user ASPNET.

Machine B - The web app on A tries to connect to this machine's SQL Server. Since the ASPNET user is neither local to this machine or to the Domain (its only local to machine A) I'm not sure how this server will recognize a connection request..

Error occurs when A tries to .Open() a connection, with an error saying...

'ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'B\Guest'

my ASP.NET code looks like this:
OdbcConnection cn;
OdbcCommand cmd;
string MyString;
MyString="Select * from Customers";

cn= new OdbcConnection("DRIVER={SQL Server};SERVER=B;DATABASE=Northwind;Trusted_connection=true;");
cmd=new OdbcCommand(MyString,cn);
cn.Open();


I thought that machine A was using windows authentication to connect to B but I guess Im wrong...

john

[pjpark]

Your connection string is incorrect. It is not a trusted connection but integrated security that you want. Also, the System.Data.SQLClient classes are recommended for working with SQL Server as they provide the best performance. This is Visual Basic, but you get the idea.

        Dim cn As New System.Data.SqlClient.SqlConnection()
        Try
            cn.ConnectionString = "Integrated Security=SSPI;" & _
                                  "Persist Security Info=True;" & _
                                  "Initial Catalog=pubs;" & _
                                  "Data Source=MY_SERVER;"
            cn.Open()
            Response.Write("Connection Open")
        Catch ex As Exception
            Response.Write(ex.ToString)
        End Try

BTW, if you insist on using ODBC you will have to add the "Driver=" bit.