Unable to log into MSsql , user SA

Published by

Posted on November 29, 2015

Login failed for user ‘sa’. The user is not associated with a trusted SQL
Server connection.
you are getting this error because you have windows authenication enabled for login within your sql instance .
to login with mixed mode authenication you must enable the mixed mode within the registry.
HKLM/SOFTWARE/MICROSOFT/MICROSOFT SQL SERVER/MSSQL/MSSQLSERVER/LOGINMODE
right click and change the valuedata to (2) for mixed mode authentication. restart sql after you make the change
Enable the sa account
ALTER login sa ENABLE
ALTER LOGIN sa WITH PASSWORD = ” ;
GO

Reset SA password
Login into the sql server via the command line using the “osql -E or -U options
ALTER LOGIN sa WITH PASSWORD = ‘Newpassword’;
GO

Alternate SA password reset

* Start SQL Server Configuration Manager
* Stop the SQL services
* Edit the properties of the SQL Service
* Change the startup parameters of the SQL service by adding a -m; in front of the existing parameters
* Start the SQL services. These are now running in Single User Mode.
* Start CMD on tthe SQL server
* Start the SQLCMD command. Now you will see following screen
* Now we create a new user. Enter following commands
+ CREATE LOGIN recovery WITH PASSWORD = `TopSecret 1′ (Remember SQL server has default strong password policy
+ Go
o Now this user is created
* Now we grant the user a SYSADMIN roles using the same SQLCMD window.
+ sp_addsrvrolemember `recovery’, `sysadmin’
+ go
* Stop the SQL service again
* Change the SQL service properties back to the default settings
* Start the SQL service again and use the new created login (recovery in my example)
* Go via the security panel to the properties and change the password of the SA account.

Categories: SQL