Category: SQL Server

28 Jul 2016 05:43

Joined:	30/03/2016
Level:	Bronze
Points:	58

Points:	3

Changing authentication mode in SQL server

Hi,

Just now i have installed SQL server 2014, during installation it asks for mixed mode or windows authentication mode. I gave Win authentication mode, but now i want to change it as mixed mode.
Can anyone suggest me, how to change the mode without uninstalling SQL server.

Thanks in Advance

#767363

28 Jul 2016 10:10

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

You do not need to reinstall the SQL server. You can change it using the Microsoft SQL Server Management Studio. You can change the server authentication in the following way.
1. Right click the SQL Server.
2. Go to the Security
You can see the Server authentication. Select "Sql server and windows authentication"

By Nathan
Direction is important than speed

#767367

28 Jul 2016 22:08

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	3

Hi,
yes agree with Mr. Nathan:
Goto SQL Server Management Studio.
Login with credentials.
Goto Object Explorer and right-click the server then click Properties.
Goto Security page--> under Server authentication, select the new server authentication mode, and then click OK.
Then restart SQL Server Management Studio.

#767368

28 Jul 2016 22:14

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi,

Open SQL Server Management Studio Object Explorer, right click the server and then select Properties, in security page under Server authentication select the new server authentication mode then click OK and then acknowledge the same and then Restart the server.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#767376

28 Jul 2016 23:25

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	1

Hi

Start your Sql Server -> Select or type your server Name -> Select Your Authentication -> there 2 option have

1.Windows Authentication ,2. Sql Server Authentication.
Windows no need user id password but sql server Authentication need to user id and pwd
you can follow this steps then you can change your authentication mode.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#767379

29 Jul 2016 00:12

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

When you go for mixed mode then you need to use SqlServerAdmin (sa)authentication and as the policy of the SQL you can not keep sa password as empty, see below steps to change the authentication mode
To change security authentication mode

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.
4.In Object Explorer, right-click your server, and then click Restart. If SQL Server 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.

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

Changing authentication mode in SQL server

About dotnetspider.com

Quick Links

General

Services