0

I've performed a fresh install of SQL Server 2012 on my local development PC. I've done this before and it has worked, however this time I'm getting the following error message when I attempt to sign into SQL Server.

"Login failed for user xxxxxxxxxx (Microsoft SQL Server, Error: 18456)"

How can I access my local instance of SQL Server? I'm rather ignorant about SQL Server security.

  • 1
    During installation, in the step where it asked you which users should be administrators, what did you put in? – Brent Ozar Nov 13 '17 at 18:29
  • That's a great question. I put myself, however when I attempt to login to the server I don't have access. – codingguy3000 Nov 13 '17 at 18:29
  • Try right-clicking and running SSMS as Administrator? – CaM Nov 13 '17 at 18:32
  • I just tried that and it didn't work. – codingguy3000 Nov 13 '17 at 18:39
  • Can you confirm the Sql Server service is running on your local pc? What type of authentication did you choose when you did the install? Both Windows and Sql? Can you post a screenshot the SSMS prompt you are using to connect to the instance? What edition of Sql Server are you using? – rvsc48 Nov 13 '17 at 18:52
2

Since you have access to the machine itself, you can follow the instructions here to (re)add yourself as a sysadmin: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

The short version of what that says is:

  1. Stop SQL Server
  2. Restart with the minimal configuration option -m
  3. Log into the server with sqlcmd -S {your machine name or localhost}
  4. Run the following commands:
    1. CREATE LOGIN [Domain\LoginName] FROM WINDOWS;
    2. EXEC sp_addsrvrolemember '[Domain\LoginName]', 'sysadmin';
  5. Stop SQL Server
  6. Restart it with the normal configuration
0

Several things you can do, check each in order.

  1. Confirm that the SQL Server service is running.
  2. Check windows Application event logs for errors from SQL Server.
  3. If you want to use mixed mode or Windows authentication, confirm that SQL Server can reach the domain (run SQL Server Configuration Manager and confirm that the services are connecting using domain user accounts and that those passwords are valid.)
  4. Take control of the SQL Server instance by running SSMS as SYSTEM. (See link for instructions.) Once you get in, you'll need to create a SQL Server login with sysadmin rights or add your user with sysadmin rights.
0

"Login failed for user xxxxxxxxxx (Microsoft SQL Server, Error: 18456)"

To find out the reason, you should open SQL Server error log (it's a simple text file, you can open it using any text editor).

Of course there is no sense to check if service is running, it's SERVER (running program) that was not able to authenticate you, and it gave you 18456 error, but without any explanation. It's done for security reasons, user who was failed to login should not know if server is configured to Windows Authentication only, or if this login is disabled, etc. But the reason was written to the error log and Administrator can check it.

It was you to install the Server, so you have the control over this pc, just open error log and find out the reason.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.