4

I am trying to connect to SQL Server 2008 from a remote server using 'sa' username and its password (I can log in normally with this username and password from my own computer - so "SQL Server and Windows authentication mode" is chosen).
In the SQL Server log file on my computer I see this error:

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

I thought it might be a remote connection problem, so I checked that the remote connection in the properties is enabled, in the configuration manager I enabled TCP/IP and Shared Pipes and restarted the service afterwards and I created a firewall rule for port 1433. I also tried to turn off the firewall in case that it is being blocked somehow, but I got the same error.

How this error appears only when accessing SQL Server from a remote server?
How can I fix it?

user990635
  • 3,979
  • 13
  • 45
  • 66
  • Not sure if it could help, but try starting the SQL Server Browser service. – Valentin V Dec 09 '11 at 07:13
  • This may sound a silly question, but are you sure you're attempting to connect to the same machine from this remote server? It sounds like you're somehow attempting to connect to a different server than the one you *think* you are. – Damien_The_Unbeliever Dec 09 '11 at 08:01
  • I am sure, because it logs the errors of the log in attempts in the SQL Server folder on my machine – user990635 Dec 09 '11 at 08:06
  • I am getting the exact same error via ODBC on the same computer via a local asp page through IIS 7. The local sa login works through SQL Server Management Studio, so obviously the server is configured for SQL authentication. Any ideas? – Jeremy Larter Nov 08 '12 at 22:45

4 Answers4

8

From the SQL Server management studio, right click on your server (after connect) in the Object Explorer window and choose Properties.

On Security item, make sure that SQL Server And Windows Authentication mode is selected.

enter image description here

Stéphane Bebrone
  • 2,713
  • 17
  • 18
  • It is selected, and it works well on my computer, the problem is only when trying to log in from a remote server – user990635 Dec 09 '11 at 07:17
  • Are you sure that you got the exact error message? Maybe it's a error due to your previous tries (check the timestamp). From where do you try to connect? An ASP.NET application? Can you catch the exception from there? – Stéphane Bebrone Dec 09 '11 at 07:31
  • I am sure, exactly the same error (I checked the time) I am trying to connect from a web application using this connection string: . The error from the log file of my application (on the server) is: "Login failed for user 'sa'", I can also copy the Stack Trace if it may help – user990635 Dec 09 '11 at 07:41
  • We can discuss it on the [SQL Server chat room](http://chat.stackoverflow.com/rooms/3825/sql-server) if you want. – Stéphane Bebrone Dec 09 '11 at 07:55
  • 1
    Don't forget to restart your sql server instance after this change. – lnu Dec 09 '11 at 08:05
  • Restart anyway to ensure it running: maybe view http://stackoverflow.com/questions/8441158/an-attempt-to-login-using-sql-authentication-failed – benni_mac_b Dec 09 '11 at 08:29
0

I had the exact same problem today. I was not able to connect to SQL server remotely using username/password, but it worked with windows authentication, and logging in locally also worked.

The reason it didn't work here was that I was using a password that was too short on the sa user. Apparently it does enforce password policy if logging on locally, but remote connections are blocked.

You can either change the password to a longer/more complex one, or disable the password policy enforcement for the sa user.

Short how-to: In SQL Server Management Studio, open Security -> Logins, find "sa" -> Properties -> Change password or uncheck this box: Login properties - sa - enforce password policy

Henrik Nordvik
  • 330
  • 3
  • 10
0

In my situation, I have SQL Server 2008 and SQL Server 2012 installed. So, in server name field, I need a concrete name (for example: 10.141.133.125\SQLServer2012). That's it!

Hope this useful for you!

qWolf
  • 43
  • 8
0

The most obvious solution for me seems to allow SQL Server authentication on the server if it's really necessary to use sa for access (what it should not normally).

To answer the "why" we need more information. Which program do you use to access the server. Is the connection string really identical for both accesses?

Just guessing: From your local machine you use integrated security=true somehow (which causes user and password to be ignored), and your local windows user is allowed to access the server. From remote you use integrated security=false so that you can't logon using user/password, as the server is configured to only accept Windows authentication.

okrumnow
  • 2,346
  • 23
  • 39