Solved

SQL Server Authentication for Web based application

Posted on 2006-10-24
14
300 Views
Last Modified: 2010-04-11
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
0
Comment
Question by:MrMalone
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 38

Expert Comment

by:younghv
ID: 17795295
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
 

Author Comment

by:MrMalone
ID: 17796790
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
 
LVL 9

Expert Comment

by:gpompe
ID: 17797023
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
 
LVL 38

Expert Comment

by:younghv
ID: 17797099
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17797109
What are you using for the front-end? ASP.NET?
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17797401
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
 

Author Comment

by:MrMalone
ID: 17801742
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 29

Expert Comment

by:Nightman
ID: 17801786
(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
 

Author Comment

by:MrMalone
ID: 17804990
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
 
LVL 3

Expert Comment

by:mahe2000
ID: 17809032
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
 

Author Comment

by:MrMalone
ID: 17810162
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
 
LVL 3

Accepted Solution

by:
mahe2000 earned 500 total points
ID: 17811403
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
 

Author Comment

by:MrMalone
ID: 17812428
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
 
LVL 38

Expert Comment

by:younghv
ID: 17812463
MrMalone,
I'm so glad you got the answer to your question.
Have a nice day.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now