Command to set server authentication to SQL Server and Windows authentication mode

Question

Is there any way to check this radio button with a T-SQL command? I'm trying to script our database setup and this was one of the things that hung me up.

Do you see the "Script" button on the top of that dialog? It's there to help you with this. — David Browne - Microsoft
– David Browne - Microsoft, Commented Dec 13, 2019 at 20:00

Max Zolotenko · Accepted Answer · 2019-12-13 19:54:45Z

4

Try this:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'  
END as [Authentication Mode]

https://www.sqlserverlogexplorer.com/recognize-authentication-mode-in-sql-server/

answered Dec 13, 2019 at 19:54

Max Zolotenko

1,1427 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2019-12-13 21:24:28Z

2

To change the setting you need to change the registry, which can be done in SQL Server with:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

As mentioned in the comment, making the change in the UI, and then "Script action to New Query Window" will give you this answer as well.

edited Dec 13, 2019 at 21:24

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 13, 2019 at 20:05

Jamie F

23.9k5 gold badges64 silver badges78 bronze badges

Collectives™ on Stack Overflow

Command to set server authentication to SQL Server and Windows authentication mode

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related