web analytics
Understanding Authentication Mode in SQL Server Options
davegate
Posted: Thursday, December 24, 2015 7:00:27 PM

Rank:Advanced Member
Groups: Member
Joined: 12/14/2015
Posts: 182
Points: 894

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.

Sponsor
Posted: Thursday, December 24, 2015 7:00:27 PM
 
davegate
Posted: Thursday, December 24, 2015 7:03:22 PM

Rank:Advanced Member
Groups: Member
Joined: 12/14/2015
Posts: 182
Points: 894

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.

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.