advice on best practice when connecting ASP.NET application to SQL Server

views:

answers:

+3 Q:

advice on best practice when connecting ASP.NET application to SQL Server

I have an ASP.NET application that uses the membership functionality. I connect to a SQL Server database that contains the aspnet_membership schema. I currently am using "sa" and the sa password in my application's connection string which, I know, is a horrible thing to do. My question is, what is the best alternative for the connection string? Should I create a user in the sql db name "MyAppUser" (or something) and assign the aspnet_membership_fullcontrol schema to that user? That way, the app can connect and get the full control priviledges, then limit the access for the individual users according to the logic I have written into the application.

Is that a good way to do it? (the access is limited to the particular database being used by the app, and not any of the others).

If not, what is a better way to get away from using user "sa" in my connection string while utilizing ASPNet membership?

Thanks for any advice you can give.

If this is an internal application, you can use windows authentication (integrated security = true), otherwise create a sql login for you app, map the db to the user and apply adequate permissions (probably read/write).

Mr Shoubs 2010-10-04 15:02:47

Thanks for your response. I am using SQL Server authentication, not windows. So I created a login, and gave it the permissions I needed to get everything to work. Unfortunately, it appears it will need dbo permissions to run the stored procedures that I have written. I would like change that so, I will keep investigating. This is definitely an improvement because I have at least limited access to only the database it needs so, that is progress. Thanks for your answer.

rogdawg 2010-10-04 15:53:50

you need to set the permissions on the stored procedure itself as well - right click on the SP and go to properties, then click permissions.

Mr Shoubs 2010-10-05 11:49:05

alternativly create a schema and assign your user to that, then create your sp's under that schema - have a read of this - http://vyaskn.tripod.com/sql_server_security_best_practices.htm , this http://msdn.microsoft.com/en-us/library/ms190387.aspx and just have a google on SQL Server security in general - there's a lot to it, but it's fairly straight forward in the end (though 2008 does make it more complex)

Mr Shoubs 2010-10-05 11:53:52

Yes - create a specific user and password for this project and have these details stored within the app.config or web.config file and go from there.

If you use a Trusted Connection, then you're not actually in control of which user has access to the database meaning debugging later is more difficult.

Brett Rigby 2010-10-04 15:22:54

you can get SQL server to tell you this - I can't remember what it is, something like sysuser, user, current_user, sys.user - something like that... you can set that as a default for a column, or use it in your query.

Mr Shoubs 2010-10-05 11:59:23

make sure you encrypt your password if your storing it in a config file...

Mr Shoubs 2010-10-05 11:59:59

For windows applications, we normally use windows authentication. If we do need to use a specific user/password, we will encrypt this information and store it as a appsetting key/value pair.

For asp.net applications, we normally create a sql login for that application and store the password in the web config. We would then encrypt the connectionstring section of the web config file

Please refer to the following link for instructions on how to do this: http://msdn.microsoft.com/en-us/library/ff647398.aspx

clyc 2010-10-04 16:03:31

ansaurus

tags:

views:

answers:

advice on best practice when connecting ASP.NET application to SQL Server

related questions