what is the differences between sql server authentication and windows authentication

Question

what is the difference between sql server authentication and windows authentication   Is there any specific situation of using each authentication

User · Answer

Authentication is the process of confirming a user or computer   s identity  The process normally consists of four steps  The user makes a claim of identity  usually by providing a username  For example  I might make this claim by telling a database that my username is    mchapple     The system challenges the user to prove his or her identity  The most common challenge is a request for a password  The user responds to the challenge by providing the requested proof  In this example  I would provide the database with my password The system verifies that the user has provided acceptable proof by  for example  checking the password against a local password database or using a centralized authentication server

User · Answer

SQL Server Authentication Modes  SQL Server 2008 offers two authentication mode options   Windows authentication mode requires users to provide a valid Windows username and password to access the database server  In enterprise environments  these credentials are normally Active Directory domain credentials   Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server

User · Answer

Mssql Authentication is highly preferable where possible  It allows you to conform with an existing windows domain already used at your workplace  and you don t have to know your user s passwords   However  It seems that it would not be possible for use in the occasion that the server s machine does not authenticate to your local workplace s intranet   If you use sql authentication  security will be entirely up to you   If you use microsoft authentication  security is essentially taken care of for you  but you ll be dealing with additional restrictions

User · Answer

If you wish to authenticate the users against windows system users  created by Administrator  then in that case you will go for Windows Authentication in your Application   But in case you want to authenticate the users against set of users available in your application database  then in that case you will want to go for SQL Authentication   Precisely if your application is an ASP NET web-app  then you can use standard Login controls which depend on Providers like SqlMembershipProvider  SqlProfileProvider  You can configure your login controls and your application whether it should authenticate against windows users or app-database users  In the first case it will be called Windows Authentication and the later will be known as Sql Authentication

User · Answer

I don t know SQLServer as well as other DBMS  but I imagine the benefit is the same as with DB2 and Oracle  If you use Windows authentication  you only have to maintain one set of users and or passwords  that of Windows  which is already done for you   DBMS authentication means having a separate set of users and or passwords which must be maintained   In addition  Windows passwords allow them to be configured centrally for the enterprise  Active Directory  whereas SQLServer has to maintain one set for each DBMS instance

User · Answer

SQL Server has its own built in system for security that covers logins and roles  This is separate and parallel to Windows users and groups  You can use just SQL security and then all administration will occur within SQL server and there s no connection between those logins and the Windows users  If you use mixed mode then Windows users are treated just like SQL logins   There are a number of features of each approach -  1  If you want to use connection pooling you have to use SQL logins  or all share the same windows user - not a good idea   2  If you want to track what a particular user is doing  then using the windows authentication makes sense   3  Using the windows tools to administer users is much more powerful than SQL  but the link between the two is tenuous  for instance if you remove a windows user then the related data within SQL isn t updated

User · Answer

When granting a user access to a database there are a few considerations to be made with advantages and disadvantages in terms of usability and security  Here we have two options for authenticating and granting permission to users  The first is by giving everyone the sa  systems admin  account access and then restricting the permissions manually by retaining a list of the users in which you can grant or deny permissions as needed   This is also known as the SQL authentication method  There are major security flaws in this method  as listed below  The second and better option is to have the Active Directory  AD  handle all the necessary authentication and authorization  also known as Windows authentication  Once the user logs in to their computer the application will connect to the database using those Windows login credentials on the operating system   The major security issue with using the SQL option is that it violates the principle of least privilege  POLP  which is to only give the user the absolutely necessary permissions they need and no more  By using the sa account you present serious security flaws  The POLP is violated because when the application uses the sa account they have access to the entire database server  Windows authentication on the other hand follows the POLP by only granting access to one database on the server   The second issue is that there is no need for every instance of the application to have the admin password  This means any application is a potential attack point for the entire server  Windows only uses the Windows credentials to login to the SQL Server  The Windows passwords are stored in a repository as opposed to the SQL database instance itself and the authentication takes place internally within Windows without having to store sa passwords on the application   A third security issue arises by using the SQL method involves passwords  As presented on the Microsoft website and various security forums  the SQL method doesn   t    enforce password changing or encryption  rather they are sent as clear text over the network  And the SQL method doesn   t lockout after failing attempts thus allowing a prolonged attempt to break in   Active Directory however  uses Kerberos protocol to encrypt passwords while employing as well a password change system and lockout after failing attempts     There are efficiency disadvantages as well  Since you will be requiring the user to enter the credentials every time they want to access the database users may forget their credentials    If a user being removed you would have to remove his credentials from every instance of the application  If you have to update the sa admin password you would have to update every instance of the SQL server  This is time consuming and unsafe  it leaves open the possibility of a dismissed user retaining access to the SQL Server  With the Windows method none of these concerns arise  Everything is centralized and handled by the AD    The only advantages of using the SQL method lie in its flexibility  You are able to access it from any operating system and network  even remotely   Some older legacy systems as well as some web-based applications may only support sa access   The AD method also provides time-saving tools such as groups to make it easier to add and remove users  and user tracking ability   Even if you manage to correct these security flaws in the SQL method  you would be reinventing the wheel  When considering the security advantages provided by Windows authentication  including password policies and following the POLP  it is a much better choice over the SQL authentication  Therefore it is highly recommended to use the Windows authentication option

User · Answer

Ideally  Windows authentication must be used when working in an Intranet type of an environment   Whereas  SQL Server authentication can be used in all the other type of cases   Here is a link which might help   Windows Authentication vs  SQL Server Authentication

User · Answer

SQL Authentication   SQL Authentication is the typical authentication used for various database systems  composed of a username and a password  Obviously  an instance of SQL Server can have multiple such user accounts  using SQL authentication  with different usernames and passwords  In shared servers where different users should have access to different databases  SQL authentication should be used  Also  when a client  remote computer  connects to an instance of SQL Server on other computer than the one on which the client is running  SQL Server authentication is needed  Even if you don t define any SQL Server user accounts  at the time of installation a root account - sa - is added with the password you provided  Just like any SQL Server account  this can be used to log-in localy or remotely  however if an application is the one that does the log in  and it should have access to only one database  it s strongly recommended that you don t use the sa account  but create a new one with limited access  Overall  SQL authentication is the main authentication method to be used while the one we review below - Windows Authentication - is more of a convenience    Windows Authentication  When you are accessing SQL Server from the same computer it is installed on  you shouldn t be prompted to type in an username and password  And you are not  if you re using Windows Authentication  With Windows Authentication  the SQL Server service already knows that someone is logged in into the operating system with the correct credentials  and it uses these credentials to allow the user into its databases  Of course  this works as long as the client resides on the same computer as the SQL Server  or as long as the connecting client matches the Windows credentials of the server  Windows Authentication is often used as a more convenient way to log-in into a SQL Server instance without typing a username and a password  however when more users are envolved  or remote connections are being established with the SQL Server  SQL authentication should be used

User · Answer

I think the main difference is security   Windows Authentication means that the identity is handled as part of the windows handashaking and now password is ever  out there  for interception   SQL Authentication means that you have to store  or provide  a username and a password yourself making it much easier to breach   A heap of effort has gone into making windows authentication very robust and secure   Might I suggest that if you do implement Windows Authentication use Groups and Roles to do it   Groups in Windows and Roles in SQL   Having to setup lots of users in SQL is a big pain when you can just setup the group and then add each user to the group    I think most security should be done this way anyway

[sql-server] what is the differences between sql server authentication and windows authentication..?

Examples related to sql-server