VS2010 ASP > SQL Connections

[davieeeee]

Hi,

I have an ASP.Net website running on IIS7.
The Application Pool is set to ASPv4 Classic.
Authentication Mode is set to ASP.Net Authentication & Windows Authentication

My question is, when I want to connect to SQL using a "trusted connection" in the Connection String, what user will actually be passed into SQL?

I thought it would pass my logon details, but it is coming back with an NT AUTHORITY\ANONYMOUS LOGON error, which seems to point towards it not passing in my logon.

Is this expected behaviour?

[danasegarane]

Will the db has the all the user logins created in it ?

Did you add <identity impersonate="true" /> in the web.config

[davieeeee]

No it wont, which brings up a good point.
I assume that I should not worry about individual user level access to the database then and just create a single ASP logon in SQL?

[danasegarane]

Yes Create single user login and password and then add this in the web.config file

<identity impersonate="true" userName="domain\user" password="password" />

[davieeeee]

By placing that in the web.config, will that still recognize the logged in user? (this is an intranet application)

I need the user to be recognized for authentication purposes (so I can control menu items etc) however access to SQL via a single logon is acceptable.

[gep13]

Hello,

If you are not going to provide user level permissions at the database, then you are not going to be able to pass the credentials of the logged in user to the database, as they will not be able to access the database.

Gary

[danasegarane]

By placing that in the web.config, will that still recognize the logged in user? (this is an intranet application)

I need the user to be recognized for authentication purposes (so I can control menu items etc) however access to SQL via a single logon is acceptable.

Yes that will consider as the logged in user. User.Identity.Name will give the user name and when connecting to user the system will try to connect using the impersonated user name and password..

[SeanGrebey]

If you are using a single SQL login, why not just use:

Dim strConnectString = _
"Persist Security Info=False;server=MyServer;" & _
"database=MyDB;User ID=MySQLLogin;Password=MyPassword"

[gep13]

Assuming of course the above was in a web.config file, so that you didn't have to re-compile your application, just to change the database connection string

Gary