ASPNET Account Login

Question

Post reply

ASPNET Account Login

Mauro Ciaccio

SSCrazy

Points: 2617
More actions
December 10, 2004 at 9:34 am

#85022

Hi,
I'm playing around with an application called BugNet [see GotDotNet Workspaces] which is used for SW bug reporting. The application installs with a Nant build file and I keep getting an error when the build tries to create the ASPNET account login.
I'm trying to grant db_owner priviliges to an ASPNET account which is running on a different machine from the SQL Server box.
The autogenerated script is the following:
     [echo] Adding ASPNET account to database
      [sql] SQL Statement:
      [sql] if not exists (select * from master.dbo.syslogins where loginname =
N'MPT\MAUROXP\ASPNET')
      [sql] BEGIN
      [sql] exec sp_grantlogin N'MPT\MAUROXP\ASPNET'
      [sql] exec sp_defaultdb N'MPT\MAUROXP\ASPNET', N'BugNetTests'
      [sql] END
      [sql] USE BugNetTests
      [sql] if not exists (select * from dbo.sysusers where name = N'ASPNET' and
uid < 16382)
      [sql] EXEC sp_grantdbaccess N'MPT\MAUROXP\ASPNET', N'ASPNET'
      [sql] USE BugNetTests
      [sql] exec sp_addrolemember N'db_owner', N'ASPNET'
      [sql]
      [sql]
BUILD FAILED - 0 non-fatal error(s), 4 warning(s)
C:\Work\BugNet\bugnet.build(163,6):
Error while executing SQL statement.:
NAnt.Core.BuildException: C:\Work\BugNet\bugnet.build(163,6):
Error while executing SQL statement. ---> System.Data.OleDb.OleDbException: Wind
ows NT user or group 'MPT\MAUROXP\ASPNET' not found. Check the name again.
To clarify, the full name + domain of my machine is MauroXP.MPT.LOCAL
I have never tried adding a login to a specific account on a machine. So what am I doing wrong?
Thanks for your help
December 13, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

Are both machines in a domain? If not, I've typically just used SQL authentication to get around this.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

ASPNET account is a Local account. Local accounts could not be accessed from another computer.

Please, run your ASP.NET application in the security context of the domain account to get around or as Steve says, use SQL Server standard logins

Could you please post a link to the bug tracking database?

Yelena

Regards,Yelena Varsha

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 3

Yelena and Steve,

thanks for the feedback. As for the domain setup, here is what I've got

- ASP.NET application running on IIS on my machine

- DB running on SQL Server running on separate server

- both my machine and SQL Server are part of the same domain

- I cannot change to SQL authentication without modifying the code, which I would really like to avoid.

I am assuming that the script I am trying to run is trying to set up Windows authentication on the database. Not having worked with Windows authentication in SQL, I just assumed it would allow me to grant access to individual accounts defined on other machines within the same domain. After all the original script I modified contained the following:

if not exists (select * from master.dbo.syslogins where loginname = N'@MachineName@\ASPNET')

BEGIN

exec sp_grantlogin N'@MachineName@\ASPNET'

exec sp_defaultdb N'@MachineName@\ASPNET', N'@DatabaseName@'

END

USE @DatabaseName@

if not exists (select * from dbo.sysusers where name = N'ASPNET' and uid < 16382)

EXEC sp_grantdbaccess N'@MachineName@\ASPNET', N'ASPNET'

USE @DatabaseName@

exec sp_addrolemember N'db_owner', N'ASPNET'

So the questions for me that still remain to be answered are:

- does the BugNet application required IIS & the ASP.NET account to be on the same machine as the SQL DB

- is the script incorrect? Or did I interpret it's purpose incorrectly?

Thanks for your help

Mauro

P.S. This is the link for the bug reporting tool (BugNet) in gotdotnet workspaces:

http://www.gotdotnet.com/workspaces/directory.aspx?&Column=ActivityPercentile&Direction=DESC&Page=&ST=BUGNET

If you have not yet used gotdotnet workspaces, be warned that they are not 100% reliable and are often not available.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Mauro,

Do I understand correctly that by N'@MachineName@\ASPNET' you mean ASPNET account on SQL Server machine this time? I am afraid it may not work. Reasons: SQL Server works with SIDs (Security IDs) of accounts which are globally unique (global unique identifiers) So:

IIScomputer\ASPNET account and SQLcomputer\ASPNET account will have different SIDs and the script will produce login failed or access denied errors. And as I explained in the previous post IIScomputer\ASPNET account stays on IIScomputer and will be nobody in SQL Server on SQLcomputer

This code will work if you run both IIS and SQL on the same machine.

Or you may modify web.config file for the app to run on the domain account and replace N'@MachineName@\ASPNET' with those credentials.

Did you work with ASP.NET security before?

Yelena

Regards,Yelena Varsha

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 5

Hi Yelena,

with regards to the ASPNET account N'@MachineName@\ASPNET', the configuration instructions for BugNet imply that the IIScomputer may be different from the SQLComputer. In which case the ASPNET account MUST be the one on the IISComputer. But from what you have said it appears that the IIS needs to be on the same server as SQL for authentication to work.

I have worked with multiple ASP.NET projects developed on one machine and with the DB on a separate machine. But in our case we have always used SQL Authentication and so have never had to deal with Windows accounts. Could you please clarify what you meant by modifying the web.config file to make the app run on the domain account?

Thanks

Mauro