0

I'm not able to use sql server authentication on the default instance.I have seen a lot of web pages and tried adding a new logins by right clicking on logins in security.Secondly changed the password of sa user but i have observed that in status the "SQl Server login is locked out".I have tried all this with admin rights and still not able to login.

user3145883
  • 1
  • 3
  • Is Mixed Authentication enabled? Per [MSDN](http://msdn.microsoft.com/en-us/library/ms144284.aspx), "If you select Windows Authentication during setup, Setup creates the sa account for SQL Server Authentication but it is disabled." – TTeeple Feb 04 '14 at 15:12
  • 1
    if you are not able to login how you are able to change the sa password? CURIOUS – Punter015 Feb 04 '14 at 15:13
  • if this is what @TTeeple says then login with windows authentication, enable mixed authentication, reboot the instance and then try logging with sa account (sql server authentication) – Punter015 Feb 04 '14 at 15:15
  • @Punter015 i'm able to use windows authentication – user3145883 Feb 04 '14 at 15:24
  • @TTeeple can you tell me how to enable the sql server authentication because i have opened secpol.msc but not able to see mixed authentication there – user3145883 Feb 04 '14 at 15:27
  • @user3145883 can you login to sql server management studion, if yes then i can provide a solution. – Punter015 Feb 04 '14 at 15:37
  • @user3145883 - On the Server Properties (Right-click the server after conecting to it in SSMS), head to the `Security` page, and you can pick between `Windows Authentication mode` and `SQL Server and Windows Authentication Mode` (which is mixed mode, and allows you to sign in using `sa` as a user. – SchmitzIT Feb 04 '14 at 15:44
  • @Punter015 yes i'm able to login using windows login – user3145883 Feb 04 '14 at 15:46
  • @SchmitzIT i'm not able to get it when i login to windows authentication and then expand security,logins and right click on sa user and then select server roles is that what you said – user3145883 Feb 04 '14 at 15:50
  • @SchmitzIT i have added windows authentication and sql server authentication but still not able to use sa.I wanted to know if i use sa then what would be the server name – user3145883 Feb 04 '14 at 15:57
  • @user3145883 Did you restart the SQL Server? Switching authentication mode will not change the servername, though. It just means that you're no longer limited to Windows users/groups, but can also use a username with password, providing it has been created on the SQL server. – SchmitzIT Feb 04 '14 at 16:06
  • @SchmitzIT i have restarted my instance but still when i login using windows authentication and then click on security/logins and then right click on my default instance all the login is locked out – user3145883 Feb 04 '14 at 16:17
  • @user3145883 It sounds as if your Windows user does not have SQL Server Administrator access. – SchmitzIT Feb 06 '14 at 10:05

0 Answers0