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.

2 Answers 2


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.


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

  • Just because the OP stated the type of information he has in the database does not mean he is bound by PCI requirements.
    – user507
    Oct 19, 2012 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? Oct 19, 2012 at 21:44
  • Point taken....
    – user507
    Oct 20, 2012 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, 2012 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. Oct 21, 2012 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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