SQL Server Authentication for Web based application

Hello experts,

I'm looking into SQL Server authentication for a web based application using asp.net.  All the standard literature I can find recommends using Windows authentication mode as opposed to SQL Server Authentication as this way no database passwords are sent over the network or stored in the application.  However I can see a major flaw in this security model in that users will be able to effectly bypass my application and access the database directly, making edits, etc that won't get logged.  My plan was to use a table that logs all activity on the system and maintains a full audit trail of the records within the system.

- I want to be 100% confident that any database access is done via my application (and hence logged) and not via any other means.

- I want to store potentially sensitve information and therefore using SQL Server Authentication is not really an option either as this would mean hard coding the database password within a code behind page which could be potentially reverse engineered and got at.

Any ideas?

Cheers,

M
MrMaloneAsked:
Who is Participating?
 
mahe2000Connect With a Mentor Commented:
it works like this:
1 - you add your users to the database without any permissions on objects
2 - you create the application role and asign to it the permissions (you have to set a password)
3 - when a user starts your program, in the program you assign him them the application role dynamically so he gets the permissions he needs

and that's all folks..!!!!!!

the only limitation you have is that he cannot access more than one database once you set him the app role.
0
 
younghvCommented:
Hi MrMalone,
You might want to put a 20 point 'Pointer' question over in the "SQL" Forum.
The folks over there are well-versed in this.
Just open a new post (minimum 20 points) with a title like "500 easy points" and include the URL of this question.

When your question is answered, you can request a refund of the 20 points.

Good Luck,

Vic
0
 
MrMaloneAuthor Commented:
Thanks Vic,

Unfortunately I seem to have used up all my points in submitting this question so I can't do that!! Silly me!

Hopefully someone might find this post that can help me otherwise I'll ask for it to be moved to the SQL section.

Cheers,

M
0
The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

 
gpompeCommented:
The usual approach in these cases is using a different user for the application.(You can still use windows authentication)

If this is not an option you can restrict access to server at OS level.
0
 
younghvCommented:
M - It's covered. I'm interested in the answer myself.

You may also want to 'browse' through the SQL forum looking for further information.


Vic
0
 
NightmanCTOCommented:
What are you using for the front-end? ASP.NET?
0
 
AaronAbendCommented:
The authentication mode will not affect how people can get to data - only how they log into the database.

With SQL authentication, a user with no privileges on the server can get to the database. With Windows Authentication, the user has to be enabled in the server AND the database.

0
 
MrMaloneAuthor Commented:
Vic, thanks.  Hopefully more people will find the post now :-)

I just want to clarify the problem for everyone because maybe I didn't explain it very well in the original post...

As far as I can tell I have 2 options,

(1) Use Windows Authentication Mode i.e. a connection string like "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" and set up my Domain users with the relevant access rights in SQL Server.

(2) Use SQL Server Authentication, set up ONE user for the Application, store these details within my application and use a connection string like "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=myUsername;Password=myPassword;"

The problem with (1) is that a legitimate user with knowledge of the database could write a program (or simply a VB script on his desktop!) that connects to the database using ADO and makes changes to the data without this being logged in my [ActivityLogs] table.

With (2), users cannot do this unless they somehow find out the UserID and Database password which could be possible given that it is stored in the application and also sent over the network (if the database server is different to the IIS server).

Neither of these solutions seem adequate to me as they both have security flaws.  I'm leaning towards (2) at the moment with some obfuscation of the code to prevent reverse engineering and using encryption on the data stored to prevent database administrators from being able to view the data.

Any other ideas?  Better solutions than this?

Cheers,
M
0
 
NightmanCTOCommented:
(2) with encryption is usually fine for most applications, as this is not visible outside of the network.
  - some examples of encryption in the web.config (still assuming that you are using ASP.NET):
http://msdn2.microsoft.com/en-us/library/ms998280.aspx
http://www.codeproject.com/aspnet/secure_connectionstrings.asp
http://www.codeproject.com/aspnet/WebFarmConnStringsNet20.asp

Alternatively, the proces could be using impersonation - i.e. a dedicated Windows account and all connections are made from the application in this context.
http://msdn2.microsoft.com/en-us/library/ms998280.aspx
http://www.dotnetjohn.com/articles.aspx?articleid=19
http://www.informit.com/articles/article.asp?p=29469&seqNum=3&rl=1
0
 
MrMaloneAuthor Commented:
After much searching and investigation I found this web site...

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx 

which explains the security model I need quite well.

My new plan is to switch to (1) - i.e. Windows Authentication Mode and use Views and Stored Procedures for database access and updates / inserts.  The Users won't be able to access the tables directly and will only be able to update data via the stored procedures.  The logs can then be actually generated from within the stored procedures (rather than my application) so that if a user say carries out an UPDATE then the stored procedure will log this in my [ActivityLogs] table and record which user carried out the UPDATE.  This will then get logged even if the USER has knowledge of the database and writes his own script to connect to the database and execute a stored procedure, it will be no different to if he did it via the actual application!

I'm quite pleased with this idea :-) Can anyone see a vulnerability??

M
0
 
mahe2000Commented:
i think the best for that is use application roles, the password for activating the role is sent encrypted (you can sniff it) and you can keep the windows authentication (I think it is better than sql authentication)
0
 
MrMaloneAuthor Commented:
Thanks mahe2000, you might be on to something there with "Application Roles" in addition to "Windows Authentication"...

I found this site http://www.databasejournal.com/features/mssql/article.php/3363521 ... I'll have to spend some time reading up on Application roles and exactly how they work but this looks promising :-)
0
 
MrMaloneAuthor Commented:
Thanks mahe2000, I think that will do the job nicely ;-)  

I'm still going to restrict all UPDATES and INSERTS via a stored procedure and log from there so that nothing can happen without it getting logged.  There's still the possiblility that someone can find out the application role's password, write their own app and therefore bypass the application (given that they are a legitimate user - which most hackers are anyway!).  So long all all updates and inserts are done via the stored procedures (and therefore their details logged) at least I'll be able to see which domain account was used if anything nasty was to happen.  Of course this relies on the hacker not gaining access to another users domain logon... but that's another story.
0
 
younghvCommented:
MrMalone,
I'm so glad you got the answer to your question.
Have a nice day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.