1

I have installed MSSQL 2012 on Windows 2012 Server using the following settings:

enter image description here

enter image description here

But when I try to login (The username is my local user that I log-on to windows 2012 server with) I get (default instance name = MSSQLSERVER):

enter image description here

enter image description here

I have looked at:

How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?

http://vsstack.blogspot.in/2013/11/microsoft-sql-server-error-18456-login.html

but that guide assumes there is already a DB available that I can enable authentication for. This is not the case. What am I missing? Do I need to re-install MSSQL to select Mixed Mode?

EDIT: In this case it seems its only possible to change to Mixed Mode by messing around in the Registry, bummer: http://www.top-password.com/knowledge/sql-server-authentication-mode.html

AND BUYING this http://www.top-password.com/purchase.html

to update the sa password afterwards. Maybe they should consider writing that as a warning in the installation wizard!

It will take a couple of days to uninstall MSSQL 2012: https://www.youtube.com/watch?v=FvjjU8nPrRc

So maybe the best solution is to re-install windows?

Community
  • 1
  • 1
u123
  • 15,603
  • 58
  • 186
  • 303
  • 3
    did you check your db server supports `windows authetication` or `mixed authetication`? – DrCopyPaste Dec 04 '14 at 10:13
  • It doesn't assume, a SQL Server installation always contains a `master` database. If you can't login it's because you have no permissions. Either you have disabled Windows authentication or someone else installed the server and didn't add any other users. Moreover, permissions are granted first at the server level, then at the database level. – Panagiotis Kanavos Dec 04 '14 at 10:15
  • 1
    Try to run SQL Server Management Studio with elevated permissions (right-click > Run as Administrator). – Dan Dec 04 '14 at 10:20
  • I have added some more details to installation. It does not help run run Management Studio as Administrator – u123 Dec 04 '14 at 10:35
  • Your first image says that SQL server administrator is: "NETWORK SERVICE". Did you added a group, a user, or "current user"? If not this is the problem: you are not NETWORK SERVICE, so you are not authorized. – user_0 Dec 04 '14 at 11:13
  • No I did not I assumed that I could use a local service account for accessing the DB. Seems it not possible and that I have to re-install MSSQL or buy some software to update the password for the (hidden) sa user - see my edited post. – u123 Dec 04 '14 at 11:53
  • Possible duplicate of [Login failed for user (Microsoft SQL Server, Error:18456) SQL Server 2005](http://stackoverflow.com/questions/37760919/login-failed-for-user-microsoft-sql-server-error18456-sql-server-2005) – Vahid Farahmandian Jun 15 '16 at 09:12

1 Answers1

1

See here. You basically do following things.

  1. Open SQL Server single user mode
  2. Connect with sqlcmd
  3. Add Necessary user as administrator.

USE [master]

GO

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’

GO

Article I linked gives step by step how to do those points.

Community
  • 1
  • 1
Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69