1

I am trying to install DNN using Web Platform Installer. It tries to connect to .\SQLEXPRESS, which I have installed along with SQL Server, and wants me password for sa. When I provide it with a password, it says "password invalid or cannot connect to database".

I thought to myself there might be a problem with the sa account. I opened SSMS 2012. There is a red arrow downward sign on the sa account. Whenever I am trying to change login status to Granted it opens up an error message:

error 15151
Cannot alter the login 'sa' because it does not exist or you don't have permission.

Nonetheless I tried it with administrator account and also by disabling UAC. None worked. Screen shot follows:

enter image description here

Details:

  • SQLEXPRESS version: 10.0.2531
  • SQL Server version: 11.0.2100
    Update: Following some guidelines I tried to change authentication in SQL Server Management Studio from Windows Authentication to Mixed Authentication, but it failed with the following permission error:
    enter image description here
Mehdi Haghgoo
  • 3,144
  • 7
  • 46
  • 91
  • I also checked out http://stackoverflow.com/questions/17788853/sql-server-2012-cannot-alter-the-login-sa, but it was no help either. – Mehdi Haghgoo Nov 23 '14 at 04:11
  • 1
    Make sure you have SQL (mixed mode) authentication enabled, not just Windows authentication. – slugster Nov 23 '14 at 04:26
  • @slugster I am receiving permission error when trying to change authentication mode both using SSMS and Transact-SQL – Mehdi Haghgoo Nov 23 '14 at 04:50

1 Answers1

3

Make sure you're connecting with a server login that has the sysadmin server-level role. You'll need that permissions level to do what you're trying.

Check the server authentication mode. If it is or was Windows Auth only, the sa account is automatically disabled. Note that if SQL Authentication was disabled and later turned back on, the sa account will still be disabled.

If you find that nobody is in the sysadmin server-level role, you'll need to stop the server and restart it in single-user mode so you can add at least one login to the sysadmin server-level role. In single-user mode, the Administrators group has sysadmin access, but you'll have a limited subset of commands to manipulate data in tables (single user is for fixing servers, not running applications). NB: Only one connection is allowed in this mode, so if you have a broker or service that's trying to connect you'll need to disable that or it can take the session you were planning to use with SSMS.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • How can I check if nobody is in sysadmin server role? And How do I start it in single-user mode? – Mehdi Haghgoo Nov 23 '14 at 05:11
  • 1
    @misaq Use `EXEC sp_helpsrvrolemember 'sysadmin'` to list logins in the sysadmin role. Try [this](http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/) to start in single-user mode. – Bacon Bits Nov 23 '14 at 15:05
  • I apparently have two SQL installations, displayed in SSMS as SQL Server and SQLEXPRESS. sa belonging to the former is enabled while sa belonging to the latter is disabled. authentication mode for the former is mixed mode, whereas it is Windows mode (cannot be changed) in the latter. thought it might help. – Mehdi Haghgoo Nov 24 '14 at 18:53
  • everyone says use -m option. where do I enter it, I'm totally confused. I tried to add to to 'advanced connection parameters' of SSMS and with sqlservr.exe in the form "sqlservr -m". None worked :( – Mehdi Haghgoo Nov 24 '14 at 19:10
  • I finally started sql server in single-user mode in administrator's command line with "net start mssql$sqlexpress /m" and "net start mssqlserver /m" and errors permission errors did not show up anymore. – Mehdi Haghgoo Nov 24 '14 at 19:31