I'm sorry if this should have been posted in the c# forums.....
I have created a database for my website and am using MS SQL. This database will need to be accessed by users on the site itself, and from a .NET application.
I am comfortable using MS SQL through .NET, but in the past, I have been the only one using the .NET application to access a database. Now that this database will need to be accessed from both my web site and a .NET application from multiple users, I am becoming paranoid about efficiency and security.
My current thought process:
1. User opens .NET app and is authenticated by running a query against the database for their login and password.
2. User is authenticated, and has access to records tied to their account in their current instance of the application.
3. User needs to retrieve records tied to their account. The .NET application runs a query using the standard assemblies that come with MS SQL server (SqlCommand, SqlDataReader, etc...).
4. The user needs to add a record, which is also done using the MS SQL server assemblies.
I was really hoping someone could provide some insight on how I should be handing the above. I understand that the domain host provides a SQL 2008 server, but I don't understand how I should be handing queries to it.
1. Should a request be sent from the application in some form of encrypted packet, then decoded server side to determine the proper query to be ran, then encrypt a packet to be sent back to the application?
2. Should I be adding permissions to the database itself for every user that will need access to it? (Number of users is unknown at this time, but could be high)
Thanks for you help with this issue.