0

I face the following isuse on a Microsoft SQL Server 2008 R2 (SP2):

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

  • My SQL server has mixed mode enabled (i verified and switch it on and off several times)
  • I am able to loging from a peer server which is a replica with the same windows accounts
  • I am unable to loging from a Windows workstation
  • The DSN configuration in Windows is succesful though

If mixed mode is enabled why would the server tell me it is not?

What could cause the server to believe it is Win authentication only although the setup is mixed mode?

limaechooscar
  • 71
  • 7
  • possible duplicate of [An attempt to login using SQL authentication failed](http://stackoverflow.com/questions/8441158/an-attempt-to-login-using-sql-authentication-failed) – bummi Oct 13 '14 at 08:36
  • Hi Bummi,I checked this is not a duplicate as on my server mixed-mode is enabled. – limaechooscar Oct 13 '14 at 08:49
  • Restart sql server service. Every time you change sql auth you must restart the service – Max Oct 13 '14 at 11:15
  • This was done already. – limaechooscar Oct 13 '14 at 12:18
  • Confirm that you are, in fact, connecting to the server you think you are connecting. It may sound stupid, but it's easier to get wrong than you think -- verify the hostname resolves to the correct IP, that the instance name matches as well and that you're actually _using_ the string involved. – Jeroen Mostert Oct 13 '14 at 18:03
  • I used to have an entry in the hostfile but now I have amended my jdbc config file with the IP address to avoid those issue. Shout the breeze cause I lack of ideas :( – limaechooscar Oct 14 '14 at 06:38
  • Well, you might have mentioned that you're using JDBC. :-) Is the server using instances? If you do nothing special it will connect to port 1433 and whatever instance is listening on that (typically the default instance). What's your exact JDBC connection string (with host, user and password replaced, of course?) – Jeroen Mostert Oct 14 '14 at 08:45
  • Jeroen, the string looks like: jdbcDriver = com.microsoft.sqlserver.jdbc.SQLServerDriver jdbcUrl = jdbc:sqlserver://HOSTNAME:1433;databaseName=DATABASENAME;responseBuffering=adaptive dbType = MSSQL I checked for space, I use an ip and not a hostname (I tried with both though) – limaechooscar Oct 14 '14 at 09:45
  • This string contains no authentication info, so presumably you do that in code. Can you edit your question to include this string and the code so we can get this comment train off the tracks? – Jeroen Mostert Oct 14 '14 at 11:50
  • I dont have access to the code. Also from one of the WKS I can connect without problem to another environment using the same application... Wouldnt that point me to a server issue? – limaechooscar Oct 14 '14 at 13:12
  • Actually, no, it points to a client issue, since the server is the same. At this point, I might go so far as to break out a network trace to see what's really going on -- Wireshark's support for TDS is very out of date and incomplete, but for something as basic like this it should be enough. – Jeroen Mostert Oct 15 '14 at 10:58
  • Agree but the server have identical configs and same window suser ( that is why I wonder what the cause could be). - Wireshark installed on desktop but no interface detected - Wireshark installe dons erver tests will be done - Processmonitor from Systinternals also installed – limaechooscar Oct 16 '14 at 05:27
  • You need to run Wireshark as root or configure a limited user for tracing. Wireshark should give you an error dialog that explains the issue and how to solve it on startup. – Jeroen Mostert Oct 16 '14 at 13:31
  • Finallz issue is fixed. Wireshark did not help but tracing back teh changes on the server it appears that the main administrator user was renamed. A new sys admin user was created and I add to add this user in both Windows SQL Security groups. – limaechooscar Oct 22 '14 at 13:43

1 Answers1

0

Wireshark did not help.

I traced back the changes on the server it appears that the main administrator user was renamed. A new sys admin user was created and I had to add this user in both Windows SQL Security groups.

limaechooscar
  • 71
  • 7