Login failed for user 'domain\Administrator'. (Microsoft SQL Server, Error: 18456)

Question

On my newly installed SQL Server 2014, I get the following error when trying to connect to the SQL Server:

2015-05-13 12:09:38.40 Logon   Error: 18456, Severity: 14, State: 5.
2015-05-13 12:09:38.40 Logon   Login failed for user 'INTRA\Administrator'.
Reason: could not find a login matching the name provided. [CLIENT: <local machine>]

I reinstalled the sql server three times.

The user for which the login fails is the AD user with which I am logged into the server itself. When logging in via SSMS, I don't have to give any credentials (it is auto-filled), so this can't be a typo error.

How can I resolve this issue?

PS: I also had problems with the SSL cert which are already resolved, so the underlying problem may be bigger.

score 8 · Answer 1 · answered May 13 '15 at 11:59

Try running SSMS on the server with 'Run As Admin' to check if you added builtin\administrators during install. If you cant find any user with enough rights then you have to add a login to the server and give it sysadmin rights.

Start the server in single user mode by starting cmd.exe on the server with administrative rights and running net stop mssqlserver and then net start mssqlserver /m

then in the same window run SQLCMD and type the following.

create login [domain\administrator] from windows;
exec sp_addsrvrolemember 'domain\administrator', 'sysadmin';
go
exit

and the run net stop mssqlserver and net start mssqlserver

If you are running a named instance the servicename is different - Check services.msc to see the name and you have to run SQLCMD whith -S server\instance -E to connect

score 6 · Answer 2 · answered May 13 '15 at 12:13

I reinstalled the sql server three times.

During the installation you are asked what users to be added as instance administrators. You should add yourself. You probably added the built-in Administrators group and now you're running a LUA process and had lost the admin token and cannot access the instance. If yes, then is simply a mater of running the client process elevated ('Run as administrator').

If you have omitted to add the built-in Administrators group as SQL instance admins then you must follow the steps described at Connect to SQL Server When System Administrators Are Locked Out.

score 1 · Answer 3 · edited May 13 '20 at 17:22

For state 5:

It occurs when a SQL server login logs in with the name that doesn’t exist in sql server. This error mostly comes in when users specify wrong user name or misspell the login name. So first thing you should check:

If the login " INTRA\Administrator" actually exist out there in logins?

You can do this by executing below T-SQL:

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;
GO

or VIA GUI from here

Also, as listed in this good read Troubleshooting Error 18456

This error also occurs if you specify the correct username and password for a contained database user, but the wrong (or no) database.

Note that if you are trying to connect to a contained database using the connection dialog in SSMS, and you try to for the database instead of typing the name explicitly, you will first receive a prompt

"Browsing the available databases on the server requires connecting to the server. This may take a few moments. Would you like to continue?"

If the SQL auth credentials do not also match a login at the server level, you will then receive an error message, because your contained user does not have access to master.sys.databases. The error message in the UI is,

"Failed to connect to server ,(Microsoft.SqlServer.ConnectionInfo) Login failed for user ''. (Microsoft SQL Server, Error: 18456)

The takeaway here: always specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature.

Login failed for user 'domain\Administrator'. (Microsoft SQL Server, Error: 18456)

3 Answers3

Linked