web analytics
Sign In | Register
Welcome, Guest

Understanding Authentication Mode in SQL Server

 Options
Previous Topic Next Topic

davegate 143 - 921
@2015-12-24 19:00:27

During installation, Microsoft SQL Server  is set to either Windows Authentication mode or Mixed Mode (Windows Authentication and SQL Server Authentication) . If you select Mixed Mode, you must enter and confirm the SQL Server system administrator (sa) password.

Windows Authentication Mode

When a user connects through a Microsoft Windows user account, SQL Server validates the account name and password using information in the Windows operating system. This is the default authentication mode, and is much more secure than Mixed Mode. Windows Authentication uses Kerberos security protocol, provides password policy enforcement in terms of complexity validation for strong passwords, provides support for account lockout, and supports password expiration.

Mixed Mode (Windows Authentication and SQL Server Authentication)

Enter and confirm the system administrator (sa) password when you choose Mixed Mode authentication. Setting strong passwords is essential to the security of your system. Never set a blank or weak sa password.

Selecting an Authentication Mode

Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible.
@2015-12-24 19:03:22

If Windows Authentication mode is selected during installation of SQL Server, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN statement.

Change security authentication mode

You can change the security authentication mode in SQL Server Management Studio.

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

Enable the sa login

You can enable sa login by using einther Transact-SQL or To Management Studio

To enable the sa login by using Transact-SQL

  1. Execute the following statements to enable the sa password and assign a password.

    ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

To enable the sa login by using Management Studio

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

  2. On the General page, you might have to create and confirm a password for the sa login.

  3. On the Status page, in the Login section, click Enabled, and then click OK.
Previous Topic Next Topic

Comments
You must Sign In to comment on this topic.


© 2024 Digcode.com