How to access a fresh install of SQL Server 2012?

Question

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.

Answer 1

Since you have access to the machine itself, you can follow the instructions here to (re)add yourself as a sysadmin: https://learn.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

Answer 2

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.

Answer 3

"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.