MSSQLSERVER_18456 – Login failed for user ”. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

SQL errors can be frustrating – and none more so than not beginning able to log in to the blasted thing! Let’s take you through some steps on understanding what the problem is (beyond the basics of not being able to login!) and ways that you can fix it.

So, what does the error even mean?

So the error message itself is something like:

Login failed for user '<UserName>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only

Not terribly helpful, but not the worst error message either. Essentially this error happens when you’re trying to login to your Microsoft based SQL server using SQL authentication. The only catch? The server doesn’t allow it.

What the server does allow is for you to use your Windows authentication to also login to the SQL server. Simple enough, right?

The Solution

There’s two ways to approach this – either we reconfigure the server or use Windows authentication. And reconfiguring the server might not be the best idea due to potential security implications – presumably the reason that Windows authentication was selected by the server administrators in the first place – for example, if 2FA is enabled on your Windows login.

But we’ll go through both to let you have a free choice.

Using Windows Authentication

“Give the SQL Server what it wants!” – or so someone could bellow out loud. Well, it is the simplest of the solutions…

If you have a Windows user account – which presumably you have otherwise how have you even made it anywhere near SQL server – and, crucially, you have permissions to be there (assigned to your account) you can follow this process:

1. Open SQL Server Management Studio
2. STOP! Don’t put in your SQL credentials – instead choose the ‘Windows Authentication’ option.
3. Enter your WINDOWS credentials for your account – NOT your SQL credentials.
4. Click the login button.

And voila! You should be logged in?

Not worked / desperate? Check below!

Server (Re)-Configuration

So, desperate times call for desperate measures – let’s go!

The one way for getting around needing to use Windows authentication to log on is, of course, to not require it to logon. Leaving it entirely open is an option – but an unwise one.

Contact your sysadmin to login with their super-duper administrative privs – and yes, they will have to use Windows authentication. The from with SQL Server Management Studio they will be able to:

1. Right click on the server that your trying to access (let them know which one).
2. Select ‘Properties’ from the menu.
3. Go to the ‘Security’ tab.
4. Choose ‘SQL Server and Windows Authentication mode’ (this will allow either/or)
5. Press ‘OK’ to save changes.
6. Let you know you can retry with your SQL username and password.

Depending on how much they know and how much they like their job – they may or may not do this for you! 🙂

Happy SQLing!