We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL Server Authentication for Web based application

MrMalone
MrMalone asked
on
Medium Priority
323 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
Comment
Watch Question

CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
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

Author

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

Commented:
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.
CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
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

Commented:
What are you using for the front-end? ASP.NET?
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.

Author

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

Commented:
(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

Author

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

Commented:
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)

Author

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 :-)
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
MrMalone,
I'm so glad you got the answer to your question.
Have a nice day.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.