what is the difference between sql server authentication and windows authentication...Is there any specific situation of using each authentication?
This question is related to
sql-server
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.
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.
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 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.
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.
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.
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.
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
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.
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.
Source: Stackoverflow.com