Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database.

What is the best approach for setting up users to access the database etc.?

My plan was:

  • On web server, create a local user which the application pool runs under and assign it the necessary rights
  • On database server, create a SQL user that can log in to the database, have necessary permissions on tables/DBs etc. This user is what is included in the connection string from the MVC web application to connect to the database.

Is this the best approach - I have read in to mixed mode SQL Server with SSPI etc. and I am confused a little as to the best secure setup. Any tips or further information would be great.

I have read that SSPI is a means of providing an integrated secure trust relationship between the web servers and the back end database servers, without having to transmit sensitive user and password information. Buy I am wondering if what I have outlined sufficent. Note sensitive information such as SSN, credit card numbers etc may be stored in the SQL Server 2012 database.

share|improve this question
add comment

migrated from stackoverflow.com Oct 18 '12 at 8:33

This question came from our site for professional and enthusiast programmers.

2 Answers

I would use Application Roles. See this link http://msdn.microsoft.com/en-us/library/ms190998.aspx Also, Integrated security is more secure than Mixed Mode, so I would use a domain account, not a SQL account, to avoid having to have 2nd tier authentication enabled.

share|improve this answer
add comment

Let's look at this in a scalaing point of view. You are already going to be bound by PCI requirements due to the nature of the data you are storing.

You could start implementing PCI or SOX compliance measures right now, so if you ever do need them in the future you won't need to go back to it.

-Stick with AD authentication. It stops you from having to hardcode passwords in connection strings and it's a requirement of most security framework/standards. Plus AD is great for providing a solution to many other PCI requirements. Do you have these machines on a domain?

-Encrypt the connections between the web servers/sql server. This addresses your issue of the data being unencrypted. This is simple to do these days.

-Make your DBOwners all the service account that SQL Server runs on. Application users do not need to be DBO. This way you are protected from others logging in as the app and dropping tables/truncating tables.

-Consider TDE. Transparent Data Encryption is very easy and protects data 'at rest'. If someone get's a hold of your MDF/LDF/NDF, hard drive, or backups, they will also need the security cert to open up the DB. Do note going with TDE you lose any compression options you would have had.

Read: http://www.iis.net/learn/application-frameworks/install-and-configure-php-on-iis/secure-your-sql-server-database

share|improve this answer
    
Just because the OP stated the type of information he has in the database does not mean he is bound by PCI requirements. –  Shawn Melton Oct 19 '12 at 2:11
    
Hi Shawn, The OP explicitly stated "Note sensitive information such as SSN, credit card numbers etc may be stored in the SQL Server 2012 database" So it's fair game to bring up PCI compliance. ANYONE in the US or a major CC partner must adhere to PCI requirements. How often do we design a solution without thinking about security, then realize pesky 'security requirements' come in and have to re-design from the beginning? Solve it the design/strategy phase and you don't waste countless hours/dollars resolving it later. My question would be why WOULDN'T you bring up PCI in this case? –  Ali Razeghi Oct 19 '12 at 21:44
    
Point taken.... –  Shawn Melton Oct 20 '12 at 2:43
    
Thanks for this answer. I am little unclear on some aspects of it, hoping to get further info. What is "AD" authentication? –  amateur Oct 21 '12 at 18:07
    
AD authentication is 'Windows Authentication', and AD means Active Directory. There are 2 forms that you can authenticate into SQL Server: Windows (AD Or Local Windows User on that server) Or Mixed Authentication. Mixed let's you create users inside SQL Server itself and they can log into that user. –  Ali Razeghi Oct 21 '12 at 22:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.