none
Use sql auth or windows auth ? RRS feed

  • Question

  • We have many applicaitons that for the most part are used by our own internal users of the firm. We have around 500+ users. Wanted to know if we should build our apps to use SQL auth or Windows auth ? Some of these apps are thick client apps and others are web based.

    Are there any good whitepapers on how best to setup security for SQL Server when you build an application ? We are open to any.. Just want to know pros and cons of each.

     

    Saturday, October 15, 2011 4:40 PM

Answers

  • As stated in the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms144284.aspx), Windows authentication is much more secure and supports lockout and expiration.  A big plus for Windows authenticatin with rich client applications is that clients can connect directly to the database under their own security credentials.  Also, you don't need to take special measures to secure clear-text passwords.

    SQL Authentication is appropriate if you don't have an Active Directory infrastructure to support Windows authentication or need to connect from other operating systems other than Windows.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, October 24, 2011 5:43 AM
    Sunday, October 16, 2011 12:03 AM
    Moderator

All replies

  • As stated in the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms144284.aspx), Windows authentication is much more secure and supports lockout and expiration.  A big plus for Windows authenticatin with rich client applications is that clients can connect directly to the database under their own security credentials.  Also, you don't need to take special measures to secure clear-text passwords.

    SQL Authentication is appropriate if you don't have an Active Directory infrastructure to support Windows authentication or need to connect from other operating systems other than Windows.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, October 24, 2011 5:43 AM
    Sunday, October 16, 2011 12:03 AM
    Moderator
  • http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 16, 2011 8:57 AM
  • If you have a web based app( say microsoft.com) that uses a backened SQL database and say now you have internet users such as you and I wanting to connect to it , how do you set this up ? So will there will be a SQL auth from the web server to the database server because I cannot authenticate the internet users ?

    Sunday, October 16, 2011 5:05 PM
  • If you have a web based app( say microsoft.com) that uses a backened SQL database and say now you have internet users such as you and I wanting to connect to it , how do you set this up ? So will there will be a SQL auth from the web server to the database server because I cannot authenticate the internet users ?


    In this case, I would connect to the database using Windows authentication under the security context of the IIS application pool.  This would need to be a domain account in order to authenticate to a SQL Server running on a different machine using Windows authentication.  If it is necessary to authenticate internet users, that could be done separately and not related to the database security context.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, October 16, 2011 6:22 PM
    Moderator
  • If you have a web based app( say microsoft.com) that uses a backened SQL database and say now you have internet users such as you and I wanting to connect to it , how do you set this up ? So will there will be a SQL auth from the web server to the database server because I cannot authenticate the internet users ?


    Do you need to be able to uniquely identify the user within the database? For instance, do you need to track user access, activity, etc... within the database? If so, it might be easier to have a SQL Server login (assuming Windows login is not an option since web users are not part of any domain in your environment). You can certainly implement all the authentication, tracking, auditing, etc... features in your application and that is not uncommon but for environments that require uniquely identifying each user, it's just easier when you have SQL Server logins.

    Application logins, application roles or just a dedicated Windows or SQL Server login for the application are all possible solutions if you do not need to uniquely identify/track each web user. Your application will have the necessary authentication mechanism for users coming in and assign the apporpriate privileges but connection to the SQL Server instance is done via a completely separate login used by the application for all users. Some large ERP applications take this approach and it works fine but you will have to manage user access, authentication, tracking, etc...

     


    No great genius has ever existed without some touch of madness. - Aristotle
    Monday, October 17, 2011 8:22 AM
  • For web apps I would also look into the possibility for the web app to do delegation/impersonation of the windows account using the web app towards SQL Server. this is outside my area of expertise, but if infrastructure allows for it, you could be able to have your own unique identities in SQL Server (through windows group logins, typically) even when using some type of app server/web app.
    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, October 17, 2011 10:33 AM