MYPIBLOGs

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, 2  /* if it is sql express set value 2 */

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.