Native Client Problem, ERROR [28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ''.

Question

Hello,

I am new to MS SQL Server. I have a SQL Server Express 2008 R2. The Server is configured in mixed mode, SQL Server Authentication is activated.

In the SQL Server Configuration Manager I enabled all protocolls for the Server and the Native Client (except VIA)

I did a Login with "testuser" (public) and in my "testdatabase" I did a "Create user", added also the "testuser" for SQL Server Authentication added the password and set the role "db_owner".

In the ODBC Native Client 10 I setup a new connection, choose the "testuser" for SQL Server Authentication, add the password, and set the default database to "testdatabase". The connection test was successfully.

The problem is: When I start my windows application (it is a c# app) there is always an error message "Error Opening: ERROR [28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ''."

When I switch the Native Client to Windows Authentication it work. It works also with the old ODBC driver "SQL Server" with SQL Server Authentication and "testuser" but I have to add "namedPipe" in the "ClientConfiguration" tab.

My question is, how can our application work with the native client and sql server authentication?

Is there anything else what I can check (firewall, protocolls, Logins, permissions...)

Thanks a lot for help

regads

Hans

Answer 1

When your are facing login failure with native client and sql server authentication at same time your SLQ server error log capture the details of login failure.

Share the complete login failure error messgae from sql server error logs for looking into details of login failure.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

Hello

thank you for help.

No I have no failure with native client and sql server authentication.

The problem is that my application which uses the native client has the error:

Failed to open: ERROR [28000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Error for Login with user 'DEVServer\ldapuser'.

But why it shows "DEVServer\ldapuser"? in the native client I have added the user "testuser" which is also a login and user on the sql server

Is there a problem with names pipes or what else can I check.

regards

Hans

Answer 3

Hi Hawk

It indicated that you have changed user from DEVServer\ldapuser to testuser at incorrect location. Please check out the things again or search the code for DEVServer\ldapuser  to check where this user is mention for SQL server connectivity.

Please look in SQL server error log & share the same to check the reason of login failure of DEVServer\ldapuser user.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 4

Hi

thanks a lot for help.

It really strange (for me)

I did a successfully connection with the native client 10 , with SQL Server authentication and user name "testuser" and default database "testdb" . This user is on the "DevServer" SQL Express server, has a login with default database "testdb". There is also a User "create user " with this usernam "testuser"

The strange thing is now: If I try to connect from my c# application which uses this Native Client connection I get an error message and in the SQL Server log of the "DevServer" is this: (sorry the original message is in german, i will try to translate)

08/08/2012 16:20:36,,Unknown,Login failed for user ''. Problem: Error with Login: The Server is only configured for Windows Authentication. [CLIENT: <named pipe>]
08/08/2012 16:20:36,,Unbekannt,Fehler: 18456<c/> Schweregrad: 14<c/> Status: 58.

But the SQL Express server is definitiv configured in mixed mode and with SQL Server Authentication. (the native client connection test will work successfully)

So I am still confused

regards

Hawk

Answer 5

The Server is only configured for Windows Authentication.

In error message, SQL server is mention to configured for windows authentication only. For SQL authentication, SQL server must be enabled for mixed uathentication.

Login to SSMS > Server properties > Security Tab > Select mixed authentication > Click OK > Restart SQL services to bring it inot effect.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 6

No no no

as I told, I have checked this several time. The server was installed with mixed mode.

But the SQL Express server is definitiv configured in mixed mode and with SQL Server Authentication.

I did this:

Login to SSMS > Server properties > Security Tab > Select mixed authentication > Click OK > Restart SQL services to bring it inot effect.

This is why I cannot understand the error log and this message

Answer 7

Run below command & share top 10 rows from output.

Exec sp_readerrorlog

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 8

You should be getting errors in your log, if not turn on Auditing for failed logins.  In the log you should see error 18456 and a State code.  The State code is what you want.  To interpret see:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

RLF

PS - Make sure that you are attaching to the instance that you expect to use.  SQL Server Express usually installs as a named instance and it can get confusing if you have more than one instance installed on your machine.

Answer 9

Hello,

thanks for help. Tomorrow I will show us the error log file from the sql server. (sorry it is now end of office day in germany :-)

But the error code was
Error: 18456 Severty: 14  State: 58.

Perhaps we have a problem with our c# app which uses the NAtive Client.But it works correct on a local installed sql server instance

kind regards

Hans

Answer 10

Error: 18456, Severity: 14, State: 58 Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

Refer Link : http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx

Run below command & share top 10 rows from output. To check in which mode your SQL server is runing windows or mixed.

Exec sp_readerrorlog

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 11

Try using window authentication once...If it connect successfully via your application it will prove that Server configuration is not mixed authentication.

cheers!!!

Vatsa

www.objectiveprogramming.com

Answer 12

Hello,

sorry for the delay. I have checked this on another Test PC with SQL Express 2008 R2. This is installed in English and the log will be better to read for you.

Here is the log: (10 rows)

2012-08-10 08:32:54.910    Server    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)    Apr  2 2010 15:53:02    Copyright (c) Microsoft Corporation   Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600: )  
2012-08-10 08:32:54.920    Server    (c) Microsoft Corporation.
2012-08-10 08:32:54.920    Server    All rights reserved.
2012-08-10 08:32:54.920    Server    Server process ID is 4632.
2012-08-10 08:32:54.920    Server    System Manufacturer: 'Dell Inc.', System Model: 'Latitude D820'.
2012-08-10 08:32:54.920    Server    Authentication mode is MIXED.
2012-08-10 08:32:54.920    Server    Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2012-08-10 08:32:54.920    Server    This instance of SQL Server last reported using a process ID of 1988 at 10.08.2012 08:32:43 (local) 10.08.2012 06:32:43 (UTC). This is an informational message only; no user action is required.
2012-08-10 08:32:54.920    Server    Registry startup parameters:     -d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf    -e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG    -l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
2012-08-10 08:32:54.980    Server    SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-08-10 08:32:54.980    Server    Detected 2 CPUs. This is an informational message; no user action is required.

The problem is:

If the native client is set to sql authentication (user testuser) default db testdb I can do the odbc connection test successfully.

But If I try to connect with my c# windows application which uses this native client I receive this error log on the sql server: AND THIS ON THE SAME PC (not remote)

08/10/2012 08:45:07,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
08/10/2012 08:45:07,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
08/10/2012 08:45:07,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
08/10/2012 08:45:07,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
08/10/2012 08:45:06,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
08/10/2012 08:45:06,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.

My problem is that I am not the developer of the C# application and I do not know how it works togehter with the native client. I can nowhere find an ini file or  something else where the SQL user name "testuser" and password must be filled . Or is normally used the user name from native client?

Is there a problem with named pipes? But I have several time rechecked, named pipes are enabled.

The other thing is. Same c# application and same database with also sql express 2008 r2 on an other win7 pc works fine and I cannot find any differences with Logins or Users.

thanks for any help

kind regards

Hans

Answer 13

08/10/2012 08:45:07,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

Hello Hans,

There you can read the reason: You SQL Server runs with authentification mode "Windows only" and you app tries to logon with a SQL Account; this don't work.

You have to change the auth mode of the SQL Server to "Mixed Mode" = "Windows and SQL Authentification" to get it work; see Change Server Authentication Mode and Choose an Authentication Mode

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 14

Hello Olaf,

thanks for help. But no, the server runs definitivly in mixed mode. I have rechecked this several time. If I could post a screenshot here I would do it.

But you can see also that the server runs in mixed mode from my Exec sp_readerrorlog log,

see here:

012-08-10 08:32:54.910    Server    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)    Apr  2 2010 15:53:02    Copyright (c) Microsoft Corporation   Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600: )  
2012-08-10 08:32:54.920    Server    (c) Microsoft Corporation.
2012-08-10 08:32:54.920    Server    All rights reserved.
2012-08-10 08:32:54.920    Server    Server process ID is 4632.
2012-08-10 08:32:54.920    Server    System Manufacturer: 'Dell Inc.', System Model: 'Latitude D820'.
2012-08-10 08:32:54.920    Server    Authentication mode is MIXED.

This is the curious thing! Connection test with the native client will work, but if my application uses this native client I have the error

Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

I have tried and checked sooooo much, Firewall, Logins, users, named pipes, all looks ok.
so my last idea is that the c# application does not use the native client sql user and always tries to connect with Windows Autherntication?

very strange that

thanks again for help

regards

Hans

Answer 15

...and the other strange behaviour is:
If I use the "old" SQL Server ODBC driver instead of the native client and use the sql authentication my c# application can successfully connect to the sql server!

So, what is the different using Native Client or SQL Server driver? (in the SQL server odbc there is a "Clientconfiguration" window where I have added the NamedPipe?

hmm.

regards

Hans

Answer 16

Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

Hello Hans,

OK, I did some test and I can reproduce the same behavior  ... whenI add a bug to my application, exactly to my connection string.

This is my test C# code:

using System;
using System.Data.SqlClient;

namespace SqlConTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var connString = @"Data Source=.\SQLEXPRESS;TRUSTED_CONNECTION=FALSE;";

            using (var conn = new SqlConnection(connString))
            {
                try
                {
                conn.Open();
                conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
            Console.ReadKey();
        }
    }
}

As you can see, I use Trusted_Connection = false, this means I am going to use SQL Server authentification, but I didn't provide a SQL account information.

And this is what I get in the error log (shortend)

2012-08-10 10:52:07.65 Server      (c) 2005 Microsoft Corporation.
2012-08-10 10:52:07.65 Server      All rights reserved.

2012-08-10 10:52:07.65 Server      Authentication mode is MIXED.

2012-08-10 10:52:10.87             Login succeeded for user 'NT-AUTORITÄT\LOKALER DIENST'. Connection made using Windows authentication.

2012-08-10 10:52:20.59             Login failed for user ''. Ursache: Fehler bei der Anmeldung mit der SQL-Authentifizierung
                                   . Der Server ist nur für die Windows-Authentifizierung konfiguriert. [CLIENT: <local machine>]

2012-08-10 10:53:10.05             Login failed for user 'HelloWorld'. Ursache: Es konnte keine Anmeldung gefunden werden,

The same empty user name in the error message. If I use a SQL Account, but a wrong one, then the user name ist printed.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 17

Hello Olaf,

thank you so much. I have forwared your code to my collegue for checking this in his c# code.

In the meantime I have found a solution but I do not know why it works :-) Perhaps it is interesting for you and the other people here.

As I told, I can made a connection with the old SQL Server Odbc driver and SQL Server Authentication, after I had set the Clientconfiguration for named pipe.

If I then switch back to the native client and try to connect with my application IT WORKS also !?

I have checked that from the old SQL Server ODBC driver there is an registry entry in;

HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo

If this entry is there, I can connect successfully without errors. If I delete it I will receive the error

08/10/2012 08:45:07,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
08/10/2012 08:45:07,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.

It works now, but I thought the Native Client 10 does not uses these entries of NamedPipes?

kind regards

Hans

Answer 18

HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo

Hans, is it possible that you have a big misconfiguration in your system and you may also have an other SQL Server running? 

In this RegKey the SQL Server aliase are stored, you can create them with the system tool "CliConfg.exe". May there have been an aliase configure that pointed to a completly different server?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 19

Hi Olaf,

No there is only one SQL Server Express 2008 running on this machine. Before testing I executed the "CliConfg.exe" and added the named port

The entry n HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo is:

Name: WIN7PROF\SQLEXPRESS

KEY: DBNMPNTW,WIN7PROF\SQLEXPRESS

And WIN7PROF is the PC name which SQL Server 2008 Express is running.

Puuhh, In the morning I could reproduce the error when I deleted this registry key. But know in the moment my app also works if the registry entry is deleted.

What happens here?

I think I give up..
I assume the problem is like you describe in your post with a wrong connection string . But to check this I first have to contact my collegues.

Thanks again for help

regards

Hans

Answer 20

Hello Hans,

That's really only a SQL Server aliase where you can define the alias name, the protocol to use and the real server name. You e.g. could create an alias "MySqlServer" pointing to "WIN7PROF\SQLEXPRESS".

But this alias don't effect the authentification mode or the used account.

BTW, are you working with ODBC connections for testing or only with your C# app?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 21

Hi Olaf,

thanks so much for spending time to my strange behavior.

<<BTW, are you working with ODBC connections for testing or only with your C# app?>>

The Test with the Native Client 10 is always successful. For Windows Authentication and SQL Server Authentication

My C# app lets call "myTestApp" uses the native client for connection. The only thing which I can set, is the DSN Name

I do this in the Native Client and also in an XML file which myTestApp use.

<Section Name="Database">
    <Setting Name="DataSource" Type="String">
      <Value>DSN=myTestApp</Value>

But in the moment I can not tell you how the c# app internal does the connection or how it uses the ODBC driver.

The problem with the "Logon,Unknown,Login failed for user ''." is always when I start the "myTestApp".

kind regards

Hans

Answer 22

My C# app lets call "myTestApp" uses the native client for connection. The only thing which I can set, is the DSN Name

Hello Hans,

Then it seems the app uses ODBC (in .Net the OdbcClient).

A DSN stores primary the data source (=server name), the database name and the credential (without pwd) which was used to setup and test the DSN. But of course the app has to pass through the credential to use in the connection string.

You can look up the settings in Registry Key: HKLM\SOFTWARE\ODBC\ODBC.INI\myTestApp\

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 23

Hello Olaf,

yes the regitry entry for the odbc is ok

You can look up the settings in Registry Key: HKLM\SOFTWARE\ODBC\ODBC.INI\myTestApp\

but I cannot see an sql user name or password there.

regards

Hans

I have to correct me:

There is an entry "Lastuser" with my user "Testuser"

But nowhere a password for this user

Answer 24

but I cannot see an sql user name or password there.

Because they are not stored in DSN, that's why I wrote "the app has to pass through the credential to use in the connection string" and if it don't pass it, then it ends in an empty user name as in the error message / my test C# code.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 25

Hi Olaf,

just a last short question because it is not totally cleary for me:

<<As you can see, I use Trusted_Connection = false, this means I am going to use SQL Server authentification, but I didn't provide a SQL account information.>>

Does this mean: If I setup my ODBC connection string with "Truested_Connection=yes" we have always a Windows Authentication?

Or does this work also for SQL Server Authentication, the SQL user is from the Native Client settings and the password is from the wndows user properties?

regards

Hans

Answer 26

Does this mean: If I setup my ODBC connection string with "Truested_Connection=yes" we have always a Windows Authentication?

Or does this work also for SQL Server Authentication, the SQL user is from the Native Client settings and the password is from the wndows user properties?

Hello Hans,

If you use "Trusted_Connection = True" (or "Integrated Security=SSPI", it's equal, see http://www.connectionstrings.com/sql-server-2005) then you always connect with your Windows Account, even if you additional pass SQL Account user id + password in the connection string.

If you use "Trusted_Connection = False", then you have to pass explicit SQL user name + password to logon.

---

Olaf Helper
Blog Xing