How To Change Windows Authentication To Sql Server Authentication 2017

Hallmark is a process to institute a successful connection. SQL Server provides two types of hallmark modes to connect to their database instances. Authentication Mode is configured while installing SQL Server instance. We can likewise change SQL Server authentication mode later postal service installation. Below are two types of authentication modes in SQL Server.

• Mixed Mode ( Windows +SQL Server login )
• Windows Mode (Only Windows)

Mixed mode provides 2 ways to institute database connections. One is using SQL Server logins at SQL Server layer and another ane is using Windows domain user authenticated using active directory and whose login is created and mapped on SQL Server instance.

Windows Style has only one way to establish database connections and that is using windows domain accounts. Nosotros must map windows accounts on SQL Server starting time earlier making any database connections. This mode doesn't provide admission to SQL Server logins.

Read More to Abound:

• SQL Server Interview Questions & Answers on Indexes
• How to Fix for a Task Interview?
• SQL Server Alwayson Interview Questions & Answers
• SQL Server Replication Interview Questions & Answers

Why Windows Authentication Mode is More Secure in SQL Server?

Windows style is secure than Mixed mode authentication because mixed mode has SQL Server authentication which cannot utilize Kerberos security protocol. Windows account needs to be authenticated to domain active directory every fourth dimension y'all establish a database connection. Too, Windows provides boosted password policies that are not available for SQL Server logins and that is why Microsoft e'er recommends to utilise Windows authentication mode wherever information technology's possible.

One more than risk with Mixed style is the encrypted SQL Server Hallmark login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the countersign at the client. These are additional attack points.

Change Authentication Mode in SQL Server

Yous can configure hallmark way in SQL Server during installing it to your organization. SQL Server installation windows will allow you to choose any one of to a higher place given authentication modes. If you will choose mixed authentication mode then you demand to provide sa password every bit per windows password policy. You can besides change existing hallmark way setting to another one equally per your business demand.

Let's assume you have installed SQL Server with Mixed authentication way and now you have to change information technology to Windows Authentication mode. This section will explain stride by pace process to change SQL Server hallmark style.

We can set up or change authentication manner in SQL Server either using SQL Server management studio or using T-SQL statements.

Using SQL Server Direction Studio

1. Launch SQL Server Management Studio, connect to your target SQL Server instance.
2. In SQL Server Management Studio Object Explorer, right-click the server, and then clickBackdrop.
3. On theSecurity page, underServer hallmark, you tin can see both authentication modes. Select any as per your need and so clickOK.
4. This change will require SQL Server service restart. A popup window volition announced to ask you lot to restart the service when you will click at OK button in to a higher place step.
5. In Object Explorer, right-click your server, and so clickRestart. If SQL Server Agent is running, it must also exist restarted.

You have changed your hallmark mode from Mixed style to Windows using SQL Server Management Studio.

If you are changing authentication mode from Windows to Mixed manner and then make certain to enable SQL Server login sa account. This account gets disabled during windows authentication way to secure your SQL Server case every bit sa is generic SQL Server login equally it tin exist targeted past hackers to access your instance. Beneath are the steps to enable this account:

1. In Object Explorer, expandSecurity, expand Logins, right-clicksa, and then clickBackdrop.
2. On theGeneral page, you lot might take to create and confirm a countersign for thesa login.
3. On theCondition folio, in theLogin section, clickEnabled, then clickOK.

Using T-SQL Statement

Microsoft has provided an extended stored procedure to chnage authenication mode in SQL Server using T-SQL argument. This extended stored process chnages windows registry entries and so exist conscientious while irresolute hallmark way in SQL Server using T-SQL statements. Make sure to take a backup of windows registry before executing this script.

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

Above control has again changed authenticating way from mixed to windows.

If you are doing other way effectually then make sure to enable sa login as i suggested above. Yous can as well use below T-SQL statement to enable sa account.

Change LOGIN sa ENABLE ;
GO
Alter LOGIN sa WITH PASSWORD = 'Strong-PASSWORD' ;
GO

Hither, i accept explained how to alter SQL Server hallmark manner from Windows to Mixed mode or vice versa. Please annotate us if y'all have any feedback on this commodity.

Related Articles:

• Ready SQL Server Mistake 18456: Login failed for user 'User_Name'.
• How to Modify SQL Server Instance Name?
• Create a Logon Trigger to Restrict sysadmin logins to connect to SQL Server for given Time Interval
• Set up Error 18452: Login failed. The login is from an untrusted domain

• Writer
• Recent Posts

I am working every bit a Technical Architect in 1 of the superlative IT consulting business firm. I accept expertise on all versions of SQL Server since SQL Server 2000. I take pb multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting piece of work.

Source: https://techyaz.com/sql-server/change-authentication-mode-in-sql-server/

Posted by: rubiohadvaid.blogspot.com

Share this post