I am trying to connect to SQL Server 2008 from a <strong>remote server</strong> using 'sa' username and its password (I can log in normally with this username and password from my own computer - so <strong>"SQL Server and Windows authentication mode" is chosen</strong>). 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?
From the SQL Server management studio, right click on your server (after connect) in the Object Explorer window and choose <strong>Properties</strong>.
On <strong>Security</strong> item, make sure that SQL Server And Windows Authentication mode is selected.
<img src="https://i.stack.imgur.com/uo8NN.png" alt="enter image description here">
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.
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.
<strong>Short how-to:</strong> In SQL Server Management Studio, open Security -> Logins, find "sa" -> Properties -> Change password or uncheck this box: <img src="https://i.stack.imgur.com/CR6cW.png" alt="Login properties - sa - enforce password policy">
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!