Best practice question

I have a aspx page that needs data from sqlexpress. When I installed sqlexpress, I installed it with "mixed-mode" security. When a user visit's my page, by default the username is IUSR_MyMachine. So, I added this user to the SQLServer2005MSSSQLUSers$MyMachine$SQLEXPRESS group. I have a connection string that looks like:

"Data Source=MyServer\sqlexpress;Initial Catalog=MyDB;Integrated Security=True;Pooling=True"

At this point I can go on my merry way and log in and do my thing. My question is this, is this the right way to do this? Should I be adding IUSR to the sql group? Should I set up a sql user instead? What's the best practice for connecting to a db from an aspx page?

Any insight would be great.
Thanks ... Ed

This is a repost. I never got a response the first time.

I'm pretty sure that you made a major error just then. By adding the IUSR account to that group, you gave anonymous visitors full database admin rights. I *think* you want to set up a limited account, and grant it specific rights on specific databases or tables w/in databases. I'm poking around myself to figure out how to do it right (which brought me here), but I think your solution is like making everybody domain admin. Sure, everything works that way :), but some things you don't want to "work" for some users.|||In general the most secure way to configure this is to create a new local user called SQLReader. Then configure this local user inside SQL Server to only allow login to your specfic database, granting the user read-only access. This keeps the access very restricted. Then map the aspx page to the SQLReader account.

Of course I am leaving out all the gory details of mapping a specific aspx page to a specific user, this can be bloody difficult. One way to do it is to create a .NET component hosted in COM+ and set the COM+ package to run as a specific user (having the aspx page make calls to the component). You can also twiggle around with the web.config file in aspx to do this mapping.

However, since you already have all your desired users mapped to IUSR_MyMachine by default, then you can just skip the SQLReader part, just add IUSR_MyMachine as login to only the MyDB database and only grant read access and you are set. This is assuming you are ok for any user that hits your web site to read from the SQL database.|||Thanks for the response. You've given me a lot to think about. I appreciate the thoughtful comments.
Later ... Ed

best practice for lookup

say i have a customer.aspx that allows a user to enter in customer data.

on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customer

but some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"

Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?

I think this is more of a business decision. When you have to do reporting later on to track the sales, would you want to see "none" under sales rep?

|||

well, probably not, so assuming you don't - then is there even another way dealing with it assuming that in the underlying db, a customer must have an associated sales rep? in that reporting scenario, you'd have to write sql to filter out the "dummies" i guess.

|||

NuJoizey:

...assuming that in the underlying db, a customer must have an associated sales rep?

If you have to code according to that assumption, then you really have no choice than to use some sort of default value in your application with an Id of "999" or whichever you are comfortable with. Jjust make sure it doesnt get repeated. You can also use a negative value like -1 so there is no conflict with any number generated by SQL Server if your table grows.

|||

Another way to handle this is to store NULL in the sales rep ID. This is, in a sense, the more elegant solution since NULL means not known and you can do this and still maintain the foreign key relationship with your sales rep master table

However, many people don't like using NULL values because of their unintuitive behavior in select statements (eg, "select * from customer where SalesRepId <> 3" will NOT return rows where SalesRepId is NULL because NULL= unknown and if something is unknown it justmight be 3).

My advice is to follow ndinakar's suggestion and use a special value like -1

|||

great - thank you for the discussions