Search code examples
sqlsql-serversql-server-2014sql-server-2014-express

Microsoft SQL Server 2014 too many services

I have a problem. I can't start SQL Server 2014.

It's ok when I connect to a server as a Windows authentication, but when I try to connect as SQL Server authentication with username sa and my password, O get a SQL Server error 18456.

Why? When installing Management Studio with youtube video, there were few differences:

When installing new instance SQLExpress, there was error because the instance with this name already existed so I changed the name of instance to MSSQLServer...

Also in SQL Server Configuration Manager in SQL Server Services,there are 5 services and I think there shouldn't be so many:

enter image description here

Solution

  • Each SQL Server instance has their own services for different usages. Here you've 2 instances {"SQLEXPRESS", "MSSQLSERVER"}, each has its own SQL Agent. This is normal.

    ... but when I try to connect as SQL Server authentication with username sa and my password, O get a SQL Server error 18456. Why?

    If you want to connect with system administrator account sa, you need to enable the mixed running mode of the target SQL Server instance, e.g. SQLEXPRESS. Because sa is not windows authentication. You've 2 options:

    • Enable this mixed running mode during the installation. Choose Windows Authentication and SQL Server Authentication.
    • Connect to your SQL Server instance with an adminstrator account using Windows Authentication and then change the server running mode.

    If the server is already running on mixed mode, but you're still getting the same error. Then you're probably using a wrong password for sa. Did you use your windows password by mistake?