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.
Best Answer
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.