login failed for user 'sa' in ms sqlexpress | Microsoft SQL Server, Error: 18456

Prem Murmu on 3/28/2021 6:28:28 AM

When you install sql server in your system, generally you get windows authentication login in microsoft sql server management studio. But we need sa as a username and some password to configure in ms sql server. 

For home use we recomend to install sqlexpress in system as data storage server. When we install sqlexpress server in local system, only windows authentication comes bydefault, so we have to configure it for sa and its password.

When you try to configure it, it takes your a bit much time, if you are not handy in sql server confiugration.

Here i am going to give some troubleshooting tips:


Configure sa password in Microsoft Sql Server Management Studio

1. Go to Microsoft Sql Server Management Studio => Security => Logins => Right Click 'sa" and click on properties => put new same password in both input box => Click OK.



2. Go to Microsoft Sql Server Management Studio => Security => Logins => Right Click 'sa" and click on properties =>Click Status => Under setting, check radio button Grant (giving permission to sa) and Enabled (enabling sa login) => Click OK.

Prefer below image:



Enable server authentication mode.

3. Right Click Server => Click properites => Click security =>  Under server authentication, check radio button sql server and windows authentication mode ( giving permission to sa ).



4. Once restart the sql server management studio and try to login with sa and password you gave.

Hope you will be able to login into the management studio with sa login credential.


If you find error like below:



If not able to login  continue the next steps.

5.  Execute the below sql script to

activate login mode in registry

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 

     N'Software\Microsoft\MSSQLServer\MSSQLServer',

     N'LoginMode', REG_DWORD, 1

GO

and changes password


ALTER LOGIN sa ENABLE ;  

GO  

ALTER LOGIN sa WITH PASSWORD = 'Novasqlpwd@16' ;  

GO  



6. Open sql server configuration manager

Make sure to enable all three things( Shared Memory, Named pipes, TCP/IP) in client protocols, protocols for SQLEXPRESS here in sql server configuration manager like below:





Now you will be able to login into sql server mangement studio.

If none these 6 steps work, then comment below.

I try to find out the possible solution for you.


0 Comments on this post

Comments(0)||Login to Comments



I've Been Vaccinated Wristband



work form home items,essentials,home office furnitures,laptops

afforable web hosting plans
Reliable Security & High Speed web hosting  plans
Microsoft Surface Book 3
InterServer Web Hosting and VPS
free .com domain and best cheap hosting plans
Premium Subscription Offer
  • see more..