“Server is configured for Windows authentication only.” Error when SQL is in mixed mode.

Question

We have an install of SQL server 2008 R2 Enterprise (Version 10.50.1617) running on Windows Server 2008 R2 Enterprise (w/ SP1). SQL is configured with two named instances. 

We are getting the error “Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxx.xxx.xxx.xxx]”

Every time someone runs a report from SQL or logs into the ERP system (using SQL login) this error appears to be generated. Our reports were built in Crystal Reports and some of them run as scheduled jobs as a windows user which is a sysadmin.  

We have verified that the SQL Server Security properties are set to “SQL Server and Windows Authentication mode”. We have also verified that the Registry values for “LoginMode” are set to “2” and have rebooted the server. This has had no effect on the error messages.

Anyone seen something like this?

Answer 1

Hello,

There is something i have not understood , you have written "SQL is configured with two named instances" ( but with my poor english, it is usual )

Are you sure that you are using the correct instance or are you using a linked server ?

2  links possiblely useful for this kind of problem :

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

http://blogs.msdn.com/search/searchresults.aspx?q=Login+failed+for+user+%27%27

Don't hesitate to post again for more help or explanations.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

We have two instances in SQL Enterprise, GP and INFUZION.  

Answer 3

One strange error described here http://msdn.microsoft.com/en-us/library/cc645917.aspx

An additional unusual possible cause

The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.

• When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection does not explicitly specify that the connection should use a trusted connection.

• When the server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection does not explicitly specify that the connection should use a trusted connection.

To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

Hello Rick,

Thanks for the link ( i have lost it  when my system disk crashed 4 months ago and was unable to retrieve it )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

Thank you.  It looks like the ODBC was set to SQL credentials and the Reports that are using it pass windows authentication.  When we changed the ODBC the errors went away.