Login Failed for Sql Server user

1

Logins using windows authentication works but we can't get any SQL server logins to work.

This is for SQL Server 2012 running on Windows Server 2012 RS.

When we try to login we get the following error.

Login failed for user 'test_user'. (.Net SqlClient Data Provider)
Server Name: theservername
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

I looked at the SQL server logs and found the following had been logged.

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

This doesn't make sense since it's not configured for Windows authentication only. To confirm this I right clicked the server (in SQL Server Management Studio) selected properties. Then selected the security tab. Then under 'Server Authentication' the option 'SQL Server and Windows Authentication mode' is selected.

I've also confirmed that only one SQL Server instance is in the server.

What could be causing this?

Thanks,
Scott

2

Your .NET code could be connecting to a different server. Your .net code could be running in a different (untrusted) domain. actually lots of possibilities

3

I'm actually using SQL Server Management Studio on the server to test the login. So I don't see how it could be a trust issue. In addition to using the server name in the server name field, I tried localhost and I tried the ip address of the server. In all cases I got the same login failure message.

Do you have any other suggestions.

Thanks,
Scott

4

Hard to believe SQL Server is bipolar on this. It's pretty basic. I'd suspect your .NET code though

5

Again, I'm testing the logins with SQL Server Management Studio not a program I wrote.

Do you know what else I can check?

Thanks,
Scott

6

Log on to the server with a different valid login, and run this query:

SELECT *
FROM sys.server_principals 
WHERE name = 'test_user';

You should get one row, and verify that it is as you expect. E.g., the type should be 'S', is_disabled should be 0 etc.

If all of that looks correct, run this (which is the same as what you verified using the user interface)

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

This should return 0.

If all of that checks out then we have to think of some other clever explanation for why test_user cannot login.

7

The first query returns

name: test_user
principal_id: 280
type: S
is_disabled: 0

The second query returns 1. How do I change this to 0?

Thanks,
Scott

8

Right click on the server name, Properties -> Security tab and click the "SQL Server and Windows Authentication Mode" and click ok. Then, you have to restart SQL Server for the change to take effect.

9

did you recently add sql authentication to that server. If so, did you restart the instance (required)

10

I was taking this over for a coworker who had been working on this for sometime now. The person must have changed this setting and not restarted the service. It seems to be working fine now.

Thanks,
Scott

11

I found this article suitable to fix login failed error 18456. See here: http://www.sqlserverlogexplorer.com/fix-microsoft-sql-server-login-failed-error-18456/