69400

Cannot log in using SQL authentication ONLY from a remote server

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?

Answer1:

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">

Answer2:

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.

Answer3:

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">

Answer4:

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!

Recommend

  • Launching SQL Server 2008 Express from VS2010 in SQL Server Management Studio
  • How do you communicate to a Modbus device within an iPhone application?
  • Mail::IMAPClient->new hangs in Windows
  • Change version php on OVH
  • How can I set the buffer size for the underneath Socket UDP?
  • Configure WAF Application Gateway in front of App Services [closed]
  • Installing SSL on AWS EC2 Bitnami Mean Stack
  • Zeromq with python hangs if connecting to invalid socket
  • MAVEN : Run Multiple Maven Project using Maven Test
  • Loading fixtures in sails tests
  • Unzip archive in .Net CORE 1.0
  • F#: In which memory area is the continuation stored: stack or heap?
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • VSCode change debug shell to bash on windows
  • C#: Import/Export Settings into/from a File
  • How to retrieve information from antrun back to maven?
  • How can I replace the server in Web Component Tester
  • Is there some graphical way to create my own configuration file on SonarLint?
  • Apache RewriteRule redirection with url encoded
  • Spring Cloud Microservice Architecture Confusion
  • Access Android Market through SSH tunnel
  • Not able to aggregate on nested fields in elasticsearch
  • Specifying Castle WCF Integration Facility Endpoint Behavior per Endpoint
  • Saving Changes After In-App Purchase Has Been Purchased
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Cannot resolve symbol 'MyApi'
  • How can I send an e-mail from a vbs script
  • Is there a amazon webstore API for customers?
  • Incrementing object id automatically JS constructor (static method and variable)
  • Adding custom controls to a full screen movie
  • Circular dependency while pushing http interceptor
  • Is there a mandatory requirement to switch app.yaml?
  • Free memory of cv::Mat loaded using FileStorage API
  • CSS Applying specific rule for a specific monitor resolution with only CSS is posible?
  • Angular 2 constructor injection vs direct access
  • -fvisibility=hidden not passed by compiler for Debug builds
  • How can I remove ASP.NET Designer.cs files?
  • unknown Exception android
  • UserPrincipal.Current returns apppool on IIS