Cannot log in using SQL authentication ONLY from a remote server

sql-server sql-server-2008 login remote-connection
user990635 picture user990635 · Dec 9, 2011 · Viewed 12.3k times · Source

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?

Answer

Stéphane Bebrone picture Stéphane Bebrone · Dec 9, 2011

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More