- Remove From My Forums
-
Question
-
Hi all,
After the struggle of installing SQL I am now having problems conecting to the server.
I can connect to the server through ‘Windows Authentication’ (as you would expect looking at the error message) but I cannot connect to the sever using ‘SQL Server Authentication’.
I right clicked on the server name in ‘Object Explorer’ and made sure that ‘SQL Server and Windows Authentication mode’ was selected in properties. I restarted SQL and I’m still having the same error message appear:
’An attempt to login using SQL authentication failed. Server is configured for windows authentication only.’
Error:18456. Severity:14. State:58
I’ve had a look around and can’t find any other solutions.
Thank you for your time,
Ben
-
Changed type
Thursday, February 23, 2012 3:46 PM
-
Edited by
SQL2008SETUP
Thursday, February 23, 2012 3:49 PM
-
Changed type
Answers
-
Thanks all,
I found the answer from this link:
http://msdn.microsoft.com/en-us/library/ms188670.aspFor some reason when you change the authentication mode to allow Windows and SQL Server the login ‘sa’ is automatically disabled.
1.Connect to server via ‘Windows Authentication’.
2.Open a new Query and copy and paste the following (making sure you change the password):
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = ‘<enter password here>’ ;
GO3.Execute the query and reconnect to the server using the login sa and password you have just entered.
Regards,
Ben
-
Marked as answer by
SQL2008SETUP
Friday, February 24, 2012 9:11 AM
-
Marked as answer by
June 18, 2018 by Kenneth Fisher
In general, the errors SQL gives are highly useful. Of course every now and again you get one that’s just confounding. The other day I saw the following error in the log:
Login failed for user ”. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]
This one confused me for a couple of reasons. First, the user ”. Why an empty user? That’s not really helpful. And second Server is configured for Windows authentication only.
So what the heck is going on? After a bit of searching I found the answer here.
The pertinent quote is this:
Bottom line, the application is trying to connect using Windows authentication, but using a blank/null username and password.
And I was able to reproduce the error just like the OP by using an ODBC connection with no user specified.
It seems that a blank username confused SQL as much as the error confused me.
Aren’t confusing errors fun?
Category: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication
| Tags: connection problems, error log
After much research, I was able to connect my netbeans to my microsoft server.
If you registered your server as window authentication, you have to create another login which should be SQL Server Authentication, this will prompt you to input your username and password, create new, if you don’t have any already existing login.
Map your new login to any database, preferably Master or Adventure…
Go to server manager configuration tool in your MSSQL folder in the start up menu,
Manager configuration tools —> SQL Server services ——> select your server instance from the right pane, select MSSQLSERVER or MSSQLEXPRESS
whichever you know is your instance, preferably, work on the two of them, one after the other.
MSSQLSERVER express —->under security—-> Startup Parameters—-> specify a parameter —> type -m in the box —-> click on ADD.
CLick APPLY, then OK.
Do same to the MSSQLEXPRESS.
Restart the two of them
Go back to your SQL management studio,
Connect to server, select SQL SERVER AUTHENTICATION
Input your username and password, then ok.
Open your netbeans, right click your database, do your new connection stuff with the JDBC driver, input your login details of the SQL server…… connect.
It works perfectly well.
Remember to enable your TCP/IP
Port remains 1433
I have SQL Server 2014 on my Windows Server 2012 R2. I can connect locally to SQL Server from Management Studio using Windows Authentication.
I decided to switch to SQL Server Authentication (because I need access from other computer). I created new Login using Management Studio, but get error: Login failed when trying to connect.
Then I changed password for sa and tried login as sa, but again get the same error:
I restarted SQL Server, but it didn’t help. What I do wrong?
From error log: Login failed for user ‘sa’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Paul White♦
82.3k28 gold badges398 silver badges624 bronze badges
asked Jan 1, 2015 at 23:25
0
Connect to SQL Server using Windows authentication. Run sp_readerrorlog and see the complete message which would have come in errorlog when you tried connecting with SQL Authentication. This message is not of much help. You can update the question with message
From error log: Login failed for user ‘sa’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
What is output of this query.
select serverproperty('IsIntegratedSecurityOnly')
It seems like authentication mode is Windows only. If above query returns 1. It means authentication mode is windows go to server . Right click on SQL Server select property and then click on security. Change authentication it to mixed mode by selecting radio button SQL Server and windows authentication mode and restart SQL Server service again.
answered Jan 2, 2015 at 9:51
ShankyShanky
18.9k4 gold badges35 silver badges58 bronze badges
- Right-click on the instance
- Click on properties
- Click on Security
- Click «Sql Server and Windows Authentication mode»
- Now click on the Security Tab Under the Database and expand on logins
- right-click on the sa and go to properties
- provide the password
- Endable Enforce Password Policy
- Click on status and click on login enabled
- MOST IMPORTANTLY RESTART THE SERVER NOT ONLY MANAGEMENT STUDIO
answered Apr 8, 2021 at 18:01
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 onlyNot 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:
- Open SQL Server Management Studio
- STOP! Don’t put in your SQL credentials – instead choose the ‘Windows Authentication’ option.
- Enter your WINDOWS credentials for your account – NOT your SQL credentials.
- 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:
- Right click on the server that your trying to access (let them know which one).
- Select ‘Properties’ from the menu.
- Go to the ‘Security’ tab.
- Choose ‘SQL Server and Windows Authentication mode’ (this will allow either/or)
- Press ‘OK’ to save changes.
- 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!