User authentication with SQL Server in an Azure virtual machine

The default authentication mode for SQL Server in an Azure virtual machine (and in a SQL Server on-premises installation) is Windows Authentication mode. With this mode, users must connect and be authenticated using an Active Directory user account. In this mode, SQL Server Authentication is disabled and the SA login account is also disabled. You can change the SQL Server Authentication mode to mixed mode authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication always is available and cannot be disabled.

User authentication using mixed mode
If you do not join your Azure virtual machine to an Active Directory domain, you generally will use SQL Server Authentication for user authentication. SQL Server Authentication is less secure than Active Directory authentication for the following reasons:

 The encrypted SQL Server Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.

 If a user is an Active Directory domain user who has a login and password, the user must provide SQL Server login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time one connects to the database can be annoying.

 SQL Server Authentication cannot use Kerberos security protocol.

However, SQL Server Authentication may be the best option for SQL Server in an Azure virtual machine in the following scenarios:

 If the SQL Server database users do not have Active Directory user accounts and cannot be authenticated by an Active Directory domain

 If the web-based applications accessing SQL Server in the virtual machine allow users to create their own identities

 If the software applications accessing the SQL Server instance use a complex permission hierarchy based on known SQL Server logins

To use SQL Server Authentication, you must enable mixed mode authentication and restart the SQL Server instance. When you do so, take the following steps to increase the security for your SQL Server instance:

 Use an account other than the SA login for SQL Server administration.
 Enforce password expiration.
 Enforce Windows password policies, including password length and complexity.
 Require users to change their password at their next login.

When configuring SQL Server in an Azure virtual machine using the Resource Manager deployment model, you have the option to have Azure configure SQL Server for mixed authentication mode when SQL Server is configured on the Azure virtual machine. When you do so, the default SQL Server login account and password are the same as the local administrator account for the virtual machine, although you can define a different account. Password policies are enforced, and the SA login account is not enabled.

If you do not change the authentication mode during the initial configuration of the SQL Server instance from the SQL Server default of Windows Authentication mode to mixed mode, you can use Windows remote desktop to connect to the virtual machine and then use SQL Server Management Studio to connect to SQL Server locally and change the authentication mode if desired.

User authentication using Windows Authentication mode
If you join your Azure virtual machine to an Active Directory domain, you can leave SQL Server in Windows Authentication mode and require your users to connect and be authenticated using Active Directory authentication. When a user connects through an Active Directory user account, SQL Server validates the account name and password using the Active Directory principal token in the operating system. This means the user identity is confirmed by Active Directory. SQL Server does not ask for the password and does not perform the identity validation. Active Directory authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration.

In addition, using Windows Authentication mode enables you to manage authentication using groups rather than individual user accounts and avoid the proliferation of user identities across database servers.

Source of Information : Migrating SQL Server Databases to Azure