Sql-server – Create a User and Password for ADO.net and SQL SERVER Authentication

Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication. The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method.

When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log into Windows (the OS). This token-based security is more secure than SQL Server's native username/password security.

In other words if you can, choose Windows Authentication. There are times though when you cannot get around SQL Server authentication (legacy application, third-party vendor, etc.).

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.