Skip to content

change-server-authentication-mode.md

Latest commit

 

History

History
89 lines (61 loc) · 4.4 KB

change-server-authentication-mode.md

File metadata and controls

89 lines (61 loc) · 4.4 KB
title Change Server Authentication Mode | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
sa account
authentication [SQL Server], changing modes
server authentication mode [SQL Server]
modifying server authentication mode
ms.assetid 79babcf8-19fd-4495-b8eb-453dc575cac0
caps.latest.revision 31
author BYHAM
ms.author rickbyh
manager jhubbard
ms.workload Active

Change Server Authentication Mode

This topic describes how to change the server authentication mode in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. During installation, [!INCLUDEssDEnoversion] is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. After installation, you can change the authentication mode at any time.

If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password. The sa login can only connect to the server by using [!INCLUDEssNoVersion] Authentication.

In This Topic

Before You Begin

Security

The sa account is a well-known [!INCLUDEssNoVersion] account and it is often targeted by malicious users. Do not enable the sa account unless your application requires it. It is very important that you use a strong password for the sa login.

Using SQL Server Management Studio

To change security authentication mode

  1. In [!INCLUDEssManStudioFull] 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 [!INCLUDEssManStudioFull] dialog box, click OK to acknowledge the requirement to restart [!INCLUDEssNoVersion].

  4. In Object Explorer, right-click your server, and then click Restart. If [!INCLUDEssNoVersion] Agent is running, it must also be restarted.

To enable the sa login

  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 login.

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

Using Transact-SQL

To enable the sa login

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. The following example enables the sa login and sets a new password.

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

See Also

Strong Passwords
Security Considerations for a SQL Server Installation
ALTER LOGIN (Transact-SQL)
Connect to SQL Server When System Administrators Are Locked Out