none
SQL 2012 Unable to login to management studio using SA password

    Question

  • Hi

    I have downloaded SQL 2012 Express to do some testing on a test environment. Install went fine and used an old database backup was used to restore the database from. However, I can only log in to the management studio by using windows authentication - SA login gives the following error...

    Cannot connect to <servername>.

    Additional information

    A connection was successfully established with the server, but then an error occurred during the login process. (Provider: shared memory provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    After googling this error, many articles pointed towards an incorrect password being used. I tried a different password and realised that the error message is exactly the same whether I type the right password or not. I tried editing the SA password via security/logins to ensure that the password was correct, but this hasn't helped.

    I am running SQL 2012 Express on a Windows 7 Laptop

    Any help would be greatly appreciated.

    Thanks

    Steve

    Monday, March 12, 2012 1:00 PM

Answers

  • Hello Steve,

    In SSMS logon to SQL Server with your windows account and connect the SQL Server in "Object Explorer". Open the properties of the SQL Server (by right mouse click) and in that dialog goto tab "Security"; at the top you will find the server authentification mode.

    If it was "Windows only" then you also have to activate the "sa" account and set a strong password; that's done in node "Security" in Object Explorer for that instance.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Steve_Flynn Monday, March 12, 2012 2:49 PM
    Monday, March 12, 2012 1:37 PM

All replies

  • Hello Steve,

    By default a SQL Server is installed with "Windows only" authentification mode, so you can use SQL Logins. Have you change the mode during installation and set a strong password for the "sa" account?


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Monday, March 12, 2012 1:03 PM
  • Hi Olaf

    I don't recall making any selection for authentication mode during installation. Is the installation is set up as 'windows only' , where would I find the setting for authentication mode? Yes, I have set a strong password for SA

    Thanks

    Steve

    Monday, March 12, 2012 1:28 PM
  • Hello Steve,

    In SSMS logon to SQL Server with your windows account and connect the SQL Server in "Object Explorer". Open the properties of the SQL Server (by right mouse click) and in that dialog goto tab "Security"; at the top you will find the server authentification mode.

    If it was "Windows only" then you also have to activate the "sa" account and set a strong password; that's done in node "Security" in Object Explorer for that instance.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Steve_Flynn Monday, March 12, 2012 2:49 PM
    Monday, March 12, 2012 1:37 PM
  • Hi,

    Enable SQL authentication in SQL server by after you connect using windows authentication right click on the server name (in SSMS) and choose properties then security and change it to SQL Server and Windows Authentication Mode

    and then go to security and make sure sa login is enabled.

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Monday, March 12, 2012 2:45 PM
  • Fixed! Thanks for your help
    Monday, March 12, 2012 2:49 PM
  • Be warned: there are malicious people out there who know "sa" is the default system administrator for SQL Server.  If your SQL port is open to the world and login via "sa" is enabled, you can expect robo-attacks against your server.

    Tuesday, March 13, 2012 8:14 PM
  • Thanks for the reply, yes I am aware of this. This instance is to be used purely for testing purposes with a backup of the live database on our local network only.
    Wednesday, March 14, 2012 3:39 PM